Set filter

[JimS]

AC 2003

I have a form with 7 or 8 tabs, each one having a subform on it. The main
form is bound to a query that is keyed by ProjectID. The subforms are tied to
the main form by ProjectID.

I want to use vba to do the equivalent of:

User enters a Project ID and Equipment ID he is interested (there may be 0
or more Equipment IDs per Project). I have this coded into a Combo Box
already, with columns for each (ProjectID and EquipmentID)

Set the main form to that unique project (this, of course, sets the subforms
to that project as well), then

Set the filter on each of the subforms to display only equipment with the
chosen Equipment ID.

Code a button to clear all of the above.

Strategy?

 

[Ron2006]

If you simply use the child/parent controls of each of the subforms
everything will be done automatically. You can use more than one field
in that relationship. Just separate them with a semi-colon.

You should not need to anything else.

If that does not seem to quite work then add one more UNBOUND and
hidden field to you main form and in the afterupdate event of the comb
more the column containing the Equipment ID to that unbound field.
and on the subforms add that unbound control name to the parent
criteria. It does work, I have done it many times. You may have to
type the field names in yourself if the wizard does not want to work
properly.

Ron

 

[Klatuu]

I would suggest you use two combo boxes. One for Project and one for Equipment.
This will make it easier to show all equipment for a project. Then use a
Union query that will give you (All) as an option in the equipment combo. I
don't know the structure of your data, so I will just make up an example you
can modify to suit your needs.

The union query would be something like this:
SELECT EquipID, EquipName FROM tblEquipment UNION SELECT 0 As EquipID,
"(All)" AS EquipName FROM tblEquimpent;

Now you can use the combo's After Update event to filter or unfilter your
sub forms:

With Me
If .cboEquipment = 0 Then
.SubformControl1.Form.FilterOn = False
.SubformControl2.Form.FilterOn = False
Else
.SubformControl1.Form.Filter = "[EquipID] = " & .cboEquipment
.SubformControl1.Form.FilterOn = True
.SubformControl2.Form.Filter = "[EquipID] = " & .cboEquipment
.SubformControl2.Form.FilterOn = True
End If
End With