Tough problem creating new multiple records, and setting values 1

[Nelz]

The following code creates a new order, and a new order detail at the same time, which is what I was trying to do. The only problem is I want to set the value of OrderID in the newly created Order detail record, to the OrderID of the new order that was just created. Where I have !OrderID = Me.OrderID, thats wrong. It just fills in the number from the record you're on. Is there any way to tell it to put the number of the most recent Order, which in this case would be the one I just created? Or is there a better way to do this????


Private Sub Command50_Click()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Orders&quot

With rs
.AddNew
.Update
End With


Set db = CurrentDb
Set rs = db.OpenRecordset("Order Details&quot

With rs
.AddNew
!OrderID = Me.OrderID
.Update
End With
db.Close


End Sub

 

[930driver]

The trick is to store the OrderID when you have it and then put it into the line item. Also, you don't have to 'set' db twice.


Private Sub Command50_Click()

Dim db As Database
Dim rs As Recordset
Dim num as Long

Set db = CurrentDb
Set rs = db.OpenRecordset("Orders&quot

With rs
.AddNew
num = !OrderID
.Update
End With

Set rs = db.OpenRecordset("Order Details&quot

With rs
.AddNew
!OrderID = num
.Update
End With
db.Close


"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Nelz]

Thanks....this opens the door to a lot more confusion, since there's about 10 other fields and values I have to pollinate into the tables...but it worked great! You are greatly appreciated. I'm pretty sure this will give me enough insight to figure the rest out myself. Otherwise, I may be back for more advice.