while statement which should exit, but doesnt??

[jgoodman00]

I have written the following:

Private Sub CmdRisk_Click()
If MsgBox("Run risk assessment for all records?", 3) = 6 Then 'Yes
While Not IsNull(Me.RefNo)
Call BrambleRiskAssessment
DoCmd.GoToRecord , , acNext
Wend
ElseIf 6 Then 'Cancel
Exit Sub
ElseIf 7 Then 'No
Call BrambleRiskAssessment
End If

End Sub

The idea is that this can run until it reaches the end of the recordset (RefNo will become null). This works fine if it only processes a few records (<100), but as soon as the number becomes larger it does not exit the loop & tries to call the function on a blank record which causes all sorts of trouble. I am thinking I will end up using dao or ado, but was trying to avoid this.

Any ideas?? James Goodman
j.goodman00@btinternet.com

 

[Craig0201]

Use DAO, James. Will be quicker as well as safer.

Craig

 

[Databaseguy]

Yeah, you should be manipulating the recordset using ADO or DAO instead. Here's a DAO example.

Dim dbs as database
Dim rst as recordset
Set dbs = currentDB
Set rst = dbs.openrecordset(&quot;Your underlying table&quot
With rst
.Movelast
.MoveFirst
Do while not .eof
Call BrambleRiskAssesment
.movenext
Loop
Set dbs = nothing