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.
Figure 1
Table 1. The tl_Country table.
Field name | Data type | Index? | Required? | Description |
tl_Country_ID | Autonumber | Y | Y | Primary Key |
Country | Text(50) | Y | Y | Data field |
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:
1. I needed to be able to add or remove data from any of the lookup tables in a system using one form.
2. I wanted the form to be generic, so that I could use it in any of my Access databases.
Organizing the data
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.
Table 2. The Lookup Manager table.
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 |
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).
Figure 2
Managing the data
So 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 3
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;
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: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.
' 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 records
However, 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:
' 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
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:Removing records
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.
Figure 4
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
Finishing up
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!
Other Pages that you might want to read
Managing State Transitions – More Complex Lookup Tables
What’s in a Table?
Access Subquery Techniques
Combining Tables using Union Queries
Tame the Crosstab Missing Column Beast
Subquery Notes
You Can Do That with Datasheets?
Do More for Less