Results 1 to 4 of 4
  1. #1
    besuchanko is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    32

    Runtime Error 3075: Apostrophe Throws an Error

    The code below is throwing a syntax error when the Submission_Title field includes an apostrophe in its value. How do I handle that?



    Code:
    Private Sub Submission_Title_AfterUpdate()
    
    Dim strSQL As String
    
    
    strSQL = "INSERT INTO tblAuditTrail (SessionID, SubmissionID, SessionNum, SessionTitle, FieldName, OldValue, NewValue, ChangeDate, ChangeBy) " & _
    "VALUES('" & SessionID & "', '" & Submission_ID & "', '" & SessionNum & "', '" & Submission_Title & "', 'Session Title', '" & Me.Submission_Title.OldValue & "', '" & _
    Me.Submission_Title & "', #" & Now() & "#, '" & NetworkUserName() & "');"
    
    
    CurrentDb.Execute strSQL

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Replace single quotes with double double-quotes (' with "").
    Code:
    strSQL = "INSERT INTO tblAuditTrail (SessionID, SubmissionID, SessionNum, SessionTitle, FieldName, OldValue, NewValue, ChangeDate, ChangeBy) " & _"VALUES('" & SessionID & "', '" & Submission_ID & "', '" & SessionNum & "', '" & Submission_Title & "', 'Session Title', """ & Me.Submission_Title.OldValue & """, """ & _
    Me.Submission_Title & """, #" & Now() & "#, '" & NetworkUserName() & "');"
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    besuchanko is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    32
    It worked! Thank you so much!

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I use a function to replace the single quote with double single quotes.
    In a standard module, paste:
    Code:
    Public Function ConvertQuotesSingle(InputVal)
       ConvertQuotesSingle = Replace(InputVal, "'", "''")
    End Function
    Useage :
    Code:
    strSQL = "INSERT INTO tblAuditTrail (SessionID, SubmissionID, SessionNum, SessionTitle, FieldName, OldValue, NewValue, ChangeDate, ChangeBy) " & _
    "VALUES('" & SessionID & "', '" & Submission_ID & "', '" & SessionNum & "', '" & Submission_Title & "', 'Session Title', '" & ConvertQuotesSingle(Me.Submission_Title.OldValue) & "', '" & _
    ConvertQuotesSingle(Me.Submission_Title) & "', #" & Now() & "#, '" & NetworkUserName() & "');"
    The nice thing about using a function is that you can use it for ANY text field, even if there are no single quotes in the data.

    -------------------------------------------------------------------------------------------------------------------------

    Just curious:

    There are 3 variables in the VALUES clause:
    , '" & Submission_Title & "',
    '" & Me.Submission_Title.OldValue & "',
    '" & Me.Submission_Title & "',

    Are all 3 referring to the same control on the form?


    There are fields named SessionID, SubmissionID and SessionNum.
    Are these text fields? I ask because normally fields with "ID" or "Num" at the end of the name are numeric (numbers) not text....

    As I said, I'm just curious.......

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Runtime error 3075
    By Homegrownandy in forum Access
    Replies: 4
    Last Post: 12-18-2019, 04:40 PM
  2. Runtime Error 3075
    By DMT Dave in forum Access
    Replies: 3
    Last Post: 12-06-2019, 05:14 AM
  3. Replies: 5
    Last Post: 09-05-2012, 09:28 AM
  4. runtime error 3075
    By Compufreak in forum Access
    Replies: 3
    Last Post: 08-14-2012, 01:40 AM
  5. Runtime 3075 error
    By whm1 in forum Programming
    Replies: 4
    Last Post: 03-24-2010, 02:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums