Printing 2 consecutive reports

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
 
F

fredg

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.
 
D

Damian

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
 
Top