Filtering a report

[gcarpenter]

I have a report with a table as the row source. I have command buttons that opens different forms and allows the user to choose criteria, the form then filters the report based on the chosen criteria in the form, but if I use the destination city form to filter the report by destination city, then filter the report using a different form, the destination city filter is lost, is there a way to filter the report with a form by say destination city, then further filter that dataset with another form for say location city.

 

[namliam]

Well how are you currently filtering the report??

 

[gcarpenter]

By the method I described, but if I click on destination button and choose a destination city the form will close and filter the report, but I cannot filter within that filtered data with another form for say current location city within the data for that destination city. This is in reference to tracking railcar movements. I hope that makes sense.

 

[namliam]

Well you could "just" offer the user one form that allows for all possible filters...
Or store the current filter in a public variable so you can "stack" them...

 

[gcarpenter]

I thought about the form with all option in a listbox, but sometimes they will not want to choose all variables. I like the idea of the Public variable, but I have never worked with them before.

 

[namliam]

Just because the option is there to enter 100 different filters for your report doesnt mean that they actually have to use them

 

[gcarpenter]

I don't know how to allow all the selection to not be used. If I post the table, form and report, can you help me with the code, I have office 2010.

 

[namliam]

Why would you "allow" it to be used? The option is there, either the user does or doesnt use it

 

[gcarpenter]

I haven't figured out how to apply the filter. The only way I can do it is with a command button for each listbox, then one command button to filter based on all listboxes. It that the way to do it?

 

[namliam]

I dont know how you do it.... what is your code looking like?

 

[gcarpenter]

There are 4 listboxes on a form, this is the code on the form for the filter button. I'm sorry but do not know how to tag the code for easier reading.

Option Compare Database
Option Explicit


Private Sub cmdFilterReport_Click()
Dim strWhere As String
Dim ctl As control
Dim varItem As Variant

'make sure a selection has been made
If Me.ListOrigCity.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 City"
Exit Sub
End If

'add selected values to string
Set ctl = Me.ListOrigCity
For Each varItem In ctl.ItemsSelected
'strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "AlphaPetShipmentCriteria", acViewReport, , "orig_city Or dest_City Or Customer Or Loc_city IN(" & strWhere & ")"


DoCmd.Close acForm, "ALphaPetAllFilter" 'then apply the filter
End Sub

 

[namliam]

There are 4 listboxes on a form, this is the code on the form for the filter button. I'm sorry but do not know how to tag the code for easier reading.

See my signature

Using one or more filters is as simple as you do one....
Though I am dubious as to the fact that this one will work properly....

You just have to add "and" and "or" and other criteria to your current code.

 

[gcarpenter]

This code doesn't work, it only sets the control.ItemsSelected to one listbox.

 

[namliam]

So you add listboxes ? something like...
Set ctl = Me.ListOrigCity
...
Build filter
....
Set ctl = Me.ListAmount
...
Build filter
....
etc...

 

[gcarpenter]

Oh, I didn't think of it that way, I will give it a try, I appreciate your help and expertise.