Managing Lookup Tables

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!

  Your download file is called  Harris_Lookup_Table.accdb

 

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

This entry was posted in Design and Tables. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.