By Todd E. Parker
When you develop an Access application, you typically need to refer to a number of variables throughout various forms or modules in your application. To manage those variables, you can, of course, use Microsoft Visual Basic for Applications (VBA) to set their values with a startup form that then calls a module, which then initializes these values.
However, the problem with this approach arises after you have finished developing your application. Someone else may need to change those values in the future and won’t know where to begin. I suggest that you create a table to your database called SystemTbl. Your users can then access this SystemTbl and modify the data as they see fit.
Setting up the table and the form
After you’ve created the table and added the variables to a new record in the table, you can then create a form for your users that only allows changes, but not additions or deletions. This table should only hold one record for storing this information. Once you’ve stored all your variables, you can use VBA to retrieve those values when your application needs them.
For instance, let’s pretend that your application needs to store the name and phone number of the Application Administrator. As you know, this information should be easily modifiable. There’s no sense in hard-coding it into a form or module.
Your table is predefined with the one record and two fields (at this point):
- Table: SystemTbl
- Field1: AdminName
- Field2: AdminPhone
When you need to display the proper Name and Number on a form for your users, use the On Open or On Load event to populate the labels. In this case, we’ll call the labels “PhoneLabel” and “NameLabel.” Listing A shows the code for our sample function.
|Private Sub SampleForm Form_Open()|
|On Error GoTo ErrorExit|
|Dim txtName As String|
|Dim txtPhone As String|
|Dim db As Database|
|Dim rs As Recordset|
|Set db = CurrentDb|
|Set rs = db.OpenRecordset("SystemTbl", dbOpenSnapshot, dbForwardOnly)|
|‘—-Test if Recordset has a record.|
|If rs.EOF Then|
|MsgBox "There were no records in your SystemTbl", vbOKOnly, "Error"|
|‘—Test for NULLS in recordset|
|If IsNull(rs!AdminName) Or IsNull(rs!AdminPhone) Then|
|MsgBox "There was no Administrator Information Address Found.", vbOKOnly, "Error"|
|‘—- Grab Recordset Values.|
|txtName = rs!AdminName|
|txtPhone = rs!AdminPhone|
|‘—- Place text onto the form Labels.|
|Me.NameLabel.Caption = "Administrator Name: " & txtName|
|Me.PhoneLabel.Caption = "Administrator Phone: " & txtPhone|
|Set rs = Nothing|
|Set db = Nothing|
In this function, we have opened the recordset in SnapShot-ForwardOnly. Doing so decreases the amount of resources used when accessing this recordset. We then applied the value of the field in the table to the caption of the Name and Phone Labels.
Finally, we closed out the recordset. Never forget to close out a recordset; it can be accumulated as an unneeded resource that can seriously degrade performance or cause even worse problems. Since there was only one record for your system variables, the values are retrieved lickety-split, and you’ve now incorporated a much more scalable solution to an effective database application.
Todd E. Parker has been a consultant in the IT field for over seven years and is currently employed by Science Applications International Corporation. SAIC is a global IT consulting corporation dealing largely with nuclear and other energy-related companies.