how do i print reports using a option group and command button?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that has three reports. I would like to be able to select a
report by option button then click a command button and print preview that
report. Can someone show me how to do this with either a macro or VBA. I have
tried with a macro but have not been able to get it to work.

Thankyou,
 
Options in a group will have a numeric value (usually 1-3). You can set the
command button On Click event to something like:

Dim strReportName as String
Select Case Me.optGroupReport
Case 1
strReportName = "rptFirstReport"
Case 2
strReportName = "rptSecondReport"
Case 3
strReportName = "rptThirdReport"
Case Else
Msgbox "Select a report"
End Select
If strReportName <> "" Then
DoCmd.OpenReport strReportName, acPreview
End If
 
Can you help me expand on this information

I have 4 reports but also would like to pick a project from a list box so
the report would only print for the 1 project.

Can this be done and if so how?
 
I would create a small table of reports with fields for the actual report
name [RptName] and the report title [RptTitle]. Use this table as the Row
Source of a list box and bind the RptName field but display the RptTitle
column.

You can also create a list box with your projects. Do you have a primary key
and title field in your project table that you could share?
 
I have 4 radio buttons

Feasibility
Project Plan
Change Request
Status Report

Then I have a list box of 300+ projects

and the radio buttons and listbox are in an option group

What I would like to do is
Click radio button for project plan and 1 project from the list box and
then click command button to print report.

Can this be done???


Duane Hookom said:
I would create a small table of reports with fields for the actual report
name [RptName] and the report title [RptTitle]. Use this table as the Row
Source of a list box and bind the RptName field but display the RptTitle
column.

You can also create a list box with your projects. Do you have a primary key
and title field in your project table that you could share?
--
Duane Hookom
MS Access MVP

Kathy Dz said:
Can you help me expand on this information

I have 4 reports but also would like to pick a project from a list box so
the report would only print for the 1 project.

Can this be done and if so how?
Click to expand...
Click to expand...
 
Kathy Dz said:
I have 4 radio buttons

Feasibility
Project Plan
Change Request
Status Report

Then I have a list box of 300+ projects

and the radio buttons and listbox are in an option group

What I would like to do is
Click radio button for project plan and 1 project from the list box and
then click command button to print report.

Can this be done???
Click to expand...

It's easy with VBA code. Because macros do not have the capability for error
handling, the only ones I ever use are AutoKeys and AutoExec (and rarely use
AutoExec any more, now that the Tools | Startup options are available).

The Command Button Wizard will give you a start, by writing some basic error
handling and a basic DoCmd.OpenReport statement. Here's some example code

On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String

stDocName = "Employees"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

To see the code, after you generate a Command Button, in the button's
Properties, in the Event tab, click the three dots at the far right of the
Click event. I'm going to assume the name of your OptionGroup is grpReport,
that the values associated with the reports are as follows, 1 - Feasibility,
2-ProjectPlan, 3-ChangeRequest, and 4-StatusReport, and the names of the
reports are: rptFeasibility, rptProjectPlan, rptChangeRequest, and
rptStatusReport. For conservation of screen real estate and for the
extremely helpful AutoExpand feature, where the list scrolls to the first
match as you type, I'd suggest you use a ComboBox instead of the List Box.
(It's not very user-friendly to force the user to scroll through a list of
hundreds of projects, or any other item for that matter.) I'm going to call
that Combo Box, cboProjects. I am also going to assume that the value
returned from the Combo is a long integer that is the Field named Project in
the table or query you use for the RecordSource of the Report. (If it's a
text Field rather than numeric, post back and I'll add the necessary quote
marks.)

(NOTE: It's just a personal preference, but I would use a Combo Box to
select the report, too -- it's no more difficult for the user and would save
some code, I think.)

You'll need to modify the generated code for selection of the Report name,
and to add a WhereCondition clause (see Access Help by putting the cursor in
the OpenReport and pressing F1). The following is the generated code, as
modified:

On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim strWhere as String 'ADDED: to contain the WhereCondition

If Me.optReport = 1 Then 'ADDED: to select the Report based on the
stDocName = "rptFeasibility" ' Option Group value
ElseIf Me.optReport = 2 Then
stDocName = "rptProjectPlan"
ElseIf Me.optReport = 3 Then
stDocName = "rptChangeRequest"

ElseIf Me.optReport = 4 Then
stDocName = "rptChangeRequest"
Else
MsgBox = "Please select a Report and try again"
GoTo Exit_cmdPreviewReport_Click
End If

If Not IsNull (Me.cboProject) Then 'ADDED: to specify which Project
is
' to be the
subject of this Report
strWhere = "[Project] = " & Me.cboProject
DoCmd.OpenReport stDocName, acPreview,,strWhere
Else
MsgBox "Please select a Project and try again"
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

Now, the caution is... this is untested "air code", but I have done a lot of
similar code to preview reports and it won't be far off (I strongly suggest
you preview the report and let the user look it over before they decide
whether to print it; the user might have accidentally clicked on the wrong
project or report, and want to ignore this and try again before they print.)
Substitute your Control and Report names, and see if that doesn't do what
you want.

Larry Linson
Microsoft Access MVP
 
Great reply with lots of good advice...

--
Duane Hookom
MS Access MVP

Larry Linson said:
Kathy Dz said:
I have 4 radio buttons

Feasibility
Project Plan
Change Request
Status Report

Then I have a list box of 300+ projects

and the radio buttons and listbox are in an option group

What I would like to do is
Click radio button for project plan and 1 project from the list box and
then click command button to print report.

Can this be done???
Click to expand...

It's easy with VBA code. Because macros do not have the capability for
error handling, the only ones I ever use are AutoKeys and AutoExec (and
rarely use AutoExec any more, now that the Tools | Startup options are
available).

The Command Button Wizard will give you a start, by writing some basic
error handling and a basic DoCmd.OpenReport statement. Here's some example
code

On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String

stDocName = "Employees"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

To see the code, after you generate a Command Button, in the button's
Properties, in the Event tab, click the three dots at the far right of the
Click event. I'm going to assume the name of your OptionGroup is
grpReport, that the values associated with the reports are as follows, 1 -
Feasibility, 2-ProjectPlan, 3-ChangeRequest, and 4-StatusReport, and the
names of the reports are: rptFeasibility, rptProjectPlan,
rptChangeRequest, and rptStatusReport. For conservation of screen real
estate and for the extremely helpful AutoExpand feature, where the list
scrolls to the first match as you type, I'd suggest you use a ComboBox
instead of the List Box. (It's not very user-friendly to force the user to
scroll through a list of hundreds of projects, or any other item for that
matter.) I'm going to call that Combo Box, cboProjects. I am also going
to assume that the value returned from the Combo is a long integer that is
the Field named Project in the table or query you use for the RecordSource
of the Report. (If it's a text Field rather than numeric, post back and
I'll add the necessary quote marks.)

(NOTE: It's just a personal preference, but I would use a Combo Box to
select the report, too -- it's no more difficult for the user and would
save some code, I think.)

You'll need to modify the generated code for selection of the Report name,
and to add a WhereCondition clause (see Access Help by putting the cursor
in the OpenReport and pressing F1). The following is the generated code,
as modified:

On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim strWhere as String 'ADDED: to contain the WhereCondition

If Me.optReport = 1 Then 'ADDED: to select the Report based on
the
stDocName = "rptFeasibility" ' Option Group value
ElseIf Me.optReport = 2 Then
stDocName = "rptProjectPlan"
ElseIf Me.optReport = 3 Then
stDocName = "rptChangeRequest"

ElseIf Me.optReport = 4 Then
stDocName = "rptChangeRequest"
Else
MsgBox = "Please select a Report and try again"
GoTo Exit_cmdPreviewReport_Click
End If

If Not IsNull (Me.cboProject) Then 'ADDED: to specify which Project
is
' to be the
subject of this Report
strWhere = "[Project] = " & Me.cboProject
DoCmd.OpenReport stDocName, acPreview,,strWhere
Else
MsgBox "Please select a Project and try again"
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

Now, the caution is... this is untested "air code", but I have done a lot
of similar code to preview reports and it won't be far off (I strongly
suggest you preview the report and let the user look it over before they
decide whether to print it; the user might have accidentally clicked on
the wrong project or report, and want to ignore this and try again before
they print.) Substitute your Control and Report names, and see if that
doesn't do what you want.

Larry Linson
Microsoft Access MVP
Click to expand...
 
Thanks for the help, it is great. Yes I use a text field, please provide code.

Larry Linson said:
Kathy Dz said:
I have 4 radio buttons

Feasibility
Project Plan
Change Request
Status Report

Then I have a list box of 300+ projects

and the radio buttons and listbox are in an option group

What I would like to do is
Click radio button for project plan and 1 project from the list box and
then click command button to print report.

Can this be done???
Click to expand...

It's easy with VBA code. Because macros do not have the capability for error
handling, the only ones I ever use are AutoKeys and AutoExec (and rarely use
AutoExec any more, now that the Tools | Startup options are available).

The Command Button Wizard will give you a start, by writing some basic error
handling and a basic DoCmd.OpenReport statement. Here's some example code

On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String

stDocName = "Employees"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

To see the code, after you generate a Command Button, in the button's
Properties, in the Event tab, click the three dots at the far right of the
Click event. I'm going to assume the name of your OptionGroup is grpReport,
that the values associated with the reports are as follows, 1 - Feasibility,
2-ProjectPlan, 3-ChangeRequest, and 4-StatusReport, and the names of the
reports are: rptFeasibility, rptProjectPlan, rptChangeRequest, and
rptStatusReport. For conservation of screen real estate and for the
extremely helpful AutoExpand feature, where the list scrolls to the first
match as you type, I'd suggest you use a ComboBox instead of the List Box.
(It's not very user-friendly to force the user to scroll through a list of
hundreds of projects, or any other item for that matter.) I'm going to call
that Combo Box, cboProjects. I am also going to assume that the value
returned from the Combo is a long integer that is the Field named Project in
the table or query you use for the RecordSource of the Report. (If it's a
text Field rather than numeric, post back and I'll add the necessary quote
marks.)

(NOTE: It's just a personal preference, but I would use a Combo Box to
select the report, too -- it's no more difficult for the user and would save
some code, I think.)

You'll need to modify the generated code for selection of the Report name,
and to add a WhereCondition clause (see Access Help by putting the cursor in
the OpenReport and pressing F1). The following is the generated code, as
modified:

On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim strWhere as String 'ADDED: to contain the WhereCondition

If Me.optReport = 1 Then 'ADDED: to select the Report based on the
stDocName = "rptFeasibility" ' Option Group value
ElseIf Me.optReport = 2 Then
stDocName = "rptProjectPlan"
ElseIf Me.optReport = 3 Then
stDocName = "rptChangeRequest"

ElseIf Me.optReport = 4 Then
stDocName = "rptChangeRequest"
Else
MsgBox = "Please select a Report and try again"
GoTo Exit_cmdPreviewReport_Click
End If

If Not IsNull (Me.cboProject) Then 'ADDED: to specify which Project
is
' to be the
subject of this Report
strWhere = "[Project] = " & Me.cboProject
DoCmd.OpenReport stDocName, acPreview,,strWhere
Else
MsgBox "Please select a Project and try again"
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

Now, the caution is... this is untested "air code", but I have done a lot of
similar code to preview reports and it won't be far off (I strongly suggest
you preview the report and let the user look it over before they decide
whether to print it; the user might have accidentally clicked on the wrong
project or report, and want to ignore this and try again before they print.)
Substitute your Control and Report names, and see if that doesn't do what
you want.

Larry Linson
Microsoft Access MVP
Click to expand...
 
If Project is a text field, change the strWhere code to:

strWhere = "[Project] = """ & Me.cboProject & """"
DoCmd.OpenReport stDocName, acPreview,,strWhere
--
Duane Hookom
MS Access MVP


Kathy Dz said:
Thanks for the help, it is great. Yes I use a text field, please provide
code.

Larry Linson said:
Kathy Dz said:
I have 4 radio buttons

Feasibility
Project Plan
Change Request
Status Report

Then I have a list box of 300+ projects

and the radio buttons and listbox are in an option group

What I would like to do is
Click radio button for project plan and 1 project from the list box and
then click command button to print report.

Can this be done???
Click to expand...

It's easy with VBA code. Because macros do not have the capability for
error
handling, the only ones I ever use are AutoKeys and AutoExec (and rarely
use
AutoExec any more, now that the Tools | Startup options are available).

The Command Button Wizard will give you a start, by writing some basic
error
handling and a basic DoCmd.OpenReport statement. Here's some example code

On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String

stDocName = "Employees"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

To see the code, after you generate a Command Button, in the button's
Properties, in the Event tab, click the three dots at the far right of
the
Click event. I'm going to assume the name of your OptionGroup is
grpReport,
that the values associated with the reports are as follows, 1 -
Feasibility,
2-ProjectPlan, 3-ChangeRequest, and 4-StatusReport, and the names of the
reports are: rptFeasibility, rptProjectPlan, rptChangeRequest, and
rptStatusReport. For conservation of screen real estate and for the
extremely helpful AutoExpand feature, where the list scrolls to the first
match as you type, I'd suggest you use a ComboBox instead of the List
Box.
(It's not very user-friendly to force the user to scroll through a list
of
hundreds of projects, or any other item for that matter.) I'm going to
call
that Combo Box, cboProjects. I am also going to assume that the value
returned from the Combo is a long integer that is the Field named Project
in
the table or query you use for the RecordSource of the Report. (If it's a
text Field rather than numeric, post back and I'll add the necessary
quote
marks.)

(NOTE: It's just a personal preference, but I would use a Combo Box to
select the report, too -- it's no more difficult for the user and would
save
some code, I think.)

You'll need to modify the generated code for selection of the Report
name,
and to add a WhereCondition clause (see Access Help by putting the cursor
in
the OpenReport and pressing F1). The following is the generated code, as
modified:

On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim strWhere as String 'ADDED: to contain the
WhereCondition

If Me.optReport = 1 Then 'ADDED: to select the Report based on
the
stDocName = "rptFeasibility" ' Option Group value
ElseIf Me.optReport = 2 Then
stDocName = "rptProjectPlan"
ElseIf Me.optReport = 3 Then
stDocName = "rptChangeRequest"

ElseIf Me.optReport = 4 Then
stDocName = "rptChangeRequest"
Else
MsgBox = "Please select a Report and try again"
GoTo Exit_cmdPreviewReport_Click
End If

If Not IsNull (Me.cboProject) Then 'ADDED: to specify which
Project
is
' to be the
subject of this Report
strWhere = "[Project] = " & Me.cboProject
DoCmd.OpenReport stDocName, acPreview,,strWhere
Else
MsgBox "Please select a Project and try again"
End If

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

Now, the caution is... this is untested "air code", but I have done a lot
of
similar code to preview reports and it won't be far off (I strongly
suggest
you preview the report and let the user look it over before they decide
whether to print it; the user might have accidentally clicked on the
wrong
project or report, and want to ignore this and try again before they
print.)
Substitute your Control and Report names, and see if that doesn't do what
you want.

Larry Linson
Microsoft Access MVP
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

Print a report without displaying print preview 1
No print menu or button available for reports? 6
access report 0
Open report with command button 1
Command Button prints 2 reports 4
Preview/Print Reports based on Group Option 1
Print command prints all reports in database? 15
Excel VBA - Problem with Closing Print Preview 0

Back
Top