open recordset

[canett]

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

 

[BuilderSpec]

Hi

I would add this line before the dbs.Execute statement

debug.print strSQL

Then run the function again and look at the immediate window in visual basic and see what the SQL statement is actually saying, check it for sytax etc. if it looks fine then copy it, go to query design and create a new query, go straight to SQL view..it should be blank and paste the text into there then run it.. does it run ok ? does it actually create the records you expected ?

Regards



Hope this helps!

Regards

BuilderSpec

 

[MajP]

No you do not have to open a second recordset. Instead you are using an append query to do it. It looks looks correct. However, you should be getting a message saying that you are adding records to a table when this fires:
dbs.Execute strSQL, dbFailOnError

So what builderSpec says it a good idea. Likely, there is a problem with your SQL string. However, you will have to modify the Sql string because in your code you used variables. Just hard code them to test.

You could do this same thing by opening another recordset, but that would be somewhat complicated. You would have to make an Order Details recordset so you could write to it. However, you would have to first open a Products recordset and loop through it and then write the appropriate products to the Order Details.