Christopher Harris
Chris presents the Lookup Table Manager -- a one-stop utility that allows data to be added into and removed from any of the lookup tables that populate your Access databases.
I build a lot of systems that use combo boxes to select data. Typically, the data for the rowsource of the combo box comes from a lookup table like the one in Figure 1 (this is a Country table that I use quite a lot). Table 1 shows the structure of the Country table, which is typical of my lookup tables.
| Field name | Data type | Index? | Required? | Description |
| tl_Country_ID | Autonumber | Y | Y | Primary Key |
| Country | Text(50) | Y | Y | Data field |
Table 1. The tl_Country table.
In fact, most systems I've come across use lookup tables just like this one (and often employ a great deal of them, too). The only problem is that it can be very time-consuming to write the code to manage the addition and removal of data in and out of all these tables.
I decided there had to be a more efficient way to manage the data in these tables, and I set out to create a lookup table manager utility. I set two design goals for myself:
To start, I put together a separate table to hold details of all the available lookup tables in a system. I called it tbl_Lookup_Manager and gave it the structure shown in Table 2.
| Field name | Data type | Index? | Required? | Description |
| tbl_ Lookup_Manager _ID | Autonumber | Y | Y | Primary Key |
| Lookup_Tbl | Text(50) | Y | Y | Lookup table name |
| Lookup_Tbl_PK | Text(50) | Y | Y | Lookup table PK name |
| Lookup_Tbl_Data | Text(50) | Y | Y | Lookup table data field name |
| Category | Text(50) | Y | Y | Setting category as displayed on form |
Table 2. The Lookup Manager table.
In tbl_Lookup_Manager, the Lookup_Tbl field contains the physical name of each lookup table in the database. I prefix all of my lookup tables with "tl_", as it helps to distinguish them from other tables and objects (which makes my code easier to understand). The Category field supplies a more meaningful description of the physical lookup table name. For example, the lookup table tl_Country has "Country" in its Category field. The Lookup_Tbl_PK field contains the name of the Primary Key field for each lookup table. For the tl_Country table, this field contains "tl_Country_ID". Similarly, the Lookup_Tbl_Data field contains the name of the data column within the table. Again, for the tl_Country table, this contains "Country". For each lookup table in a database, I insert one record into tbl_Lookup_Manager (see Figure 2 for a sample of what the table looks like).
Managing the dataSo far, so good. At this point, I had a table of information where I could go to get structural information on any of the lookup tables in my system. Next, I built the form shown in Figure 3 to do the real work of inserting and deleting lookup records.
Figure 1. A Typical Lookup Table
Figure 2. The Lookup Manager Table
On the Lookup Manager form, a combo box is used to select the lookup table to deal with. The combo box uses the Category field for the RowSource, which provides a description of the table that's far less confusing for the end user than "tl_Country"! The combo box's RowSource is set to the following SQL statement:
SELECT tbl_Lookup_Manager.Category
FROM tbl_Lookup_Manager
ORDER BY tbl_Lookup_Manager.Category;
Once the user selects a lookup table, I use the After Update event of the combo box to process the selection. The purpose of this process is twofold -- first, to load the attributes of the selected lookup table into memory (using module variables), and second, to populate the form's list box with the data in the selected lookup table.
To get the table's attributes, I use the FindFirst method to locate the table's record in tbl_Lookup_Manager, using the selected category from the form's combo box as the criteria argument. The table's attributes are then used to make a rowsource SQL statement for the list box. Here's the code that does all that:
' Open a session with the db engine
Set dbMyDb = CurrentDb()
Set rstMySet = dbMyDb.OpenRecordset _
("tbl_Lookup_Manager", DB_OPEN_DYNASET)
' Get selected item from category list
strCategory = Me!comCategory
' Find record matching selected category in table
rstMySet.MoveFirst
rstMySet.FindFirst "[category] = '" _
& strCategory & "'"
If Not (rstMySet.NoMatch) Then
' Set form module variables
mstrLookupTbl = rstMySet![Lookup_Tbl]
mstrLookupTbl_PK = rstMySet![Lookup_Tbl_PK]
mstrLookupTbl_Data = rstMySet![Lookup_Tbl_Data]
' Select details into list box
strRecSource = "SELECT " & _
mstrLookupTbl_PK & ", " & _
mstrLookupTbl_Data & " " & _
"FROM " & mstrLookupTbl & " " & _
"ORDER BY " & mstrLookupTbl_Data _
& ";"
Me!lstItems.RowSource = strRecSource
Me!lstItems.Requery
Else
MsgBox "The category could not be located.", _
vbExclamation, "Lookup Table Manager"
End If
As you can see, the Lookup_Tbl, Lookup_Tbl_PK, and Lookup_Tbl_Data fields provide the information I need to display the table's data.
Inserting lookup recordsHowever, it's when I process the changes to the data that tbl_Lookup_Manager really comes into its own. Because I know the actual physical lookup table name and the name of the corresponding data field within the lookup table, I can write some generic code to insert data into any of the lookup tables.
First, I needed to get the data to be inserted, and to achieve this, I used the Access InputBox function. I assign the output from the InputBox to a local string variable, for processing later on in the routine:
strNewItem = InputBox _
("Type the item to be added, and press OK.", "Add")
The InputBox function returns an empty string if the user presses the Cancel button or doesn't make an entry at all. After some validation to handle those situations, the data is added to the specified lookup table. To do this, I use the module string variables that were populated during the After Update event of the category combo. As the code shows, the mstrLookupTbl variable is used in the OpenRecordset statement to open the selected lookup table. I use the AddNew method to insert the data, using the mstrLookupTbl_Data variable in the assignment statement that updates the record:
Figure 2. The Lookup Manager Form
' If entered, add item to table
If strNewItem <> "" Then
' Add item to table
Set dbMyDb = CurrentDb()
Set rstMySet = dbMyDb.OpenRecordset _
(mstrLookupTbl, DB_OPEN_DYNASET)
rstMySet.AddNew
rstMySet(mstrLookupTbl_Data) = Trim(strNewItem)
rstMySet.Update
' Requery list box to reflect change
Me!lstItems.Requery
End If
This works well, but I need to avoid adding duplicates. To handle this, I used a standard VB error handler that traps error number 3022 (error 3022 occurs when inserting a duplicate record into a unique index). Of course, this requires that I add a unique index to the data field of my lookup tables, but I didn't feel that the index would be a major burden on the system. Here's the code that handles duplicates:
Select Case Err
Case 3022
' Commit attempts to create a duplicate entry
MsgBox "The item you typed already exists." _
vbExclamation, "Add"
Case Else
MsgBox "An error occurred. Operation aborted.", _
vbExclamation, "Add"
End Select
The routine for removing records is largely the same as that used for insertions. In this case, I use the data selected in the list box to find and delete the record in the lookup table. First, I stored the record's identifier locally:
lngDelItemID = Me!lstItems.Value
Then, once again, I used the FindFirst method to locate the record in the lookup table. Once it's found, I remove the record using the Delete method:
' Open recordset corresponding to selected category
Set rstMySet = dbMyDb.OpenRecordset(mstrLookupTbl, _
DB_OPEN_DYNASET)
' Find matching record and delete it
rstMySet.MoveFirst
rstMySet.FindFirst "[" & mstrLookupTbl_PK & "] = _
" & lngDelItemID
If Not (rstMySet.NoMatch) Then
' Delete record
rstMySet.Delete
' Requery list box to reflect change
Me!lstItems.Requery
End If
Again, there's a small problem to tackle at the end. The problem occurs when a lookup record that's referred to by some other table(s) in the database is deleted. The solution is to declare referential integrity between the lookup table and all those tables that refer to it. With that done, I can add an error trap in the code to look out for a specific error number (3200, in this case) and respond to it. As an example, suppose that a table, tbl_Applicant, refers to data in one of the lookup tables, tl_Title. Figure 4 shows what tying the two tables together with referential integrity looks like in Access's Relationship window.
This arrangement allows me to check for cross-reference problems by trapping error 3200 in my code. When that happens, I present a message box on screen:
Select Case Err
Case 3200
MsgBox "This item cannot be removed. " & _
"It is referenced by other records.", _
vbExclamation, "Remove"
Case Else
MsgBox "An error occurred. ", _
vbExclamation, "Remove"
End Select
Resume Exit_Sub
The aim of this article was to demonstrate that, by using some fairly simple techniques, I was able to build a quite useful utility for my Access applications. I not only shortened the development time for the project that first used this tool, but also for every project I've done since then. The next enhancement is a utility to run through the database, find all the lookup tables, and populate the Lookup Manager table. This shouldn't be too hard to write if you follow a standard naming convention for your tables.
The code for the current version of Lookup Manager is available in the accompanying Download file. Have fun!
Download LOOKUP.exeChristopher Harris is software development manager at The College of Law, UK. Chris has been developing applications in Access since version 1.1 and in Visual Basic since version 3, and he's also a PowerBuilder/Sybase specialist. Chris_harris@lawcol.co.uk.