IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Paul Murray: email@example.com
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.
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.
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.
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.
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 126.96.36.199 - 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.
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:
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.
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:  268-960
smail: JT Software, PO Box 169, Belconnen, ACT, 2616, Australia
Have your say - comment on this article.
What did you think of 'Managing Combo Boxes'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...