bad with recordsets.....

[JCross]

I've been trying to work this one out myself, and I just figured out that you have to .movelast before you recordcount to find out how many records you have! I really need some help, though - because I have to nest my loops and I just can't figure it out.

1) click event on a form button - got it

2) query to see if there are inventory dates after the invoice date on the form, list ASC - got it

3) I need go through each inventory in order, and there is a tblinventory with a tblinventory_detail. The inventory dates retrieved from the above query are in tblinventory, and the inventory items i need to run through are in tblinventory_detail. For each inventory item i need to run through an if statement that I already have written.


I can't figure out how to loop through the dates, creating a recordset for the inventory date that you are on in the loop, and loop through the items in the inventory, .edit and .update(ing) them. Help please! I'm still studying this, but I'm stuck. Please let me know if I've not been clear.

Thank you so much for reading this far!

Jennifer

 

[WayneRyan]

Jennifer,

' *************************************
Dim dbs As Database
Dim rst1 As RecordSet
Dim rst2 As RecordSet
Dim sql As String

Set dbs = CurrentDb
sql "Select * from ParentTable"
Set rst1 = dbs.OpenRecordset(sql)
While Not rst1.EOF And Not rst1.BOF
sql = "Select * from ChildTable " & _
"Where ChildKey = '" & rst1!ParentKey & "'"
Set rst2 = dbs.OpenRecordset(sql)
While Not rst2.EOF and Not rst2.BOF
If rst2!SomeField = "Whatever" Then
rst2.Edit
rst2!SomeField = "New Data"
rst2.Update
End If
rst2.MoveNext
Wend
rst1,MoveNext
Wend
Set rst1 = Nothing
Set rst2 = Nothing
Set dbs = Nothing
' **************************************

hth,
Wayne

 

[JCross]

Hi Wayne!

what it the hth that everyone signs out with?

thanks for the code, i'm going to give it a try!

Jennifer

 

[WayneRyan]

Hi Jennifer,

Hope this helps.

Good luck,
Wayne