Listbox slow to fill 1

[ZOR]

I have a listbox that gets its rowsource from a query. In some instances it can take 1 min 10 seconds to fill. If the user uses various filter options on the list, it is quick to respond. However, if the user wants to undo his filtering, ie return the list to as it was, the same delay to refill takes place. Is there a way to avoid the second process delay?, ie hold a recordset or something that would refill the list again? Thanks

 

[JoeAtWork]

Is the query itself very slow?

And yes, assuming the records in the query are not expected to change while the user is on the form, you could load it once into a recordset, and set the listbox's data source to the recordset.

 

[ZOR]

Many thanks, so I will try and remember how to get a recordset from a VB query. Regards

 

[JoeAtWork]

Here's a snippet of code for setting a listbox to an ADO recordset. The thing to remember is to set the recordset's CursorLocation to adUseClient.

    Set rsList = New ADODB.Recordset
    
    rsList.CursorLocation = adUseClient
    
    rsList.Open adoCmd, , , adLockReadOnly
    
    Set Me.lstInvOrder.Recordset = rsList

 

[ZOR]

Thanks for the code, I am a little lost as to how rsList gets it's data from my SQL/VB query. I will have a play around and see what transpires, but thanks again.

 

[JoeAtWork]

Here's the entire function to put it in context.

Private Function Search_OrderList_SQL(Optional Criteria As String = "")
    Dim dteTEMP As Date
    Dim strRowSource As String
    Dim strSearchFor As String
    Dim WHERE As String
    Dim adoConn As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim adoParam As ADODB.Parameter
    Dim rsList As ADODB.Recordset
    
On Error GoTo ErrHandler:
    
    HourglassMouse
    
    'Work around for Access bug, if there are some rows currently selected, and
    'list is requried that has a small number of items, it shows some empty rows
    'as selected.  Therefore, make sure everything is unselected before
    'requerying
    SelectAll False
    
    strSearchFor = Trim(Criteria)
    If Len(strSearchFor) = 0 Then
        strSearchFor = Trim(Nz(txtOrderSearch, ""))
    End If

    Set adoConn = New ADODB.Connection
    adoConn.Open GetSQL2005ConnString
    
    Set adoCmd = New ADODB.Command
    adoCmd.ActiveConnection = adoConn
    
    adoCmd.CommandText = "GetInventoryList"
    adoCmd.CommandType = adCmdStoredProc

    adoCmd.Parameters("@ProductSearch").Value = strSearchFor
    adoCmd.Parameters("@SupplierID").Value = Nz(cboOrderSupplier, 0)
    adoCmd.Parameters("@ShowBelow").Value = Nz(chkBelow, False)
    adoCmd.Parameters("@ShowOpen").Value = Nz(chkOpen, False)
    
    Set rsList = New ADODB.Recordset
    
    rsList.CursorLocation = adUseClient
    
    rsList.Open adoCmd, , , adLockReadOnly
    
    Set Me.lstInvOrder.Recordset = rsList
    
    adoConn.Close
    Set adoConn = Nothing
    Set adoCmd = Nothing
    
    'Only allow the "Generate All Required Orders" to be clicked if
    'a single supplier's orders are being viewed
    cmdGenerateAllOrders.Enabled = (Nz(cboOrderSupplier, 0) <> 0)
    
    'Unselect all rows currently selected
    SelectAll False

ExitRoutine:
    DefaultMouse
    Exit Function
    
ErrHandler:
    ReportError Err, Err.Description, "While filtering"
    GoTo ExitRoutine
   
End Function

 

[ZOR]

Many thanks, your an early starter?, did not expect a reply so soon. That helps a lot, many thanks again.

 

[JoeAtWork]

>> your an early starter?

Ha ha, hardly! More like a late-nighter!