Combo Box Trouble

By carterlangley ·
Hi guys,

Here is code I am using to populate a combo box from the table.

Set cnnConnection = CurrentProject.Connection
Set rstRecordset = New ADODB.Recordset
strSQL = "SELECT * FROM Country ORDER BY Country"
rstRecordset.Open strSQL, cnnConnection, adOpenDynamic, adLockOptimistic

With cboCountry
.RowSourceType = "Value List"
Do Until rstRecordset.EOF
.AddItem rstRecordset.Fields("Country").Value
End With

Set rstRecordset = Nothing

Now, I would like to be able to store the id field (autonumber field) related to the country name in the table. How do I get that unique id field from the combo box once a user makes a selection? the actual value or name means nothing to me, I need the unique id that identifies that specific selection.

I only want the new record to be added to the table once the user presses a button on the form. so my code will cycle through all the textboxes, etc and gather the necessary to write to the table. only having a problem with the combo box story at present.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

With difficulty seeing as you have't put it in.

by Tony Hopkinson In reply to Combo Box Trouble

Two ways of doing this, not counting binding.

First one is see if there are other add methods. VB6 lets you pass in an object so you can create a country class fill that from the database and then add it it to the combo.
If you can't do that and the item order in the combo does not change, then populate an array or list of Id and Name then use that to populate the combo. Then selected index on the combox will = the index in 'CountryList'

Collapse -

ComboBox Trouble

by carterlangley In reply to With difficulty seeing as ...

Thanks for the tips.

I think the array will be the way to go. The item order in the combo box is not going to change once loaded. The only problem I have is it has been over 7 years since I have done any programming at all and I just don't seem to remember how to do things anymore. Could you please provide me with some sample code? I would be most gratefull!!

Collapse -

Multi-Field Combobox

by chuckwolf In reply to ComboBox Trouble

Why not use a multi-field combobox? You could have the first element of each row be the Index but not displayed--set width to zero.

Collapse -


by carterlangley In reply to Multi-Field Combobox

Now WHY?? did I not think of that!!
Amazing how simple some things can be if you just know what to do!

Collapse -

Use Item data property of combo box to store ID

by murtuzahbookwala In reply to Combo Box Trouble

ItemData is a hidden value for each value
in the ComboBox.

ItemData can be used to store a key value
while a more descriptive text value is displayed
in the ComboBox.

You cannot rely on the ListIndex to determine which
text value was selected, especially if the list has been sorted.

In the example on the right, each city has a
code in the database.
These are used as the ItemData values.

NewIndex is the index of the item currently being
added to the ComboBox.

In this example, the NewIndex value is replaced
with the city code and set as the ItemData value.

Build a ComboBox or ListBox with Actual Data

cboBox.AddItem "Chicago"
cboBox.ItemData(cboBox.NewIndex) = 20

cboBox.AddItem "Houston"
cboBox.ItemData(cboBox.NewIndex) = 21

cboBox.AddItem "Philadelphia"
cboBox.ItemData(cboBox.NewIndex) = 22

cboBox.AddItem "San Antonio"
cboBox.ItemData(cboBox.NewIndex) = 23

Here's how to get the ItemData for the selected item.

cboBox.ItemData(cboBox.ListIndex) = 21
cboBox.List(cboBox.ListIndex) = "Houston"

ListIndex is the ComboBox index of the item selected.
The first statement returns the ItemData value for the selected item.

If Houston is selected, and Houston's ListIndex is "1",
then the ItemData for ListIndex "1" is "21".

cboBox.ItemData(cboBox.ListIndex) = 21

The List property of the ComboBox is the set of text values.
We retrieve the selected value using the ListIndex of the List array.

cboBox.List(cboBox.ListIndex) = "Houston"

To update a table with a selected key value, use the ItemData value.

If the user selected "Chicago" and we need to enter "20"
into a table, use the following code:

tblAddress.City = cboBox.ItemData(cboBox.ListIndex)

Collapse -

Combo Box Trouble

by carterlangley In reply to Use Item data property of ...

You are an absolute LIFESAVER!!!!

I knew there was a simple way to do it, just could not for the life of me remember. I had written some make do code to get around my problem for now, but I am definitely going to change it for what you have shown me here. This is exactly what the doctor ordered!!

Collapse -

ItemData property is old technology

by chuckwolf In reply to Use Item data property of ...

The ItemData property necessity went away with VB 2005 (and later ItemData was necessary in the days of listboxes and comboboxes that stored only one text value in the list and one integer value in the corresponding ItemData row. ItemData is no longer supported because it is no longer needed. Lists can now contain multiple values which can be of different data types. So, one way of omulating the ItemData method follows:
cboBox.AddItem rst.CityID
cboBox.List(cboBox.ListCount - 1, 1) = rst.CityName
The first element of a new row in the list needs the AddItem Method. Once the new row in the list is created, you reference the next item in the row by the List() assignment (note the zero-based counting). And, remember you can display all or only one of the elements per row--the Index, or CityID in this example, is a good candidate to hide.

Collapse -

cboCountry Recordset Property

by albin.moroz In reply to Combo Box Trouble

Why not use the recordset property to populate the combobox?

Set Me.cboCountry.Recordset = rstRecordset

Collapse -

Bound Controls are Unstable

by chuckwolf In reply to cboCountry Recordset Prop ...

I have not been very satisfied with bound controls (using the Record set property). Too often (once per year is really too often in some cases), I've had to re-establish the record set property at run-time when using bound controls so I gave up on it years ago. Maybe it is more stable now but looping and assigning values to a control has never failed me.

Collapse -

Same here

by Tony Hopkinson In reply to Bound Controls are Unstab ...

Go down this route and you can easily end up with sizeable percentage of your logic in the UI.

Disconnected datasets, I can live with, but connected ones, cause more problems than they solve, the coupling is too tight across too many layers.

Related Discussions

Related Forums