Cannot get loop to advance

[pambroben]

Here is the code:

Private Sub Form_Load()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select * from tblpatient, dbOpenDynaset)

If Not (rs.BOF And rs.EOF) Then

rs.MoveFirst
Do While Not rs.EOF

' do stuff


rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End If
End Sub

I have watched it step through on debug and it does everything right for the first record, but it seems to come back to the same record.

Any ideas?

Cheers
Brodie

 

[JHB]

but it seems to come back to the same record.

Why do you think that?
The code looks all right - what are you doing in the 'Do stuff?

 

[pambroben]

Here is the full code :

Private Sub Form_Load()
Dim rs As Recordset
Dim varTotal As Long
Set rs = CurrentDb.OpenRecordset("select * from tblpatient ", dbOpenDynaset)
varTotal = RecordsetClone.RecordCount

If Not (rs.BOF And rs.EOF) Then

rs.MoveFirst
Do While Not rs.EOF

If (Me.ScanFlag) = "-1" Then ' means it has been scanned and waiting process
Me.DateModified = Now()
Me.ScanFlag = "0" ' reset the 'scanned' flag
If (Me.txtR14) = "N" Then 'Makes it following Monday if NEG
If Weekday(Me.DateModified) = 1 Then '
Me.txtADVISE_P = Date + 1 '
Else '
Me.txtADVISE_P = Date - Weekday(Me.DateModified) + 9 '

End If
Else
Me.txtADVISE_P = Date 'Makes it scanned date if POS

End If
End If

rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End If

What I have is scanning system that pushes data into the table tblpatient and sets a general number "ScanFlag" to -1 for yes - new data is present.

In debug I can watch the process go normally for the first record. The flag is cleared , it does its stuff then should loop to the next record. However it skips out because the flag is 0 indicated that record has been processed.

I look at the table and the first record is updated and the flag is 0, but the rest are unprocessed.

Another curious thing with this code, and I was going to tackle next, is varTotal shows zero even though BOF and EOF are false. Could that have anything to do with it?

Cheers,
Brodie

 

[bob fitz]

I assume that the form being loaded is bound to tblpatient.
As I understand your code, you create a recordset based on tblpatient which you loop through, but the data changes you make are made to the record in the form.
For example: Me.DateModified = Now()
This changes the value of the DateModified control on the form (Me.)