It's driving me CRAZY!

[rainymac]

I've used this same thing (just different object names) in another database to preview only the current record and it works fine.

In this database, however, when I click the cmd button, I get an error message of "Data type mismatch in criteria expression". My report is "Report2", my field is [Request #], & my form is CEARequest_form. Can you see anything?

The only thing different in this database is that the form has a subform, which was my original issue until I realized this wasn't even working. I have no idea !

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
DoCmd.RunCommand acCmdRefresh
DoCmd.OpenReport "Report2", acViewPreview, , "[Request #]=" & "'" & Forms!CEARequest_form![Request #] & "'"

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

Thanks for any help or just confirm that I am crazy.

 

[pdx_man]

First off, it is a very good idea to never use symbols in field names. They are reserved characters and the # symbol is one that Access uses for recognizing dates so... right off the bat I would say change that. We don't need to confuse Access anymore than we have to. Next, you may want to force a conversion to the correct datatype for your field using one of the Type Conversion Functions depending on the datatype of Request_Num. ie CStr(Forms!CEARequest_form![Request_Num])

HTH

 

[rainymac]

I figgured it out !!!!!

I finally figured out how to Preview Report for ONLY the current record WITH its current subform records. I can't beleive it !!!

This is what worked for me.

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
DoCmd.RunCommand acCmdRefresh
DoCmd.OpenReport "CEARequest_report", acViewPreview, , "[Request_Num]=" & Forms!CEARequest_form![Request_Num]

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

Thanks for all your help and the tip on the # thing. I did corrected that also.