Back in May 2012, I wrote a blog article on Writing Custom Functions for Access VBA. Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers. We do this by creating a public function with the VBA programming language and save it inside a global module within the database.
The blog I wrote in 2012 explained how these custom functions can be accessed from sub procedures associated with forms and reports elsewhere in the database. Today, however, I am going to explain how custom functions can be utilised in calculated form controls, without needing to invoke them with VBA itself.
Let's begin by looking at the custom function we are going to use.
Let's begin by looking at the custom function we are going to use.
Custom Function to get Week Commencing
I have created a custom function called getWeekCommencing to calculate the week commencing date of a given week, in a given year. For example, the week commencing date of the 14th week of 2019 is 1st April 2019 (if we count Monday as the first day of the week). As such the function takes two arguments - week number and year. The full syntax for using the function is as follows:
getWeekCommencing(weekNumber, Year)
The code I used to create this function is as follows:
Public Function getWeekCommencing(intWeekNo, intYear)
On Error GoTo myError
Dim intJan1 As Integer
Dim varStartDate As Variant
intJan1 = Weekday(DateSerial(intYear, 1, 1), vbMonday)
varStartDate = DateAdd("d", -(intJan1 - 1), DateSerial(intYear, 1, 1))
getWeekCommencing = DateAdd("ww", intWeekNo - 1, varStartDate)
leave:
Exit Function
myError:
MsgBox Error$
Resume leave
End Function
If you are unfamiliar with VBA you can still copy this code into your database and use it within a calculated control. There are instructions for doing this below:
Instructions for adding the function to your database.
- Copy the code above onto your clipboard.
- Open the database you are going to add the function to.
- Select the CREATE ribbon.
- Click the MODULE icon from the MACROS AND CODE group. This opens the VBA editor and creates a new module ready for the code to be added:
- Paste the code into the VBA editor as in the screenshot below:
The custom function is now ready to use in a calculated form control.
Setting up the Test Data for this Exercise
Before we create the form to test the function, we first need to set up a simple database table containing test data. The table needs just two fields: WeekOfYear and Year, both of which are of the NUMBER data type.
Above: tblWeeks in Design View |
Above: tblWeeks in Form View with test data entered. |
Once the table is set up, we can add our test data as I have done in the screenshot above. Although there are 52 week in the year, the first week may not start on a Monday, so the data entered in WeekOfYear field can be anything between 1 and 53 (so the possibility of a partial first week is offset by Week 53).
The Calculated Control
Now we have added the getWeekCommencing function along with test data to our database, we can create the form containing the calculated control.
Above: The Week Commencing Calculator form in Design View. |
To do this we need to create a form bound to the tblWeeks table. We then need to add three text boxes: the first text box (txtWeekOfYear) is bound to the WeekOfYear field in tblWeeks, and the second (txtYear) is bound to the Year field. The third text box (txtWeekCommencing) is unbound. This is where we are going to add our calculated control which returns the week commencing date (based on the data displayed in the WeekOfYear and Year textboxes).
Here are the instructions for creating the calculated control:
- Create the third text box that we are going to use for the calculated control as in the screenshot above.
- Display the PROPERTY SHEET if it is not already visibile. The PROPERTY SHEET icon is located in the TOOLS group of the DESIGN ribbon.
- Select the new text box by clicking on it.
- Click the OTHER tab of the PROPERTY SHEET, and change the NAME property to txtWeekCommencing. (I also suggest you change the names of the first textbox to txtWeekOfYear and the second to txtYear by selecting them and changing the NAME properties).
- Click the DATA tab on the PROPERTY SHEET (having first selected txtWeekCommencing). We are now going to enter an expression which uses our getWeekCommencing custom function.
- Enter the expression below directly into the CONTROL SOURCE property:
=getWeekCommencing([txtWeekNumber],[txtYear])
The txtWeekNumber parameter references the value in the txtWeekNumber textbox, and the txtYear parameter does the same for the txtYear text box.
And that's it! All you need to do now is save the form and open it in FORM VIEW. Use the form's navigation buttons to view each record in the tblWeeks table. The calculated control uses the getWeekCommencing custom function to processes the week number and year data for the current tblWeeks record, and displays the appropriate date.
No comments:
Post a Comment