Form to Customize Report

[HeatherD25]

I have a form where a user can select different values from 3 different
drop-downs to create a customized report. (See code below). A requirement I
have is to have the option to select "All Regions" on one of the drop-downs
instead of a specific one. Well... how it is set up now is if they don't
select anything then it will bring by All Regions. We would just like this
option on the drop-down to make it more user friendly. How it brings back
All Regions, though, is if it sees a null value. If I add "All Regions" it
won't be null anymore. What can I do to get around this? Thanks!!!!!


Private Sub Command112_Click()
On Error GoTo Err_Command112_Click

Dim stDocName As String
Dim strDataFilter

strDataFilter = "1=1 "

If Not IsNull(Me.Combo110) Then
strDataFilter = strDataFilter & " AND [Account Region] = '" &
Combo110.Value & "'"
End If
If Not IsNull(Me.Combo106) Then
strDataFilter = strDataFilter & " AND [GDAP Status] = '" &
Me.Combo106 & "'"
End If
If Not IsNull(Me.Combo100) Then
strDataFilter = strDataFilter & " AND [Current OneView Sales Status]
= " & Me.Combo100 & ""
End If

stDocName = "Full Data Report"
DoCmd.OpenReport stDocName, acPreview, , strDataFilter

Exit_Command112_Click:
Exit Sub

Err_Command112_Click:
MsgBox Err.Description
Resume Exit_Command112_Click
End Sub

 

[Paul Hammond]

Use a 'Like' operator in the query or filter and have the bound column of the
combo box = "*". When the report runs the criteria wll be Like * and
select all values.

Paul

 

[HeatherD25]

Thanks for the reply... I'm a novice in VB -- where would I input the * in my
coding?

Use a 'Like' operator in the query or filter and have the bound column of the
combo box = "*". When the report runs the criteria wll be Like * and
select all values.

Paul
--

I have a form where a user can select different values from 3 different
drop-downs to create a customized report. (See code below). A requirement I
have is to have the option to select "All Regions" on one of the drop-downs
instead of a specific one. Well... how it is set up now is if they don't
select anything then it will bring by All Regions. We would just like this
option on the drop-down to make it more user friendly. How it brings back
All Regions, though, is if it sees a null value. If I add "All Regions" it
won't be null anymore. What can I do to get around this? Thanks!!!!!


Private Sub Command112_Click()
On Error GoTo Err_Command112_Click

Dim stDocName As String
Dim strDataFilter

strDataFilter = "1=1 "

If Not IsNull(Me.Combo110) Then
strDataFilter = strDataFilter & " AND [Account Region] = '" &
Combo110.Value & "'"
End If
If Not IsNull(Me.Combo106) Then
strDataFilter = strDataFilter & " AND [GDAP Status] = '" &
Me.Combo106 & "'"
End If
If Not IsNull(Me.Combo100) Then
strDataFilter = strDataFilter & " AND [Current OneView Sales Status]
= " & Me.Combo100 & ""
End If

stDocName = "Full Data Report"
DoCmd.OpenReport stDocName, acPreview, , strDataFilter

Exit_Command112_Click:
Exit Sub

Err_Command112_Click:
MsgBox Err.Description
Resume Exit_Command112_Click
End Sub

 

[Paul Hammond]

I would put it in the query. Use the Expression Builder to reference the
combo box on the form,

e.g. Like [Forms]![myForm]![myCombo]

Base the report on the query. There's hardly any VB involved except for
the button opening the report which you can build using the wizard.

Good Luck

Paul
--
Can't we all just get along?

Thanks for the reply... I'm a novice in VB -- where would I input the * in my
coding?

Use a 'Like' operator in the query or filter and have the bound column of the
combo box = "*". When the report runs the criteria wll be Like * and
select all values.

Paul
--

I have a form where a user can select different values from 3 different
drop-downs to create a customized report. (See code below). A requirement I
have is to have the option to select "All Regions" on one of the drop-downs
instead of a specific one. Well... how it is set up now is if they don't
select anything then it will bring by All Regions. We would just like this
option on the drop-down to make it more user friendly. How it brings back
All Regions, though, is if it sees a null value. If I add "All Regions" it
won't be null anymore. What can I do to get around this? Thanks!!!!!


Private Sub Command112_Click()
On Error GoTo Err_Command112_Click

Dim stDocName As String
Dim strDataFilter

strDataFilter = "1=1 "

If Not IsNull(Me.Combo110) Then
strDataFilter = strDataFilter & " AND [Account Region] = '" &
Combo110.Value & "'"
End If
If Not IsNull(Me.Combo106) Then
strDataFilter = strDataFilter & " AND [GDAP Status] = '" &
Me.Combo106 & "'"
End If
If Not IsNull(Me.Combo100) Then
strDataFilter = strDataFilter & " AND [Current OneView Sales Status]
= " & Me.Combo100 & ""
End If

stDocName = "Full Data Report"
DoCmd.OpenReport stDocName, acPreview, , strDataFilter

Exit_Command112_Click:
Exit Sub

Err_Command112_Click:
MsgBox Err.Description
Resume Exit_Command112_Click
End Sub