Filter By Combo Box Selection (1 Viewer)

[aliasquark]

I have a form which basically displays all the products sold by a take-away shop contained within a subform (recordset is based on a query called sales).

The fields in the subform (query) are
Date
Code
Product
Cost
Catagory (ie Mains, drinks, side salads, pasta, pizza)

The form I've created displays the above fields in order of date an the footer of the form displays a sum of the cost and catagorie (i.e. $11,111 and a total of 543 sales from the catagories that are displayed).

In the header I have combo-boxes for Date, Code and Catagory. What I want to be able to do is filter the data in the subform based on the selections chosen in the combo-boxes and if the combo-box is blank then to remove all filtering of that field. This is so the user can work out the total sales for a perticular item (i.e supreme pizza), a perticular category (ie pastas), or a perticular date.

I have tried to restrict the results of the query using criterias such as
Code = Form!SalesSubform!CodeComboBox!Code
However this doesn't seem to work because the query runs before the form opens, thus there is no information yet relating the query to the combo-box value.

Also, I don't know how the system will handle a change in the combo-box value - will the query run again after the change has been made to the combo-box value???

Thanks for any help you may be able to provide

Q.

 

[WayneRyan]

Q,

I'm assuming that you are using a bound form.
Replace your current combobox with a bound
text box, like the others.

Make a new unbound combobox, not related to
your table (no control source). Make its
row source:

Select code from YourTable order by code.

Base your form on a query and set the criteria
for the code field to

=Forms![YourForm]![NewCombo]


In the AfterUpdate event for this new combo:

Me.Requery

That way whenever you change your new combo box,
the Me.Requery will use your form's query,
with the new criteria, and show you the results.

Wayne

 

[Jack Cowley]

Using this article as a guide create a command button using the code in the article. Set the subforms Record Source to the 'Dynamic_Query'. As the last line in your code in the command button refesh the subform and you will see the correct data when you make selections from your combo boxes and then press the command button.

hth,
Jack