How to make an Ms Access Report call a VBA Function
Once you design a vba library, you can take it with you where ever you need it and just
like programmers do, plagiarize it to do other things as they come up. The functions are
placed into the Modules of your Ms Access Database and you can have many Modules. I try
to have a few and one large one that contains all the general functions and then some
smaller ones that are specific to one thing or another and of course the smaller ones
can call functions in the larger ones.
Our web site at
Function from query
shows how to call a function from a query and now I'd like to show how it can be done from
a report. So lets start by designing a new report. With the Report tab open, select
'New', then when the next form appears, select from the drop down a table such as our
Employee table (Note that you can select Tables or Queryies at this point). Then select the
'Design View' (Note that just using the 'Report Wizard' will only create a one time report).
Now the report design view/form will appear.
The idea with this is that you select control types from the 'tools' on the left by clicking
on them and then dragging out a rectangle of them on top of the Form that represents your
report. To do so, create one field on your report that for instance shows the Employee Name,
follow the steps below but remember it will go one step further and instead of selecting
the Employee Name from the data source, it will call a function and that function will
return a value that will be printed.
In the report design view, hoover you mouse over some of the tools shown on the left
and a little popup will display what that tool name is. Look for the one labeled:
'Textbox'. Click it once, then move your mouse to the report design and click your mouse
down at the point where you want something to display. It usually is in the detail section
but also can be in the header or footer section. Now drag the mouse to create a rectangle
area about the size of what ever you're going to be printing (at its greatest length).
Now right mouse click that rectangle control you just created and in the data tab, 'control
source' section, call a function as follows e.g.
Note that a function call in a reports always will start with the equal sign. Also when
defining which function to call, it's always easier if you know the name of your function. If
you don't know the name of the function, you can click on the three dots and drill down
into the 'functions' category until you find your function..but believe me it's a lot easier
if you not only know your function name but also any parameters that you are passing to it.
In the above example Read_System("1") we are passing a hard coded "1" to the function because
we have a system table with 1 record in it and the function 'Read_System' knows its record id
and we are just passing the "1" to tell it to return our company Name. We could pass it a '2'
if we wanted it to return our address. In other words, we've programmed the function to return
the fields that we tell it to.
Remember that Reports derive their data from a table or a query and When you first create a
report, at the same point where you select 'Design View', you must select the table or query
that the report will use. If you are wondering how to see an existing reports driving table or
query, open the report in design view and then from the top menu select 'View', 'Properties' and
then select the data tab and look at the value in the 'Record Source' field.
Another type of function call is where we don't hard code the parameters like we did above with
the "1". It's a more dynamic type because we are passing something that the query/table which the
report is driving off has obtained. Here is an example of what the Textbox Data Control's (mentioned
above..thing you drag into a rectangle) right mouse properties, data, control source would look like:
In this example, we are calling the function: Format_Record_Report and we are passing the current
reports Employee Id to that function. Since the function is designed to accept an incoming
employee id, it will use that Id to read the employee. In case you were wondering what this
function does, it reads all of the employee's fields and then formats them into one long line
of employee information that is then printed on the form. Since we get one long line of fields,
it saves us the time of adding each and every employee field to the report and gives us more
control over how things are printed.
The point here is that we can call a function from a report using the format:
=[function name]([data]) where function name is our own function and ([data]) is either a hard coded
value or the value we have obtained from the driving table or query. Notice that in the function
call: =Format_Record_Report_0([Employee Id]) that the 'Employee Id' is a field in the employee
table. Also notice that the square brackets are required because there is a space between the
words 'Employee' and 'Id'.
The fact that our function formats all the fields for the Employee that we specified only shows
how much we can do with a function. OUr main point is only to say how to call a function from a
report. Using VBA functions is another level up in the process of using MS Access. There is
a massive amount of information about this obtained with a simple google search such as:
'ms access module functions'
This image show the Report designer with one field that calls a function
Notice that it shows the 'Tool box' where the user selects the type of control to use. The most
often used tool is the 'Textbox' and is used to select data that is to be printed on the Report.
This example call the function 'Get_Employee_Yearly_Amt and passed the Employee Id. The nice
thing about VBA module functins is that once they are written, you never need to write it