"data type mismatch" when printing report from form

[Kyle]

I have a form on which I placed a button that allows me to print the
page of the report for the record displayed in the form. When I click
on the button to print the report page, I get the error "data type
mismatch in criteria expression". I'm not sure where my code is
failing, but I know it's in the code; what am I missing?:

Private Sub Print_Memo_Click()
On Error GoTo Err_Print_Memo_Click

Dim stDocName As String

Dim strRecID As String
strRecID = "[Event_ID]='" & Me![Event_ID] & "'"

stDocName = "rpt_Detail_Request_Memo"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport stDocName, acPreview, , strRecID
DoCmd.PrintOut acPages, 1, 1

Exit_Print_Memo_Click:
Exit Sub

Err_Print_Memo_Click:
MsgBox Err.Description
Resume Exit_Print_Memo_Click

End Sub


Many thanks for any and all help!

 

[fredg]

I have a form on which I placed a button that allows me to print the
page of the report for the record displayed in the form. When I click
on the button to print the report page, I get the error "data type
mismatch in criteria expression". I'm not sure where my code is
failing, but I know it's in the code; what am I missing?:

Private Sub Print_Memo_Click()
On Error GoTo Err_Print_Memo_Click

Dim stDocName As String

Dim strRecID As String
strRecID = "[Event_ID]='" & Me![Event_ID] & "'"

stDocName = "rpt_Detail_Request_Memo"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport stDocName, acPreview, , strRecID
DoCmd.PrintOut acPages, 1, 1

Exit_Print_Memo_Click:
Exit Sub

Err_Print_Memo_Click:
MsgBox Err.Description
Resume Exit_Print_Memo_Click

End Sub

Many thanks for any and all help!

What is the datatype of the [Event_ID] field?
You have written the code as though it was Text datatype, but I
suspect [Event_ID] is Number datatype.

strRecID = "[Event_ID]=" & Me![Event_ID]

 

[Kyle]

What is the datatype of the [Event_ID] field?

You have written the code as though it was Text datatype, but I
suspect [Event_ID] is Number datatype.

strRecID = "[Event_ID]=" & Me![Event_ID]

Yep, that's the problem. Event_ID is an autonumbering field which keeps
each event request as a unique record in the database (since we have
repeating events).

I really do need to keep that field as a number field rather than a
text field. How would I change it so it's no longer a string type?

This is what I get for copying the code from another database I
received help on last year, and not completely understanding the code.

Thanks!

 

[fredg]

What is the datatype of the [Event_ID] field?
You have written the code as though it was Text datatype, but I
suspect [Event_ID] is Number datatype.

strRecID = "[Event_ID]=" & Me![Event_ID]

Yep, that's the problem. Event_ID is an autonumbering field which keeps
each event request as a unique record in the database (since we have
repeating events).

I really do need to keep that field as a number field rather than a
text field. How would I change it so it's no longer a string type?

This is what I get for copying the code from another database I
received help on last year, and not completely understanding the code.

Thanks!

Let's assume the valuie of the [Event_ID] control on the form is 123
(as Text Datatype).

Dim strRecID as String
strRecID = "[Event_ID]='" & Me![Event_ID] & "'"

will evaluate, using your syntax as
strRecID = "[Event_ID]= '123'"

Notice that the 123 above is within single quotes.
That is considered a Text value. When Access tries to match values in
the table, it's looking for '123' as text in a Number datatype field.
You'll get an #Error.

I gave you the correct syntax if [Event_ID] is a Number datatype.

Dim strRecID as String
strRecID = "[Event_ID]=" & Me![Event_ID]

The value of the [Event_ID] control on the form must be concatenated
into the string without the Single Quotes which delimits text.

So, if the value of [Event_ID] on the form is 123 (a Number Datatype)
strRecID = "[Event_ID] = " & [Event_ID]
will evaluate in the string variable as
strRecID = "[Event_ID] = 123"

strRecID is correctly a string but the value is 123 (a number).
Acess willl now search the [Event_ID] (Number datatype) field in the
table looking for the Number 123 and all is well.