Combo box After Update chokes on apostrophes in the selected field

  • Thread starter Thread starter Paul Ponzelli
  • Start date Start date
P

Paul Ponzelli

I've got a combo box in a form used to "find a record on my form based on a
record I select in the combo box."

I created this control with the Combo Box Wizard, and the desired record is
selected with the line

rs.FindFirst "[Applicant] = '" & Me![cboSelectApplicant2] & "'"

The Bound Control is a text field, and it works fine until I try to select a
string that has a single apostrophe in it, at which time I get the error
message

Run-time error '3077':
Syntax error (missing operator) in expression.

Is there anything I can do to overcome this problem?

Thanks in advance,

Paul
 
Use the Replace function to double up the ' characters. Two ' characters in
a row is interpreted as a single ' character in a text string.

rs.FindFirst "[Applicant] = '" & Replace(Me![cboSelectApplicant2], "'",
"''", 1, -1, vbText) & "'"
 
Thanks for the code, Ken. (Wow, only 5 minutes after I posted the
question).

However, I'm getting a Compile Error on the "vbText" argument. It says
"Variable not defined."

I'm using Access 2002. Any ideas?

Paul



Ken Snell (MVP) said:
Use the Replace function to double up the ' characters. Two ' characters
in a row is interpreted as a single ' character in a text string.

rs.FindFirst "[Applicant] = '" & Replace(Me![cboSelectApplicant2], "'",
"''", 1, -1, vbText) & "'"

--

Ken Snell
<MS ACCESS MVP>


Paul Ponzelli said:
I've got a combo box in a form used to "find a record on my form based on
a record I select in the combo box."

I created this control with the Combo Box Wizard, and the desired record
is selected with the line

rs.FindFirst "[Applicant] = '" & Me![cboSelectApplicant2] & "'"

The Bound Control is a text field, and it works fine until I try to
select a string that has a single apostrophe in it, at which time I get
the error message

Run-time error '3077':
Syntax error (missing operator) in expression.

Is there anything I can do to overcome this problem?

Thanks in advance,

Paul
Click to expand...
Click to expand...
 
Well, one way to solve the problem is to use an Autonumber field as the
bound column for the combo box, and then I don't have to handle apostrophes
in the text string.

Thanks for your suggestion in any event, Ken.


Paul Ponzelli said:
Thanks for the code, Ken. (Wow, only 5 minutes after I posted the
question).

However, I'm getting a Compile Error on the "vbText" argument. It says
"Variable not defined."

I'm using Access 2002. Any ideas?

Paul



Ken Snell (MVP) said:
Use the Replace function to double up the ' characters. Two ' characters
in a row is interpreted as a single ' character in a text string.

rs.FindFirst "[Applicant] = '" & Replace(Me![cboSelectApplicant2], "'",
"''", 1, -1, vbText) & "'"

--

Ken Snell
<MS ACCESS MVP>


Paul Ponzelli said:
I've got a combo box in a form used to "find a record on my form based
on a record I select in the combo box."

I created this control with the Combo Box Wizard, and the desired record
is selected with the line

rs.FindFirst "[Applicant] = '" & Me![cboSelectApplicant2] & "'"

The Bound Control is a text field, and it works fine until I try to
select a string that has a single apostrophe in it, at which time I get
the error message

Run-time error '3077':
Syntax error (missing operator) in expression.

Is there anything I can do to overcome this problem?

Thanks in advance,

Paul
Click to expand...
Click to expand...
Click to expand...
 
Sorry... memory lapse... Use vbTextCompare (it's value is 1).

--

Ken Snell
<MS ACCESS MVP>

Paul Ponzelli said:
Thanks for the code, Ken. (Wow, only 5 minutes after I posted the
question).

However, I'm getting a Compile Error on the "vbText" argument. It says
"Variable not defined."

I'm using Access 2002. Any ideas?

Paul



Ken Snell (MVP) said:
Use the Replace function to double up the ' characters. Two ' characters
in a row is interpreted as a single ' character in a text string.

rs.FindFirst "[Applicant] = '" & Replace(Me![cboSelectApplicant2], "'",
"''", 1, -1, vbText) & "'"

--

Ken Snell
<MS ACCESS MVP>


Paul Ponzelli said:
I've got a combo box in a form used to "find a record on my form based
on a record I select in the combo box."

I created this control with the Combo Box Wizard, and the desired record
is selected with the line

rs.FindFirst "[Applicant] = '" & Me![cboSelectApplicant2] & "'"

The Bound Control is a text field, and it works fine until I try to
select a string that has a single apostrophe in it, at which time I get
the error message

Run-time error '3077':
Syntax error (missing operator) in expression.

Is there anything I can do to overcome this problem?

Thanks in advance,

Paul
Click to expand...
Click to expand...
Click to expand...
 
Ah! That did the trick.

Thanks.

Looking at the expression now, I have a question about it. I would have
thought that you need to put the Replace() function on both sides of the
equality for it to work. You have pointed out that two ' characters in a
row is interpreted as a single ' character in a text string, but I would
have thought that would apply to both sides of the equal sign. Why is it
only necessary to use it on the one side
 
Are you asking why you don't need to use the Replace function with the
Applicant field name on the left side of the equality? That is because it is
just the name of the field that contains the values that are to be searched,
so there is no need for any modification of its contents/values. Also, VBA
will not allow you to put functions, etc. on the left side of an equality,
as the syntax is that you're setting the value of the left side to the
result of some expression on the right side.
 
I'm having the same problem with the Error 3077 and I tried your fix. I'm
not getting the error now, but it's not finding the record. Can you see what
I'm doing wrong ...


Private Sub Combo114_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FileName] = '" & Replace(Me![Combo114], "'", "''", 1, -1,
vbTextCompare) & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks.


Ken Snell (MVP) said:
see answer in forms newsgroup.

--

Ken Snell
<MS ACCESS MVP>

Paul Ponzelli said:
I've got a combo box in a form used to "find a record on my form based on
a record I select in the combo box."

I created this control with the Combo Box Wizard, and the desired record
is selected with the line

rs.FindFirst "[Applicant] = '" & Me![cboSelectApplicant2] & "'"

The Bound Control is a text field, and it works fine until I try to select
a string that has a single apostrophe in it, at which time I get the error
message

Run-time error '3077':
Syntax error (missing operator) in expression.

Is there anything I can do to overcome this problem?

Thanks in advance,

Paul
Click to expand...
Click to expand...
 
Back
Top