list box refresh/requery

[JaredNJames]

hi, i have a simple database storing customer information.

i currently have a search page with a list box on it which lists all of the customers held within the database. now what i would like, is to have it so that when the form loads, there is a complete list of customers, and then if the user chooses, he can use the text box to enter all or part of a surname, press find and then only the appropriate records show in the list box.

any ideas much appreciated.

jared james

 

[JaredNJames]

anything guys? im sure you can do it or something similar?

 

[Kempes]

OK, Create a new query containing the names of the customers or use the current query, (not the table). Save it.

Open your form, change the list box so it feeds from this new query. (let say your list box is called list0 on the form)

Add a command button to your form and add the following code for the on click event.

me.list0.requery

You mention that the user keys into a text box. Lets say that text box is called text0 (unbound) for the purpose of this, and your form is called form0.

go back into the design of your query, and in the criteria for customer name, enter your equilivant to the following.

Like "*" & [Forms]![form0]![text0] & "*"

now open your form and key all/part of a name then click the button.

This should now work.

Basically, all your doing is refreshing the query based on the value keyed into text0 on the form.

Any trouble, let me know

 

[JaredNJames]

hi, yes i have looked at using a query, but i found that until you enter something into the text box and press the button it doesnt show anything in the list (isnull).

i havent used the Like function in a query, but would this allow all records to be shown on form load and the user to search using "text0"?

many thanks for the reply
jared james

 

[Kempes]

The answer is , yes, all records will show in the list until you submit your criteria.

Like "*" basically means show everything. If you enter nothing in text0, the query will know to show all records.
if you then add something in text0, the criteria then says show every record that has your chosen text in any part of the field.
Again, if you clear text0 and click the button, all records will again show in the list.

Hope this helps