I am trying to create a form which contains a combo box, the list elements of which are populated by one table (the fullname field from a table named users) - the value of which comes from another table (the rep_id field from a table named clients).
ie. the choice from the combobox should read and write to the rep_id field, but the other list values should come from the fullname field.
I have found the following example at MSDN which explains how to do this using two data sources:
However, I am trying to achieve the same result using ADO code. I have the following code but it errors when it reaches line 26:
1: ClientId = 1
3: Dim db As Connection
4: Set db = New Connection
5: db.CursorLocation = adUseClient
6: db.Open "PROVIDER=MSDASQL;dsn=dev;
8: Set adoPrimaryRS = New Recordset
9: adoPrimaryRS.Open "select client_id,client,contact1,
d from clients where client_id = " & ClientId, db, adOpenStatic, adLockOptimistic
11: Dim userdb As Connection
12: Set userdb = New Connection
13: userdb.CursorLocation = adUseClient
14: userdb.Open "PROVIDER=MSDASQL;dsn=call
16: Set adoUserRS = New Recordset
17: adoUserRS.Open "select fullname from users order by fullname", userdb, adOpenStatic, adLockOptimistic
19: Dim oText As TextBox
20: For Each oText In Me.txtFields
21: Set oText.DataSource = adoPrimaryRS
24: Set DBCombo1.DataSource = adoPrimaryRS
25: DBCombo1.DataField = "rep_id"
26: Set DBCombo1.RowSource = adoUserRS
27: DBCombo1.ListField = "fullname"
28: DBCombo1.BoundColumn = "fullname"
Any help would be much appreciated.