Hyteware Logo

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.
=Read_System("1")

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:

=Format_Record_Report_0([Employee Id])

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 again.