Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    Unanswered: NotInList Procedure

    I'm trying to use the NotInList event to add an item to a list of health complications from the data entry into a combo box, but I keep getting the following error message "3134: Syntax error in INSERT INTO statement". Maybe I've been looking at it so long that I just don't see the error.

    Private Sub SpecifyComplications_NotInList(NewData As String, Response As Integer)
    On Error GoTo Err_SpecComp

    Dim varSQL As String
    varSQL = "INSERT INTO [usysPickListComplications].[Complication] SpecifyComplications.Value;"

    If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
    DoCmd.RunSQL (varSQL)
    SpecifyComplications.Requery
    Else
    Response = acDataErrContinue
    End If

    Exit_Err_SpecComp:
    Exit Sub

    Err_SpecComp:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Err_SpecComp

    End Sub 'On NotInList

  2. #2
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168
    You'll have to change the inverted commas as it thinks all the stuff in "" is a single string.

    Maybe:

    varSQL = "INSERT INTO [usysPickListComplications].[Complication]" & specifyComplications.Value

  3. #3
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29
    Point well taken, you're absolutely right about the quotes. Made the changes you suggested but still get a syntax error.

  4. #4
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50

    Re: NotInList Procedure

    Originally posted by thoffer
    I'm trying to use the NotInList event to add an item to a list of health complications from the data entry into a combo box, but I keep getting the following error message "3134: Syntax error in INSERT INTO statement". Maybe I've been looking at it so long that I just don't see the error.

    Private Sub SpecifyComplications_NotInList(NewData As String, Response As Integer)
    On Error GoTo Err_SpecComp

    Dim varSQL As String
    varSQL = "INSERT INTO [usysPickListComplications].[Complication] SpecifyComplications.Value;"

    If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
    DoCmd.RunSQL (varSQL)
    SpecifyComplications.Requery
    Else
    Response = acDataErrContinue
    End If

    Exit_Err_SpecComp:
    Exit Sub

    Err_SpecComp:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Err_SpecComp

    End Sub 'On NotInList

    Hi,

    try this sql statement.
    strsql = "INSERT INTO usysPickListComplications(complications)SELECT forms!formname.specifycomplications as expr1;"

    tjacobs

  5. #5
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29
    Thanks, Your Idea works. However, I did something a little different. The syntax error was because the keyword VALUES was missing. My procedure that now works great is as follows:

    On Error GoTo Err_SpecComp

    Dim varSQL As String, varPassData As String
    varSQL = "INSERT INTO usysPickListComplications (Complication) VALUES ('" & NewData & "');"

    If MsgBox("Value is not in the list. Add it?", vbOKCancel) = vbOK Then
    DoCmd.RunSQL (varSQL)
    Refresh
    Else
    Response = acDataErrContinue
    End If

    Exit_Err_SpecComp:
    Exit Sub

    Err_SpecComp:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Err_SpecComp

    Terry Hoffer
    cai21

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •