Combo box filtering

[charleedongo]

Hello Everyone,
Iam facing a problem when i try to filter a continuous form with
multiple combo boxes.
here is the situation. The form is based on a query. The query has
fields,
Last name
First Name
Subject
Status
Classroom

Subjects offered are Math and reading. Status is current, vacation,
discontinued.
Classroom is Junoir, Middle, Higher.
I would like to create such a filter where iam able to see all the math
students alone or reading students alone. These are dependent on the
status field. For example, i would like to see all math students who
are current in the Junior class room. I understand that this requires
three combo boxes which are interconnected.
Pls help me with the code by which i can achieve this.

 

[Allen Browne]

So you want to place 3 unbound combo boxes in the Form Header, and a command
button to apply the filter based on whatever combination of values the user
entered?

You will need to use VBA code to create the string to use for the Filter of
the form. The actual details will depend on the data type of these fields.

If you can use VBA, and would like an exmaple to follow, download:
http://allenbrowne.com/unlinked/Search2000.zip
The small sample database shows how to build the Filter string from the
boxes that have values, and how to use the delimters for the different data
types. Requires Access 2000 or later.

 

[charleedongo]

Thanks Allen,
I downloaded the sample. All my data type is Text.
The user has limited choice in each of the combo boxes to pick from.
It is for a School. In the first combo box, the values are Math,
Reading , or Math and reading.
The second combo box value is the classroom they are assigned to, which
is limited. Junoir, Middle and Higher.
The third combo box, the most important one, is the status of each
student. They are current, Vacation, Discontinued.
I looked at your sample and that is exactly what i wanted, but with no
date filters or number filters.
If you need more info of my table structure, let me know.
Thanks for your time

 

[Allen Browne]

Okay. You can ignore the examples for numbers, and dates, and just use the
code that has all those quotes for each of your cases.

 

[charleedongo]

Thank You allen.
Your code worked. I have one more favor to ask you. One of the combo
boxes has values "Current", "vacation""Discontinued". I would like my
form to open to show only the current students. So when i open the
continuous form, it should give me a filtered result of the current
students only.
Please help.

 

[Allen Browne]

Method A: Always show only the current students in this form
=======
1. Create a query using this table.

2. I the Criteria under this field, enter:
"Current"

3. Save the query.

4. Use the query as the RecordSource for your form.

Method B: Always open the form with a Filter, so the user can remove it.
=======
1. Open the form in design view.

2. Open the Properties box so it shows the properties of the form.

3. On the Events tab, set the On Click property to:
[Event procedure]

4. Click the Build button (...) beside this.
Access opens the code window.

5. Set up the event procedure so it looks like this:
Private Sub Command99_Click()
Me.Filter = "[Status] = 'current'"
Me.FilterOn = True
End Sub

(Use the name of your command button instead of Command99.)

Method C: Open the form from a button, so it only shows current students
=======
In the Click event procedure of the command button, use the WhereCondition
of OpenForm, e.g.:
DoCmd.OpenForm "MyForm", WhereCondition:="[Status] = 'current'"
where MyForm represents the name of the form, and Status the name of the
field.

Note:
===
If this Status field is a Number field, use the number without quotes in
each case, e.g.:
"[Status] = 2"