Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INSERT INTO syntax error (A2000)

    In the code below I'm trying to:
    1. <LI>set a global variable
      <LI>create a new record in table unrelated to the current form.
      <LI>assign the values in two global variables to the fields in the new record
      <LI>open a form based on the table I just added the record to
      <LI>have this form display the record I just created.
    This code is generating both a INSERT INTO syntax error and a "can't find the form, frmConsumer, referred to...". Can you see what I'm doing wrong?

    Dim frm As Form
    Dim strFormName As String
    Dim strLinkCriteria As String
    Dim strSQL As String
    Dim objRs As Recordset

    gintOrgID = Me.txtOrgID.Value
    strFormName = "frmConsumer"
    Set frm = Forms(strFormName)
    Set objRs = frm.RecordsetClone

    strSQL = "INSERT INTO tblConsOrg SET lngConsumerID = gintConsumerID, lngOrgID = gintOrgID;"

    DoCmd.RunSQL strSQL
    DoCmd.OpenForm strFormName, , , strLinkCriteria
    objRs.MoveLast

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: INSERT INTO syntax error (A2000)

    The INSERT INTO has the following formats:
    1. INSERT INTO tablename (a,b,c,d,e) SELECT g,h,i,j,k FROM AnotherTablename
    or
    2. INSERT INTO tablename (a,b,c,d,e) VALUES ("a",b,c,"d","e")

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO syntax error (A2000)

    How do I assign the value in a global variable to lngConsumerID and lngOrgID.

    I used the following SQL statement:

    INSERT INTO tblConsOrg (lngConsumerID, lngOrgID) VALUES (gintConsumerID, gintOrgID);

    It generated parameter popups for both the global variable values.

    E

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: INSERT INTO syntax error (A2000)

    Try this:
    strSQL = "INSERT INTO tblConsOrg (lngConsumerID, lngOrgID) Values (" & gintConsumerID & ", " & gintOrgID & ");"

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO syntax error (A2000)

    That did it.

Posting Permissions

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