Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    9

    Unanswered: Runtime 3134 - Syntax Error Insert Into Statement

    Hi,
    I am trying to add a record to the table and am getting Runtime error 3134 Syntax Error INSERT INTO Statement

    Here are my fields/datatypes:
    UniqueID auto number
    AuditID Text
    EmployeeName Text
    Monthof Sample Date
    DCN Text
    Total Allowed Amount Currency
    Total Paid Amount Currency
    TotalLines number
    Xray yes/no
    OtherCoverage yes/no

    And here's the code:

    Dim conCPAS As ADODB.Connection

    Dim SQLString As String

    SQLString = "INSERT INTO table (UniqueID, AuditID, EmployeeName, MonthofSample, DCN, TotalAllowedAmt, TotalPaidAmt, TotalLines, Xray, OtherCoverage)" & _
    "Values (" & UniqueID & ", '" & AuditID & "', '" & EmployeeName & "', #" & MonthofSample & "#, '" & ippDCN & "', " & TotalAllowedAmt & ", " & TotalPaidAmt & ", " & TotalCSL & ", " & Xray & ", " & OtherCoverage & ")"

    DoCmd.RunSQL (SQLString) (it's highligting on this line so don't really know which it does not like?

    Any ideas on how to get around this error? Thank you.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Debug that sql string before you call DoCmd.RunSQL and post it.

    See what you're actually passing...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't (and don't want to) write to an auto number - the clue is in the data types name
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2006
    Posts
    9
    You can't (and don't want to) write to an auto number - the clue is in the data types name

    So, if I am adding a record to the table that, I don't need to pass the auto number? Do I just delete it?

    I also checked the values that are passed and they seem to be ok.

    Thank you.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cristy
    So, if I am adding a record to the table that, I don't need to pass the auto number? Do I just delete it?
    Yup - the autonumber increments (or comes up with a random number - depends on the setting) itself so you don't write to that column. Remove it from your SQL and Access will handle it for you.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2006
    Posts
    9
    Thank you footle plump for your reply.

    I removed the auto number and tried again. I am still getting the same error.

    Am I using the correct syntax?

    Thanks again,

    Cristy

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Looks fine here.

    Follow Teddys advice (Debug.Print SQLString). Post it.

    Also try pasting into SQL view of Query designer and then return the view to design. Check Access has not created any Expr1 fields or similar. Run and see what the error message is.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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