473,327 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Syntax error 3075 when typing name with apostrophe in form

1
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
  4.  
  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?
May 7 '22 #1

✓ answered by NeoPa

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.

4 20422
NeoPa
32,556 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.
May 7 '22 #2
NeoPa
32,556 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.
May 7 '22 #3
NeoPa
32,556 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.
May 7 '22 #4
jameswalter
4 Nibble
"Runtime Error 3075: Syntax Error (missing operator) in query expression ID () and Link LIKE 'SET'"This error occurs when exporting a row format to a . tdb file.

This may help,
James
Oct 28 '22 #5

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

Similar topics

1
by: Jozef | last post by:
Hello, I'm installing a package created with Office XP developers edition Packaging Wizard. I get an "Error 3075" Cannot I've looked at the Microsoft Website and this seems to be an Access 97...
0
by: j.rogel | last post by:
Hello everyone! I am new to this sort of postings so I'm not even sure if this is the correct place to start. Anyway, I would realy appreciate any help. I have a some VBA code that works quite...
3
by: injanib via AccessMonster.com | last post by:
Hello Access Monsters, I have a table with three columns. "CostCenter", "ProjectNumber", "GLCode" On my form I have three texboxes, "txtCostCenter", "txtProjectNumber" and "txtGLCode". the...
17
by: trose178 | last post by:
Good day all, I am working on a multi-select list box for a standard question checklist database and I am running into a syntax error in the code that I cannot seem to correct. I will also note...
2
by: GLEberts | last post by:
I can not seem to get rid of this syntax error - hope someone can help out. the error I am getting is: Run time Error 3075 Syntax error missing operator in query expression =name I have...
1
by: inbarik | last post by:
Hi, i'm getting syntax error message for this button that tries to get name (of building) with apostrophe. Any idea how to solve this? Note that i must use name with apostrophe e.g. O'neal: ...
2
by: aldwin aldwin | last post by:
Any help what I miss in this code. It shows an error if the value of MeCustName has apostrophe ex: MeCustName = wheel's weight ,it shows an error 3075.Thnx... DoCmd.RunSQL "INSERT INTO...
2
LeighW
by: LeighW | last post by:
Hi all, I have a form, Form 1 with primary key PK_ID I have a second form, Form 2 with foreign key FK_ID I link through to Form 2 using the field PK_ID. It is also possible to open Form 2...
1
by: mknewnham | last post by:
Hello! New to the PHP/coding world so hoping to get some help :) I created a contact form on Adobe Muse and created a PHP file. I found it on a blog and copied it from there (plus changing...
1
by: lmedina3 | last post by:
$paypal_item = $sitename." Service Monthly Subscription"; $paypal_item_number = $sitename; $mainipn = $siteurl."/ipn.php"; $paypal_ipn = $siteurl."/site_ipn.php"; $paypal_cancel_return =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.