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

The first thing you would want to do is open a recordset on each table. Then start into the first table, if T, then enter a subroutine (function) to count down three rows and concatenate the values... you do want to concatenate the three rows, is that correct?

It also sounds like a design problem. Why would you be doing this? Do you have table structure to share? Are you doing this:

Table1
Field1

Table2
Field2

If table1.field1 = "T" Then sVariable = table2.field2.record1 & table2.field2.record2 & table2.field2.record3
Add sVariable to table1?

Some of the more experienced people here might know how to do this in a query or two, but I can show you the code if you are interested.



mmcdonal

I was a little off with the first explaination. I realized (like you said mmcdonal) that it wasn't making much sense doing what I thought I was supposed to be doing, so I checked w/ my Boss man again. Turns out when there is say, a "T" I need to get first row from the other table and then skip 2 rows. I would then get the 4th row on the next loop. I've found that I can use the Move method to do this. I'm trying to work out a few kinks now, but I think I'm on the right track. The problem I'm having now is that the 2nd table "rsLoc", is ending b/f 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

'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
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
Loop

'reclaim memory that recordset was using
Set rsLoc = Nothing
Set rsRev = Nothing

rsRev.Close
rsLoc.Close

End Function

New problem:
Do While rsRev.EOF = False
This is giving me an "object variable not set" error.

What can I do to correct this?

First of all, I think there are some fundamental design issues here that require this kind of work around. But that is opinion, eh?

I am not sure why you are getting that error. I looks like the code will do what you want bu that .EOF is throwing this off. Try retyping the line, and seeing what autocomplete is showing you after you hit the "." EOF should be in the list.


mmcdonal

I can't argue about the fundamental design issues. This is the first I've worked on this program though.
I changed up a few things and it seems to be working now. I changed the Do...Loop and the EOF is working now.
Now my only prob. is that the rsLoc table is running out of records before the rsRev table is completely looped through. There's not much I can do about that from my end though.

Thanks for the input mmcdonal

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 sBreak = "T" Then
        rsLoc.Move 3
    ElseIf sBreak = "D" Then
        rsLoc.Move 2
    Else
        rsLoc.MoveNext
    End If

Wend