Combo Box as parameters for a query (2003 All Updates)

Topic

New Reply

Is there any easy way to use the equivalent of a combo box to filter a query ie when you run the query , have combo box appear with a list of allowable paramaters (eg States or countries) and have the ability to select all of them, just one or a number of them to feed the query
TIA
Steve

Reply | Quote

Replies

Use a combo box on a form, and set the criteria to something like

[Forms]![NameOfForm]![NameOfComboBox]

The form should be open before the query is used. The form won’t be opened automatically if the query is run from the database window, but since end users should never run something from the database window, that shouldn’t be a problem.

Reply | Quote

Sorry Hans – totally lost -put the criteria where
Steve

Reply | Quote

In the design of the query, in the Criteria line of the column you want to filter on.

Reply | Quote

Following on from help by Hans and John, I attach a cut down version of the database.
Two major tables: STKB which holds data relating to Stock and DAVEID which holds the list of GROUPS.
The ChooseGroupsForm is designed to allow you to choose one, several or All Groups. This process is working fine (after much confusion on my part !!). However I want to apply the results of this query to the query qryProductProfitabilitySearch and have no idea how to do this.
Any help appreciated
TIA
Steve

Reply | Quote

You have an organizational (or design) problem that you’ll have to solve before continuing. The IDCODEs in the DAVEID table do not correspond to the IDCODEs in the STKB table. For example, DAVEID contains the IDCODE “WINE”, containing descriptions such as “WINES”, “WINES RED”, “WINES WHITE” etc. However, the records in STKB that are about wines contain IDCODEs such as REDS, WHIT and SWIN. Because of this inconsistency, you cannot join the tables on the IDCODE field. You will have to tackle this first. You’ll have to decide for yourself in which table you’re going to change the codes to match those in the other one. And perhaps you should use the CODENO field from DAVEID, but I don’t see an equivalent field in STKB at first sight.

Reply | Quote

Hans
Stupid of me that I didnt check.(not my database by the way)
I think I’m there now – thnks to John and yourself for your help
Steve

Reply | Quote

I see that you edited your reply. For what it’s worth, I have attached the version I created in reaction to the question in the original version.

Reply | Quote

Hans
Thank you – the query is now working well. I had to change the source from the Table STKB to a query as there are calculated fields required in the report and I noticed that it would only read the first 4 letters of the query name (presumably because the original table source was 4 letters long). I just changed the name of the query to be 4 letters long
However, because of my inexperience, I have had to make production of the associated report a 2 step process (2 buttons on the form)
Button 1 calls up the query after selections are made
Button 2 opens the report based on this query
It works but its not pretty. Is there anyway to open the report in preview mode as soon as the groups are selected and the “run query” button clicked
Steve

Reply | Quote

You don’t have to alter a query before opening the report. you can pass the where-condition to the report. See the attached version. The report is nothing special, just a slightly modified autoreport; it is only meant as an illustration that you can use one query and one report to display different selections.

Reply | Quote

[indent]

---

just one or a number of them to feed the query

---

[/indent]

Combo Boxes do not allow multiple selection. To allow more than one you need a list box with the multi select property set to either Simple or Extended

Reply | Quote