Query based on combobox

[scotty562]

Hello all. I have a one table that has my different fields in it. What I want to do is make a query that will search those fields depending on what is selected in the combobox.

For instance, if I want to search my data for serial numbers my query will chose the serial field if that's what the combo box is set for and then ask for an input to search against that field.



Right now I have the combobox displaying my fields, but I cannot get a query to work based on the content of the combobox.

[NTC]

Its a common task. It helps to have a textbook as this will be shown. A combobox has a 'bound' field. Be sure you understand what is actually being bound - as it isn't always what one thinks.

In your query, when in design view, one puts the criteria to call the value from the control in the form via syntax:

Forms!FormName.ControlName

Hope this give you some direction.

[scotty562]

Ok thanks for the reply. I'll look into getting a book and the bound field.

I have a table named ACS, a field named ID, and my criteria look like this.

[Forms]![ACS].[Combo46]="50" I want it to spit out the id of 50 in the id field when ID is selected in my combobox. This is just an example, the combobox will have many different options to search from.

[NTC]

well what you seek is beyond novice level.

Based on your description, you would need to dynamically construct an sql statement and then run it. A little too involved for a forum assist.

One could, perhaps, teach users to use the embedded filter capability of a continuous form; which is found in the toolbar/menu.

Sorry not to be more helpful.

[scotty562]

Perhaphs there is another way I can get what I'm looking for. I'm trying to make an easy way to search through this database without needing a button for every different kind of search I want to do. Having a combobox and then entering in criteria was the easiest way I could think of.

Edit: I may have found something useful. I made a text box and put =[Forms]![formcombo1]![locationlistcombo] in the control source and it's updating as I'm changing the values of the combobox.

On the event procedure of my combobox I added:

"stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit."

Now when I change the value of the comobox it just spits out 1 collumn of 22000 rows of the combobox value . This is much better than the nothing I've been getting before. I think I may be close.

This is my query:

SELECT [Forms]![ACS]![Combo56] AS Expr1
FROM ACS;

Edit2:
Actually this is working for the half. I added the rest of my fields and now it'll display all of my data whenever I change the combobox. Progress! I tried adding a criteria of [ filter ], but when I do this it just gives me blank results.

Is there some code I could add in that says. If [Forms]![ACS]![Combo56] = ID then add filter to query? Because what I'm finding is I can add a [filter] to my query, but I can only add it to one field at a time since each field won't match the same filter if I add [ filter ] for all of my fields.

Edit3:
I put the code in a button and just created a different query for each of the queries I want to do. Looks ugly on the backend, but the users will see a nice sleek interface .

[NTC]

generally one shouldn't be in a table itself; which is why I suggest a continuous form with all relevant fields; but in either case the Sort&Filter area in the Home tab of Access 2007 is very easy to learn.

Put your cursor in the column/field you want to search (any record - it doesn't matter)...select 'Filter'...and then try the Text Filters option....once you try it you might find it gives what you need.

Hope this helps.

[scotty562]

I don't really understand, but that's okay . You've helped me get to what I was after.