Managing Combo Boxes

MS-Access Tips for Serious Users

Provided by Paul Murray

Managing Combo Boxes

I find that often I have fields that must be filled with an abbreviation. For instance: PT - Part Time, FT - Full Time, CS - Casual. For space/efficiency reasons, you only want to store the abbreviation, but you would like the user to see the expanded names in a combo box list. In fact, a database may contain several short lists like this.

Tip 1.1 - Filling different combo boxes from one table

Normally you will want to fill the combo box from a table like this:

     Val, Text
     PT,  Part Time
     FT,  Full Time
     CS,  Casual

The problem with this is that you need a separate table for each combo box - one for "Staff Type", one for "Item Type", one for "Colour" etc. The solution to this is to include a third column to distinguish the lists, and to use a single table (which I generally name "code")

     List,      Val, Text
     StaffType, PT,  Part Time
     StaffType, FT,  Full Time
     StaffType, CS,  Casual
     Colour,    BLK, Black
     Colour,    BLU, Blue
     Colour,    GRN, Green
     Colour,    CYN, Cyan
     Colour,    RED, Red

The key of this table should be (List,Val).

The Row Source for the staff combo box should be:

     "Select Val, Text from Code where List='Stafftype'"

And Column Count: 2.

This gives you the flexibility of being able to allow the users to add items to your combo boxes. (see tip 1.3), and the neatness of only having one table for all these different values.

Disadvantages:

1. The codes you use in your system have a maximum length limited by the width of the [Val] in the code table.
2. It is not possible to enforce referential integrity on the codes in your data tables. You can go part of the way if you limit the key field on the code table to [Val], but this means that you cannot use the same code for two different Lists of codes, and does not stop someone entering the staff type as "GRN" directly into the staff table. This is not as severe a problem as it might be given that your users are entering values from a form combo.

Tip 1.2 - Not displaying the bound column

When a combo box is closed, normally it displays the value of the field it is bound to. Due to a convenient feature of access combo boxes, you can cause it to display whatever you like. Let's take our staff type combo, which has a row source of
"Select Val, Text from Code where List='Stafftype'"

A column count of 2, and a bound column of 1. This combo will display the list of abbreviations and their values in the drop down list, and the field will display PT/FT/CS when the list is closed. To inhibit the display of the abbreviations, set the Column Widths to "0;". This will make column 1 to be of zero width, and column 2 to the default width.

Disadvantages:

1. The list is sorted in order of the abbreviations, which is not necessarily the same as that of the text.
2. Users who are familiar with the abbreviations used in the data entry system cannot type those abbreviations into the combo.

Tip 1.2.1 - Getting the list sorted

Simply switch the order of the columns:

    Row Source:    Select Text, Val from Code where List='Stafftype'
    Column Count:  2
    Column Widths: ";0"
    Bound Column:  2

Tip 1.2.2 - Allowing the users to type in the abbreviation

The problem is that we can structure the combo either to display the [Val] or the [Text] from our code field. Ideally we would like to do both. To accomplish this, the rowsource can be a Union query. The easiest way to type this in is to open the query builder on the rowsource property and then to hit the 'SQL' button. The query should be:

     Select Text, Val from code where list='stafftype'
     union
     select Val, Val from code where list='stafftype'

As you can see, we wind up with a list of all the 'text' and all the 'Val' abbreviations in column 1 which gets displayed, and the bound column - col 2 - contains the abbreviations.

Problems

The sorting is all wrong again. The codes and values are mixed in together in the combo box list. Even worse, that value that gets displayed when the list is closed is the first item in the list where the bound column matches the underlying field. So if 'PT' is in the underlying field then it is displayed as "Part Time", because "Part Time" is alphabetically in front of "PT", whereas if "FT" is in the underlying field, the abbreviation gets displayed.

Tip 1.2.2.1 - Getting the union list sorted

To do this, you include a dummy column as the first row of the query. Like so:

    RowSource:
         Select 1, Text, Val from code where list='stafftype'
         union
         select 2, Val, Val from code where list='stafftype'
    Column Count:   3
    Column Widths: "0;;0"
    Bound Column:   3

And it all works beautifully. Give it a go - when you type 'PT' into the combo, the combo replaces it with the text 'Part Time' in the display, but stores 'PT' in the underlying table. This final query is the one that I actually use.

Tip 1.3 - Handling NotInList

A final thing that you want your combo to do is to have provision for entering new list items if a user types in a new list item. I shall switch examples here: let's say you have a table of customers - name, address, phone no etc. You have a combo based on 'select name from custlist' and want the user to be able to enter a new customer name and have a dialog pop up.

The first go at programming this will look something like this:

Sub CustName_NotInList (NewData As String, Response As Integer)
    If MsgBox("""" & NewData & """ is not in the customer list. Add it?", 33) <> 1 Then
        Response = DATA_ERRCONTINUE
        Exit Sub
    End If
    DoCmd OpenForm "AddNewCust", , , , 1 'Data Entry Mode
    Forms!AddNewCust!Name = NewData
    Response = DATA_ERRADDED
Exit Sub

This will not work as expected. The new form will pop up, then control will return to the combo. The combo, having been told that the data has been added, will look for it and not find it, and will pop up an error message over the top of the form.

1.3.1 - Solution 1

Sub CustName_NotInList (Newdata As String, Response As Integer)
    If Not IsNull(DLookup("Name", "CustList", "Name=""" & Newdata & """")) Then
        Response = DATA_ERRADDED
        Exit Sub
    End If
    If MsgBox("""" & Newdata & """ is not in the customer list. Add it?", 33) <> 1 Then
        Response = DATA_ERRCONTINUE
        Exit Sub
    End If
    DoCmd OpenForm "AddNewCust", A_NORMAL, , A_ADD  ' Data Entry Mode.
    Forms!AddNewCust!Name = Newdata
    Response = DATA_ERRCONTINUE
End Sub

The flow of events is this:

• The user types in an unknown customer name, and so NotInList gets triggered.
• NotInList looks for the customer name via DLookup, doesn't find it, asks if the user wants to continue, and pops up the dialog form.
• It then exits, telling the combo that the error has been handled.
• The user then enters the customer details on the dialog and then closes it.
• When the user moves to the next field, the combo does not yet know that a new customer has been added, and so NotInList is triggered again.
• This time, the DLookup finds the customer name in the table and so tells the combo to requery itself by returning DATA_ERRADDED.
• The combo requeries, finds the newly added customer, and closes happy.

Disadvantages: DLookup is slow

1.3.2 - Solution 2

Sub CustName_NotInList (Newdata As String, Response As Integer)
    If MsgBox("""" & Newdata & """ is not in the customer list. Add it?", 33) <> 1 Then
        Response = DATA_ERRCONTINUE
        Exit Sub
    End If
    DoCmd OpenForm "AddNewCust", , , , 1 'Data Entry Mode
    Forms!AddNewCust!Name = Newdata
    Response = DATA_ERRCONTINUE
End Sub

The key here is to stick a Forms![MainForm]!CustName.Requery inside the AfterUpdate event on the AddNewCustName form. This will update the combo box list after you add the new customer in the dialog.

Disadvantages:

Ties the AddNewCustName form to the MainForm - you can't use it from another form.

This tip was supplied, by Paul Murray, 14 June 1995.

Who is Paul Murray? (blatant plug). I have been working in MS Access for nearly 3 years, and have been a participant on the comp.databases.ms-access newsgroup. I also work in word/excel, and have 10 years experience in C, as well as C++/windows. I am looking for a full-time position, preferably in Canberra, Australia, but I would be willing to relocate (Brisbane would be nice). To contact me:
phone: [015] 268-960
smail: JT Software, PO Box 169, Belconnen, ACT, 2616, Australia