Reaching the last record...

[wilko]

Wasnt sure what sub-forum to post this in so went for this one and the VBA one!

Hi Guys,

I am building a db where part of the functionality includes a practice tests. It is currently working well, with the form moving onto the next question when a student gets an answer correct, hoowever the problem comes when there are no questions left, the code still tries to move onto the next question, when there isnt one, thus causing a run-time error. Any hints/tips/advice on what I should consider to fix this would be much appreciated, here is the code:

open db with a sql-statement
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

'loop thru records
Dim i
i = 1
Do While Not rs.EOF
If rs!CorrectOrIncorrect = True Then
    Exit Do
End If
i = i + 1
rs.MoveNext
Loop
If Frame10.Value = i Then
    total = total + 1
    MsgBox "True" & total
        
    'command to go to next question
    DoCmd.GoToRecord , , acNext
    
    
ElseIf Frame10.Value <> i Then
    MsgBox "False"
    'show up boxes to see examples or theory
End If
'release resouces
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Cheers,

Ross

 

[wilko]

Hello again... to add further clarification..

Ideally I want to code to stop before it gets to the point of error. Therefore the logic should be something like:

If QuestionForm is EOF Then
MsgBox "You have completed the test"

Therefore the code should look something like

If Frame10.Value = i Then
    total = total + 1
    MsgBox "True" & total
        
    If Me.Recordset.EOF = True Then
        MsgBox "You have reached the end of the test"
    Else
        'command to go to next question
        DoCmd.GoToRecord , , acNext
    End If
ElseIf Frame10.Value <> i Then
however it doesnt seem to like "If Me.Recordset.EOF = True Then" 
Ideally I want to code to stop before it gets to the point of error. Therefore the logic should be something like:

If QuestionForm is EOF Then
MsgBox "You have completed the test"

Therefore the code should look something like


Code:
If Frame10.Value = i Then
    total = total + 1
    MsgBox "True" & total
        
    If Me.Recordset.EOF = True Then
        MsgBox "You have reached the end of the test"
    Else
        'command to go to next question
        DoCmd.GoToRecord , , acNext
    End If
ElseIf Frame10.Value <> i Then
Ideally I want to code to stop before it gets to the point of error. Therefore the logic should be something like:

If QuestionForm is EOF Then
MsgBox "You have completed the test"

Therefore the code should look something like


Code:
If Frame10.Value = i Then
    total = total + 1
    MsgBox "True" & total
        
    If Me.Recordset.EOF = True Then
        MsgBox "You have reached the end of the test"
    Else
        'command to go to next question
        DoCmd.GoToRecord , , acNext
    End If
ElseIf Frame10.Value <> i Then

however it doesnt seem to like "If Me.Recordset.EOF = True Then"


Can anyone please shed some light?

Many thanks in advance,

Ross

 

[Bodisathva]

change to a While loop as opposed to a Do While loop. The While loop checks the condition prior to execution, the Do loop performs the loop, then checks to condition.

 

[RuralGuy]

You have to use rs.EOF and not Me.RecordSet.EOF.