Caught in a loop

[jkfeagle]

I am running some code that searches one data set looking for matches in another. The code works fine until it finds a match that involves the last data entry in one of the data sets and then it seems to get caught in a loop at that entry. Here is the code:


Set rstCheckInfo = CurrentDb.OpenRecordset("tblAfterSisMods")

With rstCheckInfo
rstCheckInfo.MoveFirst
Do While Not .EOF
Set rstCBSisters = CurrentDb.OpenRecordset("tblCB_SISTERS")
With rstCBSisters
rstCBSisters.MoveFirst
Do Until .EOF
If rstCBSisters("CB #") = rstCheckInfo("PdPCB") Then
varBigSister = rstCBSisters("ID")
rstCheckInfo.Edit
rstCheckInfo("CBLink") = varBigSister
rstCheckInfo.Update
rstCBSisters.MoveLast
Else
rstCBSisters.MoveNext
End If
Loop
End With
rstCheckInfo.MoveNext
Loop
End With


I have verified where it gets caught in the loop and as I said it is when the last record in rstCBSisters is the match to the record in rstCheckInfo. I suspect that it is somehow getting hung on the .EOF issue but I can't figure it out. Any ideas??

 

[AncientOne]

You have nested Do loops. If the number of records in the two recordsets differ, the longer recordset's Do loop must hang because its EOF condition cannot be satisfied.

You only need to set one do loop for the shortest recordset, which can be ascertained by doing a recordcount at the beginning of the procedure.

 

[jkfeagle]

Thanks for your input but I don't think that is the case for two reasons. First, the recordset it is reaching the end of and hanging at is actually considerably larger than the other. Second, the way I have it written (I think) is that it steps one record at a time down the rstCheckInfo recordset and searches rstCBSisters recordset for a match until it either finds the match or reaches the end of the rstCBSisters dataset. Any other ideas?

 

[jkfeagle]

I found a way around the problem although I still would like to know why this occurs. The solution was to replace the .MoveLast with .MoveNext and then put in the code:

If Not rstCBSisters.EOF = True Then
rstCBSisters.MoveLast
End If

I appears that if the pointer is at the last record and you use the .MoveLast command it somehow resets the .EOF value as False even though it should be true. By using the .MoveNext I was able to force it into the .EOF -= true condition and end the loop. This would explain why it got caught in the loop although I still don't understand why it does that. Can anyone explain it to me?

 

[AncientOne]

My rationale for that is that if you are on the last record and attempt to move to the next, you actually topple over into EOF territory. If you are on the last record and ask to move to the last, you stay in the recordset.

Sorry about my original post, I had one of my frequent aberrant attacks.

Nevertheless, I don't know why you don't use FindFirst and Nomatch instead of looping through the recordset.

 

[fuzzygeek]

(Movenext vs movelast ) and eof

The Access help shows the following for DAO:

"If the first or last record is already current when you use MoveFirst or MoveLast, the current record doesn't change."

It also shows:

"If you use MoveNext when the last record is current, the EOF property is True, and there is no current record. If you use MoveNext again, an error occurs, and EOF remains True."

By your code, if it is the last record, the current record stays the same. Therefore you are not reaching the EOF, you are on the last record. That's why you stay in the loop. The way around it would be the following:

With rstCBSisters
rstCBSisters.MoveFirst
Do Until .EOF
If rstCBSisters("CB #") = rstCheckInfo("PdPCB") Then
varBigSister = rstCBSisters("ID")
rstCheckInfo.Edit
rstCheckInfo("CBLink") = varBigSister
rstCheckInfo.Update
rstCBSisters.MoveLast
End If
rstCBSisters.MoveNext
Loop


Then the movenext would force the EOF.

I am not judging the efficiency of your code...only explaining why the result you were getting happened.