Almost all applications need some standard code that can be used throughout the application. My Menu Manager add-in includes a basUtilities module with some standard procedures, and you can add your own as needed (each of the sample databases has different extra procedures in basUtilities, depending on its needs). Some of these standard module procedures are described in the following sections.
I use fields in tblInfo (the table to which the main menu is bound) to store miscellaneous information I need to use in a database. To retrieve information from this table or store a new value in a field, I use functions like the following so that I can set the value of a variable with the GetDocsDir function in VBA code, retrieving the value from tblInfo. The GetDocsDir function (from basWordAutomation in the Word Data Exchange sample database) picks up the value of the DocsPath field from tblInfo, falling back on the default path C:\My Documents if the field is empty. Finally, the subfolder Access Merge\ is appended to the documents path to get the folder for creating merge documents.
Public Function GetDocsDir() As String On Error GoTo ErrorHandler Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset) With rst .MoveFirst strDocsDir = Nz(![DocsPath]) If strDocsDir = "" Then GetDocsDir = "C:\My Documents\" Else GetDocsDir = ![DocsPath] End If End With GetDocsDir = GetDocsDir & "Access Merge\" ErrorHandlerExit: Exit Function ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Function
The syntax for using this function in a procedure is:
strDocsDir = GetDocsDir
To save a value to tblInfo from code (this would be required if the value can’t be picked up from a control on a form bound to tblInfo), a procedure like the following will do the job (also from basWordAutomation in the Word Data Exchange sample database).
Public Sub SaveDocsDir(strDocsDir) On Error GoTo ErrorHandler Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset) With rst .MoveFirst .Edit ![DocsPath] = strDocsDir .Update .Close End With ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
The Sub procedure can be called with a line like one of the following (depending on whether you need to hard-code the path or pick it up from a control on a form):
Call SaveDocsDir("C:\My Documents") Call SaveDocsDir(Nz(Me![txtDocsDir].Value))
The standard main menu created by my Menu Manager add-in has two textboxes that can be used to create a date range for filtering reports. The basUtilities standard module (also created by Menu Manager) has two functions that retrieve the dates from these textboxes, each with a fallback date in case the textbox is blank.
Public Function ToDate() As Date On Error GoTo ErrorHandler ‘Pick up To date from Info table Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable) With rst .MoveFirst ToDate = Nz(![ToDate], "12/31/2004") .Close End With ErrorHandlerExit: Exit Function ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Function Public Function FromDate() As Date On Error GoTo ErrorHandler ‘Pick up from date from Info table Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInfo", dbOpenTable) With rst .MoveFirst FromDate = Nz(![FromDate], "1/1/2003") .Close End With ErrorHandlerExit: Exit Function ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Function
Instead of a hard-coded fallback date, you could use the DateAdd function to create fallback From and To dates. The following expressions yield dates from one year before the present to the present date:
FromDate = Nz(![ToDate], DateAdd(“yyyy”, -1, Date)) ToDate = Nz(![FromDate], Date)
If dates have been entered into the textboxes, the FromDate and ToDate functions are set to the specified dates; otherwise, default dates of 1/1/2003 and 12/31/2004 are used. A textbox in the standard report template header displays the FromDate and ToDate values, and they are used as a criterion for filtering the report’s record source. Figure 7.7 shows the main menu, with dates, and a filtered report, and Figure 7.8 shows the report’s record source, with a criteria expression using FromDate and ToDate.
Figure 7.7
Figure 7.8