Display parameters on a Microsoft Access report

Recently I wanted to create a report in Microsoft Access that would display my query search (say, at the top of the report) and who was generating the report (say, the user logon in the footer).

I discovered that there were two parts to this issue: 1) insert textboxes during the report design; 2) for the user logon name, get the code that will capture this.

1. Display parameter search query on a report

This is straightforward and there’s lots of useful information online – search online for “display parameter report microsoft access”.

You have already created your parameter query. Open this up and look at the parameter. It’s probably something like:

[Enter start date]

I often use LIKE queries, e.g.

LIKE "*" & [Please enter a keyword] & "*"

The bit of the query that is in square brackets is what is wanted here, in my case it would be:

[Please enter a keyword]

Copy the parameter or take note of it on paper.

Now go to your report in design view. Insert a textbox, look at its properties and in the control source type in:

="Search for " & [Please enter a keyword]

The part that is within quotes is text that you can change to whatever you like, the ampersand (&) then adds on the parameter. If you want the output to be something like, Search for ‘New York restaurants’, type this into the control source:

="Search for " & "'" & [Please enter a keyword] & "'"

Whatever is searched for is what ends up on the report, so if you search for new York restaurants then this is what will be printed on the report, so be careful if presentation is important.

The great thing about this way of inserting parameters is that a separate label doesn’t have to be created – I’ve found trying to align the label and textbox isn’t easy.

 

2. Display user logon name on a report

There’s lots of information about this online but it took me ages to get it sorted. Getting the user logon might depend on how your computer is set up, whether you’re logged onto to a server, etc. This item is slightly more advanced. There’s a good tutorial about modules and functions at Build Custom Functions for your Access Applications.

What worked for me was the fOSUserName function. Search for this code online – I used the one at API: Get login name by Dev Ashish but you may find something more suited to your purposes elsewhere.

In Access, create a module. Copy the code you’ve found into it and save the module under whatever name you like (but probably not fOSUserName as this seems to cause problems). Take a note of the function name (there’s only one in this particular code): how do you know the name of the function? Look for a line like: “Function fOSUserName() As String” – this is the name that will be used in the textbox control source.

Now open your report. I wanted to have a footer that showed who had printed the report, so in the footer I inserted a textbox, looked at its properties and in the control source inserted:

="Printout generated by: " &  fOSUserName()

I really like this neat way of inserting useful information. This textbox can be copied (use Ctrl C) for use on other reports. Search for “functions and modules in microsoft access” in your favourite internet search engine to find out more.