Combo Box selections generate Report

[Guest]

I have a form with 4 combo boxes and an "Apply Filter" button. I want to be
able to make my 4 selections, click on Apply Filter, and generate my report
based on the selections.

Problem: My combo box selections only appear on the report IF the report is
already open. If I don't open the report, and then I click Apply Filter
button, all the records show up on the report. How can I make my selections
and have the report open automatically with the filtered data?

Thanks,
Cheri624

 

[Guest]

I would write the code to check if the report is open and if not open it, and
insert this code before the code on you Apply Filter button.
Such as

If SysCmd(acSysCmdGetObjectState, acReport, "YourReport") <> acObjStateOpen
Then
DoCmd.Open acReport, "YourReport"

Good luck
Dylan Moran

 

[Guest]

Cheri624,

Sorry code was wrong. There correct tested code follows

If SysCmd(acSysCmdGetObjectState, acReport, "rptCustomerServiceArea") <>
acObjStateOpen Then
DoCmd.OpenReport "rptCustomerServiceArea", acViewPreview
End If

Dylan Moran

 

[Guest]

Obviously replacing "rptCustomerServiceArea" with the name of your report.

If SysCmd(acSysCmdGetObjectState, acReport, "rptCustomerServiceArea") <>
acObjStateOpen Then
DoCmd.OpenReport "rptCustomerServiceArea", acViewPreview
End If

Dylan Moran

 

[Guest]

Hi Dylan - I actually tried similar code to begin with, but perhaps I have it
in the wrong spot. I'm pasting a copy of my code below, as it is now.
Please let me know where it should go:

Private Sub cmdApplyFilter_Click()
Dim strRegion As String
Dim strHeadquarters As String
Dim strSegment As String
Dim strFgroup As String
Dim strFilter As String

If SysCmd(acSysCmdGetObjectState, acReport, "rALL_REPORT") <>
acObjStateOpen Then
DoCmd.OpenReport "rALL_REPORT", acViewPreview
Exit Sub

End If

If IsNull(Me.cbo2REGION.Value) Then
strRegion = "Like '*'"
Else
strRegion = "='" & Me.cbo2REGION.Value & "'"
End If

If IsNull(Me.cbo2HEADQUARTERS.Value) Then
strHeadquarters = "Like '*'"
Else
strHeadquarters = "='" & Me.cbo2HEADQUARTERS.Value & "'"
End If

If IsNull(Me.cbo2SEGMENT.Value) Then
strSegment = "Like '*'"
Else
strSegment = "='" & Me.cbo2SEGMENT.Value & "'"
End If

If IsNull(Me.cbo2FGROUP.Value) Then
strFgroup = "Like '*'"
Else
strFgroup = "='" & Me.cbo2FGROUP.Value & "'"
End If

' Combines the criteria to form a WHERE clause for the filter
strFilter = "[Region] " & strRegion & " AND [Headquarters] " &
strHeadquarters & " AND [Segment] " & strSegment & " AND [Fgroup] " &
strFgroup


With Reports![rALL_REPORT]
.Filter = strFilter
.FilterOn = True
End With
End Sub

 

[Guest]

See comments in text.

Hi Dylan - I actually tried similar code to begin with, but perhaps I have it
in the wrong spot. I'm pasting a copy of my code below, as it is now.
Please let me know where it should go:

Private Sub cmdApplyFilter_Click()
Dim strRegion As String
Dim strHeadquarters As String
Dim strSegment As String
Dim strFgroup As String
Dim strFilter As String

If SysCmd(acSysCmdGetObjectState, acReport, "rALL_REPORT") <>
acObjStateOpen Then
DoCmd.OpenReport "rALL_REPORT", acViewPreview
Exit Sub

Delete the above Exit Sub (or comment it out). The code above is basically
saying if the report rALL_REPORT is not open, then open it and the exit sub
is then ending this procedure, which means the rest of your code will not be
interpreted.
Delete the exit sub so your code can continue and interpret your filter
(where clause)code.
Good Luck
Let us know how you went.

End If

If IsNull(Me.cbo2REGION.Value) Then
strRegion = "Like '*'"
Else
strRegion = "='" & Me.cbo2REGION.Value & "'"
End If

If IsNull(Me.cbo2HEADQUARTERS.Value) Then
strHeadquarters = "Like '*'"
Else
strHeadquarters = "='" & Me.cbo2HEADQUARTERS.Value & "'"
End If

If IsNull(Me.cbo2SEGMENT.Value) Then
strSegment = "Like '*'"
Else
strSegment = "='" & Me.cbo2SEGMENT.Value & "'"
End If

If IsNull(Me.cbo2FGROUP.Value) Then
strFgroup = "Like '*'"
Else
strFgroup = "='" & Me.cbo2FGROUP.Value & "'"
End If

' Combines the criteria to form a WHERE clause for the filter
strFilter = "[Region] " & strRegion & " AND [Headquarters] " &
strHeadquarters & " AND [Segment] " & strSegment & " AND [Fgroup] " &
strFgroup


With Reports![rALL_REPORT]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Obviously replacing "rptCustomerServiceArea" with the name of your report.

If SysCmd(acSysCmdGetObjectState, acReport, "rptCustomerServiceArea") <>
acObjStateOpen Then
DoCmd.OpenReport "rptCustomerServiceArea", acViewPreview
End If

Dylan Moran