Nested OpenRecordset's???? (1 Viewer)

[johndoh!!]

I am trying to use the following:

Dim db As Database
Dim rst As Recordset
Dim strSql As String

OID = 415
strSql = "SELECT * FROM qryPurchaseOrder WHERE OID = " & OID & " ORDER BY Line"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)

rst.MoveFirst
Do Until rst.EOF

Dim rst2 As Recordset
rst2 = CurrentDb.OpenRecordset("PurchaseOrder")
rst2.AddNew
rst2!Qty = rst!Quantity
rst2!Sku = rst!Sku
If Quote > 0 Then
rst2!Cost = rst!Quote
Else
rst2!Cost = rst!DN
End If
rst2.update

rst.MoveNext
Loop

I get a "Compile Error: Invalid use of property" error pointing to the point "rst2= CurrentDb.OpenRecordset("PurchaseOrder")".

Is it not possible to nest like this?

 

[GJT]

JohnDoh

Of course, anything is possible in Access!

You have simply forgotten to Set rst2 : i.e.

Set rst2 = CurrentDb.OpenRecordset("PurchaseOrder")

JohnDoh!! - You've certainly earnt your name!

 

[johndoh!!]

Doh!!!!!!

D'oh!!!!!!!!!!!!!!!!

Thank you, thank you, thank you!!