Calling a report from popup form

[Chris]

From my client form the user uses a button to call a popup form that has a
combo box for the selection of that client's invoices. Once selected, I
populate controls from the columns of the combo box for the user to verify.

On the popup/selection form I have a button to call a report preview for
only the invoice selected.

Here is the coding for the button:
Private Sub btnGetReport_Click()
On Error GoTo Err_btnGetReport_Click
Dim stDocName, stLinkCriteria, msg, style, response, title As String
stDocName = "rpt 100 ReservationVoucher"
stLinkCriteria = "[InvNum]=" & Me![InvNum]
title = "Missing Data!"
style = vbOKOnly
msg = "Please make a selection or close this form."
If IsNull(Me.InvNum.Value) Or Me.InvNum = 0 Then
response = MsgBox(msg, style, title)
Exit Sub
End If
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False
Exit_btnGetReport_Click:
Exit Sub
Err_btnGetReport_Click:
MsgBox Err.Description
Resume Exit_btnGetReport_Click
End Sub


The combo box has the following item in its after update event: InvNum =
Format(Me.Combo0.Column(0), "00 0000"). InvNum is one of the verifying
controls.

The results are "Reserved Error", no number, but left justified as if there
should be one.

What am I not seeing? Please help this amateur, never Access schooled and
lightweight.

 

[Wayne Morgan]

You have spaces in the name of the report, it needs to be enclosed in
brackets.

stDocName = "[rpt 100 ReservationVoucher]"

 

[Chris]

Wayne, thanks for replying. Adding the brackets yields a message of
inability to find the report. BTW, I use only quotes around several
reports/forms containing spaces, and successfully. Any other thoughts?
--
Thanks for your help,
Chris

You have spaces in the name of the report, it needs to be enclosed in
brackets.

stDocName = "[rpt 100 ReservationVoucher]"

--
Wayne Morgan
MS Access MVP

From my client form the user uses a button to call a popup form that has a
combo box for the selection of that client's invoices. Once selected, I
populate controls from the columns of the combo box for the user to
verify.

On the popup/selection form I have a button to call a report preview for
only the invoice selected.

Here is the coding for the button:
Private Sub btnGetReport_Click()
On Error GoTo Err_btnGetReport_Click
Dim stDocName, stLinkCriteria, msg, style, response, title As String
stDocName = "rpt 100 ReservationVoucher"
stLinkCriteria = "[InvNum]=" & Me![InvNum]
title = "Missing Data!"
style = vbOKOnly
msg = "Please make a selection or close this form."
If IsNull(Me.InvNum.Value) Or Me.InvNum = 0 Then
response = MsgBox(msg, style, title)
Exit Sub
End If
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False
Exit_btnGetReport_Click:
Exit Sub
Err_btnGetReport_Click:
MsgBox Err.Description
Resume Exit_btnGetReport_Click
End Sub


The combo box has the following item in its after update event: InvNum =
Format(Me.Combo0.Column(0), "00 0000"). InvNum is one of the verifying
controls.

The results are "Reserved Error", no number, but left justified as if
there
should be one.

What am I not seeing? Please help this amateur, never Access schooled and
lightweight.

 

[Wayne Morgan]

Sorry Chris,

I reread your first message, I misread it the first time through. You're
right, it will work without the brackets.

stLinkCriteria = "[InvNum]=" & Me![InvNum]

The combo box has the following item in its after update event: InvNum =
Format(Me.Combo0.Column(0), "00 0000"). InvNum is one of the verifying
controls.

The value in InvNum isn't a number, it is a text string that has numbers in
it. A number wouldn't have the space in it that your formatting creates. The
string being passed to stLinkCriteria has InvNum being concatenated as if it
is a number, not a string.

Try:
stLinkCriteria = "[InvNum]='" & Me![InvNum] & "'"

If that doesn't fix it, have you stepped through the code to see which line
is generating the error?

 

[Chris]

Thanks. InvNum is a currency, formatted to "00 0000"

I've experimented:
The combo box after update event populates "InvNumA", using 'Me.InvNumA =
Format(Me.Combo0.Column(0), "00 0000")'. I've created a second control,
"InvNum", stripped of formatting at the control level, and is populated via
'Me.InvNum = Me.Combo0.Column(0)'. Note that the report has NoData Event of
"cancel=true".

Here's what happens:
1. When report cancel = true -- msg is "The Open Report Action was cancelled."
2. When report cancel commented out -- report is viewed with no data.

Does this help you to help me?
--
Thanks for your help,
Chris

Sorry Chris,

I reread your first message, I misread it the first time through. You're
right, it will work without the brackets.

stLinkCriteria = "[InvNum]=" & Me![InvNum]

The combo box has the following item in its after update event: InvNum =
Format(Me.Combo0.Column(0), "00 0000"). InvNum is one of the verifying
controls.

The value in InvNum isn't a number, it is a text string that has numbers in
it. A number wouldn't have the space in it that your formatting creates. The
string being passed to stLinkCriteria has InvNum being concatenated as if it
is a number, not a string.

Try:
stLinkCriteria = "[InvNum]='" & Me![InvNum] & "'"

If that doesn't fix it, have you stepped through the code to see which line
is generating the error?

--
Wayne Morgan
MS Access MVP

Wayne, thanks for replying. Adding the brackets yields a message of
inability to find the report. BTW, I use only quotes around several
reports/forms containing spaces, and successfully. Any other thoughts?

 

[Douglas J Steele]

In the routine that's opening the form, you need to have error checking that
intercepts the specific error that's raised when the form is closed (2501):

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
' Error 2501 is "The OpenForm action was cancelled"
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume End_cmdOpen_Click
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. InvNum is a currency, formatted to "00 0000"

I've experimented:
The combo box after update event populates "InvNumA", using 'Me.InvNumA =
Format(Me.Combo0.Column(0), "00 0000")'. I've created a second control,
"InvNum", stripped of formatting at the control level, and is populated via
'Me.InvNum = Me.Combo0.Column(0)'. Note that the report has NoData Event of
"cancel=true".

Here's what happens:
1. When report cancel = true -- msg is "The Open Report Action was cancelled."
2. When report cancel commented out -- report is viewed with no data.

Does this help you to help me?
--
Thanks for your help,
Chris

Sorry Chris,

I reread your first message, I misread it the first time through. You're
right, it will work without the brackets.

stLinkCriteria = "[InvNum]=" & Me![InvNum]

The combo box has the following item in its after update event: InvNum =
Format(Me.Combo0.Column(0), "00 0000"). InvNum is one of the verifying
controls.

The value in InvNum isn't a number, it is a text string that has numbers in
it. A number wouldn't have the space in it that your formatting creates. The
string being passed to stLinkCriteria has InvNum being concatenated as if it
is a number, not a string.

Try:
stLinkCriteria = "[InvNum]='" & Me![InvNum] & "'"

If that doesn't fix it, have you stepped through the code to see which line
is generating the error?

--
Wayne Morgan
MS Access MVP

Wayne, thanks for replying. Adding the brackets yields a message of
inability to find the report. BTW, I use only quotes around several
reports/forms containing spaces, and successfully. Any other

thoughts?

 

[Wayne Morgan]

This is a normal occurrence when opening a report by code. Doug has given
you the correct way to handle the 2501 error.

 

[Chris]

OK, did that. traps error 2046: the command or action 'fit to window' isn't
available now.'

This code produces 2046 error:
stLinkCriteria = "[InvNum]=" & Me![InvNum]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False

This code fits report to window, FOR ALL INVNUMs:
stLinkCriteria = "[InvNum]=" & Me![InvNum]
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False

Note that only the stLinkCriteria call was changed. Also note that my combo
box selects only InvNums for the client selected.

Can you see anything here?
--
Thanks for your help,
Chris

In the routine that's opening the form, you need to have error checking that
intercepts the specific error that's raised when the form is closed (2501):

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
' Error 2501 is "The OpenForm action was cancelled"
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume End_cmdOpen_Click
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. InvNum is a currency, formatted to "00 0000"

I've experimented:
The combo box after update event populates "InvNumA", using 'Me.InvNumA =
Format(Me.Combo0.Column(0), "00 0000")'. I've created a second control,
"InvNum", stripped of formatting at the control level, and is populated via
'Me.InvNum = Me.Combo0.Column(0)'. Note that the report has NoData Event of
"cancel=true".

Here's what happens:
1. When report cancel = true -- msg is "The Open Report Action was cancelled."
2. When report cancel commented out -- report is viewed with no data.

Does this help you to help me?
--
Thanks for your help,
Chris

Sorry Chris,

I reread your first message, I misread it the first time through. You're
right, it will work without the brackets.

stLinkCriteria = "[InvNum]=" & Me![InvNum]

The combo box has the following item in its after update event: InvNum =
Format(Me.Combo0.Column(0), "00 0000"). InvNum is one of the verifying
controls.

The value in InvNum isn't a number, it is a text string that has numbers in
it. A number wouldn't have the space in it that your formatting creates. The
string being passed to stLinkCriteria has InvNum being concatenated as if it
is a number, not a string.

Try:
stLinkCriteria = "[InvNum]='" & Me![InvNum] & "'"

If that doesn't fix it, have you stepped through the code to see which line
is generating the error?

--
Wayne Morgan
MS Access MVP


Wayne, thanks for replying. Adding the brackets yields a message of
inability to find the report. BTW, I use only quotes around several
reports/forms containing spaces, and successfully. Any other thoughts?

 

[Wayne Morgan]

This may be a timing problem. I tried running that code from a module right
after opening a report and it worked. However, if you just clicked on a
button to open the report and the form still has the focus, the command may
be trying to execute on the form instead of the report.

As a test, I ran this from a form:

DoCmd.OpenReport "Report1", acViewPreview
Me.SetFocus
DoCmd.RunCommand acCmdFitToWindow

and it caused the error since the form had the focus. Try setting focus to
the report before you issue the fit to window command.

Example:
stLinkCriteria = "[InvNum]=" & Me![InvNum]
DoCmd.OpenReport stDocName, acPreview
DoEvents 'this is to give the report a chance to open
Reports(stDocName).SetFocus
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False


--
Wayne Morgan
MS Access MVP

OK, did that. traps error 2046: the command or action 'fit to window'
isn't
available now.'

This code produces 2046 error:
stLinkCriteria = "[InvNum]=" & Me![InvNum]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False

This code fits report to window, FOR ALL INVNUMs:
stLinkCriteria = "[InvNum]=" & Me![InvNum]
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False

Note that only the stLinkCriteria call was changed. Also note that my
combo
box selects only InvNums for the client selected.

Can you see anything here?
--
Thanks for your help,
Chris

In the routine that's opening the form, you need to have error checking
that
intercepts the specific error that's raised when the form is closed
(2501):

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
' Error 2501 is "The OpenForm action was cancelled"
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume End_cmdOpen_Click
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. InvNum is a currency, formatted to "00 0000"

I've experimented:
The combo box after update event populates "InvNumA", using 'Me.InvNumA
=
Format(Me.Combo0.Column(0), "00 0000")'. I've created a second
control,
"InvNum", stripped of formatting at the control level, and is populated via
'Me.InvNum = Me.Combo0.Column(0)'. Note that the report has NoData
Event of
"cancel=true".

Here's what happens:
1. When report cancel = true -- msg is "The Open Report Action was cancelled."
2. When report cancel commented out -- report is viewed with no data.

Does this help you to help me?
--
Thanks for your help,
Chris


:

Sorry Chris,

I reread your first message, I misread it the first time through.
You're
right, it will work without the brackets.

stLinkCriteria = "[InvNum]=" & Me![InvNum]

The combo box has the following item in its after update event: InvNum =
Format(Me.Combo0.Column(0), "00 0000"). InvNum is one of the verifying
controls.

The value in InvNum isn't a number, it is a text string that has
numbers in
it. A number wouldn't have the space in it that your formatting
creates. The
string being passed to stLinkCriteria has InvNum being concatenated
as if it
is a number, not a string.

Try:
stLinkCriteria = "[InvNum]='" & Me![InvNum] & "'"

If that doesn't fix it, have you stepped through the code to see
which line
is generating the error?

--
Wayne Morgan
MS Access MVP


Wayne, thanks for replying. Adding the brackets yields a message
of
inability to find the report. BTW, I use only quotes around
several
reports/forms containing spaces, and successfully. Any other thoughts?

 

[Chris]

Again, thanks for helping.

I used the code (I added stLinkCriteria to the DoCmd OpenReport) and get the
following message:

"2451: The report name 'rpt 100 ReservationVoucher' you entered is
misspelled or refers to a report that isn't open or doesn't exist.

My complete code follows:

Private Sub btnGetReport_Click()
On Error GoTo Err_btnGetReport_Click

Dim stLinkCriteria, stDocName As String
stLinkCriteria = "[InvNum]=" & Me![InvNum]
stDocName = "rpt 100 ReservationVoucher"

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoEvents
Reports(stDocName).SetFocus
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False

End_btnGetReport_Click:
Exit Sub
Err_btnGetReport_Click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume End_btnGetReport_Click
End If
End Sub

I am very grateful for your time.
--
Thanks for your help,
Chris

This may be a timing problem. I tried running that code from a module right
after opening a report and it worked. However, if you just clicked on a
button to open the report and the form still has the focus, the command may
be trying to execute on the form instead of the report.

As a test, I ran this from a form:

DoCmd.OpenReport "Report1", acViewPreview
Me.SetFocus
DoCmd.RunCommand acCmdFitToWindow

and it caused the error since the form had the focus. Try setting focus to
the report before you issue the fit to window command.

Example:
stLinkCriteria = "[InvNum]=" & Me![InvNum]
DoCmd.OpenReport stDocName, acPreview
DoEvents 'this is to give the report a chance to open
Reports(stDocName).SetFocus
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False


--
Wayne Morgan
MS Access MVP

OK, did that. traps error 2046: the command or action 'fit to window'
isn't
available now.'

This code produces 2046 error:
stLinkCriteria = "[InvNum]=" & Me![InvNum]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False

This code fits report to window, FOR ALL INVNUMs:
stLinkCriteria = "[InvNum]=" & Me![InvNum]
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False

Note that only the stLinkCriteria call was changed. Also note that my
combo
box selects only InvNums for the client selected.

Can you see anything here?
--
Thanks for your help,
Chris

In the routine that's opening the form, you need to have error checking
that
intercepts the specific error that's raised when the form is closed
(2501):

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
' Error 2501 is "The OpenForm action was cancelled"
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume End_cmdOpen_Click
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. InvNum is a currency, formatted to "00 0000"

I've experimented:
The combo box after update event populates "InvNumA", using 'Me.InvNumA
=
Format(Me.Combo0.Column(0), "00 0000")'. I've created a second
control,
"InvNum", stripped of formatting at the control level, and is populated
via
'Me.InvNum = Me.Combo0.Column(0)'. Note that the report has NoData
Event
of
"cancel=true".

Here's what happens:
1. When report cancel = true -- msg is "The Open Report Action was
cancelled."
2. When report cancel commented out -- report is viewed with no data.

Does this help you to help me?
--
Thanks for your help,
Chris


:

Sorry Chris,

I reread your first message, I misread it the first time through.
You're
right, it will work without the brackets.

stLinkCriteria = "[InvNum]=" & Me![InvNum]

The combo box has the following item in its after update event:
InvNum =
Format(Me.Combo0.Column(0), "00 0000"). InvNum is one of the
verifying
controls.

The value in InvNum isn't a number, it is a text string that has
numbers
in
it. A number wouldn't have the space in it that your formatting
creates.
The
string being passed to stLinkCriteria has InvNum being concatenated
as
if it
is a number, not a string.

Try:
stLinkCriteria = "[InvNum]='" & Me![InvNum] & "'"

If that doesn't fix it, have you stepped through the code to see
which
line
is generating the error?

--
Wayne Morgan
MS Access MVP


Wayne, thanks for replying. Adding the brackets yields a message
of
inability to find the report. BTW, I use only quotes around
several
reports/forms containing spaces, and successfully. Any other
thoughts?

 

[Wayne Morgan]

Well, as the error says, you've either misspelled the report name or the
report isn't open. Which line of code generates this error, the
DoCmd.OpenReport or the SetFocus? If it is the first one, then you've
misspelled something. If it is the second one, then a timing problem is the
problem, the report isn't open yet. There is a way to force a loop in the
code until the report is open, but we need to know if that's the problem by
finding out which line of code is generating the error.

 

[Chris]

Commenting out the SetFocus line results in the FitToWindow problem again.
--
Thanks for your help,
Chris

Well, as the error says, you've either misspelled the report name or the
report isn't open. Which line of code generates this error, the
DoCmd.OpenReport or the SetFocus? If it is the first one, then you've
misspelled something. If it is the second one, then a timing problem is the
problem, the report isn't open yet. There is a way to force a loop in the
code until the report is open, but we need to know if that's the problem by
finding out which line of code is generating the error.

--
Wayne Morgan
MS Access MVP

Again, thanks for helping.

I used the code (I added stLinkCriteria to the DoCmd OpenReport) and get
the
following message:

"2451: The report name 'rpt 100 ReservationVoucher' you entered is
misspelled or refers to a report that isn't open or doesn't exist.

My complete code follows:

Private Sub btnGetReport_Click()
On Error GoTo Err_btnGetReport_Click

Dim stLinkCriteria, stDocName As String
stLinkCriteria = "[InvNum]=" & Me![InvNum]
stDocName = "rpt 100 ReservationVoucher"

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoEvents
Reports(stDocName).SetFocus
DoCmd.RunCommand acCmdFitToWindow
Me.Visible = False

End_btnGetReport_Click:
Exit Sub
Err_btnGetReport_Click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume End_btnGetReport_Click
End If
End Sub

 

[Wayne Morgan]

In that case, it sounds as if the report isn't open yet. How long does it
take the report to open and display data? Does the report display if you
comment out the Fit To Window line? Could this be caused when you get the
2501 error because you are using Resume Next instead of Resume
End_btnGetReport_Click. So, the code is going to the next line after the
OpenReport line, but the report isn't open (the reason for the 2501 error in
the first place) and an error gets generated. If so, change the Resume line
in the 2501 If statement.

One possible way to set up the loop to wait for the report to open would be
in your error handler. The problem with any type of loop is that if the
report never opens, the loop will never end. It may be worth while getting
the current time when you start into the loop and drop out of the loop if
too much time has passed.

Example:
Dim dteTime As Date

'before the line that is expected to generate the error
dteTime = Now

'Then in the error handler
If Err.Number = 2451 Then
DoEvents 'release processing time to the report
If DateDiff("n", dteTime, Now) > 2 Then 'allow 2 minutes
MsgBox "Appears to be stuck. Exiting!"
Resume End_btnGetReport_Click
Else
Resume 'retry the line that generated the error
End If
End If

 

[Chris]

Finally joy!

I changed my theory of design to a continuous form away from the combo box
and I can call the form for correct invoice now.

Thanks. I actually learned from this process.