Problems with "All" option in combo box

K

Kurt

When a user selects the 3rd option in an option group (of
various reports), a combo box appears (cboRespondant).
The user can then select either a) a patient id or
b) "(All)" and then click a button to preview, print, or
email a report for that patient or patients.

My problem is that if the user selects "(All)" and then
goes to preview or print the report, the report opens but
with no data. (If the combo box is empty, everything
works - but I like the use of an "(All)" option if I can
get it to work.)

The report print/preview feature is part of a Select Case
function. How can I edit this (or the combo box Row
Source, Report, etc.) so that the ("All") option works?

Thanks. - Kurt (code below)

The Row Source for the combo box is:
----------------------------------------------
SELECT DISTINCTROW tblSrvRspns.RspnsID
FROM tblSrvRspns
UNION SELECT "(All)"
From tblSrvRspns
ORDER BY tblSrvRspns.RspnsID;

The report has this Filter:
------------------------------
(RspnsID = Forms![frmnuReportSelect]!cboRespondant).

The option group uses a Select Case function:
------------------------------------------------------
Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and
' Print_Click Sub procedures.
' Preview or print report selected in the option group.

Dim strWhereRspnsID As String

strWhereRspnsID = "RspnsID = Forms![frmnuReportSelect]!
cboRespondant"

Select Case Me!optSelectReport

Case 1
Me![cboRespondant].Visible = False
DoCmd.OpenReport "rptStatistics", PrintMode
Case 2
Me![cboRespondant].Visible = False
DoCmd.OpenReport "rptStatisticsWithGraphs", PrintMode
Case 3
Me![cboRespondant].Visible = True
If IsNull(Forms![frmnuReportSelect]!cboRespondant)
Then
DoCmd.OpenReport "rptIndividualSurvey", PrintMode
Else
DoCmd.OpenReport "rptIndividualSurvey", PrintMode,
, strWhereRspnsID
End If

End Select

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub
 
G

Gary Miller

Kurt,

A simple way would be to wrap your "strWhereRspnsID ="
statement in an IF statement that checks to see if the value
of the combo is "All" or not. If it is "All" you just leave
the Where statement out and make the string empty.

Gary Miller
Sisters, OR
 
D

Dale Fye

Try chaning the code for when case = 3.

I don't know why you think you need the Me![cboRespondant].Visible =
True " code in this subroutine.

Case 3
If IsNull(Forms![frmnuReportSelect]!cboRespondant) OR
Forms![frmnuReportSelect]!cboRespondant = "All" Then
DoCmd.OpenReport "rptIndividualSurvey", PrintMode
Else
DoCmd.OpenReport "rptIndividualSurvey", PrintMode, ,
strWhereRspnsID
End If


--
HTH

Dale Fye


When a user selects the 3rd option in an option group (of
various reports), a combo box appears (cboRespondant).
The user can then select either a) a patient id or
b) "(All)" and then click a button to preview, print, or
email a report for that patient or patients.

My problem is that if the user selects "(All)" and then
goes to preview or print the report, the report opens but
with no data. (If the combo box is empty, everything
works - but I like the use of an "(All)" option if I can
get it to work.)

The report print/preview feature is part of a Select Case
function. How can I edit this (or the combo box Row
Source, Report, etc.) so that the ("All") option works?

Thanks. - Kurt (code below)

The Row Source for the combo box is:
----------------------------------------------
SELECT DISTINCTROW tblSrvRspns.RspnsID
FROM tblSrvRspns
UNION SELECT "(All)"
From tblSrvRspns
ORDER BY tblSrvRspns.RspnsID;

The report has this Filter:
------------------------------
(RspnsID = Forms![frmnuReportSelect]!cboRespondant).

The option group uses a Select Case function:
------------------------------------------------------
Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and
' Print_Click Sub procedures.
' Preview or print report selected in the option group.

Dim strWhereRspnsID As String

strWhereRspnsID = "RspnsID = Forms![frmnuReportSelect]!
cboRespondant"

Select Case Me!optSelectReport

Case 1
Me![cboRespondant].Visible = False
DoCmd.OpenReport "rptStatistics", PrintMode
Case 2
Me![cboRespondant].Visible = False
DoCmd.OpenReport "rptStatisticsWithGraphs", PrintMode
Case 3
Me![cboRespondant].Visible = True
If IsNull(Forms![frmnuReportSelect]!cboRespondant)
Then
DoCmd.OpenReport "rptIndividualSurvey", PrintMode
Else
DoCmd.OpenReport "rptIndividualSurvey", PrintMode,
, strWhereRspnsID
End If

End Select

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub
 
K

Kurt

Dale:

You code did it. Thanks.

And the reason I set the cboRespondant to Visible = True
for Case 3 is because, by default, it's not visible on
the form. It should only become visible if the user
selects option 3.

Kurt
-----Original Message-----
Try chaning the code for when case = 3.

I don't know why you think you need the Me! [cboRespondant].Visible =
True " code in this subroutine.

Case 3
If IsNull(Forms![frmnuReportSelect]!cboRespondant) OR
Forms![frmnuReportSelect]!cboRespondant = "All" Then
DoCmd.OpenReport "rptIndividualSurvey", PrintMode
Else
DoCmd.OpenReport "rptIndividualSurvey", PrintMode, ,
strWhereRspnsID
End If


--
HTH

Dale Fye


When a user selects the 3rd option in an option group (of
various reports), a combo box appears (cboRespondant).
The user can then select either a) a patient id or
b) "(All)" and then click a button to preview, print, or
email a report for that patient or patients.

My problem is that if the user selects "(All)" and then
goes to preview or print the report, the report opens but
with no data. (If the combo box is empty, everything
works - but I like the use of an "(All)" option if I can
get it to work.)

The report print/preview feature is part of a Select Case
function. How can I edit this (or the combo box Row
Source, Report, etc.) so that the ("All") option works?

Thanks. - Kurt (code below)

The Row Source for the combo box is:
----------------------------------------------
SELECT DISTINCTROW tblSrvRspns.RspnsID
FROM tblSrvRspns
UNION SELECT "(All)"
From tblSrvRspns
ORDER BY tblSrvRspns.RspnsID;

The report has this Filter:
------------------------------
(RspnsID = Forms![frmnuReportSelect]!cboRespondant).

The option group uses a Select Case function:
------------------------------------------------------
Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and
' Print_Click Sub procedures.
' Preview or print report selected in the option group.

Dim strWhereRspnsID As String

strWhereRspnsID = "RspnsID = Forms![frmnuReportSelect]!
cboRespondant"

Select Case Me!optSelectReport

Case 1
Me![cboRespondant].Visible = False
DoCmd.OpenReport "rptStatistics", PrintMode
Case 2
Me![cboRespondant].Visible = False
DoCmd.OpenReport "rptStatisticsWithGraphs", PrintMode
Case 3
Me![cboRespondant].Visible = True
If IsNull(Forms![frmnuReportSelect]!cboRespondant)
Then
DoCmd.OpenReport "rptIndividualSurvey", PrintMode
Else
DoCmd.OpenReport "rptIndividualSurvey", PrintMode,
, strWhereRspnsID
End If

End Select

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub


.
Click to expand...
 
D

Dale Fye

I understand that you need to set the cboRessponse.visible when they select
option #3, but you should do that in the afterupdate or the change event of
the option group.

Something like the following one liner will do it. That way, the cbo is
made visible immediately after the option is selected and hidden if option
#3 is not selected.

Me.cboRespondant.Visible = (me.option = 3)

HTH
Dale

Kurt said:
Dale:

You code did it. Thanks.

And the reason I set the cboRespondant to Visible = True
for Case 3 is because, by default, it's not visible on
the form. It should only become visible if the user
selects option 3.

Kurt
-----Original Message-----
Try chaning the code for when case = 3.

I don't know why you think you need the Me! [cboRespondant].Visible =
True " code in this subroutine.

Case 3
If IsNull(Forms![frmnuReportSelect]!cboRespondant) OR
Forms![frmnuReportSelect]!cboRespondant = "All" Then
DoCmd.OpenReport "rptIndividualSurvey", PrintMode
Else
DoCmd.OpenReport "rptIndividualSurvey", PrintMode, ,
strWhereRspnsID
End If


--
HTH

Dale Fye


When a user selects the 3rd option in an option group (of
various reports), a combo box appears (cboRespondant).
The user can then select either a) a patient id or
b) "(All)" and then click a button to preview, print, or
email a report for that patient or patients.

My problem is that if the user selects "(All)" and then
goes to preview or print the report, the report opens but
with no data. (If the combo box is empty, everything
works - but I like the use of an "(All)" option if I can
get it to work.)

The report print/preview feature is part of a Select Case
function. How can I edit this (or the combo box Row
Source, Report, etc.) so that the ("All") option works?

Thanks. - Kurt (code below)

The Row Source for the combo box is:
----------------------------------------------
SELECT DISTINCTROW tblSrvRspns.RspnsID
FROM tblSrvRspns
UNION SELECT "(All)"
From tblSrvRspns
ORDER BY tblSrvRspns.RspnsID;

The report has this Filter:
------------------------------
(RspnsID = Forms![frmnuReportSelect]!cboRespondant).

The option group uses a Select Case function:
------------------------------------------------------
Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and
' Print_Click Sub procedures.
' Preview or print report selected in the option group.

Dim strWhereRspnsID As String

strWhereRspnsID = "RspnsID = Forms![frmnuReportSelect]!
cboRespondant"

Select Case Me!optSelectReport

Case 1
Me![cboRespondant].Visible = False
DoCmd.OpenReport "rptStatistics", PrintMode
Case 2
Me![cboRespondant].Visible = False
DoCmd.OpenReport "rptStatisticsWithGraphs", PrintMode
Case 3
Me![cboRespondant].Visible = True
If IsNull(Forms![frmnuReportSelect]!cboRespondant)
Then
DoCmd.OpenReport "rptIndividualSurvey", PrintMode
Else
DoCmd.OpenReport "rptIndividualSurvey", PrintMode,
, strWhereRspnsID
End If

End Select

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

filter report based on list box selection 5
Option Button 4
need help with multi-selct / option group 6
Using Parameters in a Form 2
Formatting values to 00000 not working 8
User entering search criteria 1
Preview and print report problems 1
Option Group 14

Top