Populate List Box with QueryDefs

[sandra45]

Hi All, I need urgent help here. I need to populate a listbox based on data input by users. I have many textboxes for inputting the data by users. Then these data will be set as parameters for the QueryDef. Below code works fine but the problem is: it takes so long time to populate the result on the list box. Can anybody let me know if there is a better way to do this? Thanks very much.

Dim qryQuote As DAO.QueryDef
Dim rec As DAO.Recordset
Set qryQuote = CurrentDb.QueryDefs("qry_ALL")
qryQuote.Parameters!test = Me.NGOName
qryQuote.Parameters!test1 = Me.DName
Set rec = qryQuote.OpenRecordset()
Me.lstResult.RowSource = rec("NGOName")
If rec.RecordCount <> 0 Then
With Me.lstResult
.RowSource = ""
Do Until rec.EOF
'TAKES AGES IN POPULATING RECORDS HERE
.AddItem """" & rec("NGOName") & """;""" & rec("DName") & """"
rec.MoveNext
Loop
.Requery
End With

Regards,
Sandra

 

[PHV]

With recent version of access you may try this:
Set Me!lstResult.Recordset = rec

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sandra45]

Hi, thanks very much, it works fine and faster. I have another problem now,
if the parameter is "" in this line of code e.g.
qryQuote.Parameters!test = Me.NGOName
error message will show up.

Do you know how to deal with this? if one parameter is "", then the query will only look for record for filled parameter. Thanks.

Regards,
Sandra

 

[PHV]

You may try this:
qryQuote.Parameters!test = Nz(Me.NGOName)
Why not post the WHERE clause of qry_ALL ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244