Populate DBCombo at run time

I have a simple form with a DBCombo that I want to populate from a field from and SQL database. I can make the connection ok, but can't get anything but the first record to add to the combo. Here is what I have so far.

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Dim vSQL As String
Dim vUsers() As String
Dim x As Integer
    Set rs = New ADODB.Recordset
    GetServerFromReg 'Gets server connection info from registry *WORKING*
    Open_Conn 'This opens the connection *WORKING*
    vSQL = "SELECT pUser from tblMembers"
    rs.Open vSQL, intCon, adOpenStatic, adLockOptimistic, adCmdText
    Set cmbUser.DataSource = rs 'Seems to be working

'**This is where I think I am missing something unless I am totally on the wrong track.

    cmbUser.Refresh
End Sub
nacilepAsked:
Who is Participating?
 
ShauliCommented:
Private Sub Form_Load()
Dim rs As ADODB.Recordset
Dim vSQL As String
Dim vUsers() As String
Dim x As Integer
    Set rs = New ADODB.Recordset
    GetServerFromReg 'Gets server connection info from registry *WORKING*
    Open_Conn 'This opens the connection *WORKING*
    vSQL = "SELECT pUser from tblMembers"
    rs.Open vSQL, intCon, adOpenStatic, adLockOptimistic, adCmdText
    With rs
        Do Until .EOF
            cmbUser.AddItem !pUser
            .MoveNext
        Loop
    End With
    rs.Close
End Sub

S
0
 
nacilepAuthor Commented:
That would work with a normal combobox, but the recordset is > 500 and that is extremely slow. I was hoping that using a data bound control would speed up the process.
0
 
ShauliCommented:
The speed really depends on a) your connection, b) the aql statement. 500 or so sould be slow.

S
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ShauliCommented:
Shouldn't be slow :)

S
0
 
ShauliCommented:
btw, I assume that the major consuming time is opening the connection, and not loading the data to the combo.

S
0
 
nacilepAuthor Commented:
Opening the connection takes no time at all.

It is the loop for populating a normal combobox. I am trying to use a DBCombo which can't use the .AddItem method.

0
 
ShauliCommented:
Well, the only way I know to have all records in the combo is to loop through the records.

S
0
 
nacilepAuthor Commented:
Well, this does seem to be the only answer I could find. I had it working that way before, but since I didn't clarify that, points awarded.

I'll ask another question on this, but when I do the combo update that way the form basically 'freezes' (I put a progress bar in to show the user how long is left). Is this easily fixable?
0
 
ShauliCommented:
Yes, add DoEvents to the sub, as in:

 Private Sub Form_Load()
Dim rs As ADODB.Recordset
Dim vSQL As String
Dim vUsers() As String
Dim x As Integer

DoEvents '<<<<<<<<<<<<<<<<<<<<<<<<<<

    Set rs = New ADODB.Recordset
    GetServerFromReg 'Gets server connection info from registry *WORKING*
    Open_Conn 'This opens the connection *WORKING*
    vSQL = "SELECT pUser from tblMembers"
    rs.Open vSQL, intCon, adOpenStatic, adLockOptimistic, adCmdText
    With rs
        Do Until .EOF
            cmbUser.AddItem !pUser
            .MoveNext
        Loop
    End With
    rs.Close
End Sub

S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.