Multiple filters

[vaughan198]

Hi there.
I have a continuous form that pulls in records from a query depending on the selected user. It does this by having the user field in the query criteria. If I wanted to give users the option of also filtering by client (and ignoring the user filter) how would I go about that (I want to give the user the option of filtering either by user of by client.

Thanks

 

[bob fitz]

Can you show us the sql for your current query?

 

[vaughan198]

OK here is the sql code

SELECT [New Accounts].User, [New Accounts].[Date/Time Sent], [New Accounts].[Client], [New Accounts].[US SalesPerson], [New Accounts].DeskHead, [New Accounts].[TR Name], [New Accounts].DesiredProd, [New Accounts].AccountStatus, [New Accounts].RequestID, [New Accounts].[Date Account Opened]
FROM [New Accounts]
WHERE ((([New Accounts].User)=[Forms]![Track Requests]![fltrUser]))
ORDER BY [New Accounts].[Date/Time Sent] DESC;

 

[vaughan198]

anyone got any more ideas about this. Any help hugely appreciated.

 

[jdraw]

I think a form before the continuous form where you could select the type of filter to apply.

Then a button to actually open the continuous form with the appropriate argument.

see http://www.datawright.com.au/access_resources/access_docmd.openform_arguments.htm

Just some thoughts for consideration

 

[Kiwiman]

Howzit

I use a solution by Uncle Gizmo to filter my forms - see this post - see post 3 for the latest version (i'm not sure if there is one later than that???). I have had up to 7 combo boxes filtering my data on a mix and match basis.

 

[bob fitz]

From what you have said in your original post and from the SQL that you have posted, I assume that the user currently selects the user that is to be used as a filter in a control called “fltrUser” on your form called “Track Requests”.
The SQL below should give the result you are seeking but you would need to create a control called “fltrClient” on the “Track Requests” form for the user to make their selection.

SELECT [New Accounts].User, [New Accounts].[Date/Time Sent], [New Accounts].[Client], [New Accounts].[US SalesPerson], [New Accounts].DeskHead, [New Accounts].[TR Name], [New Accounts].DesiredProd, [New Accounts].AccountStatus, [New Accounts].RequestID, [New Accounts].[Date Account Opened]
FROM [New Accounts]
WHERE ((([New Accounts].Client)=[Forms]![Track Requests]![fltrClient]))
ORDER BY [New Accounts].[Date/Time Sent] DESC;

Now you just need to change the SQL for the continuous form depending on which set of data is required.

You don’t say how the user opens the continuous form so I will assume that this is done with a button on the “Track Requests” form with code something like:

DoCmd.OpenForm”NameOfContinuousForm”

Replace that with:
DoCmd.OpenForm”NameOfContinuousForm” , , , , , , "User"
Make sure you include the 6 comers.

Create another button to open the continuous form when it is to be filtered by Client. Use this code:
DoCmd.OpenForm”NameOfContinuousForm” , , , , , , "Client"
Again, make sure you include the 6 comers.

These will open the continuous form, setting the form’s OpenArgs property

Remove the current SQL or Query name from the Record Source property of the continuous form. Enter the code below in the form’s OnOpen event:

If Me.OpenArgs = "Client" Then
'set the record source prop to filter by Client
Me.RecordSource = "SELECT [New Accounts].User, [New Accounts]. [Date/Time Sent], [New Accounts].[Client], [New Accounts].[US SalesPerson], [New Accounts].DeskHead, [New Accounts].[TR Name], [New Accounts].DesiredProd, [New Accounts].AccountStatus, [New Accounts].RequestID, [New Accounts].[Date Account Opened] " & _
"FROM [New Accounts] " & _
"WHERE ((([New Accounts].Client) = [Forms]![Track Requests]![fltrClient])) " & _
"ORDER BY [New Accounts].[Date/Time Sent] DESC;"
End If

If Me.OpenArgs = "User" Then
'set the record source prop to filter by User
Me.RecordSource = "SELECT [New Accounts].User, [New Accounts].[Date/Time Sent], [New Accounts].[Client], [New Accounts].[US SalesPerson], [New Accounts].DeskHead, [New Accounts].[TR Name], [New Accounts].DesiredProd, [New Accounts].AccountStatus, [New Accounts].RequestID, [New Accounts].[Date Account Opened] " & _
"FROM [New Accounts] " & _
"WHERE ((([New Accounts].User) = [Forms]![Track Requests]![fltrUser]))" & _
"ORDER BY [New Accounts].[Date/Time Sent] DESC;"
End If

If Me.RecordsetClone.RecordCount < 1 Then
'cancel form openning if no criteria selected
MsgBox "No Filter Criteria Selected"
Cancel = True
End If

This code will run when the form opens. It will examine the OpenArgs property and set the forms Record Source property accordingly.