Displaying a report from a form

[Bobk]

I have a form used to input data for order entry. When the data is entered I
have a button that displays a report with the data on the order entry form.
The report serves as an order acknowledgement. When I click on the button I
get the error message "Data type mismatch in criteria expression". The code I
am using in the form is:

Private Sub Command188_Click()
On Error GoTo Err_Command188_Click

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "OEack-rpt", acViewPreview, , "[oeheader_oejobnumber]="
& [oejobnumber]

Exit_Command188_Click:
Exit Sub

Err_Command188_Click:
MsgBox Err.Description
Resume Exit_Command188_Click

End Sub


The report has this code for a filter:


([oeheader_oejobnumber]= [oejobnumber])



And the query has this code:

oeheader_oejobnumber: oejobnumber


The key, oejobnumber is text.



I have also tried this code in the form:

stDocName = "OEack-rpt"
DoCmd.OpenReport stDocName, acPreview

- And without the added code for the filter in the report and the code in
the query. This works except it only displays data from the first record in
the order entry file and not data being displayed on the current form.


What am I doing wrong?

 

[Allen Browne]

Since oeheader_oejobnumber is a Text field, you need extra quotes:
DoCmd.OpenReport "OEack-rpt", acViewPreview, , _
"[oeheader_oejobnumber] = """ & [oejobnumber] & """"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Bobk]

Thanks. The error message went away, but I now have no data showing on the
report. I think I have a problem with the report filter code. Normally I
would see the oejobnumber show up in the filter box. That doesn't appear
anymore. Somehow the report is not passing on the oejobnumber to the query.
Any ideas on what to look for?
I have code similar to this working ok, but the key is numeric. Could that
be the problem?

Since oeheader_oejobnumber is a Text field, you need extra quotes:
DoCmd.OpenReport "OEack-rpt", acViewPreview, , _
"[oeheader_oejobnumber] = """ & [oejobnumber] & """"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form used to input data for order entry. When the data is entered
I
have a button that displays a report with the data on the order entry
form.
The report serves as an order acknowledgement. When I click on the button
I
get the error message "Data type mismatch in criteria expression". The
code I
am using in the form is:

Private Sub Command188_Click()
On Error GoTo Err_Command188_Click

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "OEack-rpt", acViewPreview, , "[oeheader_oejobnumber]="
& [oejobnumber]

Exit_Command188_Click:
Exit Sub

Err_Command188_Click:
MsgBox Err.Description
Resume Exit_Command188_Click

End Sub


The report has this code for a filter:


([oeheader_oejobnumber]= [oejobnumber])



And the query has this code:

oeheader_oejobnumber: oejobnumber


The key, oejobnumber is text.



I have also tried this code in the form:

stDocName = "OEack-rpt"
DoCmd.OpenReport stDocName, acPreview

- And without the added code for the filter in the report and the code
in
the query. This works except it only displays data from the first record
in
the order entry file and not data being displayed on the current form.


What am I doing wrong?

 

[Allen Browne]

The principle still works for Text field, so something else is going on
here.

On your form, is this a combo box?
If so, look at its Column Widths property (in design view.)
If the Bound column is not the display column (e.g. if it is zero-width),
the actual data may not be what you see in the box.

To help debug it, use a String variable, like this:
Dim strWhere As String
strWhere = "[oeheader_oejobnumber] = """ & [oejobnumber] & """"
Debug.Print strWhere
Stop
DoCmd.OpenReport "OEack-rpt", acViewPreview, , strWhere

Run the code. When it stops, open the Immediate Window (Ctrl+G) to see what
was printed there. Press F5 to resume. After the report opens, check the
actual filter of the report like this:
? Reports("OEack-rpt").Filter

Hopefully you will see where things are going wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. The error message went away, but I now have no data showing on the
report. I think I have a problem with the report filter code. Normally I
would see the oejobnumber show up in the filter box. That doesn't appear
anymore. Somehow the report is not passing on the oejobnumber to the
query.
Any ideas on what to look for?
I have code similar to this working ok, but the key is numeric. Could that
be the problem?

Since oeheader_oejobnumber is a Text field, you need extra quotes:
DoCmd.OpenReport "OEack-rpt", acViewPreview, , _
"[oeheader_oejobnumber] = """ & [oejobnumber] & """"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

I have a form used to input data for order entry. When the data is
entered
I
have a button that displays a report with the data on the order entry
form.
The report serves as an order acknowledgement. When I click on the
button
I
get the error message "Data type mismatch in criteria expression". The
code I
am using in the form is:

Private Sub Command188_Click()
On Error GoTo Err_Command188_Click

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "OEack-rpt", acViewPreview, ,
"[oeheader_oejobnumber]="
& [oejobnumber]

Exit_Command188_Click:
Exit Sub

Err_Command188_Click:
MsgBox Err.Description
Resume Exit_Command188_Click

End Sub


The report has this code for a filter:


([oeheader_oejobnumber]= [oejobnumber])



And the query has this code:

oeheader_oejobnumber: oejobnumber


The key, oejobnumber is text.



I have also tried this code in the form:

stDocName = "OEack-rpt"
DoCmd.OpenReport stDocName, acPreview

- And without the added code for the filter in the report and the
code
in
the query. This works except it only displays data from the first
record
in
the order entry file and not data being displayed on the current form.


What am I doing wrong?

 

[Bobk]

Thank you for the help. I looked up your instructions for "Print the record
in the form". It was very helpful. I think the [oeheader_oejobnumber] was
creating a problem. I changed it to [oejobnumber] and things began to work.
The filter in the report started to work. Thanks again. I would be lost
without the help I get from you guys.

The principle still works for Text field, so something else is going on
here.

On your form, is this a combo box?
If so, look at its Column Widths property (in design view.)
If the Bound column is not the display column (e.g. if it is zero-width),
the actual data may not be what you see in the box.

To help debug it, use a String variable, like this:
Dim strWhere As String
strWhere = "[oeheader_oejobnumber] = """ & [oejobnumber] & """"
Debug.Print strWhere
Stop
DoCmd.OpenReport "OEack-rpt", acViewPreview, , strWhere

Run the code. When it stops, open the Immediate Window (Ctrl+G) to see what
was printed there. Press F5 to resume. After the report opens, check the
actual filter of the report like this:
? Reports("OEack-rpt").Filter

Hopefully you will see where things are going wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. The error message went away, but I now have no data showing on the
report. I think I have a problem with the report filter code. Normally I
would see the oejobnumber show up in the filter box. That doesn't appear
anymore. Somehow the report is not passing on the oejobnumber to the
query.
Any ideas on what to look for?
I have code similar to this working ok, but the key is numeric. Could that
be the problem?

Since oeheader_oejobnumber is a Text field, you need extra quotes:
DoCmd.OpenReport "OEack-rpt", acViewPreview, , _
"[oeheader_oejobnumber] = """ & [oejobnumber] & """"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

I have a form used to input data for order entry. When the data is
entered
I
have a button that displays a report with the data on the order entry
form.
The report serves as an order acknowledgement. When I click on the
button
I
get the error message "Data type mismatch in criteria expression". The
code I
am using in the form is:

Private Sub Command188_Click()
On Error GoTo Err_Command188_Click

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "OEack-rpt", acViewPreview, ,
"[oeheader_oejobnumber]="
& [oejobnumber]

Exit_Command188_Click:
Exit Sub

Err_Command188_Click:
MsgBox Err.Description
Resume Exit_Command188_Click

End Sub


The report has this code for a filter:


([oeheader_oejobnumber]= [oejobnumber])



And the query has this code:

oeheader_oejobnumber: oejobnumber


The key, oejobnumber is text.



I have also tried this code in the form:

stDocName = "OEack-rpt"
DoCmd.OpenReport stDocName, acPreview

- And without the added code for the filter in the report and the
code
in
the query. This works except it only displays data from the first
record
in
the order entry file and not data being displayed on the current form.


What am I doing wrong?