skipping records while looping

stealthdevil · October 23rd, 2006, 11:01 AM

Hello once again,
I'm trying to loop through the table(below). I've written the code that loops through a table that has three levels, but this new table has 4 levels. I need to loop through this table while totally ignoring the records on level 4. Any ideas how I might accomplish this?

Thanks,
Dave

Code:

rsRev.MoveFirst
While Not rsRev.EOF And Not rs4Loc.EOF

    sBreak = rsRev.Fields("[BREAKPT]").Value
    sLoc = rs4Loc.Fields("[location]").Value
    iLevel = rs4Loc.Fields("[level]").Value

    rsRev.Edit
    rsRev.Fields("[LOC SEQ]").Value = iLevel
    rsRev![Location] = sLoc
    rsRev.Update
    rsRev.MoveNext
    If sBreak = "T" Then
        rs4Loc.Move 3
    ElseIf sBreak = "D" Then
        rs4Loc.Move 2
    Else
        rs4Loc.MoveNext
    End If
Wend

Code:

location    level
11ALR001    1
11ALR001    2
11ALR001    3
11ALR001    4
11ALR002    4
11ALR002    3
11ALR002    2
11ALR002    1
11ALR003    1
11ALR003    2
11ALR003    3
11ALR003    4
11ALR004    4
11ALR004    3
11ALR004    2
11ALR004    1
11ALR005    1
11ALR005    2
11ALR005    3
11ALR005    4

pjm · October 24th, 2006, 08:45 AM

Just put your processing code inside an if statement:

if iLevel <> 4 then
    rsRev.Edit
    rsRev.Fields("[LOC SEQ]").Value = iLevel
    rsRev![Location] = sLoc
    rsRev.Update
    rsRev.MoveNext
    If sBreak = "T" Then
        rs4Loc.Move 3
    ElseIf sBreak = "D" Then
        rs4Loc.Move 2
    Else
        rs4Loc.MoveNext
    End If
End If

-Phil-

stealthdevil · October 24th, 2006, 03:56 PM

Thanks for the reply Phil!
Unfortunately I don't think it is that simple.

Code:

If sBreak = "T" Then

Code:

        rs4Loc.Move 3
    ElseIf sBreak = "D" Then
        rs4Loc.Move 2
    Else
        rs4Loc.MoveNext
    End If

Even inside this piece of code I need 4 to be skipped. So, if I used the <>4 if statement it might weed out some 4's, but not all. Because if say there's a level 3 that passes the <>4 test then it gets inside the statement. If sBreak = T then it would move from 3-4-4 and end up on the next 3. I need it to start at 3 skip 4 skip 4 -3-2 and end on 1.

stealthdevil · October 26th, 2006, 10:22 AM

Well, I got it working. Not exactly the way I wanted, but it works. I made a new table and made a query that copied the records that were being looped through to it. Then I made a query that would delete all the rows that were level 4. That way I didn't have to skip the 4's, they weren't even there.