Syntax error on report button in form?

[Gustavson]

I posted this originally in the wrong forum. Sorry about that.

I'm using Access 97. I'm trying to open my report with the current record on my form. I do this with a button on my form.

When I click the button I get this error message:
Syntax error ( missing operator ) in query expression '([Building Name] = Gilbert Hall )'

Here's my code:

Private Sub State_Fire_Marshal_button_Click()
On Error GoTo Err_Command187_Click

Dim stDocName As String
Dim stWhere As String
stDocName = "EHS Reports"
stWhere = "[EHS Form] = " & Me.[Building Name]
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_Command187_Click:
Exit Sub

Err_Command187_Click:
MsgBox Err.Description
Resume Exit_Command187_Click

End Sub

The right side of this line seems to be working fine:
stWhere = "[Building Name] = " & Me.[Building Name]

[Building Name] = Gilbert Hall

When I remove the second comma before stWhere in the last line of the code:

DoCmd.OpenReport stDocName, acPreview, stWhere

the report does open but to the first record not the current record on the form.

I think I'm doing something wrong with the left side?
Any suggestions?

If you need more info, let me know
Thanx
Gustavson

 

[lblastick]

Just a thought of something to check -
on the form properties, under other - make sure the cycle is set to current record. Hope this helps

 

[Gustavson]

iblastick - it was set to all records so I changed it and it made no difference. One thing that came to mind was that my form stores all information into a query called EHS Form, and my report was designed on another query called EHS Reports. Now, the EHS Reports query was designed from the EHS Form query so all the information that exists in th EHS Reports query came from the EHS Form query... Could this be problem?

Since I changed cycle to current record, do I need to change any of the code that I've written?

Again, I don't get the error message when I remove the second comma before stWhere in the line:

DoCmd.OpenReport stDocName, acPreview, ,stWhere

But, the button takes me to the first record which I do not want.

Thanx for your help
Gustavson

 

[WMcGregor]

Try changing the line to read:

stWhere = "[EHS Form] = '" & Me.[Building Name] & "'"

 

[Gustavson]

WMcGregor - Ok some progess, but now when I click the report button on the form, a Enter Parameter Value box appears for Building Name and it doesn't seem to matter what I enter in the box. The report opens blank...

Thanx for your feedback
Gustavson

 

[Gustavson]

I tried entering Gilbert Hall into the parameter box and it opens the report but to the first record. Note, at this time I have two records, one is Carson Hall and the other is Gilbert Hall. Carson Hall is the first record. So, when I have Gilbert Hall showing in the form it is the second record in the form. When I click the report button, the perameter box appears and I typed in Gilbert Hall. The report still displays Carson Hall, the first record...
Thanx
Gustavson

 

[Gustavson]

Finally got it to work. I got it working in Access XP then copied and pasted the code from XP to 97. For some reason, that worked. Might have been a minor detail. Anyways, here is the code:

Private Sub State_Fire_Marshal_button_Click()
On Error GoTo Err_Command187_Click

Dim stDocName As String
Dim stWhere As String
stDocName = "EHS Reports"
stWhere = "[Building Name] = '" & Me.[Building Name] & "'"
DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_Command187_Click:
Exit Sub

Err_Command187_Click:
MsgBox Err.Description
Resume Exit_Command187_Click

End Sub

Thanx for everyones help on this
Gustavson