Desktop

Ask something
Question Locked

Combo Box Trouble

0 helpful
12 replies

by carterlangley 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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
rstRecordset.MoveFirst

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

rstRecordset.Close
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.

12 replies

by tony hopkinson

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'

by carterlangley

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

by chuckwolf

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.

by carterlangley

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

by murtuzahbookwala

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)

by carterlangley

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

by chuckwolf

The ItemData property necessity went away with VB 2005 (and later VB.net). 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.

by albin.moroz

Why not use the recordset property to populate the combobox?

Set Me.cboCountry.Recordset = rstRecordset

by chuckwolf

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.

by tony hopkinson

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.

by tony hopkinson

as it's disconnected, otherwise no. It means you have to put the business logic in the storage layer or more likely in the UI. Either one, ends up being an inflatable dartboard.
Just something as simple as the order of the items in the control, or wanting an 'All' selection at the top, can have you writing reams of code just to keep using the binding and save yourself writing about three lines of code.

This discussion is closed to new replies.