Selecting Record for Report Macro

  • Thread starter Thread starter AJ Raiber
  • Start date Start date
A

AJ Raiber

I have a command button that is programmed to run reports when clicked. How
can I change the code below to select only the current record for printing?

Private Sub Print_Reports_Click()
On Error GoTo Err_Print_Reports_Click

Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String
Dim stDocName5 As String

stDocName1 = "BackgroundInvestigationRequest"
stDocName2 = "Business Rules Pg 3"
stDocName3 = "InprocChecklist"
stDocName4 = "ISO Certification"
stDocName5 = "VA Form 2280a"

DoCmd.OpenReport stDocName1, acNormal
DoCmd.OpenReport stDocName2, acNormal
DoCmd.OpenReport stDocName3, acNormal
DoCmd.OpenReport stDocName4, acNormal
DoCmd.OpenReport stDocName5, acNormal

Exit_Print_Reports_Click:
Exit Sub

Err_Print_Reports_Click:
MsgBox Err.Description
Resume Exit_Print_Reports_Click

End Sub
 
AJ,

This question relates to a VBA procedure, which is quite different from
a macro, which is the focus of this newsgroup.

Still, since you're here... :-)

There are 2 main approaches to printing reports based on specific
records. One is to use the Where Condition argument of the OpenReport
method in your code. The other is to base your report on a query that
uses its Criteria setting to select the required record(s).

In either case, you need to refer to whichever field it is that
identifies the "current record". This is normally the primary key field
of the main table that the form is based on. And of course this same
field must be included in the report's Record Source table/query. So
let's suppose for this example that this field is named YourID.

So the first methid would look like this:
(stripping out the unnecessary verbosity of the code, and assuming the
requirement applies to all reports, and assuming the the Print_Reports
button is on the form)
___________

Private Sub Print_Reports_Click()
On Error GoTo Err_Print_Reports_Click

DoCmd.OpenReport "BackgroundInvestigationRequest", , , "[YourID] =
" & Me.YourID
DoCmd.OpenReport "Business Rules Pg 3", , , "[YourID] = " & Me.YourID
DoCmd.OpenReport "InprocChecklist", , , "[YourID] = " & Me.YourID
DoCmd.OpenReport "ISO Certification", , , "[YourID] = " & Me.YourID
DoCmd.OpenReport "VA Form 2280a", , , "[YourID] = " & Me.YourID

Exit_Print_Reports_Click:
Exit Sub

Err_Print_Reports_Click:
MsgBox Err.Description
Resume Exit_Print_Reports_Click

End Sub
___________

The other approach would involve putting the equivalent of this in the
Criteria of the YourID field in the query that the report is based on:
[Forms]![NameOfYourForm]![YourID]
 
Steve,

I changed the query as you suggested with the employeeID criteria set
to [Forms]![TableName]![EmployeeID]. I have two test records in my table and
when I use the print button on record 1 it works fine. However, when I use
it on the second record, it prints blank information. Any ideas how I messed
this one up?

While I was typing this I added a third record and it wont print for that
one either. Please help. Also, I don't see a VBA forum anymore, has it
become the "Programming" forum? Thanks.

AJ

Steve Schapel said:
AJ,

This question relates to a VBA procedure, which is quite different from
a macro, which is the focus of this newsgroup.

Still, since you're here... :-)

There are 2 main approaches to printing reports based on specific
records. One is to use the Where Condition argument of the OpenReport
method in your code. The other is to base your report on a query that
uses its Criteria setting to select the required record(s).

In either case, you need to refer to whichever field it is that
identifies the "current record". This is normally the primary key field
of the main table that the form is based on. And of course this same
field must be included in the report's Record Source table/query. So
let's suppose for this example that this field is named YourID.

So the first methid would look like this:
(stripping out the unnecessary verbosity of the code, and assuming the
requirement applies to all reports, and assuming the the Print_Reports
button is on the form)
___________

Private Sub Print_Reports_Click()
On Error GoTo Err_Print_Reports_Click

DoCmd.OpenReport "BackgroundInvestigationRequest", , , "[YourID] =
" & Me.YourID
DoCmd.OpenReport "Business Rules Pg 3", , , "[YourID] = " & Me.YourID
DoCmd.OpenReport "InprocChecklist", , , "[YourID] = " & Me.YourID
DoCmd.OpenReport "ISO Certification", , , "[YourID] = " & Me.YourID
DoCmd.OpenReport "VA Form 2280a", , , "[YourID] = " & Me.YourID

Exit_Print_Reports_Click:
Exit Sub

Err_Print_Reports_Click:
MsgBox Err.Description
Resume Exit_Print_Reports_Click

End Sub
___________

The other approach would involve putting the equivalent of this in the
Criteria of the YourID field in the query that the report is based on:
[Forms]![NameOfYourForm]![YourID]

--
Steve Schapel, Microsoft Access MVP

AJ said:
I have a command button that is programmed to run reports when clicked. How
can I change the code below to select only the current record for printing?

Private Sub Print_Reports_Click()
On Error GoTo Err_Print_Reports_Click

Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String
Dim stDocName5 As String

stDocName1 = "BackgroundInvestigationRequest"
stDocName2 = "Business Rules Pg 3"
stDocName3 = "InprocChecklist"
stDocName4 = "ISO Certification"
stDocName5 = "VA Form 2280a"

DoCmd.OpenReport stDocName1, acNormal
DoCmd.OpenReport stDocName2, acNormal
DoCmd.OpenReport stDocName3, acNormal
DoCmd.OpenReport stDocName4, acNormal
DoCmd.OpenReport stDocName5, acNormal

Exit_Print_Reports_Click:
Exit Sub

Err_Print_Reports_Click:
MsgBox Err.Description
Resume Exit_Print_Reports_Click

End Sub
Click to expand...
Click to expand...
 
AJ,

Is the form a continuous view or single view form?

Where is the button that prints the report? Detail section, form header?

If you click the button to print the report for a newly entered record,
it could be that the record has not yet been saved to the table, and
therefore not available to the report. You may need to add code to
explicitly save the record first:
DoCmd.RunCommand acCmdSaveRecord

But why you would get a blank report for an already existing record, at
the moment I can't think of a reason.
 
Steve,

The form is a single view form. The command button is saved in the
form footer. I do have the record being saved now (I figured that one out
during trials today), but my problem I think stems from my record identifier.
In my Query, if I put in a record number in the employeeID field that is not
1, I receive a blank result. I have checked my employeeID field in the table
and it is a long integer autonumber and that part seems to work fine. Is
there a reason my query wouldn't work like that. Maybe I am missing
something more basic here.

AJ

Steve Schapel said:
AJ,

Is the form a continuous view or single view form?

Where is the button that prints the report? Detail section, form header?

If you click the button to print the report for a newly entered record,
it could be that the record has not yet been saved to the table, and
therefore not available to the report. You may need to add code to
explicitly save the record first:
DoCmd.RunCommand acCmdSaveRecord

But why you would get a blank report for an already existing record, at
the moment I can't think of a reason.

--
Steve Schapel, Microsoft Access MVP

AJ said:
Steve,

I changed the query as you suggested with the employeeID criteria set
to [Forms]![TableName]![EmployeeID]. I have two test records in my table and
when I use the print button on record 1 it works fine. However, when I use
it on the second record, it prints blank information. Any ideas how I messed
this one up?

While I was typing this I added a third record and it wont print for that
one either. Please help. Also, I don't see a VBA forum anymore, has it
become the "Programming" forum? Thanks.
Click to expand...
Click to expand...
 
Steve,

That worked like a charm. I am continuing to test it, but for now,
"You're a genius!". Maybe if you have time you can explain to me why it
makes a difference if the button is in the detail section versus the footer,
because I obvioulsy don't know or I wouldn't have had this issue so long. At
one time I was getting fairly decent at this and then I didn't use it for
several years and have forgotten much apparently. Thanks.

AJ
 
Back
Top