Printing 2 consecutive reports

  • Thread starter Thread starter Damian
  • Start date Start date
D

Damian

It seems to me like this shouldn't be as difficult as it
is, but I'm trying to print out two reports with the same
ID field. I want the user to be prompted for the ID number
once, then have both reports print out based on that input.
It seems to me that this should work:

Dim Message as string, IDnum As String
Dim ReportA as string, ReportB As String

ReportA = "rpt_first"
ReportB = "rpt_second"
Message = "Please enter ID number."
IDnum = InputBox(Message)

DoCmd.OpenReport ReportA, acViewNormal, , _
[key_ID] = IDnum
DoCmd.OpenReport ReportB, acViewNormal, , _
[key_ID] = IDnum

but when I try to run it I get the error message "can't
find the field '|' referred to in your expression"
(runtime error 2465) on the Openreport command. I can't
figure out what field they're talking about.

Any ideas?

Damian
 
It seems to me like this shouldn't be as difficult as it
is, but I'm trying to print out two reports with the same
ID field. I want the user to be prompted for the ID number
once, then have both reports print out based on that input.
It seems to me that this should work:

Dim Message as string, IDnum As String
Dim ReportA as string, ReportB As String

ReportA = "rpt_first"
ReportB = "rpt_second"
Message = "Please enter ID number."
IDnum = InputBox(Message)

DoCmd.OpenReport ReportA, acViewNormal, , _
[key_ID] = IDnum
DoCmd.OpenReport ReportB, acViewNormal, , _
[key_ID] = IDnum

but when I try to run it I get the error message "can't
find the field '|' referred to in your expression"
(runtime error 2465) on the Openreport command. I can't
figure out what field they're talking about.

Any ideas?

Damian
Click to expand...

Damian,
You have dimmed IDNum as a String.
When you use the OpenReport method with a Where Clause, the Where
clause must follow a specific syntax, and itself must be a string.

1) Is the [Key_ID] field a String or a Number datatype.
If it is a string then the syntax will be:
' *** Notice the double and single quotes in the where clause. ***
Dim Message as string, IDnum As String
Dim ReportA as string, ReportB As String

ReportA = "rpt_first"
ReportB = "rpt_second"
Message = "Please enter ID number."
IDnum = InputBox(Message)

DoCmd.OpenReport ReportA, acViewNormal, , "[key_ID] = '" & IDnum & "'"
DoCmd.OpenReport ReportB, acViewNormal, , "[key_ID] = '" & IDnum & "'"

However, if [Key_ID] is actually a Number datatype, then you must
change the datatype of the variable IDNum and also the syntax of the
OpenReport.
' *** Note there is no single quote as well as no quotes after the
IDNum.***

Dim Message as string, IDnum As Integer
Dim ReportA as string, ReportB As String

ReportA = "rpt_first"
ReportB = "rpt_second"
Message = "Please enter ID number."
IDnum = InputBox(Message)

DoCmd.OpenReport ReportA, acViewNormal, , "[key_ID] = " & IDnum
DoCmd.OpenReport ReportB, acViewNormal, , "[key_ID] = " & IDnum

I count 8 lines of code above to run the 2 reports.
Is there any reason why dropping that down to 4 lines would create a
problem?

Dim IDNum as String
IDNum = InputBox("Enter the ID Number wanted")
DoCmd.OpenReport "rpt_first", , , "[Key_ID] = '"& IDNum & "'"
DoCmd.OpenReport "rpt_second", , , "[Key_ID] = '"& IDNum & "'"

Note also that because acViewNormal is the default view, it is not
necessary to explicitly state it.
 
Fred,
Thank you very much for your prompt reply. I knew it would
be something simple I was doing wrong; In this case, not
treating the Where clause like a string. I fixed that and
it works like a charm now.
The IDNum is supposed to be a string, and there are in
fact reasons why the code is as many lines long as it is,
so as far as I'm concerned it's all good!

Thanks again,
Damian
 
Back
Top