Syntax Error...must fix this

[steve711]

Dim stDocName As String

On Error GoTo Err_cmdRecurrent_Click

DoCmd.RunCommand acCmdSaveRecord
stDocName = "rpt_recurrent_reminder"
DoCmd.OpenReport stDocName, acViewPreview, , "[Crewmember] = " & Me.Crewmember
DoCmd.Maximize

Exit_cmdRecurrent_Click:
Exit Sub

Err_cmdRecurrent_Click:
MsgBox Err.Description
Resume Exit_cmdRecurrent_Click

The error says Syntax Error (comma) in Crewmember= Adams, Steve

Ok basically I have a small form that has a combo box. the user selects a name from the combo and clicks a button this should open a report based on the name selected. How can I modify this so it works?

I use the routine using a unique employee number but I need to view this list by the name as nobody knows whos who by employee number.

Confused? Me too but hopefully you got it a little bit.

 

[KenHigg]

DoCmd.OpenReport stDocName, acViewPreview, , "[Crewmember] = '" & Me.Crewmember & "'"


???
ken

 

[ByteMyzer]

Try changing:

DoCmd.OpenReport stDocName, acViewPreview, , "[Crewmember] = " & Me.Crewmember

...to

DoCmd.OpenReport stDocName, acViewPreview, , "[Crewmember] = """ & Me.Crewmember & """"

 

[steve711]

I tried both of those and neither worked.

Could it be that the field it is looking at has a space in it. For example...

In the combo it is listing..... Adams, Steve not Adams,Steve

Would this have any effect and the exact wording on the error is:

Syntax error in string in query expression '([Crewmember] = Adams, Steve")'.

 

[KenHigg]

if:

([Crewmember] = Adams, Steve")

is the exact error message, you still have not used our examples correctly.

???
ken

 

[steve711]

You're right Ken. I missed a " . Thanks for the gentle prod.

 

[steve711]

New addition to this problem. It isn't showing the report based on anyone except the first guy in the list.

The form I'm using has two things on it. A combo box and a button to show the report.

The combobox gets its list from a query which is where the report gets its info of course. The combobox itself has no control source. Could this be why it isn't showing anyone else?

 

[WayneRyan]

Steve,

Just read this.

You have a form with a combo.

You have report that should display the record that's in the combo.

The button should launch the report.

The code:

DoCmd.OpenReport stDocName, acViewPreview, , "[Crewmember] = '" & Me.Crewmember & "'"

The above is OK, (as long as the names don't have apostrophes in them).

Is [Crewmember] the correct field name?

Is there data where [Crewmember] = Adams, Steve?

What's the rowsource for the combobox? If it's "wizard-made" you might have a
hidden column and you're referencing the combo wrong.

Have you stopped the code and looked with the Debugger?

What's the RecordSource for the report?

Wayne

 

[steve711]

Steve,


DoCmd.OpenReport stDocName, acViewPreview, , "[Crewmember] = '" & Me.Crewmember & "'"

The above is OK, (as long as the names don't have apostrophes in them).

Is [Crewmember] the correct field name?

Is there data where [Crewmember] = Adams, Steve?

What's the rowsource for the combobox? If it's "wizard-made" you might have a
hidden column and you're referencing the combo wrong.

Have you stopped the code and looked with the Debugger?

What's the RecordSource for the report?

Wayne

Crewmember is the correct field which is in the qry_recurrent_reminder.
There is data in the query [Crewmember] in this case it is Adams, Steve and Wall, Clay
The rwosource is qry_recurrent_reminder nothing else just that.

Not yet on the debugger.

The recordsource for the report is the qry_recurrent_reminder.

It is getting the name off of the combo correctly, while doing the WATCH in the code. The only thing I think is happening is that the first part [Crewmember] isn't working correctly or isn't filtering that to the report.

To be honest I am not exactly sure how this routine works. That one line is complicated and the explanation within Access isn't all that clear to me.

 

[WayneRyan]

steve,

DoCmd.OpenReport stDocName, acViewPreview, , "[Crewmember] = '" & Me.Crewmember & "'"

DoCmd.OpenReport - Open an Access Report (works)
stDocName        - Name of report (works)
acViewPreview    - Present report in Preview mode (works)
,,               - No filter (works)

"[Crewmember] = '" & Me.Crewmember & "'"

This is just which record to display.  If, in the debugger, you should see this:

?Me.Crewmember
Adams, Steve

and:

?"[" & Me.Crewmember & "]"
[Adams, Steve]

If you see that, then the report will display for "Adams, Steve"

If you could attach a sample, it'd make it easier.

Wayne

 

[steve711]

I'm such a bonehead or maybe I was just too tired to be working on this last night. I missed placed a single comma and now all is working. Crazy but true.

Thanks for your help.

 

[Rich]

Had you used the PK, which I assume is the employee ID, throughout here and just hidden it, you wouldn't have had to bother with space quotes etc. etc