Examples of Access VBA Code Modules - define your own variables

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.

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.

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

      

      MyErrorBit:

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

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.Edit

       MySet!KeyItem.Value = NewData

       MySet.Update

      Debug.Print "UpdateKeyData: Record " & Format(RecordNo, "#") & " changed to " & NewData

      MySet.Close

      End Sub

Office
Leave
Planner

an Access database for
recording and displaying
planned staff absences

[View details]

Home	Excel	Access
\| Feedback \| Access queries \|

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?

User defined program variables in Access databases