Filtering from a drop down list

[tgavin]

I don't know if I do this in the query or with code....

I have a tabular list based on a totals query of all the clients orders with
the sales reps. I created a drop down to allow the sales managers to filter
that list for only the clients of the sales reps they handle using a criteria
of [Forms]![frmBilling]![sfrmMonthlyStatus].[Form]![SupervisorFilter] int the
underlying query BUTno records show because I don't know how to configure it
to show all records if one is not chosen and I don't know how to add an
option to the list that allows them to choose Show All.

The actual form does not show the supervisors and I don't have room to add
it as a field to allow for Filter by Selection.

Thanks
Terri

 

[Klatuu]

You can specify All by using a Union query as the row source for the combo.
Also, you don't want the filter in the query, you want to do that at the form
level. The following code is only an example you can work from:

Here is a query example:
SELECT "(ALL)" AS Dummy From tblClient UNION SELECT MainName From tblClient;

Private Sub cboFilter_AfterUpdate()

If Me.cboFilter = "(ALL)" Then
Me.FilterOn = False
Else
Me.Filter = "[MainName] = """ & Me.cboFilter & """"
Me.FilterOn = True
End If

End Sub

 

[tgavin]

Dave, I am doing something wrong with the row source. Here is what I have:

SELECT "(ALL)" As Dummy From tblCoordinator UNION SELECT
tblCoordinators.CoordinatorID, [txtCoordinatorLName] & ", " &
[txtCoordinatorFName] AS Supervisor, tblCoordinators.txtTitle FROM
tblCoordinators WHERE (((tblCoordinators.txtTitle)="Supervisor")) ORDER BY
[txtCoordinatorLName] & ", " & [txtCoordinatorFName];

You can specify All by using a Union query as the row source for the combo.
Also, you don't want the filter in the query, you want to do that at the form
level. The following code is only an example you can work from:

Here is a query example:
SELECT "(ALL)" AS Dummy From tblClient UNION SELECT MainName From tblClient;

Private Sub cboFilter_AfterUpdate()

If Me.cboFilter = "(ALL)" Then
Me.FilterOn = False
Else
Me.Filter = "[MainName] = """ & Me.cboFilter & """"
Me.FilterOn = True
End If

End Sub

--
Dave Hargis, Microsoft Access MVP

I don't know if I do this in the query or with code....

I have a tabular list based on a totals query of all the clients orders with
the sales reps. I created a drop down to allow the sales managers to filter
that list for only the clients of the sales reps they handle using a criteria
of [Forms]![frmBilling]![sfrmMonthlyStatus].[Form]![SupervisorFilter] int the
underlying query BUTno records show because I don't know how to configure it
to show all records if one is not chosen and I don't know how to add an
option to the list that allows them to choose Show All.

The actual form does not show the supervisors and I don't have room to add
it as a field to allow for Filter by Selection.

Thanks
Terri

 

[John W. Vinson]

Dave, I am doing something wrong with the row source. Here is what I have:

SELECT "(ALL)" As Dummy From tblCoordinator UNION SELECT
tblCoordinators.CoordinatorID, [txtCoordinatorLName] & ", " &
[txtCoordinatorFName] AS Supervisor, tblCoordinators.txtTitle FROM
tblCoordinators WHERE (((tblCoordinators.txtTitle)="Supervisor")) ORDER BY
[txtCoordinatorLName] & ", " & [txtCoordinatorFName];

PMFJI but... a UNION query must have the same number of fields of matching
datatypes in both SELECT clauses. Since your tblCoordinators is returing three
fields - ID, Supervisor and Title - so must your Dummy:

SELECT 0 AS CoordinatorID, "(All)" AS Supervisor, "" AS txtTitle FROM
tblCoordinator
UNION ALL
SELECT ...

You'll need to trap the 0 value rather than the text string "(All)" in your
query.