cyrianagujo
New member
- Local time
- Yesterday, 18:53
- Joined
- Jul 24, 2015
- Messages
- 3
Hi guys, here's my problem. I have a report based on a query that I put inside a form. What I want to do is create 4 comboboxes from which users can choose options that will filter the report without leaving the form or opening the report in a separate window. Also, I want to make the filters dependent on each other, meaning if a select option A on combobox 1, the options i will select on combobox 2 will only be based on the earlier filter on combobox 1 (sorry if I can't explain it clearly.)
So far I'm able to do at least 2 of the comboboxes to filter the report, but they aren't based on which one was used to filter the report first. So if i filter the records by choosing option A on combobox 1, combobox 2 will still filter every record.
I'm really a beginner in VBA (almost 0 knowledge) but here's my 2 codes so far:
=======
Private Sub cboLocation_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboLocation) Then
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
Else
Me.qrySalesByLocation.Report.Filter = "[Location]=" & Me.cboLocation
Me.qrySalesByLocation.Report.FilterOn = True
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub
==========
Private Sub cboProduct_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboProduct) Then
Me.qrySalesByLocation.Report
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
Else
Me.qrySalesByLocation.Report.Filter = "[Product]=" & Me.cboProduct
Me.qrySalesByLocation.Report.FilterOn = True
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub
=====
the other 2 comboboxes, I can't apply the same code cause the enter parameter dialog keeps popping out.
Thanks in advance for the help!
So far I'm able to do at least 2 of the comboboxes to filter the report, but they aren't based on which one was used to filter the report first. So if i filter the records by choosing option A on combobox 1, combobox 2 will still filter every record.
I'm really a beginner in VBA (almost 0 knowledge) but here's my 2 codes so far:
=======
Private Sub cboLocation_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboLocation) Then
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
Else
Me.qrySalesByLocation.Report.Filter = "[Location]=" & Me.cboLocation
Me.qrySalesByLocation.Report.FilterOn = True
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub
==========
Private Sub cboProduct_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboProduct) Then
Me.qrySalesByLocation.Report
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
Else
Me.qrySalesByLocation.Report.Filter = "[Product]=" & Me.cboProduct
Me.qrySalesByLocation.Report.FilterOn = True
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub
=====
the other 2 comboboxes, I can't apply the same code cause the enter parameter dialog keeps popping out.
Thanks in advance for the help!