How to Filter on a List box

[jamesnkk]

I have a listbox which show customer Names on the 1st column and PO numbers on the 2nd column.
When user select the customer and press the OK button, it will access to the PO form.

Problem here is the list will grow longer and longer and I need to scroll up and down to look for the correct PO.

I would like to create a textbox below the listbox form, so when user type the customer name say 3 characters, it will filter only the customer started with 3 characters ,when user type the PO number say 3 char,it will filter all Po started with this number, so the list will be shorter and easy for user to see and select the PO.

I have tried using Filter on, realize that Listbox doesn't allow me to filter like below statement.

Me.Filter = "[customer] Like '*" & Me.txtCust & "*'"
Me.FilterOn = True

Do you have any better idea on how I should I do it, I don;t want to use the query expression that popup and display in a datasheet view, and user can change the data easily.

I am sure, the expert out there can enlighten me.

[PEB]

Hey in the combo boxes it can be done a bit easier!

If u have a combbox and u type the first 3-4 characters the list is limited to the entries that begin with those characters!

But however maybe u want to keep the list?

I have a listbox which show customer Names on the 1st column and PO numbers on the 2nd column.
When user select the customer and press the OK button, it will access to the PO form.

Problem here is the list will grow longer and longer and I need to scroll up and down to look for the correct PO.

I would like to create a textbox below the listbox form, so when user type the customer name say 3 characters, it will filter only the customer started with 3 characters ,when user type the PO number say 3 char,it will filter all Po started with this number, so the list will be shorter and easy for user to see and select the PO.

I have tried using Filter on, realize that Listbox doesn't allow me to filter like below statement.

Me.Filter = "[customer] Like '*" & Me.txtCust & "*'"
Me.FilterOn = True

Do you have any better idea on how I should I do it, I don;t want to use the query expression that popup and display in a datasheet view, and user can change the data easily.

I am sure, the expert out there can enlighten me.

[PEB]

Me.Filter = "[customer] Like '*" & Me.txtCust & "*'"
Me.FilterOn = True

for the list u should try this Mylistbox is the name of your list! and imagine that for your list you get the information from mytable!

Me!Mylistbox.RowSource= "SELECT * FROM mytable Where [customer] Like '*" & Me.txtCust & "*';"

[jamesnkk]

for the list u should try this Mylistbox is the name of your list! and imagine that for your list you get the information from mytable!

Me!Mylistbox.RowSource= "SELECT * FROM mytable Where [customer] Like '*" & Me.txtCust & "*';"

Wow !, thank you so much, You are really an Expert ! Yes It Work, Hoorah !

[jwolfley]

I have a really stupid question. When you say add;

Me!Mylistbox.RowSource= "SELECT * FROM mytable Where [customer] Like '*" & Me.txtCust & "*';"

Where exactly do you add that? I go to my combo box properties and then is this added as VBA through an expression or in the row source or what?

Thanks!

[Rabbit]

From what it looks like to me you'd add it to the After Update event of a text box control.

What happens is they'll type part of the name into a text box. The code will run and change the names that are displayed in the listbox.