Create a report with drop-down selections

[HeatherD25]

Hi,

I have a form built with 5 drop-down selections for different things to
filter a report. I need a user to be able to select one of these items and
run the report, or be able to select 2 or 3 or 4 or 5 of the items and run
the report. Right now I only have it coded for the each item. If the user
picks something on one of the drop-down menus, there is a button below that
says "Scoring Data Report" that they can run and it will filter the data for
that one item. I have 5 of these buttons on the form -- one for each of the
combo boxes. Here's a sample of the code behind one of the buttons:

Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim strDataFilter
strDataFilter = "[JV or SA] = '" & Combo40.Value & "'"
'MsgBox (strDataSource)
stDocName = "rpt Scoring2"
DoCmd.OpenReport stDocName, acPreview
Reports![rpt Scoring2].Filter = strDataFilter
'MsgBox (Reports![rpt Scoring2].Filter)
Reports("rpt Scoring2").FilterOn = True

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

My question -- I can't figure out how to run the filter if they only select
2 of the items. How do you tell access to ignore the other combo boxes and
not try to filter a null value? Can you pass a *?

Thanks in advance for your help!!!!

 

[Duane Hookom]

Use code that creates a dynamic strDataFilter
Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim strDataFilter
strDataFilter = "1=1 "
If Not IsNull(Me.Combo40) Then
strDataFilter = strDataFilter & " AND [JV or SA] = '" &
Combo40.Value & "'"
End If
If Not IsNull(Me.cboNudderOne) Then
strDataFilter = strDataFilter & " AND [NudderField] = '" &
Me.cboNudderOne & "'"
End If
'add more references to controls.
'also, find naming convention for you controls so you don't have code
with "Combo40"
stDocName = "rpt Scoring2"
DoCmd.OpenReport stDocName, acPreview, , strDataFilter

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

 

[HeatherD25]

Thanks!! That worked!!

Use code that creates a dynamic strDataFilter
Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim strDataFilter
strDataFilter = "1=1 "
If Not IsNull(Me.Combo40) Then
strDataFilter = strDataFilter & " AND [JV or SA] = '" &
Combo40.Value & "'"
End If
If Not IsNull(Me.cboNudderOne) Then
strDataFilter = strDataFilter & " AND [NudderField] = '" &
Me.cboNudderOne & "'"
End If
'add more references to controls.
'also, find naming convention for you controls so you don't have code
with "Combo40"
stDocName = "rpt Scoring2"
DoCmd.OpenReport stDocName, acPreview, , strDataFilter

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub


--
Duane Hookom
MS Access MVP

Hi,

I have a form built with 5 drop-down selections for different things to
filter a report. I need a user to be able to select one of these items and
run the report, or be able to select 2 or 3 or 4 or 5 of the items and run
the report. Right now I only have it coded for the each item. If the user
picks something on one of the drop-down menus, there is a button below that
says "Scoring Data Report" that they can run and it will filter the data for
that one item. I have 5 of these buttons on the form -- one for each of the
combo boxes. Here's a sample of the code behind one of the buttons:

Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim strDataFilter
strDataFilter = "[JV or SA] = '" & Combo40.Value & "'"
'MsgBox (strDataSource)
stDocName = "rpt Scoring2"
DoCmd.OpenReport stDocName, acPreview
Reports![rpt Scoring2].Filter = strDataFilter
'MsgBox (Reports![rpt Scoring2].Filter)
Reports("rpt Scoring2").FilterOn = True

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

My question -- I can't figure out how to run the filter if they only select
2 of the items. How do you tell access to ignore the other combo boxes and
not try to filter a null value? Can you pass a *?

Thanks in advance for your help!!!!