Standard Module Code Samples


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.

Using Data in tblInfo

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)) 

Date Range Code on Main Menu

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.

click to expand
Figure 7.7

click to expand
Figure 7.8




Expert One-on-One(c) Microsoft Access Application Development
Expert One-on-One Microsoft Access Application Development
ISBN: 0764559044
EAN: 2147483647
Year: 2006
Pages: 124
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net