Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    23

    Unanswered: Reaching the last record...

    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:

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ross
    The simple answer is trap for the error andd handle it appropriately

    you would need to handle the error gracefully

    the access vba error handler is invoked using the "on error" statement. The problme with "on error" is that it will divert execution to the code block on any error, so you need to decide if you want to do that or just handle the specific error you are getting. one thing to bear in mind is that once invoked an error handler handles all futher statements in the function, unless it is cleared with something similar to "on error goto 0"

    so on first pass, have a look at the 'on error statement' in the help system.
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2006
    Posts
    23
    Hi Mark,

    Thanks for the reply. Its not exactly what Im aiming for though. 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"

    Do you have any further suggestions?

    Many thanks,

    Ross

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I think the problem is the execution order. A Do While will execute the loop first then check for the condition (Not .EOF). You shoul use a While Wend loop. Remove the Do and replace the Loop with Wend.

    The other thing you will have to do is use a boolean (booFound or something). Set it to false before entering the loop then set it to true if you found the True case then exit. So something like:

    Code:
    booFound = False
    
    While Not rs.EOF And Not booFound
       
        booFound = rs!CorrectOrIncorrect
        i=i+1
        rs.MoveNext
    
    Wend

  5. #5
    Join Date
    Feb 2006
    Posts
    23
    Quote Originally Posted by DCKunkle
    I think the problem is the execution order. A Do While will execute the loop first then check for the condition (Not .EOF). You shoul use a While Wend loop. Remove the Do and replace the Loop with Wend.

    The other thing you will have to do is use a boolean (booFound or something). Set it to false before entering the loop then set it to true if you found the True case then exit. So something like:

    Code:
    booFound = False
    
    While Not rs.EOF And Not booFound
       
        booFound = rs!CorrectOrIncorrect
        i=i+1
        rs.MoveNext
    
    Wend
    Hi DCKunkle, thanks for your suggestion, however unless I am mistaken you are have tried to improve a piece of code that already works (having said this, i could be wrong due to be a bit of a novice).

    The purpose of that DO WHILE was to loop through the records on the subform. To add clarification this form is a one to many relationship between question and possible answers. That part of the code seems to work. However the problem comes when I want to move onto the next question ie move through the mainform, not the subform. I can do this, however what I need to do is get it to recognise when it reaches EOF so results of the test can be displayed.

    Please let me know if you need a further clarification, a reply would be very much appreciated,

    Ross

  6. #6
    Join Date
    Feb 2006
    Posts
    23
    Quote Originally Posted by healdem
    Ross
    The simple answer is trap for the error andd handle it appropriately

    you would need to handle the error gracefully

    the access vba error handler is invoked using the "on error" statement. The problme with "on error" is that it will divert execution to the code block on any error, so you need to decide if you want to do that or just handle the specific error you are getting. one thing to bear in mind is that once invoked an error handler handles all futher statements in the function, unless it is cleared with something similar to "on error goto 0"

    so on first pass, have a look at the 'on error statement' in the help system.
    HTH
    Hi Mark, i looked into you advice, it seems that I was a bit hastey to pass it off straight away, apologies! I have got it working now, many thanks for the suggestion

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •