Managing Lookup Tables

<< Click to Display Table of Contents >>

Navigation:  Tables >

Managing Lookup Tables

199808_ch4 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.

199808_ch1 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).

199808_ch2 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.

199808_ch3 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;

 
 
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 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

      

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

 
 
Removing records

 
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.

199808_ch4 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 LOOKUP.ZIP  in the file SA1999-08down.zip

This can be purchased with all the other downloads on this page
 
 
 

Other Pages at vb123.com 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

 

We sell FMS Tools and bundle them with other discounted and free products