Combo box does not display all the records in query...

[colezpapa3]

I am having wierd behavior in a combo box that is bound to a pass-thru query. Sometimes when you search the drop down, I don't get all the records from the query...It will show a small number. If I repair the database, then go in, I see all the records. Something is corrupting the query and I havent a clue as what it could be. Any ideas.

 

[jordanking]

if you are using a pass through, what is your backend DB? Are you calling the recordset from VBA in the open event of the form and assigning it to the combo box, or have you just placed a query string in the rowsource value of the combo box?


.....
I'd rather be surfing

 

[colezpapa3]

Hi thanks for responding.
The backend is DB2, and I placed a query string in the rowsource value of the combo box. If I use a table, it works fine, but I would rather use the query because it would reflect real time info...and I would not have to run a make table.

 

[jordanking]

how long does it take to execute the query?

Try the following code in the on open event of the form to see if it solves your problem, it will force the recorset to be complete before the rowsource is set. I use this for combo boxes of ~25 thousand records and it works fine.
(this code is based on a sql server 2005 backend using ADO)
you will have to ammend for your own connection string variables.

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim sqlCust As String

    'initialise connection string and open connection
    cnn = CurrentProject.Connection
    cnn.Open
    
    'set the list values of the combo box for customers
    'Replace my sql string with the one you placed in the rowsource property of the combo box
    sqlCust = "SELECT * FROM dbo.vw_Customer_Active ORDER BY Company, Client"

    'intCustomerID is the name of the combo box control on the form
    Me.intCustomerID.RowSource = sqlCust
    
    ' Close the connection.
    cnn.Close

.....
I'd rather be surfing

 

[colezpapa3]

Beautiful, simply beautiful, thanks for the routine.
I changed it so suit DAO. Works like a charm.