Form Event Procedure to Report

[ladybug via AccessMonster.com]

I have a form called frm_pip_at. On this form I have a text box called
pip_id. I also have a command button with an on click even procedure. This
opens a report called rpt_pip_at. The report also has the text box pip_id.
When the command button is clicked I want the report to open in print preview
and the pip_id that was on the form is now opened on the report. Can someon
help me with the code for this?

This is what I have:

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "rpt_pip_at"

stLinkCriteria = "[pip_id]=" & "'" & Me![pip_id] & "'"
DoCmd.OpenReport stDocName, , , stLinkCriteria

Where do I put to open it in print preview. It is trying to print it. I am
also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[Klatuu]

The data type mismatch is probably bcause pip_id is a numeric field. The
report wants to print because you have not given it the argument to set it to
preview mode. Try this:

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview , , stLinkCriteria

 

[ladybug via AccessMonster.com]

I tried what you gave me and it opened it in print preview and I am not
getting the error anymore. However, when the report opens there is no data
in any of the textboxes.
Any suggestions?

The data type mismatch is probably bcause pip_id is a numeric field. The
report wants to print because you have not given it the argument to set it to
preview mode. Try this:

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview , , stLinkCriteria

I have a form called frm_pip_at. On this form I have a text box called
pip_id. I also have a command button with an on click even procedure. This

[quoted text clipped - 17 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP
If the record you are wanting to do the report for is a new record, then it
will not yet be in the table, so the report will not find it. If it is an
existing record (one that was in the table when you opened the form) and it
is not showing, then likely you are using a query as the record source of the
report and it is not correct.

If the problem is that it is a new record, then you have to force a save of
the record so it will be in the table for the report to find it. You do that
with a form requery. But, a requery causes the form to return to the first
record in the form's recordset, so you have to keep track of which record you
were on and navigate back to it before you try to print the report. Here is
a sample of how to do that:

Dim lngPipID

lngPipID = Me.pip_id
Me.Requery
With Me.RecordsetClone
.FindFirst "[pip_id] = " & lngPipID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview , , stLinkCriteria

I tried what you gave me and it opened it in print preview and I am not
getting the error anymore. However, when the report opens there is no data
in any of the textboxes.
Any suggestions?

The data type mismatch is probably bcause pip_id is a numeric field. The
report wants to print because you have not given it the argument to set it to
preview mode. Try this:

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview , , stLinkCriteria

I have a form called frm_pip_at. On this form I have a text box called
pip_id. I also have a command button with an on click even procedure. This

[quoted text clipped - 17 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[ladybug via AccessMonster.com]

I put in the code you gave me and I rcvd this error:
The action or method requires Report Name arguement.

I then went back and put this in:
Dim lngPipID

stDocName = "rpt_pip_at"

lngPipID = Me.pip_id
Me.Requery
With Me.RecordsetClone
.FindFirst "[pip_id] = " & lngPipID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

No I get this error:
Syntax error (missing operator) in query expression '[pip_id]=',

I am stuck again. When I hit debug it highlights DoCmd.OpenReport stDocName,
acViewPreview, , stLinkCriteria. I don't understand what is wrong.
I appreciate your help!

I tried what you gave me and it opened it in print preview and I am not
getting the error anymore. However, when the report opens there is no data

[quoted text clipped - 13 lines]

 

[Klatuu]

Not sure about that. Here are some things to check.
Is there a value in Me![pip_id] ?
Is [pip_id] a numeric field?

You might try running the code in debug mode. Put a breakpoint on this line:
stDocName = "rpt_pip_at"

Then trace the code and check the value of each varialbe, control, and field
involved as you proceed.


stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

--
Dave Hargis, Microsoft Access MVP

I put in the code you gave me and I rcvd this error:
The action or method requires Report Name arguement.

I then went back and put this in:
Dim lngPipID

stDocName = "rpt_pip_at"

lngPipID = Me.pip_id
Me.Requery
With Me.RecordsetClone
.FindFirst "[pip_id] = " & lngPipID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

No I get this error:
Syntax error (missing operator) in query expression '[pip_id]=',

I am stuck again. When I hit debug it highlights DoCmd.OpenReport stDocName,
acViewPreview, , stLinkCriteria. I don't understand what is wrong.
I appreciate your help!

I tried what you gave me and it opened it in print preview and I am not
getting the error anymore. However, when the report opens there is no data

[quoted text clipped - 13 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[ladybug via AccessMonster.com]

Yes, I can see the pip_id on the form as soon as I enter data in the first
field of the form.
Yes, pip_id is an autonumber in tbl_pip_at
I do not know what you mean by running in debug mode and entering a
breakpoint

Sorry...i do not know much about VB

Not sure about that. Here are some things to check.
Is there a value in Me![pip_id] ?
Is [pip_id] a numeric field?

You might try running the code in debug mode. Put a breakpoint on this line:
stDocName = "rpt_pip_at"

Then trace the code and check the value of each varialbe, control, and field
involved as you proceed.

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

I put in the code you gave me and I rcvd this error:
The action or method requires Report Name arguement.

[quoted text clipped - 27 lines]

 

[Klatuu]

Open the VB editor
Put your cursor on the line of code I suggested
Press F9
Open the form in form view.
Click the button that runs the report.
Now in the VBE, the line will be highlighed in Yellow.
Each time you press F8, the highlighted line will execute and the next line
to be executed will highlight.
You can hover the mouse over variables and you will get a control tip type
of box that will show the value. It works with some objects, but not all.
You can use the Debug menu to set a watch and you can see what the value of
any object or variable. So you just F* your way through the code looking at
things until you determine what the error is.
--
Dave Hargis, Microsoft Access MVP

Yes, I can see the pip_id on the form as soon as I enter data in the first
field of the form.
Yes, pip_id is an autonumber in tbl_pip_at
I do not know what you mean by running in debug mode and entering a
breakpoint

Sorry...i do not know much about VB

Not sure about that. Here are some things to check.
Is there a value in Me![pip_id] ?
Is [pip_id] a numeric field?

You might try running the code in debug mode. Put a breakpoint on this line:
stDocName = "rpt_pip_at"

Then trace the code and check the value of each varialbe, control, and field
involved as you proceed.

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

I put in the code you gave me and I rcvd this error:
The action or method requires Report Name arguement.

[quoted text clipped - 27 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[ladybug via AccessMonster.com]

Ok, i followed your instructions. Throughout the code when I would run the
cursor over the code the id equaled 25, which is correct. Then when it got
to:
stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
pip_id did not = anything.

Here is the complete code that i have:

Private Sub Command28_Click()
Dim lngPipID

stDocName = "rpt_pip_at"

lngPipID = Me.pip_id
Me.Requery
With Me.RecordsetClone
.FindFirst "[pip_id] = " & lngPipID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Thank you again for all your help!

Open the VB editor
Put your cursor on the line of code I suggested
Press F9
Open the form in form view.
Click the button that runs the report.
Now in the VBE, the line will be highlighed in Yellow.
Each time you press F8, the highlighted line will execute and the next line
to be executed will highlight.
You can hover the mouse over variables and you will get a control tip type
of box that will show the value. It works with some objects, but not all.
You can use the Debug menu to set a watch and you can see what the value of
any object or variable. So you just F* your way through the code looking at
things until you determine what the error is.

Yes, I can see the pip_id on the form as soon as I enter data in the first
field of the form.

[quoted text clipped - 22 lines]

 

[Klatuu]

Is it still not working? I could not tell from your post.
--
Dave Hargis, Microsoft Access MVP

Ok, i followed your instructions. Throughout the code when I would run the
cursor over the code the id equaled 25, which is correct. Then when it got
to:
stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
pip_id did not = anything.

Here is the complete code that i have:

Private Sub Command28_Click()
Dim lngPipID

stDocName = "rpt_pip_at"

lngPipID = Me.pip_id
Me.Requery
With Me.RecordsetClone
.FindFirst "[pip_id] = " & lngPipID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

stLinkCriteria = "[pip_id]=" & Me![pip_id]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Thank you again for all your help!

Open the VB editor
Put your cursor on the line of code I suggested
Press F9
Open the form in form view.
Click the button that runs the report.
Now in the VBE, the line will be highlighed in Yellow.
Each time you press F8, the highlighted line will execute and the next line
to be executed will highlight.
You can hover the mouse over variables and you will get a control tip type
of box that will show the value. It works with some objects, but not all.
You can use the Debug menu to set a watch and you can see what the value of
any object or variable. So you just F* your way through the code looking at
things until you determine what the error is.

Yes, I can see the pip_id on the form as soon as I enter data in the first
field of the form.

[quoted text clipped - 22 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[ladybug via AccessMonster.com]

No it is not. It opens up the vb code and hightlights stDocName =
"rpt_pip_at"
I went back to the vb and selcted F9. Then when I click the button i still
get Run Time Error 3075
Syntax Error (missing operator) in query expression '[pid_id]='

Is it still not working? I could not tell from your post.

Ok, i followed your instructions. Throughout the code when I would run the
cursor over the code the id equaled 25, which is correct. Then when it got

[quoted text clipped - 42 lines]

 

[Klatuu]

It seems the problem revolves around the control me![pip_id]. It seems this
control has no value. Is there such a control on your report? If so, what
is in it at the time you try to run the report?
--
Dave Hargis, Microsoft Access MVP

No it is not. It opens up the vb code and hightlights stDocName =
"rpt_pip_at"
I went back to the vb and selcted F9. Then when I click the button i still
get Run Time Error 3075
Syntax Error (missing operator) in query expression '[pid_id]='

Is it still not working? I could not tell from your post.

Ok, i followed your instructions. Throughout the code when I would run the
cursor over the code the id equaled 25, which is correct. Then when it got

[quoted text clipped - 42 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[ladybug via AccessMonster.com]

Yes, the report has a text box called pip_id. When I open the report the
pip_id is the first id from the table.
I cannot see what the id is when clicking the button from the form, because
it gives me that error and won't open the report.

It seems the problem revolves around the control me![pip_id]. It seems this
control has no value. Is there such a control on your report? If so, what
is in it at the time you try to run the report?

No it is not. It opens up the vb code and hightlights stDocName =
"rpt_pip_at"

[quoted text clipped - 8 lines]

 

[Klatuu]

I overlooked this statement earlier:
Syntax Error (missing operator) in query expression '[pid_id]='

It is saying there is a problem in the query. Can you post the SQL of the
query that is the report's record source, please.

--
Dave Hargis, Microsoft Access MVP

Yes, the report has a text box called pip_id. When I open the report the
pip_id is the first id from the table.
I cannot see what the id is when clicking the button from the form, because
it gives me that error and won't open the report.

It seems the problem revolves around the control me![pip_id]. It seems this
control has no value. Is there such a control on your report? If so, what
is in it at the time you try to run the report?

No it is not. It opens up the vb code and hightlights stDocName =
"rpt_pip_at"

[quoted text clipped - 8 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[ladybug via AccessMonster.com]

It is
SELECT tbl_PIP_AT.*, tblEmployeeDetail.chrEmployeeLastName, tblEmployeeDetail.
chrEmployeeFirstName
FROM tbl_PIP_AT INNER JOIN tblEmployeeDetail ON tbl_PIP_AT.chrUserID =
tblEmployeeDetail.chrUserID;

I overlooked this statement earlier:
Syntax Error (missing operator) in query expression '[pid_id]='

It is saying there is a problem in the query. Can you post the SQL of the
query that is the report's record source, please.

Yes, the report has a text box called pip_id. When I open the report the
pip_id is the first id from the table.

[quoted text clipped - 9 lines]

 

[Klatuu]

Sorry, but this is very unusual. Everything looks straightforward and should
work.
What happens when you run the query by itself?
--
Dave Hargis, Microsoft Access MVP

It is
SELECT tbl_PIP_AT.*, tblEmployeeDetail.chrEmployeeLastName, tblEmployeeDetail.
chrEmployeeFirstName
FROM tbl_PIP_AT INNER JOIN tblEmployeeDetail ON tbl_PIP_AT.chrUserID =
tblEmployeeDetail.chrUserID;

I overlooked this statement earlier:
Syntax Error (missing operator) in query expression '[pid_id]='

It is saying there is a problem in the query. Can you post the SQL of the
query that is the report's record source, please.

Yes, the report has a text box called pip_id. When I open the report the
pip_id is the first id from the table.

[quoted text clipped - 9 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[ladybug via AccessMonster.com]

It returns all of the entries from the table tbl_pip_at.

Sorry, but this is very unusual. Everything looks straightforward and should
work.
What happens when you run the query by itself?

It is
SELECT tbl_PIP_AT.*, tblEmployeeDetail.chrEmployeeLastName, tblEmployeeDetail.

[quoted text clipped - 13 lines]

 

[Klatuu]

I assume this line is the problem because it is the closest to the partial
line you posted.
stLinkCriteria = "[pip_id]=" & Me![pip_id]
If so, there are only two things to look at. Does the form control [pip_id]
have a value in it?
And, what is the data type of [pip_id] in the table?
As written, the code is expecting [pip_id] to be a numeric field.
Since the code is chocking on that line and your query runs by itself. then
the actual error is happening on this line:
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

So either there is an invalid value in Me![pip_id] or the data type is
incorrect, but based on the error, I suspect there is no value in the control.
--
Dave Hargis, Microsoft Access MVP

It returns all of the entries from the table tbl_pip_at.

Sorry, but this is very unusual. Everything looks straightforward and should
work.
What happens when you run the query by itself?

It is
SELECT tbl_PIP_AT.*, tblEmployeeDetail.chrEmployeeLastName, tblEmployeeDetail.

[quoted text clipped - 13 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!

 

[ladybug via AccessMonster.com]

There are 6 text boxes on the form I am using. When I enter data in the
first text box. The text box pip_id populates. Pip_id in the table is an
Autonumber.

Yes, when I get the Syntax error and then I hit Debug it takes me to the vb
and highlights DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

I assume this line is the problem because it is the closest to the partial
line you posted.
stLinkCriteria = "[pip_id]=" & Me![pip_id]
If so, there are only two things to look at. Does the form control [pip_id]
have a value in it?
And, what is the data type of [pip_id] in the table?
As written, the code is expecting [pip_id] to be a numeric field.
Since the code is chocking on that line and your query runs by itself. then
the actual error is happening on this line:
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

So either there is an invalid value in Me![pip_id] or the data type is
incorrect, but based on the error, I suspect there is no value in the control.

It returns all of the entries from the table tbl_pip_at.

[quoted text clipped - 6 lines]

 

[Klatuu]

Sorry, but I am at a total loss on this. I have reviewed the code several
times and do not see what the problem could be. There should be a value your
control, but you are telling me there is none. Without the database in front
of me, I don't know what else to offer.
--
Dave Hargis, Microsoft Access MVP

There are 6 text boxes on the form I am using. When I enter data in the
first text box. The text box pip_id populates. Pip_id in the table is an
Autonumber.

Yes, when I get the Syntax error and then I hit Debug it takes me to the vb
and highlights DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

I assume this line is the problem because it is the closest to the partial
line you posted.
stLinkCriteria = "[pip_id]=" & Me![pip_id]
If so, there are only two things to look at. Does the form control [pip_id]
have a value in it?
And, what is the data type of [pip_id] in the table?
As written, the code is expecting [pip_id] to be a numeric field.
Since the code is chocking on that line and your query runs by itself. then
the actual error is happening on this line:
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

So either there is an invalid value in Me![pip_id] or the data type is
incorrect, but based on the error, I suspect there is no value in the control.

It returns all of the entries from the table tbl_pip_at.

[quoted text clipped - 6 lines]

also getting an error that "Data type mismatch in criteria expression." Do I
need to rename somthing? Please help!