Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Reaching the last record......

WilkoWilko Member Posts: 8
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 for any help, Ross


Comments

  • dokken2dokken2 Member Posts: 532
    : 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 for any help, Ross
    :
    :
    :

    i think the problem is with your "next question" statement
    [code]
    'command to go to next question
    DoCmd.GoToRecord , , acNext
    [/code]

    there is no way to know if you are on the last question. you could create another recordset and sync it to the one on the form, then test for an EOF- [no way I recall to directly determine record EOF for a form]

    [code]
    Option Compare Database

    'module level
    Dim rs As DAO.Recordset


    '***comment out your line***
    'command to go to next question
    'DoCmd.GoToRecord , , acNext

    'MOVENEXT ONLY IF NOT LAST RECORD
    If Not rs.EOF Then
    DoCmd.GoToRecord , , acNext
    rs.MoveNext
    End If

    '[your code continues]


    Private Sub Form_Load()
    'SET RECORDSET TO 1ST RECORD ON FORM
    Set rs = Me.Recordset
    rs.MoveFirst
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    'RELEASE RESOURCES
    rs.Close
    Set rs = Nothing
    End Sub

    [/code]
Sign In or Register to comment.