How do I use an Access combobox with a lookup table?

[blackduck603]

I have an Access 2000 Form that is bound to a table (InventoryItems). One of the fields in my InventoryItems table is ItemSubTypeID. I have a separate "lookup" table (ItemSubTypes) which contains the follwoing fields:
ItemSubTypeID - Autonumber
ItemSubTypeDesc - Text
ItemTypeID - Number - references ItemTypeID in ItemTypes table.

I have a combo box on my bound form and the combo is named comboItemSubTypes. This combo is bound to the ItemSubTypeID field in InventoryItems. I also use this combo to allow the SubType to be changed. The combo should list the appropriate SubTypes based on the ItemType combo. So....in my comboItemTypes AfterUpdate handler, I set the rowsource of comboItemSubTypes to get the list of subtypes for the selected type. Here is the SQL:

strSQL = "SELECT ItemSubTypes.ItemSubTypeDesc, ItemSubTypes.ItemSubTypeID" & _
" FROM " & _
" ItemSubTypes" & _
" WHERE " & _
" ItemSubTypes.ItemTypeID = " & Me.ComboTypes.Column(1) ' & _

This works fine and dandy, but I need the combo to display the SubType for the current InventoryItem record.

I think the problem that I am having is because I want to display the DESC NOT the ID (ItemSubTypeDesc NOT ItemSubTypeID), BUT the combo is bound to the ID field. I don't think the combo can associate the ID with it's list of items.
For example:
With comboTypes = Electronics
comboSubTypes has the following items:
ID: 5 --> DESC: Pagers
ID: 6 --> DESC: PDAs
ID: 9 --> DESC: PCs
When my InventoryItems field is 6, I want to the combo to display PDAs (NOT 6). I have tried hiding column 1, but the combo still lists the ID value (numeric) - but when I click on the combo, I see the list of electornics sub-types.

I had it working when I was storing the ItemSubTypeDesc in my InventoryItems table instead of the ItemSubTypeID, but that jsut doesn't seem like a good db desing.


I appreciate any help with this.....

 

[MajP]

double check these fields:
bound column = 2
column count = 2
column widths = column1size";0"

I think the confusion is that most people return the hidden field first, but you have Desc as the first column and the ID second.

The more standard way is:
SELECT itemSubTypes.ItemSubTypeID, ItemSubTypes.ItemSubTypeDesc...
bound column = 1
column count = 2
column widths = 0"; column2size