Almost but not quite - requery listbox problem (1 Viewer)

[Pepperhed]

Hi all - first post for me, a long-time user of the site though!

I have a form (frmEnquiryFor) where so far everything works almost as I want it! I have a text box (txtCompanyName) and below it a list box (lstCustomer). What I want to happen is that as a user types into the textbox, the listbox below updates on each keystroke. At the moment, this doesn't quite happen: what happens currently is that if the user types into the listbox and then hits return, the listbox updates as it should.

The listbox's rowsource is based on the SQL statement:
"SELECT [qryCustomerLookup].[CustomerName], [qryCustomerLookup].[CustomerCode] FROM qryCustomerLookup; "

The query underlying this, qryCustomerLookup, works as it should. What it does is if the user has typed in nothing, it returns all the Customer names and customer codes. If the user types in "a", it returns all the customer names and codes of customers beginning with "a". If the user types in "ab", it returns them all from customers beginning with "ab". (In case you need it, the SQL for this query is as follows:
"SELECT tblCustomers.CustomerName, tblCustomers.CustomerCode
FROM tblCustomers
WHERE (((tblCustomers.CustomerName) Like [Forms]![frmEnquiryFor]![txtCompanyName] & "*")) OR (((([tblCustomers].[CustomerName]) Like [Forms]![frmEnquiryFor]![txtCompanyName] & "*") Is Null));"

The final bit of code is just a bit of VBA - "Me!lstCustomer.requery" - that I have tried putting in various places. I have put this code in the "after update" event of txtCompanyName, and this comes close to working. What this does is update the listbox correctly, but only after the user hits the enter key after entering a letter or two. What I want is to find a way to make the listbox update after each keypress.

I've tried putting this requery code in the keypress event, the keyup event, the onchange event, all to no avail. I think the problem may lie in the timing of the event firing, but I'm not sure.

So - any ideas?!?!

 

[draaYak]

Updating a Listbox based on a the value of a Textbox

Hi,

If you put this code into the Change event and OnEnter event, your listbox should update (like the help screen in Windows) and without a lag.

Hope this is helpful.


This updates the listbox based on the textbox value.

Private Sub TextBox_Change()
Me.ListBox.SetFocus
Me.TextBox.SetFocus
Me.ListBox.Requery
End Sub


This places the curser at the end of the text box.

Private Sub txtTextBox_Enter()
Me.TextBox.SelStart = Me.TextBox.SelLength
End Sub

 

[24sharon]

I Try Do This Code

But my problem is with space

for example if I write "hello " (with space)
the space delete

how can I fix it?
thanks!

 

[24sharon]

and to see the example here

http://www.access-programmers.co.uk/forums/showthread.php?t=54244&highlight=KeyPress