Search Box in form

[Roland87]

Hi,

I have a form with a combo box I use to lookup records in one table, and I also have a list box with all of the records from another table.

Whenever I type in the combo box the name of a client the corresponding record and its fields appear, but the list box is unchanged, and does not select the client's name.

How can I get rid of the combo box, and have a simple search box which updates both the fields, and the list box?

I don't know any VBA so please explain. Thanks.

 

[AlexHedley]

How are you getting the data with the combo?
Are you typing a name and other text boxes on the form are populating?

You could set you listbox to have a control source of a query that uses a search box as a parameter

i.e. SELECT * FROM tblTABLENAME WHERE fieldName LIKE * Forms!FormName!frmControl *

Add a textbox on the form, make sure it is matches "frmControl" name above.
Then run a listbox.Requery() in the AfterUpdate of the above textbox and or put it in a button event so you can press that instead.

Alex

 

[Roland87]

I am not sure what you mean, I want one search box to populate other text boxes and to filter the list box at the same time. I tried putting that in a query, it says the syntax is wrong.

 

[AlexHedley]

What does your query look like and what is the name of your search textbox?

 

[Roland87]

regular query,

I put this in criteria:

SELECT * FROM Client Information WHERE Client Name LIKE * Forms!Form com!box1 *

 

[AlexHedley]

Its best practice to not have spaces in your table names, field names etc
Try adding [ ] around those names.

Is that in the SQL view or did you add all of that in the query builder in the criteria row?
You would need to add the * as one column then add the client name as another column, untick it and add the Like... In the criteria

 

[Roland87]

SELECT * FROM [Client Information] WHERE [Client Name] LIKE * Forms![Form com]!box1 *

it still gives me a message about syntax

 

[AlexHedley]

Did you create the Query with the Query Builder?
(I updated a previous post)

 

[Roland87]

I put it all in the criteria row, It's just for one field, do I need SQL here ?

 

[AlexHedley]

You need to build the query up like I suggested in the Query Builder or change the view to SQL and paste in that full statement.

 

[AlexHedley]

Here's a mini tutorial that might be helpful
http://599cd.com/tips/access/multi-field-search-form-like/

 

[Roland87]

Okay, I think it works now. I've entered listbox.Requery() on the click of a button, and I've replaced listbox with its name, but it says it can't find the object, strange... Any ideas ? Should I put an equal sign or sthg?

 

[John Big Booty]

The sample posted here may be of interest to you.

 

[Roland87]

John your tutorial is great, but I keep getting a message on the query and the form asking me to supply the parameter value for the text box SrchText. When I hit cancel, I get a run-time error 3021 in the visual basic window. Can you help me out? thanks.

 

[AlexHedley]

Has the name of the Search textbox been changed to match the name you are using in the Query?
When Access pops up asking for a parameter it's because it can't find the one you are passing it, usually due to the spelling.

 

[Roland87]

Has the name of the Search textbox been changed to match the name you are using in the Query?
When Access pops up asking for a parameter it's because it can't find the one you are passing it, usually due to the spelling.

Yes, of course, I followed the steps carefully. It has to be something else.

 

[Roland87]

I finally got it to work, I don't know what went wrong, but when I changed the form's name it worked like a charm. Thank you very much Alex.

 

[John Big Booty]

Glad you got it working

thanks to AlexHedley for assisting in my abscess