-
I have a form with a text control and a ADO datacombo box on it.
In the form_load event I created two ADO recordsets.
Recordset1 (rsContacts) reads data from the main table called Contacts
Recordset2 (rsContactTypes) reads data from a reference table called ContactTypes.
Both tables are part of an Access 97 database
After instantiating the recordsets I bind the textbox and the combobox with the following code
...
With dbcmbContactTypes
Set .DataSource = rsContacts
Set .RowSource = rsContactTypes
.BoundColumn = "ContactTypeID"
.Datafield = "ContactTypeID"
.ListField = "ContactTypeDesc"
End With
With txtContactName
Set .Datasource = "rsContact"
.DataField = "ContactName"
End With
rsContacts.Movefirst
...
When I run the code, I see a value in the txtbox but the combobox stays empty. Both the list as well as the current selected item. I checked that all contacts have a valid ContactTypeId.
How can I get the combobox to fill up?
When I use ADO datasource controls to bind the combobox at design time all works well. However I don't want to use the datacontrols because they use up a connection per control.
This is a simple example but the application I am writting this for as about 10 of these look-ups. That would mean 10 separate connections were only one is required.
-
I found the solution to my problem.
The key is in the way you open the recordset for the reference table (contacttypes).
When opening this recordset you must open it with either adOpenKeySet or adOpenStatic.
so:
rsContactTypes.Open cmContactTypes, , adOpenStatic, adLockReadOnly
or
rsContactTypes.Open cmContactTypes, , adOpenKeySet, adLockReadOnly
Then it works just fine.
