Populating Combobox columns with SQL

[Keith Nichols]

I am trying to improve the performance (loading time) of my main form. The big tip seems to be use SQL in VBA to populate subforms and comboboxes when needed, rather than when the main form loads.

I have a combobox configured for 2 columns, bound column = 1 and column widths set at 2cm each. After a struggle, I have managed to get the combo box to populate with SQL, but only for the first column. My second column appears when I click the down arrow but there is no data showing.

How do I tell the SQL to place the 'Identifier' data in the second column of my combobox?

Here is the subroutine populates the combobox when the subform loads:

Private Sub Form_Load()
On Error GoTo ErrHandler
[COLOR="Green"]'=========================================================================
'Populate cboEmployeeID with SQL when form loads
'This is done to reduce time taken to load parent form
'=========================================================================

'Define the SQL text[/COLOR]    
Dim stSql As String
        stSql = "Employee_ID, Identifier, Section_ID, Office_Phone_Ranking, Role FROM [tbl_Prj_Details]"
        stSql = stSql & "WHERE (((tbl_Emp_Details.Section_ID)=2) AND ((tbl_Emp_Details.Role)='Technical'))"
        stSql = stSql & " ORDER BY tbl_Emp_Details.Office_Phone_Ranking;"

[COLOR="green"]'Populate combo box with SQL[/COLOR]
    With Me![cboEmployeeID]
        .RowSource = stSql
        .Requery
    End With

ExitHere:
    Exit Sub

ErrHandler:
    MsgBox Err.Number & " - " & Err.Description & Chr(13) _
    & Chr(13) & "Error in 'fsubPrjDet01EDT1': Err 003"
    Resume ExitHere
End Sub

Any help/pointers gratefully received .

 

[FoFa]

Can't you effectivly do the same thing by disabling those controls? I thought if they were disabled, the query for the control would not run. Then when needed you can enable them and maybe have to requery them at that time?
Just thinking outloud. Enabling and disabling is much easier.
Anyone else?

 

[Keith Nichols]

FoFa,

that does sound much easier. I will try it right now and post back if it works.