Can't open single record in report with command button (1 Viewer)

[gschimek]

I have a database that I wrote a few years ago in Access 2000 originally. It has a form with some command buttons to open reports with data from the current record displayed in the form. Here's the code for one of the buttons:

Private Sub AcceptanceLetter_Click()
Dim strReportName As String
   Dim strCriteria As String
   
    DoCmd.RunCommand acCmdSaveRecord
   strReportName = "rptAcceptanceLetters"
   strCriteria = "[FirstName]&[LastName]='" & Me![FirstName] & [LastName] & "'"
    
   DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub

When I click that button, I get "Run time error '2427': You entered an expression that has no value."

But if I open the report without the WHERE clause in the OpenReport command, it opens just fine, though it displays all the records.

I have this same code in another database and it's working just fine.

Here's the only code in the report I'm trying to open:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim strBody As String
Dim strPart1 As String
Dim strstartdate As Integer
strstartdate = [WeekendStartDayOfMonth] + 1

strBody = "Congratulation!  I am happy to confirm that arrangements have been made for you to attend the " & _
"Church Retreat " & [WeekendID] & " weekend to be held " & [WeekendStartMonth] & " " & [strstartdate] & " - " & [WeekendEndDayOfMonth] & _
" at " & [LocationChurch] & ", " & [LocationAddress] & ", " & [LocationCity] & ", " & [LocationState] & "." & _
"  If for any reason you will be unable to attend this weekend, please let me know as soon as possible, because others may be on a waiting list to attend.  If you have any questions or concerns, please call me.  We have a fun filled weekend planned, and we are pleased that you will be with us."

Body.Value = strBody

End Sub

If I don't include any of the database fields in the strBody variable above, it also opens fine with the WHERE clause intact.

This used to work just fine when it was originally written. I'm not sure why it doesn't anymore. The database has moved to a different computer running Windows 7, and it's now on Access 2007 instead of the original Access 2000.

Any help is appreciated. Thanks!

 

[boblarson]

The criteria is a bit off:

Should be:

strCriteria = "[FirstName] = " & Chr(34) & Me.FirstName & Chr(34) & " AND [LastName]=" & Chr(34) & Me.LastName & Chr(34)

Oh, and I substituted Chr(34) for your single quotes because names are notorious for having single quotes in them (O'Brian, etc.) so this way it doesn't melt down should it come across one of those.

 

[gschimek]

That didn't seem to make any difference. I still get the same error. Any other suggetions?

 

[vbaInet]

Try prefixing all the fields in strBody with Me!, e.g. Me![WeekendID]. Ensure that the control names is not that the same as the field names.

You can also include strBody as an alias field in the report's record source and use that field as the control source of Body.