Button to open report

  • Thread starter Thread starter Wayne Livingstone
  • Start date Start date
W

Wayne Livingstone

I have a button on my form that is supposed to open a
report showing data relating to the currently displayed
record in the form. I have the following code on the On
Cleck event of the button:

Private Sub PreviewPrintReq_Click()
On Error GoTo PreviewPrintReq_Err


Dim strSqlSelect As String
Dim strSqlWhere As String
Dim strSqlExpr As String

strSqlSelect = "SELECT PrintReqID " & _
"FROM [Print_Requisition] "

strSqlWhere = " WHERE [Print_Requisition].PrintReqID =" _
& Chr$(34) &
[Forms]![PrintRequisition]![PrintReqID] & Chr$(34)

strSqlExpr = strSqlSelect & strSqlWhere & ";"

If IsNull(Me![PrintReqID]) Then
MsgBox "Enter printing data before previewing the
Printing Requisition report."
Else
DoCmd.OpenReport "PrintRequisition Report",
acPreview, , strSqlExpr
End If

PreviewPrintReq_Exit:
Exit Sub

PreviewPrintReq_Err:
MsgBox Err.Description
Resume PreviewPrintReq_Exit
End Sub

I get the error: Data type mismatch in criteria expression.
The report itself seems to work ok on its own and displays
data for all records.
 
If you open the Print_Requisition table in design view, what is the Data
Type of the PrintReqID?

If it is a Number type (not a Text type), drop the extra quotes, i.e.:

strSqlWhere = "WHERE [Print_Requisition].PrintReqID =" & _
[Forms]![PrintRequisition]![PrintReqID]
 
You were right...it was an autonumber field. So now I dont
get any error message and the report opens, but it still
displays all PrintReqID records, not just the one currently
open in the form.

-----Original Message-----
If you open the Print_Requisition table in design view, what is the Data
Type of the PrintReqID?

If it is a Number type (not a Text type), drop the extra quotes, i.e.:

strSqlWhere = "WHERE [Print_Requisition].PrintReqID =" & _
[Forms]![PrintRequisition]![PrintReqID]

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

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

Wayne Livingstone said:
I have a button on my form that is supposed to open a
report showing data relating to the currently displayed
record in the form. I have the following code on the On
Cleck event of the button:

Private Sub PreviewPrintReq_Click()
On Error GoTo PreviewPrintReq_Err


Dim strSqlSelect As String
Dim strSqlWhere As String
Dim strSqlExpr As String

strSqlSelect = "SELECT PrintReqID " & _
"FROM [Print_Requisition] "

strSqlWhere = " WHERE [Print_Requisition].PrintReqID =" _
& Chr$(34) &
[Forms]![PrintRequisition]![PrintReqID] & Chr$(34)

strSqlExpr = strSqlSelect & strSqlWhere & ";"

If IsNull(Me![PrintReqID]) Then
MsgBox "Enter printing data before previewing the
Printing Requisition report."
Else
DoCmd.OpenReport "PrintRequisition Report",
acPreview, , strSqlExpr
End If

PreviewPrintReq_Exit:
Exit Sub

PreviewPrintReq_Err:
MsgBox Err.Description
Resume PreviewPrintReq_Exit
End Sub

I get the error: Data type mismatch in criteria expression.
The report itself seems to work ok on its own and displays
data for all records.
Click to expand...


.
Click to expand...
 
Hmm. The code looks right. Perhaps you could ask the report about its
filter.

After the report has opened and should be showing one record, open the
Immediate Window (press CTRL+G), and enter:
? Reports("PrintRequisition Report").Filter
Does the result match what you expect?

You could also add the line:
Debug.Print strSqlWhere
just before the OpenRpeort line to see what condition is being applied.

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

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

Wayne Livingstone said:
You were right...it was an autonumber field. So now I dont
get any error message and the report opens, but it still
displays all PrintReqID records, not just the one currently
open in the form.

-----Original Message-----
If you open the Print_Requisition table in design view, what is the Data
Type of the PrintReqID?

If it is a Number type (not a Text type), drop the extra quotes, i.e.:

strSqlWhere = "WHERE [Print_Requisition].PrintReqID =" & _
[Forms]![PrintRequisition]![PrintReqID]

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

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

Wayne Livingstone said:
I have a button on my form that is supposed to open a
report showing data relating to the currently displayed
record in the form. I have the following code on the On
Cleck event of the button:

Private Sub PreviewPrintReq_Click()
On Error GoTo PreviewPrintReq_Err


Dim strSqlSelect As String
Dim strSqlWhere As String
Dim strSqlExpr As String

strSqlSelect = "SELECT PrintReqID " & _
"FROM [Print_Requisition] "

strSqlWhere = " WHERE [Print_Requisition].PrintReqID =" _
& Chr$(34) &
[Forms]![PrintRequisition]![PrintReqID] & Chr$(34)

strSqlExpr = strSqlSelect & strSqlWhere & ";"

If IsNull(Me![PrintReqID]) Then
MsgBox "Enter printing data before previewing the
Printing Requisition report."
Else
DoCmd.OpenReport "PrintRequisition Report",
acPreview, , strSqlExpr
End If

PreviewPrintReq_Exit:
Exit Sub

PreviewPrintReq_Err:
MsgBox Err.Description
Resume PreviewPrintReq_Exit
End Sub

I get the error: Data type mismatch in criteria expression.
The report itself seems to work ok on its own and displays
data for all records.
Click to expand...
Click to expand...
Click to expand...
 
I tried both of your suggestions but I'm not getting any
results from either of them. i dont know if I'm doing
something wrong.

-----Original Message-----
Hmm. The code looks right. Perhaps you could ask the report about its
filter.

After the report has opened and should be showing one record, open the
Immediate Window (press CTRL+G), and enter:
? Reports("PrintRequisition Report").Filter
Does the result match what you expect?

You could also add the line:
Debug.Print strSqlWhere
just before the OpenRpeort line to see what condition is being applied.

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

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

Wayne Livingstone said:
You were right...it was an autonumber field. So now I dont
get any error message and the report opens, but it still
displays all PrintReqID records, not just the one currently
open in the form.

-----Original Message-----
If you open the Print_Requisition table in design view, what is the Data
Type of the PrintReqID?

If it is a Number type (not a Text type), drop the extra quotes, i.e.:

strSqlWhere = "WHERE [Print_Requisition].PrintReqID =" & _
[Forms]![PrintRequisition]![PrintReqID]

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

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

I have a button on my form that is supposed to open a
report showing data relating to the currently displayed
record in the form. I have the following code on the On
Cleck event of the button:

Private Sub PreviewPrintReq_Click()
On Error GoTo PreviewPrintReq_Err


Dim strSqlSelect As String
Dim strSqlWhere As String
Dim strSqlExpr As String

strSqlSelect = "SELECT PrintReqID " & _
"FROM [Print_Requisition] "

strSqlWhere = " WHERE [Print_Requisition].PrintReqID =" _
& Chr$(34) &
[Forms]![PrintRequisition]![PrintReqID] & Chr$(34)

strSqlExpr = strSqlSelect & strSqlWhere & ";"

If IsNull(Me![PrintReqID]) Then
MsgBox "Enter printing data before previewing the
Printing Requisition report."
Else
DoCmd.OpenReport "PrintRequisition Report",
acPreview, , strSqlExpr
End If

PreviewPrintReq_Exit:
Exit Sub

PreviewPrintReq_Err:
MsgBox Err.Description
Resume PreviewPrintReq_Exit
End Sub

I get the error: Data type mismatch in criteria expression.
The report itself seems to work ok on its own and displays
data for all records.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top