Requery on Listbox as you type? 1

[glxman]

Hi

I have a form with a list box showing a lot of records. I have set up a text box to enter start of field data to requery the list box and reduce the matches. I can get this to work fine with a seperate button with a requery macro on the OnClick event. But is there anyway that I can get the list box to requery as the data is typed into the text box? - like the old help files in MS Office.

I tried putting the requery macro in the On Change event of the text box, but it doesnt work, as I think it has to lose focus to register the data. Also tried some of the other possible events with no luck.

Any advice greatly appreciated.

Thanks
Rich

 

[nickdel]

Use the 'KeyUp' event. This wont be very efficient though!

 

[PHV]

In the Change event procedure of the textbox use the .Text property to rebuild the RowSource of the listbox.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgramError]

Why not change the textbox to a combobox which will automatically jump to the first similar item as you type.

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[glxman]

Thanks but doesn't work. It does seem to run the requery macro after each character is input (like the On Change), but resulting in all the records still showing in the listbox.

I may well just leave it with the button click as that works ok, it was just that a user asked me if this was possible.

Thanks
Rich

 

[PHV]

What is the SQL code of the RowSource property of the listbox ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glxman]

Hi PHV, could you give an example of the code I would write for the .Text property to rebuild the RowSource. Sorry but my VBA knowledge is not great.

Thanks
Rich

 

[PHV]

What is the actual SQL code for the listbox ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glxman]

Hi PHV

SQL Code for the RowSource of the listbox:

SELECT DISTINCTROW qryAnnexN_CodeCount.[Part Number], qryAnnexN_CodeCount.NSN, qryAnnexN_CodeCount.[Active / Inactive], qryAnnexN_CodeCount.A, qryAnnexN_CodeCount.C, qryAnnexN_CodeCount.D, qryAnnexN_CodeCount.P, qryAnnexN_CodeCount.I FROM qryAnnexN_CodeCount ORDER BY qryAnnexN_CodeCount.[Part Number];

 

[PHV]

Which match you want to reduce ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glxman]

Part Number

 

[glxman]

On the underlying query there is the following on the [Part Number] criteria:

Like [Forms]![frmzzMainForm]![Text34] & ("*")

 

[PHV]

In the Change event procedure of Text34:

Me![your list box].RowSource = "SELECT [Part Number], NSN, [Active / Inactive], A, C, D, P, I" _
 & " FROM qryAnnexN_CodeCount WHERE [Part Number] Like '" & Me![Text34].Text & "*'" ORDER BY 1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glxman]

Great it works, and it's very efficient - even with over 20,000 records!

I had to remove the ORDER BY 1" at the end though, as it was giving a compile error.

Many thanks for your help!

Rich

 

[PHV]

Sorry for the typo:
... & Me![Text34].Text & "*' ORDER BY 1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glxman]

Thanks PHV, amendment works although the code works fine without the ORDER BY 1.

I guess it means to order records by column 1, like "ORDER BY [Part Number]" ?

Thanks
Rich