Listbox to requery as I type in a Textbox 1

[AndyHorn]

I have a form based on a table which stores peoples contact information. On this form I have a listbox that is based on a query. That query is setup to show anyone who has a postcode 'Like' what is in the current records 'Postcode' field.

What I want to happen is as the user is typing a postcode into the 'Postcode' field on the form, the listbox requeries itself after each character is entered, therefore reducing the number of results in the listbox.

I have tried using the following VBA code in the 'After Update', 'On Change' & 'On Key Up' events but none of them seem to requery the listbox unless I leave the 'Postcode' field. I want it to do it each time after I've entered a character.

Private Sub Postcode_Change()
Me.List233.Requery
End Sub

This is the SQL code from my query:

SELECT [Main Contact Data].ID AS [Customer Number], [Title] & " " & [Firstname] & " " & [Surname] AS Name, [Main Contact Data].Postcode
FROM [Main Contact Data]
WHERE ((([Main Contact Data].Postcode) Like [Forms]![AlexLightDatabaseNEW]![Postcode] & "*"))
ORDER BY [Main Contact Data].Postcode;

The whole idea of this is to try and stop the users from entering the same person twice into the database without having to do a tedious search each time they enter somebody.

Any suggestions would be appreciated. Thanks.

 

[RoyVidar]

I think you'd need to try something like this:

[tt]Private Sub Postcode_Change()
Me!list233.Rowsource="SELECT [Main Contact Data].ID AS [Customer Number], [Title] & ' ' & [Firstname] & ' ' & [Surname] AS [Name], [Main Contact Data].Postcode
FROM [Main Contact Data]
WHERE ((([Main Contact Data].Postcode) Like '" & Me!Postcode.Text & "*'))
ORDER BY [Main Contact Data].Postcode;"
Me!List233.Requery
End Sub[/tt]

- the SQL on one line, or if you wrap, mind the concatination (thread705-833660)

i e - using the .Text property of the postcode control, as opposed to the default .Value property (which is what the stored query retrieves) and assign thru VBA. Wouldn't be surprised if it flickers a little, reduced performance...

Roy-Vidar

 

[lupins46]

Personally I would have a separate input textbox and base the rowsource on this textbox. There just feels like too much opportunity for confusion of events when doing it in ther same control.

 

[AndyHorn]

Works perfectly. Thanks RoyVidar.

Thanks for your comment lupins46, I'll keep it in mind. I'll see how it goes.

Thanks again.

 

[spitzmuller]

Well almost a year later, this thread has helped one more guy. Thanks to RoyVidar and this great Forum!