I am creating a new recordset in 2 tables, orders and order
details.Obviously 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
