search listbox

[JonatABS]

I have a list box of employees.
I need to be able to type in a text box what ever information needed and the need the list box to update and show only records that match the search criteria.

Im terrible with queries so if anybody knows how to explain this easily please try. I know im gonna need a querey so I better learn it now.


Thanks

PS I also want the ability to select an employee in that list box and bring up the form that is created for that employee.

Thanks in advance I appreciate it. Im almost out of hair. I had a full head this morning LOL

 

[lloydmav]

Ok your right you need to create a query. This is the way I would do it. First of all you need to create a query using the wizard. Use your employee table and follow the instructions. When you run that query it should show all the employee records. Now you need to create a listbox on your form. Follow the wizard and make it get the values from your query you just created. Now create a textbox on the form next to the listbox. For an example event why not also create a commandbutton next to the textbox called 'Search'. When the wizard comes up for that commandbutton go to cancel.
Now go back to your query and open it in design view. Select the field for which you want to search eg. if you want to type in a Name click in the Criteria box for the Employee Surname field. In here type in the following criteria;
[Forms]![FormName]![TextBoxName]
Now go back to your form and add some code to the click event of the commandbutton;
List2.Requery
or whatever your listbox name is!
Now when you run the form enter a surname into the textbox and click the button.
The listbox will then find all the employees with that surname.

Hope this helps

 

[Andren]

Ok your right you need to create a query. This is the way I would do it. First of all you need to create a query using the wizard. Use your employee table and follow the instructions. When you run that query it should show all the employee records. Now you need to create a listbox on your form. Follow the wizard and make it get the values from your query you just created. Now create a textbox on the form next to the listbox. For an example event why not also create a commandbutton next to the textbox called 'Search'. When the wizard comes up for that commandbutton go to cancel.
Now go back to your query and open it in design view. Select the field for which you want to search eg. if you want to type in a Name click in the Criteria box for the Employee Surname field. In here type in the following criteria;
[Forms]![FormName]![TextBoxName]
Now go back to your form and add some code to the click event of the commandbutton;
List2.Requery
or whatever your listbox name is!

------------------

INTERESTING - just adding a question:

What if I wanted to use this teqniqe - but wanted to be able to search on multiple fields inte query? Could that be done ??

Thanks/
Anders

Now when you run the form enter a surname into the textbox and click the button.
The listbox will then find all the employees with that surname.

Hope this helps

 

[lloydmav]

Yes,

You could set the extra criteria within the design of the query.

You could also have other textboxes etc with criteria, just refer to that textbox within the query criteria in the correct field.

 

[Andren]

OK Lloyd!
Thanks for answering.
But how do I "set the extra criteria within the design of the query" ?
I have five keywords. I think that I want users to look-up five different key-words by combo-boxes (or whatever is best). Each key-word is contained in its own table that could be included in the main query.
In the database I will put tousends and tousends of reccords that could be classified by any combination of these five key-words. So I want the user to be able to pick any combination from what is already stored in the database and I want other pre-defined fields to reflect the choise of that (for me unknown and uniqe ) combination.

Did I make myself clear enough ?

If not - I am sorry for taking your time.
Otherwise - this might be someting that we all can learn from.

I´m as always grateful for all comments.

And - a comment from me - This Forum have been so helpful to me. I am amazed of how many people there is around the Globe that are willing to help each other also with a big portion of humor. Why is there war ? Why can´t hey learn from us.

Yours: Anders

 

[lloydmav]

A an example you could put three textboxes on your form (unbound). These will be your criteria for the query that your listbox is bound to. Name the textboxes eg Criteria 1, Criteria 2, Criteria 3.
Now go to the code event for the afterupdate/beforeupdate of your textboxes and for each of these events type in the code

Me.requery
Me.ListBox1.Requery

Now select your query and go to the design view of it.

In each of the fields of the query where you want a criteria to be set put the below;(change the formname to your formname and change the txtName to the name of your textboxes;

Like "*" & Forms![YourFormName]![txtName] & "*"
IsNull(Forms![YourFormName]![txtName])

As you update the textbox criteria your query should update along with your listbox

 

[JonatABS]

Ok nm about the previous replay.

I do have a question however.

This search is working just fine for me when searching for a employee number

However I want to use the same "searchbox" to search via last name or first name.

I thought that pasting the same criteria that I have for the eomployee number would also work for the lastname but it ignores the last name criteria and still only uses the employee number field.

WHY!!! ahhhhhhh help!!!!!

I want to basically be able to search for anything (first, last names, phone numbers, email address, cities.etc....)

How in the world doI do that and still only use ONE search box. i dont want to have a search box for each field that the person wants to search.

Thanks

Jon

 

[Andren]

Re: search

Access doesnt like the expression

IsNull(Forms![YourFormName]![txtName])

it says its a syntax error

It says its something to do with the !

any suggestions?

Well. I got the same message. and do not know what to do.

/ Anders

 

[Mile-O]

You might find the demo database I posted on this thread useful for what you are trying to do.

It is a 2000 database.

 

[lloydmav]

Miles, I don't know if Anders is having any trouble but I had an error when trying to open this file.

I don't know if this is a problem with the file or my computer!

I've attached a screenshot of the error!

 

[Mile-O]

Probably my file - worked fine on my laptop but when I downloaded it in work today I got an "Unable to find..." error which baffled me - that's why I asked if anyone else was having problems with it on that thread.

Thanks.

 

[Andren]

Probably my file - worked fine on my laptop but when I downloaded it in work today I got an "Unable to find..." error which baffled me - that's why I asked if anyone else was having problems with it on that thread.

Thanks.

Well - I could not open it either.
Thanks anyway!
/ Anders

 

[Andren]

And if I want to use combo-boxes?

A an example you could put three textboxes on your form (unbound). These will be your criteria for the query that your listbox is bound to. Name the textboxes eg Criteria 1, Criteria 2, Criteria 3.
Now go to the code event for the afterupdate/beforeupdate of your textboxes and for each of these events type in the code

Me.requery
Me.ListBox1.Requery

Now select your query and go to the design view of it.

In each of the fields of the query where you want a criteria to be set put the below;(change the formname to your formname and change the txtName to the name of your textboxes;

Like "*" & Forms![YourFormName]![txtName] & "*"
IsNull(Forms![YourFormName]![txtName])

As you update the textbox criteria your query should update along with your listbox

This was very helpful. You made me understand something that I did not understood before. However - instead of textboxes I want five combo-boxes so that users could shoose any combination of five items (key-words) and base the search on that
combination. I tried to replace the "Me.Listbox1.Requery" with
"Me.ComboKey1.Requery" (i.e. the name of the first combo containing keywords) and "Me.ComboKey2.Requery" (the seccond
combobox containing keywords) etc. Acess did not protest about this but writing the seccond part of your suggestion the "Like "*" & Forms ....stuff" replacing "txtName" with the name of the combo
in the query , Access got some kind of nervous breakdown.

From this behaviour I figured out that you just can not replace
a textbox with a combo. But if I still want my users to be able to use several kriteria (keywords) that already are typed into the database how should I do.

If anyone could enlight me in this matter, I sure would be
VERY thankful.

/
Anders

 

[JonatABS]

I have an updated question regarding this post.

I have the list of filtered records based on my search text box. It works like a charm.

I also got it to open up the record I double click on.. However after it opens it, it wont show ALL the other records. It shows just that one record. That becomes a problem when I want to scroll through the records. Right now I have to close down the form and renter the form. The problem is im using linked databases and it takes a while todo that. LOL