By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,807 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

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

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function fHandleApostrophe(strPass As String) As String
  2.     Dim strRet As String
  3.  
  4.     strRet = strPass
  5.     If InStr(1, strRet, "'", vbTextCompare) > 0 Then
  6.         strRet = "'" & Replace(strRet, "'", "''", , , vbTextCompare) & "'"
  7.     Else
  8.         strRet = "'" & strRet & "'"
  9.     End If
  10.     fHandleApostrophe = strRet
  11. End Function
Expand|Select|Wrap|Line Numbers
  1. Sub cmbOrgName_AfterUpdate()
  2. ' Find the record that matches the control.
  3. Me.RecordsetClone.FindFirst "[OrganizationName]= " & fHandleApostrophe(Me![cmbOrgName])
  4. Me.Bookmark = Me.RecordsetClone.Bookmark
  5. 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?
Nov 23 '11 #1
Share this Question
Share on Google+
2 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 23 '11 #2

NeoPa
Expert Mod 15k+
P: 31,489
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) :
Expand|Select|Wrap|Line Numbers
  1. Public Function fHandleApostrophe(strPass As String) As String
  2.     fHandleApostrophe = "'" & Replace(strPass, "'", "''''") & "'"
  3. 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.
Nov 24 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.