Results 1 to 2 of 2
  1. #1
    DWS is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    5

    Not In List Error

    I am using the following code to add an item to a combo box when not in list....

    Private Sub pilgrimchurch_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String
    Dim i As Integer


    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Church Name...")
    If i = vbYes Then
    strSQL = "Insert Into tbl_church ([churchname]) " & _
    "values ('" & NewData & "');"

    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If
    End Sub


    When I add a name that has an apostrophe in it, such as St. Joseph's Church, I get an error. Run time error 3075 - Syntax error (missing operator) in query expression

    Help??

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try this:
    Code:
    Private Sub pilgrimchurch_NotInList(NewData As String, Response As Integer)
       Dim strSQL As String
       Dim i As Integer
       Dim Msg As String
       Response = acDataErrContinue
       'Exit this sub if the combo box is cleared
       If NewData = "" Then Exit Sub
       Msg = "[" & NewData & "] is not currently in the list." & vbCr & vbCr
       Msg = Msg & "Do you want to add it?"
       i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Church Name...")
       If i = vbYes Then
          strSQL = "Insert Into tbl_church ([churchname]) " & _
                   "values (""" & NewData & """);"
          CurrentDb.Execute strSQL, dbFailOnError
          Response = acDataErrAdded
       End If
    End Sub
    You are aware that you have no error handling code to catch any errors, right?

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

Similar Threads

  1. assign value from list
    By roman.pro in forum Forms
    Replies: 0
    Last Post: 05-16-2009, 04:20 PM
  2. About value in list box?
    By viccop in forum Forms
    Replies: 0
    Last Post: 04-08-2009, 08:05 PM
  3. List box to populate other list boxes
    By Nathan in forum Forms
    Replies: 0
    Last Post: 03-03-2009, 07:22 PM
  4. Replies: 1
    Last Post: 02-25-2009, 07:29 PM
  5. list box
    By lead27 in forum Forms
    Replies: 0
    Last Post: 07-21-2007, 04:09 AM

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