dropdown question?

[mapperkids]

Hi,

I have a customer table that contains two fields customer ID, customer name, what i want to do is having a dropdown box for user to pick the customer name, but when save the data back to the database

I want to save the customer id value instead of the customer name.

Here is the code how I populate the dropdown from database when the form is loaded

p=0
Do Until customer_table.EOF()
Cbocompany.AddItem rs![customername], p
p = p + 1
rs.MoveNext
Loop


Could anyone help me how to do that in dropdown box.

Thanks

[Hack]

Does your query select both fields so both are in the resulting recordset?

[Static]

in VB6 or Access?

[mapperkids]

Hi,

Yes both fields are in result recordset and it is vb6 application

Thanks!

[si_the_geek]

When you put data into the control, also put the ID into the ItemData property.

You can see an example of how to do that in the Database FAQ How can I fill a ComboBox/ListBox with values from a database?

Then when you save, simply save the ItemData

[Hack]

Then try

cbocompany.Additem RS.Fields.Item("customername").Value & " " & RS.Fields.Item("customerid").Value

[Static]

does the user NEED to see the id? or just the name....
you could store the ID in itemdata

[mapperkids]

does the user NEED to see the id? or just the name....
you could store the ID in itemdata

I only want the use see the customer name, not the id.

[Static]

cbocompany.itemdata(row) = id

each row can have its own itemdata.. so as you loop through and add the name, set the itemdata for that row

[mapperkids]

I use the code example to populate the dropdown, so the dropdown get populate with two fields value in it, but how can I tell which is the row that user pick then update the customer id back to the database

cbocustomer.itemdata(index) ?

[Static]

cbocustomer.itemdata(cbocustomer.listindex)

or is it index? selected? hmmm.. i dont have VB with me to check... lol

[Hack]

Based on what you were doing here

p=0
Do Until customer_table.EOF()
Cbocompany.AddItem rs![customername], p
p = p + 1
rs.MoveNext
Loop

I thought you wanted to see both.

[mapperkids]

Hi,

based on the code example from DATABASE FAQ mentioned above, I did populate both customer name and customer id to the dropdown box. the customer id is store on itemdata of the dropdown.

Now, i want to put the data - customer id back to the database. how do I know which one is the user picked from the dropdown and write it back to the database.

I think I need to put some code in the dropdown box CHANGE/CLICK events?

Thanks!

[Hack]

Click event

[si_the_geek]

Static was correct, to read the data from the ItemData you would use:

Cbocompany.itemdata(Cbocompany.listindex)

..as to where (and how) you would use it, that depends on what you want.

[mapperkids]

Hi,

Cbocustomer.itemdata(Cbocustomer.listindex) works, but the problem is when I pick the first one from the list which means the listindex is 0, it doesn't contain the customer ID for in the itemdata(0), just return the 0 and it crash my application.


Here is the code that I'm using

====================================
To Save the data back to the file

CboCustomer.ItemData(CboCustomer.ListIndex)
====================================

To populate the dropdown

Call FillCombo(CboCustomer, Conn1, "SELECT * FROM customer_tbl order by customer_name", "customer_name", "customer_id")


==================================================


Sub to fill the combon box


Public Sub FillCombo(objComboBox As ComboBox, _
Conn1 As ADODB.Connection, _
strSQL As String, _
strFieldToShow As String, _
Optional strFieldForItemData As String)

Dim oRS As ADODB.Recordset  'Load the data
Set oRS = New ADODB.Recordset
oRS.Open strSQL, Conn1, adOpenForwardOnly, adLockReadOnly, adCmdText
With objComboBox 'Fill the combo box
.Clear

If strFieldForItemData = "" Then

Do While Not oRS.EOF      '(without ItemData)
.AddItem oRS.Fields(strFieldToShow).Value
oRS.MoveNext
Loop
Else

Do While Not oRS.EOF      '(with ItemData)
.AddItem oRS.Fields(strFieldToShow).Value
.ItemData(.NewIndex) = oRS.Fields(strFieldForItemData).Value
oRS.MoveNext
Loop

End If

End With

oRS.Close                 'Tidy up  Set oRS = Nothing End Sub

End Sub

===================================================

Any idea?

Thanks!

[Hack]

When you save "crash the application" I assume that means you get a run time error.

What is the error?

[mapperkids]

Hi,

The run time error is related to the foreigen key not match on the SQL tables, since when I write the data back to the record, I have to put the customer ID from the itemdata(index) to the field in the customer iD field and
since it contain the value as 0, the relationship between the table is broken and that is the runtime error comes up.

When you save "crash the application" I assume that means you get a run time error.

What is the error?