VBA and command button

[Guest]

I'm trying to get a command button to go to a specific record in a report in
my DB when clicked. On the form where the command button is located is a
combo box that is populated by a query that returns all names of Sub Names
from a table. The name of my report is All Subs & the field in my report
where I want this info to come from is Sub Name. I get an error that says
this-"Run-time error '3075' Syntax error (missing operator) in query
expression '(Sub Name=Joes Desserts)' Here is my code...

Option Compare Database
Option Explicit

Private Sub Command4_Click()
DoCmd.OpenReport "All Subs", acViewPreview, , "Sub Name=" & Me.Combo0
End Sub

 

[fredg]

I'm trying to get a command button to go to a specific record in a report in
my DB when clicked. On the form where the command button is located is a
combo box that is populated by a query that returns all names of Sub Names
from a table. The name of my report is All Subs & the field in my report
where I want this info to come from is Sub Name. I get an error that says
this-"Run-time error '3075' Syntax error (missing operator) in query
expression '(Sub Name=Joes Desserts)' Here is my code...

Option Compare Database
Option Explicit

Private Sub Command4_Click()
DoCmd.OpenReport "All Subs", acViewPreview, , "Sub Name=" & Me.Combo0
End Sub

1) You need to surround your field name with brackets as you have a
space in it "[Sub Name] = .....

2) If the bound column of the combo box is text, then use:

a) the usual method is to surround the variable with single
quotes:

"[Sub Name] = '" & Me.Combo0 & "'"
Which results in a criteria of:
[Sub Name] = 'Joes Desserts'
which is fine if there is no apostrophe in the name.

b) The above, however, will fail if the name has an apostrophe in
it, i.e. Joe's Desserts

Therefore, I recommend using the following syntax instead:
"Sub Name= " & chr(34) & Me.Combo0 & chr(34)

which will result in the criteria being:
[Sub Name] = "Joe's Desserts"
which will work in both instances.