ComboBox Recordset

[Graeme Richardson]

Hi, I'm experiencing some problems with a unbound form with ~150 controls
(Access 2003 generates "Can't open any more tables" error after a few
minutes of use)

10 of the combobox controls use the same data source so I've implemented the
following code in order to work around the problem.

Dim cnn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.CommandText = "qfltMember_SurnameForename"
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc

Set rst = New ADODB.Recordset
rst.Open cmd, , adOpenKeyset

For intCount = 1 To 10
Set Me("cboFkTeamMember" & Format(intCount, "00")).Recordset = rst
Next

Avantage:
1) Problem goes away (appears that way anyway)
2) Speed is noticably improved (very good)

Problems:
1) Fields are reordered alphabetically when recordset is assigned to the
combobox - I've overcome this by aliasing the fields with A, B, C, ... in
the order I require but am not proud of this fix
2) Bound field is not obeyed! I've discovered that setting BoundField to
0 will bind the row index, though am looking to bind to column 1 of the
recordset.

Question:
Resolution to either of the problems would be appreciated.

Thanks, Graeme.

 

[Penguin]

First, if you have a form with 150 controls then you need to rethink
your form design. That many controls on a form will confuse the users.