Run-time error "3077"

[Vicki]

I used the wizard to create a combo box to look up a
record on a form (based on a query). Sometimes it works
and sometimes I get the run-time error '3077' message.
Syntax error (missing operator)in expression. The debug
tool points to this line: rs.FindFirst "[RLookup] = '" &
Me![Combo34] & "'"
I tried a list box with the same results. Thanks in
advance for your help

 

[Ken Snell]

Do you get the error when the combo box has no value in it? Chances are,
you're passing a Null value to the expression.

Try this code step instead and see if it helps:

rs.FindFirst "[RLookup] = '" & Nz(Me![Combo34], "") & "'"

If not, post back and let us know what happens with the above step.

 

[Guest]

Ken, Thanks for your response. I pasted the new code in
with the same results. I also checked the table and all
records contain a value in that field. Any more
thoughts? Vicki

-----Original Message-----
Do you get the error when the combo box has no value in it? Chances are,
you're passing a Null value to the expression.

Try this code step instead and see if it helps:

rs.FindFirst "[RLookup] = '" & Nz(Me![Combo34], "") & "'"

If not, post back and let us know what happens with the above step.

--
Ken Snell
<MS ACCESS MVP>

I used the wizard to create a combo box to look up a
record on a form (based on a query). Sometimes it works
and sometimes I get the run-time error '3077' message.
Syntax error (missing operator)in expression. The debug
tool points to this line: rs.FindFirst "[RLookup] = '" &
Me![Combo34] & "'"
I tried a list box with the same results. Thanks in
advance for your help

.

 

[Vicki]

Since my last response to your suggestions, I changed the
data source of the form to the table and the look-up
worked fine. So, thinking it was the query, I created a
new query which contained all the data in the table, no
criteria or sorts, etc. Once again I am getting the same
error message. The mystery continues....

-----Original Message-----
Ken, Thanks for your response. I pasted the new code in
with the same results. I also checked the table and all
records contain a value in that field. Any more
thoughts? Vicki

-----Original Message-----
Do you get the error when the combo box has no value in it? Chances are,
you're passing a Null value to the expression.

Try this code step instead and see if it helps:

rs.FindFirst "[RLookup] = '" & Nz(Me![Combo34], "") & "'"

If not, post back and let us know what happens with the above step.

--
Ken Snell
<MS ACCESS MVP>

I used the wizard to create a combo box to look up a
record on a form (based on a query). Sometimes it works
and sometimes I get the run-time error '3077' message.
Syntax error (missing operator)in expression. The debug
tool points to this line: rs.FindFirst "[RLookup]

= '"
&

Me![Combo34] & "'"
I tried a list box with the same results. Thanks in
advance for your help

.

.

 

[Kelvin]

Does the text you are looking for contain the "&" symbol, an apostrophe
symbol, a "?", or a "*"? Any of these symbols in the text when passed to
your expression will cause the expression to blow up.

Kelvin

Since my last response to your suggestions, I changed the
data source of the form to the table and the look-up
worked fine. So, thinking it was the query, I created a
new query which contained all the data in the table, no
criteria or sorts, etc. Once again I am getting the same
error message. The mystery continues....

-----Original Message-----
Ken, Thanks for your response. I pasted the new code in
with the same results. I also checked the table and all
records contain a value in that field. Any more
thoughts? Vicki

-----Original Message-----
Do you get the error when the combo box has no value in it? Chances are,
you're passing a Null value to the expression.

Try this code step instead and see if it helps:

rs.FindFirst "[RLookup] = '" & Nz(Me![Combo34], "") & "'"

If not, post back and let us know what happens with the above step.

--
Ken Snell
<MS ACCESS MVP>

I used the wizard to create a combo box to look up a
record on a form (based on a query). Sometimes it works
and sometimes I get the run-time error '3077' message.
Syntax error (missing operator)in expression. The debug
tool points to this line: rs.FindFirst "[RLookup]

= '"
&

Me![Combo34] & "'"
I tried a list box with the same results. Thanks in
advance for your help


.

.

 

[Vicki]

Yes - An apostrophe!! Taking it out did it. Is there a
programming way around this so my data entry person
doesn't get caught in this trap? Thanks for your help!

-----Original Message-----
Does the text you are looking for contain the "&" symbol, an apostrophe
symbol, a "?", or a "*"? Any of these symbols in the text when passed to
your expression will cause the expression to blow up.

Kelvin

Since my last response to your suggestions, I changed the
data source of the form to the table and the look-up
worked fine. So, thinking it was the query, I created a
new query which contained all the data in the table, no
criteria or sorts, etc. Once again I am getting the same
error message. The mystery continues....

-----Original Message-----
Ken, Thanks for your response. I pasted the new code in
with the same results. I also checked the table and all
records contain a value in that field. Any more
thoughts? Vicki
-----Original Message-----
Do you get the error when the combo box has no value in
it? Chances are,
you're passing a Null value to the expression.

Try this code step instead and see if it helps:

rs.FindFirst "[RLookup] = '" & Nz(Me![Combo34], "") & "'"

If not, post back and let us know what happens with the
above step.

--
Ken Snell
<MS ACCESS MVP>

message
I used the wizard to create a combo box to look up a
record on a form (based on a query). Sometimes it
works
and sometimes I get the run-time error '3077' message.
Syntax error (missing operator)in expression. The debug
tool points to this line: rs.FindFirst "[RLookup] = '"
&
Me![Combo34] & "'"
I tried a list box with the same results. Thanks in
advance for your help


.

.

.

 

[Ken Snell]

You can put this function in a regular module:

Public Function SingleQDouble(ByVal xstrReplaceStringValue) As String
'***THIS FUNCTION CONVERTS ONE SINGLE-QUOTE CHARACTER INTO TWO SINGLE-QUOTE
'***CHARACTERS IN A TEXT STRING.

' xstrReplaceStringValue is string variable that contains the text string
that
' needs to be converted

SingleQDouble = Replace(xstrReplaceStringValue, "'", "''")
End Function


Then use the function as a wrapper for your code:
rs.FindFirst "[RLookup] = '" & SingleQDouble(Me![Combo34], "") & "'"

This function doubles up ' characters so that ACCESS correctly sees them as
one in the string.

--
Ken Snell
<MS ACCESS MVP>

Yes - An apostrophe!! Taking it out did it. Is there a
programming way around this so my data entry person
doesn't get caught in this trap? Thanks for your help!

-----Original Message-----
Does the text you are looking for contain the "&" symbol, an apostrophe
symbol, a "?", or a "*"? Any of these symbols in the text when passed to
your expression will cause the expression to blow up.

Kelvin

Since my last response to your suggestions, I changed the
data source of the form to the table and the look-up
worked fine. So, thinking it was the query, I created a
new query which contained all the data in the table, no
criteria or sorts, etc. Once again I am getting the same
error message. The mystery continues....
-----Original Message-----
Ken, Thanks for your response. I pasted the new code in
with the same results. I also checked the table and
all
records contain a value in that field. Any more
thoughts? Vicki
-----Original Message-----
Do you get the error when the combo box has no value in
it? Chances are,
you're passing a Null value to the expression.

Try this code step instead and see if it helps:

rs.FindFirst "[RLookup] = '" & Nz(Me![Combo34], "")
& "'"

If not, post back and let us know what happens with the
above step.

--
Ken Snell
<MS ACCESS MVP>

message
I used the wizard to create a combo box to look up a
record on a form (based on a query). Sometimes it
works
and sometimes I get the run-time error '3077' message.
Syntax error (missing operator)in expression. The
debug
tool points to this line: rs.FindFirst "[RLookup]
= '"
&
Me![Combo34] & "'"
I tried a list box with the same results. Thanks in
advance for your help


.

.

.