Multiple Combo Box Queries?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'm wanting to setup a form that displays various combo boxes relating to
previous orders. I want the user to be able to select various criteria in
each combo box and then a query to run based on this criteria.

For example there might be 3 combo boxes.

1) The user selects the customer they are querying.
2) The user selects the product type they are querying.
3) The user selects the colour they are querying.

How do I best do this? Also is it possible to query text fields, i.e if the
user enters two date ranges?
 
In the new query, under the field that will be selected by them use this
format in the criteria box:

[Forms]![YourFormName]![ComboBoxName]

For the dates, you would add,

AND > [Forms]![YourFormName]![BeginningDateComboBoxName]
AND > [Forms]![YourFormName]![EndingDateComboBoxName]

For the command button they press to invoke the query, paste it on the form
in design view, select properties > event > on click - then use this event
procedure:

Private Sub CommandButtonName_Click()
Me.RecordSource = "YourQueryName"
Me.Refresh
End Sub

- mike
 
Hi Mike thanks for your help.

I've still not quite got it working though.
Do all the fields I'm referring to in my combo boxes have to be in the same
table for this to work?

Also when I click my command button - which has the code attached - it does
nothing, the screen flashes and the form stays on screen. When I run the
query manually, it seems to have run but there are multiple listings.

Any further suggestions?

magmike said:
In the new query, under the field that will be selected by them use this
format in the criteria box:

[Forms]![YourFormName]![ComboBoxName]

For the dates, you would add,

AND > [Forms]![YourFormName]![BeginningDateComboBoxName]
AND > [Forms]![YourFormName]![EndingDateComboBoxName]

For the command button they press to invoke the query, paste it on the form
in design view, select properties > event > on click - then use this event
procedure:

Private Sub CommandButtonName_Click()
Me.RecordSource = "YourQueryName"
Me.Refresh
End Sub

- mike
--
MEDICATION ASSISTANCE PROGRAM
Qualifying applicants get their medication free.
It's easy to qualify. Use the qualification wizard at
http://www.rexaid.com




Richard Horne said:
Hi

I'm wanting to setup a form that displays various combo boxes relating to
previous orders. I want the user to be able to select various criteria in
each combo box and then a query to run based on this criteria.

For example there might be 3 combo boxes.

1) The user selects the customer they are querying.
2) The user selects the product type they are querying.
3) The user selects the colour they are querying.

How do I best do this? Also is it possible to query text fields, i.e if the
user enters two date ranges?
Click to expand...
Click to expand...
 
Hello again.
I've sort of got this working using the wonderful tutorial here
http://www.fontstuff.com/access/acctut08.htm#dialog

I have two combo boxes - Customer and Product both queried in a table called
Order_Details but as this table is linked to another table called Customers
the combo box is actually grabbing the Customer ID# and not the customer's
name. How do I get the combo box to display the customer name and not the
customer's ID?
 
You need to use a single source. That is the purpose that queries serve.
Create a query that combines the two sources into one. If you have fields in
both tables that have the same name, the name in the query will have to be
different.

In the field names in query design view you can create a new name for a
field this way:

customer_name: [table1].[customername]

You would put this in the field box. Normally in the field box, you just
type the exact name of a field. That not only becomes the label, but also
the actual source of the data. If it is the only field with that name, then
Access automatically fills in the the Table box with the right corresponding
table. However, if you have two different fields with the same name (usually
applies with the ID field) or you wish the label to be different than the
actual name of the field (i.e. instead of ID, you wish to call it UserID),
then you would use the above formula. Anything before the colon is just a
name you have given that field in the query, just like label on a form.
Since there are two tables in the query, you would have the labeled field
populate itself with the data in table1 from the field you had named
customername. You cannot have two fields with the same name in a query.

On the form, with the multiple records, it should pull up multiple records,
if they meet that criteria. If you only want to show one at a time, forcing
the user to navigate the records, you need to reflect that in the form's
properties. On the format tab of the properties, and in the Default View
field, make sure that "Single Form" is the choice. It sounds as if
"continuous" is currently selected. You may also need to disable Datasheet
view which should be two fields below. By the way, have you designed fields
on this form, to take the data from the query? The command button tells the
current form, to populate itself with the query data which in turn has
requested data from the same form to populate the query. If you are trying
to get a new form to come open that is a completely different scenario.

Mike
 
Back
Top