|| Feedback | Access queries ||
It is useful to be able to refer to some particular variables throughout your database. For example you may wish to display the current period name and the organisation name in various reports and forms. In some programs you may wish to record particular default folder names (e.g. D:\Download\) on the users PC into which exported reports are to be saved.
Some information can be saved as named variables which are created upon starting up the database. If you use a start-up screen, the following procedure (DefVars) could be called from the Open Event.
Would you like to learn more?
Enter the following visual basic code in a Module:
Option Compare Database 'Use database order for string
Public UserName As String
Public ProgVersion As String
UserName = "Finance Department"
ProgVersion = "v2.98"
These variables are hard coded and can not easily be changed by different users or as situations change. They can be used on forms or reports by creating a text label field and then setting its caption property using a VBA module. Select the form or report Open Event property and add the code: [Me!Label43.Caption = ProgVersion ] into a procedure.
A more flexible method is to save all useful variables in a table and create a user defined function to extract any required item.
A table called KeyData
|1||Month 2 (May 2004)||the current financial period|
|2||C:\MyData\Distribution\||the default folder for exported RTF reports|
|3||13/05/2001 13:43||the last time the account codes file was updated|
|4||ALL||print all records or only a selection from alternative query?|
Particular bits of information are given specific record numbers which can be called by a user defined function (which in this example is called LookUpKeyData) to use them in queries, forms, reports and other VBA code.
|Function LookUpKeyData(MyItem as
Integer) As String
Dim Temp As String
On Error GoTo MyErrorBit
Temp = "[recordID] = " & MyItem
LookUpKeyData = DLookup("[KeyItem]", "Keydata", Temp)
LookUpKeyData = "Err " & Format(Err.Number, "#") & Err.Description
Using the above example: ? LookUpKeyData(2) would return 'C:\MyData\Distribution\'.
A text field in a report heading could be defined as: = "Agency staff report - " & LookUpKeyData(1) and this would display as 'Agency staff report - Month 2 (May 2004)'.
You may require a routine for updating a field within the KeyData table. The following procedure updates a specified record number with a new text string. It could be used as a part of a program to record the time at which a data table was last updated - or simply added to the close event of a form. e.g. UpdateKeyData 3, Format(Now(),"hh:mm dd/mm/yy")
|Sub UpdateKeyData(RecordNo As Long, NewData As String)
Dim myDb As Database, MySet As Recordset
Set myDb = CurrentDb()
Set MySet = myDb.OpenRecordset("keydata", dbOpenTable)
MySet.Index = "PrimaryKey"
MySet.Seek "=", RecordNo
MySet!KeyItem.Value = NewData
Debug.Print "UpdateKeyData: Record " & Format(RecordNo, "#") & " changed to " & NewData
an Access database for
|file: aprogvars.htm||© MeadInKent.co.uk 2005||Page last updated Sep06|