Forms with listbox to filter report

[gcarpenter]

I have a report on this report there is a Destination City field and a Current City field, loaded from a table. I have a command button that loads a form to filter on the destination city, I have another command button that loads a form to filter on the current city location. What I would like is, when I use the form to filter the destination city, then load the form to filter on current city, I want the listbox on the form for the current city to only list cities that are associated with that destination. Both form listboxes have the rowsource from the same table, just different fields. Is this possible?

As always thanks for any help you can provide.

 

[EternalMyrtle]

I think what you want to do is make "cascading combo boxes". Search for that term and if you have trouble post back with your specific problem.

Good luck!

 

[gcarpenter]

Not sure this will work since there are 2 different forms involved, one form has listbox with the Destination City and the other form has listbox with the Current City. The command buttons are on the report and will load whichever form you want to filter Destination or Current.

 

[EternalMyrtle]

Ok, well it is hard to give suggestions without an understanding of your data structure.

I am assuming you tried this already but you may be able to filter the forms in the WHERE clause of the DoCmd.OpenForm

 

[gcarpenter]

I open the report. I click on Update Report, this will delete the current records in the CustomerStatus table and add records to the Customer Status table based on the selection of the parameters in the append query. Once these queries run, the report will only with the CustomerStatus table as record source. If I click on the button to load the destination form, the form loads and the rowsource for the listbox is a field in the CustomerStatus table. Once I choose a destination, I click on the Apply Filter button and it filter the report. The problem is if I click on the location button to load that form rowsource supplied from CustomerStatus table CurrentCity field. Problem is I may hace 684 records in this table, if I filter on a single destination with the destination form, then I want to filter the location that are associated with this destination, it list all the CurrentCities in the table which can be quite long. HEr is the code behind the apply filter button on the destination filter.
Private Sub Filter_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.ListCarrier.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Destination City"
Exit Sub
End If
'add selected values to string
Set ctl = Me.ListCarrier
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.Close acReport, "rptCustRailcarStatus", acSaveYes
DoCmd.OpenReport "rptCustRailcarStatus", acViewReport, , "dest_city IN(" & strWhere & ")"

Forms!CustomerDestinationfrm.Visible = False
End Sub

 

[EternalMyrtle]

There is a lot more involved here than you initially let on. I cannot read it all right now have to get to a meeting. Maybe post a more specific question in the VBA section of the forum.

 

[gcarpenter]

Ok. thanks.

 

[Frothingslosh]

As a rule, when you post code, make sure to wrap it in code tags. Indenting and spacing your code helps make it more legible, too.

Private Sub Filter_Click()
 
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
 
    'make sure a selection has been made
    If Me.ListCarrier.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 Destination City"
        Exit Sub
    End If
 
    'add selected values to string
    Set ctl = Me.ListCarrier
 
    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.Close acReport, "rptCustRailcarStatus", acSaveYes
    DoCmd.OpenReport "rptCustRailcarStatus", acViewReport, , "dest_city IN(" & strWhere & ")"
 
    Forms!CustomerDestinationfrm.Visible = False
 
End Sub