| Feedback | Access queries |

User defined program variables in Access databases

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.

A selection of books about Access from Amazon

Enter the following visual basic code in a Module:

Option Compare Database 'Use database order for string comparisons

Public UserName As String
Public ProgVersion As String

Sub DefVars()
UserName = "Finance Department"
ProgVersion = "v2.98"
End Sub

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

Table: Keydata ox
  RecordID KeyItem ItemDescription
  1 September (M6) 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)

Exit Function

LookUpKeyData = "Err " & Format(Err.Number, "#") & Err.Description
End Function

Using the above example: ? LookUpKeyData(2) would return 'C:\MyData\Distribution\'.

A text field in a report heading could be defined as: "Summary report for " & LookUpKeyData(1) and this would display as 'Summary report for September (M6)'.

A budget report including a program variable in the title

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
End Sub


an Access database for
recording and displaying
planned staff absences

[View details]

file: aprogvars.htm 2014 Page last updated May14