DAO Recordset

[torb123]

I am working with three DAO recordsets, rst0 and rstK are related. When I create a record for rst0 I want to take the primary key of that new record which is SystemID and relate it by adding the SystemID to the recordset rstK. Unfortunately I am not sure how to do this. I have the code to the best of my ability, if someone could help out that would be great.

:Code Start:

Dim rstO As DAO.Recordset
Dim rstT As DAO.Recordset
Dim rstK As DAO.Recordset

Set rstT = CurrentDb.OpenRecordset("Products")
Set rstO = CurrentDb.OpenRecordset("tblSystem")
Set rstK = CurrentDb.OpenRecordset("tblSystemDetail")

With rstT
.MoveFirst
Do While Not .EOF
rstO.AddNew
rstO!SystemName = !ProductName
rstO!SystemDescription = !ProductDescription
rstO!PictureLink = !PictureLink
rstO!SystemSummary = !ProductSummaryPrint
rstK.AddNew

'The line below is where I am trying to get the systemID,
'from the newly created system (rst0), and put it into
'rstK (for the relationship). Unfortunately the line of
'code doesn't work, and that is why I am posting here

rstK!SystemID = rst0!SystemID
rstK!ProductID = !ProductID
rstK!OrderQuantity = 1

rstO.Update
rstK.Update
.MoveNext
Loop
.Close
rstO.Close
rstK.Close

End With

Set rstT = Nothing
Set rstO = Nothing
Set rstK = Nothing
:End Code:

Thanks,

Eric

 

[PHV]

Try to put rstO.Update before rstK.AddNew

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[torb123]

Thanks for the tip, but it didn't work. I was thinking since it is a new record, if there is a way for me to have it go to the last record in rst0 or tblSystem and give me the SystemID (which is an autonumber). I am not sure how to do that though either.

Thanks again.

 

[PHV]

And this ?
...
rstO.Update
rstO.Bookmark = rstO.LastModified
rstK.AddNew
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya torb123 . . . . .

Hope this is just a typo ([purple]can you see it![/purple]):
rst[purple]O[/purple]!SystemSummary =
= rst[purple]0[/purple]!SystemID

[purple]Should work if corrected . . .[/purple]

See Ya! . . . . . .

 

[torb123]

LOL, didn't even notice the typeo. Thanks I will try it out and let you know how it goes.
Thanks PHV and TheAceMan1

 

[torb123]

Yeah, it worked great! (stupid typeo's)
Thanks again