3077 error remedied, does not work with index, yes no duplicates

[Jeanie]

I have created a search field on a form that refers to a query. It searches by Organization Name. Some organizations have an apostrophe in the name, and that caused an error No. 3077. This was remedied with the following code:

Public Function fHandleApostrophe(strPass As String) As String
    Dim strRet As String
  
    strRet = strPass
    If InStr(1, strRet, "'", vbTextCompare) > 0 Then
        strRet = "'" & Replace(strRet, "'", "''", , , vbTextCompare) & "'"
    Else
        strRet = "'" & strRet & "'"
    End If
    fHandleApostrophe = strRet
End Function

Sub cmbOrgName_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[OrganizationName]= " & fHandleApostrophe(Me![cmbOrgName])
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Now I am able to perform the search and have the form autofill after a selection.

My problem is that I then changed gave that field an index of "Yes (No Duplicates)". After I did that, my search still works, UNLESS I SELECT AN ORGANIZATION WITH AN APOSTROPHE in the name. It works if it is not indexed, but not if it is indexed. I need the field to be indexed, no duplicates, but also need the search field to work. Any suggestions?

[TheSmileyCoder]

This is one of those questions where I start out thinking that the original poster is just confused and has messed up something. I thought it would be ridiculous for the index to have any effect on the findfirst method.

However, I was able to recreate the issue you described, and I must admit to being confused myself, and my quick search didn't find any reasons as to why it would be so.

A thing to note, is that when you are doing comparisons with strings, you need to let the SQL engine (in your case probably the native access JET engine) know, that you want to compare a string. So in a normal case, where there would be no apostrophes, you would need to write:

Me.RecordsetClone.FindFirst "[OrganizationName]= '" & Me![cmbOrgName] & "'"

Note the apostrophe before and after the search word (cmbOrgName).

Back to your problem, Access will in alot of cases be able to switch out a single apostrophe with a double, and thats the case here as well, but it needs to be done a bit differently, since if we simply write the " the VBA engine will interpret it, as being the start or end of a string literal. So we use chr(34) which is the ascii charecter for ".

In the end it looks like this:

Me.RecordsetClone.FindFirst "[OrganizationName]=" & chr(34) & Me![cmbOrgName] & chr(34)

A short note to add is that if you should have any names with a double apostrophe, you would still get an error 3077, but I find that unlikely.

[NeoPa]

It sounds like the string is being processed twice for some reason. The doubling up of the quote chars gets it past the first, but when it gets to the second, the pair of quotes has already been converted to a single, which then is treaded as the end of the string (instead of a character in its own right).

Why not try quadrupling them instead in your function (which could be simpler btw) :

Public Function fHandleApostrophe(strPass As String) As String
    fHandleApostrophe = "'" & Replace(strPass, "'", "''''") & "'"
End Function

There is no need to check the value first as Replace() will leave as is if none is found anyway.

Using double-quotes (") instead of single-quotes (') is unlikely to work if the problem is as I assume. If you do choose to use them doubling them up is generally preferable to the more confusing method of using Chr() calls to build up your strings (Sorry Smiley). It's often suggested as a solution but I fail to see any merit in it whatsoever (over and above other clearer ways of saying the same thing). More on this topic can be found at Quotes (') and Double-Quotes (") - Where and When to use them.