Do Loop

[stealthdevil]

Hi,
I'm trying to use a Do Loop to look at 2 seperate tables. It needs to check the value in one field on the first table. If it finds a "T" it should look to a field in the other table and count down 3 rows. Then it needs to put the values from those 3 rows into a field beside the field that contains the "T". LOOP
Then it goes to the next row "below the "T"". If its another "T" then it goes to the other table and counts down 3 rows from where it left off last time. Then puts those 3 values below the last ones.

T = 3
D = 2
S = 1

There are actually 2 fields that get copied from the second table.
What property could I use for this? I thought maybe the Count property, but I don't think it actually get the value.

Thanks for any help.
-Dave

[stealthdevil]

This is my code so far. I keep getting an "object variable not defined" error. I''m not sure why I'm getting this.

Option Compare Database
Option Explicit

Function setlocations()
DoCmd.SetWarnings False
'declare necessary variables
Dim db As Database
'Dim rsRev As New ADODB.Recordset
Dim rsRev As Recordset
Dim rsLoc As Recordset
Dim sLoc As String
Dim iLevel As Integer
Dim sBreak As String
'point to database and tables
Set db = CurrentDb()
Set rsLoc = db.OpenRecordset("select *" & "from tbl_loc")
Set rsRev = db.OpenRecordset("select *" & "from tbl_os_reverse")
'Loop through records
rsRev.MoveFirst
'Do Until rsRev.Fields("[BREAKPT]").Value = " "
'Do While rsRev.EOF = False
Do While Not rsRev.EOF
sBreak = rsRev.Fields("[BREAKPT]").Value
sLoc = rsLoc.Fields("[LOCATION]").Value
iLevel = rsLoc.Fields("[LEVELS]").Value
rsRev.Edit
rsRev.Fields("[LOC SEQ]").Value = iLevel
rsRev![LOCATION] = sLoc
rsRev.Update
rsRev.MoveNext
If sBreak = "T" Then
rsLoc.Move 3
ElseIf sBreak = "D" Then
rsLoc.Move 2
Else
rsLoc.MoveNext
End If
'reclaim memory that recordset was using
Set rsLoc = Nothing
Set rsRev = Nothing
Loop

rsRev.Close
rsLoc.Close
End Function

[stealthdevil]

Well, I got the error fixed. Now I just have to figure out how to end the rsLoc table if it runs out of records before the rsRev table.

Option Compare Database
Option Explicit

Function setlocations()
DoCmd.SetWarnings False
'declare necessary variables
Dim db As Database
Dim rsRev As Recordset
Dim rsLoc As Recordset
Dim sLoc As String
Dim iLevel As Integer
Dim sBreak As String
Dim sPar As String

'point to database and tables
Set db = CurrentDb()
Set rsLoc = db.OpenRecordset("tbl_loc", DB_OPEN_TABLE)
Set rsRev = db.OpenRecordset("tbl_os_reverse", DB_OPEN_TABLE)
'Loop through records
rsRev.MoveFirst
While Not rsRev.EOF
   
    sBreak = rsRev.Fields("[BREAKPT]").Value
    sLoc = rsLoc.Fields("[LOCATION]").Value
    iLevel = rsLoc.Fields("[LEVELS]").Value
    rsRev.Edit
    rsRev.Fields("[LOC SEQ]").Value = iLevel
    rsRev![LOCATION] = sLoc
    rsRev.Update
    rsRev.MoveNext
    If rsLoc.EOF Then
    If sBreak = "T" Then
        rsLoc.Move 3
    ElseIf sBreak = "D" Then
        rsLoc.Move 2
    Else
        rsLoc.MoveNext
    End If
Wend

rsRev.Close
rsLoc.Close
End Function