Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open recordset (Access 2000)

    I am creating a new recordset in 2 tables, orders and order details. However my function does not open the second table order details and this table is not filled in with the new order. Why is it so ? Perhaps i have to open the recordset for two tables.Shall i open the recordset for the table order details and when shall i open it ? My function shown below does not create a new recordset in the second table,only in the table orders:
    Public Function CreateOrder()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngOrderID As Long
    Dim strSQL As String
    On Error GoTo ErrHandler

    ' 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!SubOrder = True
    rst!Audit = True
    rst!bankid = 1
    lngOrderID = rst!orderid
    rst.Update
    ' Add order details
    strSQL = "INSERT INTO [Order Details] (OrderID, ProductID, Cartons, Quantity) " & _
    "SELECT " & lngOrderID & ", ProductID, Branch0, Items0 FROM Products " & _
    "WHERE Branch0 > 0 AND Items0 > 0"
    dbs.Execute strSQL, dbFailOnError
    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Function
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

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

    Re: Open recordset (Access 2000)

    First: have you checked that there are records in Products with Branch0 > 0 and Items0 > 0? If there are no such records, nothing will be appended to Order Details.

    Second: the code assumes that the OrderID field in the Orders table is an AutoNumber field. If it isn't, the code will not work correctly. You can test by changing the line

    dbs.Execute strSQL, dbFailOnError

    temporarily to

    DoCmd.RunSQL strSQL

    When you run the code, you should be prompted "You are about to append nn records ...". If nn is 0 (zero), the OrderID is probably not correct.

Posting Permissions

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