Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax error (Access 2000)

    How can i remove the syntax error in my sql ? I want to make a table called Customers1 with only one customer, whichi get from my recordset, used for other functions too

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngOrderID As Long
    Dim StrSQL As String


    ' Create a new order and obtain its OrderID
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
    rst.AddNew
    rst!customerid = DMin("Customerid", "Customers")
    ' rst!Audit = True
    rst!bankid = 1
    rst!PaymentMethodID = 1
    lngOrderID = rst!orderid
    rst.Update

    StrSQL = "INSERT INTO [customers1],customerid SELECT Customers1.Customerid = Customers.Customerid WHERE Customers.Customerid = rst!customerid"

    dbs.Execute StrSQL

    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax error (Access 2000)

    Do you really want to create a new table Customers1? It doesn't make sense to create a new table for each new order.
    Or do you want to add a new record to the Customers1 table?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Syntax error (Access 2000)

    If Customer1 already exists, the correct syntax for adding record(s) would be:

    StrSQL = "INSERT INTO [customers1] (customerid) SELECT Customers.Customerid FROM Customers WHERE Customers.Customerid = rst!customerid"

    But why are you doing this? Why do you even need Customers1 at all? And nothing about what you are doing seems to make sense. For one thing, you are only creating Orders for the first customer in your Customers table. And since you can use DMin to find that, why bother with Customers1?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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