Changing query criteria to supply a form

[Rob M]

Hi all,

I have what's certainly a newbie question.

I have a query that supplies data to a form. Is there a way to filter
the data shown on the form according to different criteria in one
query field? For example, if the user were to press ToggleButton1,
only records with query criteria as "new" would be shown. If they
press ToggleButton2, only records with "active" would be shown.

I can do this manually if I simply type in the criteria in the query
itself. If it's helpful, the query name is "qryUsers" and the query
field name is "Status".

I'd be very grateful for any help!

Thanks,
Rob

 

[Marshall Barton]

I have a query that supplies data to a form. Is there a way to filter
the data shown on the form according to different criteria in one
query field? For example, if the user were to press ToggleButton1,
only records with query criteria as "new" would be shown. If they
press ToggleButton2, only records with "active" would be shown.

I can do this manually if I simply type in the criteria in the query
itself. If it's helpful, the query name is "qryUsers" and the query
field name is "Status".

Try using the form's Filter property.

You didn't provide a lot of details, but your
button's Click event procedure might be something like:

Dim stFilter As String

Select Case True
Case ToggleButton1
stFilter = "[status field] = 'Active' "
Case ToggleButton2
stFilter = "[status field] = 'New"
. . .
End Select

Me.Filter = stFilter
Me.FilterOn = True

 

[kc-mass]

Hi Rob.

The simple way is to invoke different queries based on which toggle button
has been selected.

ToggleButton1 invokes a query of "SELECT * WHERE CriteriaField = "New" ;"
ToggleButton2 invokes a query of "SELECT * WHERE CriteriaField = "Active" ;"

Then requery the form with something like Forms!MyForm.Requery and the
changed data will
display.

You could also change the recordsource of the form in code with something
like:
"Me.RecordSource = "qryMySourceNew"

Regards

Kevin

 

[Rob M]

Thanks to both, this is exactly what I need!