|
|
|
date: Wed, 30 Jul 2008 10:21:01 -0700,
group: microsoft.public.access.macros
back
Re: Selecting Record for Report Macro
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 Raiber wrote:
> 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
date: Thu, 31 Jul 2008 12:29:45 +1200
author: Steve Schapel
Re: Selecting Record for Report Macro
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" wrote:
> 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 Raiber wrote:
> > 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
>
date: Thu, 31 Jul 2008 07:01:00 -0700
author: AJ Raiber
Re: Selecting Record for Report Macro
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 Raiber wrote:
> 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.
date: Fri, 01 Aug 2008 08:05:26 +1200
author: Steve Schapel
Re: Selecting Record for Report Macro
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" wrote:
> 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 Raiber wrote:
> > 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.
>
date: Thu, 31 Jul 2008 13:14:40 -0700
author: AJ Raiber
Re: Selecting Record for Report Macro
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
"Steve Schapel" wrote:
> AJ,
>
> Try putting the command button in the Detail section of the form.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> AJ Raiber wrote:
> > 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.
> >
>
date: Fri, 1 Aug 2008 07:16:09 -0700
author: AJ Raiber
|
|
