Run time error 3077

[Terrence Carroll]

I am getting a run time error 3077 whenever I select a record from the
combobox with an apostophe as part of the text in the combo box. Can anyone
tell me what the apostrophes do in the code below and if there is a good
error handling feature to prevent this so I can have company names with
apostrphes in the combo box without getting the run time error? Listed below
is the code:


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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Client Name] = '" & Me![CboCompanyLookup] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks,

Terry Carroll

 

[John W. Vinson]

I am getting a run time error 3077 whenever I select a record from the
combobox with an apostophe as part of the text in the combo box. Can anyone
tell me what the apostrophes do in the code below and if there is a good
error handling feature to prevent this so I can have company names with
apostrphes in the combo box without getting the run time error? Listed below
is the code:


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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Client Name] = '" & Me![CboCompanyLookup] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks,

Terry Carroll

The problem is that the apostrophe is being seen as the closing quote in the
string. There are two solutions - replace the ' with two consecutive '
characters, or (probably more simply) use " as the delimiter. For the first
option use

rs.FindFirst "[Client Name] = '" _
& Replace(Me![CboCompanyLookup], "'", "''") & "'")

For the second (which will fail if you have a company name containing a
doublequote), use

rs.FindFirst "[Client Name] = """ & Me![CboCompanyLookup] & """"

That's three " before the value, and four after.