Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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
0
nacilep
Asked:
nacilep
  • 6
  • 3
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now