470,632 Members | 1,442 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

Syntax error 3075 when typing name with apostrophe in form

I am using this vba code to prevent double entry:
Expand|Select|Wrap|Line Numbers
  1. Private Sub BusinessName_AfterUpdate()
  2.   Dim NewBusinessName As String
  3.   Dim stlinkcriteria As String
  5.   NewBusinessName = Me.BusinessName.Value
  6.   stlinkcriteria = "[BusinessName] = " & "'" & NewBusinessName & "'"
  7.   If Me.BusinessName = DLookup("[BusinessName]", "Sheet1", stlinkcriteria) Then
  8.   MsgBox "" & NewBusinessName & " is already in the Database." _
  9. & vbCr & vbCr & "Data Entry Denied!!!", vbInformation, "DUPLICATE ENTRY"
  10.   Me.Undo 'undo the process and clear all fields
The code works well to prevent double entry, unfortunately, if the name I typed includes an APOSTROPHE (ex. John's Cafe), syntax error 3075 appears.
Do you have any suggestions to correct the error?
2 Weeks Ago #1
3 6896
32,301 Expert Mod 16PB
Hi there, and welcome to Bytes.com.

Yes indeed. Interesting question :-)

This occurs because you're using the correct quote character for SQL strings. To avoid this being a problem you should always pass your string parameters - at least those that could ever contain such characters - via a filter function that doubles them up for you. For more on this see Quotes (') and Double-Quotes (") - Where and When to use them.

An example of such a function procedure is (from SQL Injection Attack) :
Expand|Select|Wrap|Line Numbers
  1. Public Function SafeSQL(strArg As String) As String
  2.     SafeSQL = Replace(strArg, "'", "''")
  3. End Function
Your line #5 would then be :
Expand|Select|Wrap|Line Numbers
  1. NewBusinessName = SafeSQL(Me.BusinessName.Value)
Note that, in most cases where no quotes are contained within the string passed, the value returned will be exactly the same as the original value. Only strings with embedded quotes will ever be changed to ensure they work correctly within SQL.
2 Weeks Ago #2
32,301 Expert Mod 16PB
By the way, correct quotes often fall foul of this with names, but the incorrect ones (") - also allowed in Access & Jet/ACE - have a similar problem when dealing with measurements in inches (") or even seconds (") when dealing with parts of a degree as well as of an hour ;-) Thus it still makes sense to use the correct ones and just ensure you protect your code using this procedure.
2 Weeks Ago #3
32,301 Expert Mod 16PB
Before I leave you I must just point out that, while it makes sense to catch such problems before trying to save the data, and thus end up with your user being subjected to a basic Access error message, it is also advisable to configure the underlying table field not to accept duplicates by setting the Indexed property to Yes (No duplicates). This will create an index on that field and disallow entry of any duplicate values.
2 Weeks Ago #4

Post your reply

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

Similar topics

3 posts views Thread by injanib via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.