|
|
|||
| | 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.
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
 |
|||
| RecordID | KeyItem | ItemDescription | |
| 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) 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: = "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.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 |
| file: aprogvars.htm | © MeadInKent.co.uk 2005 | Page last updated Sep06 |
