Multiple Fields Record Selection Form

[razorking]

The answer to this may be posted elsewhere, but after considerable time searching I am not quite finding what I am looking for. I also suspect that others can learn from this so...will post the question that has probably been answered several times already.

I have a table that has some customer contact information. Now I want to produce a report by letting the user enter parameters on a form. And I want them to be able to select any combination of values from 4-5 fields. For example: show me all customers where the salesperson is John and the city is Seattle. Or show me all customers from the state of Alaska (or whatever combination). I am looking for different ways to accomplish this. What I have now is not working - and I suspect in the end it is not the perfect solution - even if it does work. But, I would like to get this to work or understand why it does not work.

So if anyone has any example databases they can post with a more elegan solution to the above that would be great.

Or if anyone would be interested in looking at my attached example and possible tweaking it and re-posting the changed db that would also be much appreciated.

in the attached example there is a table with test data. Then there is a form - frmSelect. From here I envision entering the parameter values and then clicking the button to return the record selection. But it does not work - see underlying query. Can it work like this??

 

[pbaldy]

The sample db here does generally what I would do:

http://www.mdbmakers.com/forums/showthread.php?t=4895

Rather than build a full SQL string for a subform recordsource, I would build a wherecondition for OpenReport (a wherecondition is basically an SQL WHERE clause without the word WHERE).

 

[Kryst51]

I have one suggestion.

1.) Delete your current Controls.

2.) Build combo boxes using the wizard, which look up the values in your contacts table.

3.) In each combo box property, in the base query for the combo box (Pull up the QBE) go to the SQL view, (I am not exactly sure about this part, but others here have posted this before, so do a search for exact directions) Somewhere in here you need to put something like "SELECT DISTINCT". So that you don't get multiple in your combo box list of the same thing.

4.) Have your query pull the value that you select on your open form. In the QBE, (Have your form open in the background), go to the criteria section for one of the fields, open the funtion builder, Click Forms --> Loaded Forms --> "The name of Your Form". You should then see a list of all the controls on your forms. Double click the applicable one, then click OK. Then do this for each of your fields.

5.) I know that you also need a way of dealing with it if there is nothing selected in the boxes, but am not sure exactly how to do that. I have a form in a database, that was built a while ago that utilizes the * - but I had help with the form and don't remember how it was done. But I am sure you can get help there.

I am also sure that there is more than one way accomplish what you want, but this is the way I know how. I hope it helps!