Runtime error on form

[JKoyis]

I'm having some trouble with the code on this form - it's intended to increment the grades of all the students in the database.

If I open the form in design view and then switch to normal view it runs fine. If I double click it directly, however, it throws this error:

Runtime Error '2046'
The command or action 'GoToRecord' isn't available now.

When I debug the main loop in the code has always worked fine a few times before crashing.

The crash occurs on line 11.

Can anyone throw any light on the situation?

Private Sub Form_Open(Cancel As Integer)

'Ups the grades of all students
  
'count records
Dim recordCount As Integer
recordCount = DCount("[ID]", "tblStudents")
DoCmd.GoToRecord acDataForm, "frmGradeUpdater", acGoTo, 1

For a = 1 To recordCount
DoCmd.GoToRecord acDataForm, "frmGradeUpdater", acGoTo, a
If Form_frmGradeUpdater.chkAlumni.Value = "0" Then GradeUp
Next a

Form_frmKeyData.flgUpdated = -1
DoCmd.Close
End Sub

Private Function GradeUp()

Dim maxGrade As Integer
maxGrade = Form_frmKeyData.cmbHighestGrade.Value

Dim response As String 'for alumni question

'check if the grade value is null, and throw up a warning message if so.
If (IsNull(Form_frmGradeUpdater.Grade.Value)) Then
    MsgBox ("No grade data for: " & Form_frmGradeUpdater.txtForename.Value & " " & Form_frmGradeUpdater.txtSurname.Value)
    Exit Function
End If

Dim currentGrade As String
currentGrade = Form_frmGradeUpdater.Grade.Value

Dim nameStore As String
nameStore = (Form_frmGradeUpdater.txtForename.Value & " " & Form_frmGradeUpdater.txtSurname.Value)
If currentGrade = "Grade 8" Then
    If maxGrade = 8 Then
     response = MsgBox("Should " & nameStore & " become an alumni student?", vbYesNo)
     If response = 6 Then Form_frmGradeUpdater.chkAlumni = -1
    End If
    currentGrade = "Grade 9"
End If
If currentGrade = "Grade 7" Then currentGrade = "Grade 8"
If currentGrade = "Grade 6" Then currentGrade = "Grade 7"
If currentGrade = "Grade 5" Then currentGrade = "Grade 6"
If currentGrade = "Grade 4" Then currentGrade = "Grade 5"
If currentGrade = "Grade 3" Then currentGrade = "Grade 4"
If currentGrade = "Grade 2" Then currentGrade = "Grade 3"
If currentGrade = "Grade 1" Then currentGrade = "Grade 2"
If currentGrade = "Kindergarten" Then currentGrade = "Grade 1"
If currentGrade = "Preschool" Then currentGrade = "Kindergarten"

Form_frmGradeUpdater.Grade.Value = currentGrade
Exit Function
End Function

[ADezii]

I'm having some trouble with the code on this form - it's intended to increment the grades of all the students in the database.

If I open the form in design view and then switch to normal view it runs fine. If I double click it directly, however, it throws this error:

Runtime Error '2046'
The command or action 'GoToRecord' isn't available now.

When I debug the main loop in the code has always worked fine a few times before crashing.

The crash occurs on line 11.

Can anyone throw any light on the situation?

Private Sub Form_Open(Cancel As Integer)

'Ups the grades of all students
  
'count records
Dim recordCount As Integer
recordCount = DCount("[ID]", "tblStudents")
DoCmd.GoToRecord acDataForm, "frmGradeUpdater", acGoTo, 1

For a = 1 To recordCount
DoCmd.GoToRecord acDataForm, "frmGradeUpdater", acGoTo, a
If Form_frmGradeUpdater.chkAlumni.Value = "0" Then GradeUp
Next a

Form_frmKeyData.flgUpdated = -1
DoCmd.Close
End Sub

Private Function GradeUp()

Dim maxGrade As Integer
maxGrade = Form_frmKeyData.cmbHighestGrade.Value

Dim response As String 'for alumni question

'check if the grade value is null, and throw up a warning message if so.
If (IsNull(Form_frmGradeUpdater.Grade.Value)) Then
    MsgBox ("No grade data for: " & Form_frmGradeUpdater.txtForename.Value & " " & Form_frmGradeUpdater.txtSurname.Value)
    Exit Function
End If

Dim currentGrade As String
currentGrade = Form_frmGradeUpdater.Grade.Value

Dim nameStore As String
nameStore = (Form_frmGradeUpdater.txtForename.Value & " " & Form_frmGradeUpdater.txtSurname.Value)
If currentGrade = "Grade 8" Then
    If maxGrade = 8 Then
     response = MsgBox("Should " & nameStore & " become an alumni student?", vbYesNo)
     If response = 6 Then Form_frmGradeUpdater.chkAlumni = -1
    End If
    currentGrade = "Grade 9"
End If
If currentGrade = "Grade 7" Then currentGrade = "Grade 8"
If currentGrade = "Grade 6" Then currentGrade = "Grade 7"
If currentGrade = "Grade 5" Then currentGrade = "Grade 6"
If currentGrade = "Grade 4" Then currentGrade = "Grade 5"
If currentGrade = "Grade 3" Then currentGrade = "Grade 4"
If currentGrade = "Grade 2" Then currentGrade = "Grade 3"
If currentGrade = "Grade 1" Then currentGrade = "Grade 2"
If currentGrade = "Kindergarten" Then currentGrade = "Grade 1"
If currentGrade = "Preschool" Then currentGrade = "Kindergarten"

Form_frmGradeUpdater.Grade.Value = currentGrade
Exit Function
End Function

I can honestly say that I would not recommend this logic for updating Student's Grades, but it should, nonetheless, work regardless of the manner in which the Form is opened. Would you be able to send me the Database as an E-Mail Attachment, I would be more than happy to have a look at it. Currently, I do not have an explanation as to why this Error would occur only for a Form Dbl-Click.

[mshmyob]

I have found that Double clicking on the form and just switching back and forth between Form/Design view seems to always work a little differently depending on what you have done to the data.


RecordCount method should be avoided in my opinion all all costs. You might just consider using a MOVEFIRST and then MOVENEXT to cycle and just stop when eof.

[JKoyis]

I've played a little, and found that the form doesn't crash when opened from a module (as in normal use) so I'm going to leave the problem for now, thanks.

ADezii: Thanks for the offer, but I can't really email it out at this stage, I don't think my employers would like it :-)

mshmyob: thanks, I'll try doing that in future. Is RecordCount buggy? Are there other methods I should be avoiding that can cause similar errors?

[ADezii]

I have found that Double clicking on the form and just switching back and forth between Form/Design view seems to always work a little differently depending on what you have done to the data.


RecordCount method should be avoided in my opinion all all costs. You might just consider using a MOVEFIRST and then MOVENEXT to cycle and just stop when eof.

Hello msymyob, I'm a little confused about your point on RecordCount. In the current code context, it, (recordCount), is used as an Integer Variable as referenced in Lines 6 and 7, and has no relation to either the DAO or ADO Property (RecordCount) whatsoever. Probably just getting old and missing something as usual. (LOL).

[mshmyob]

It seems to be a known bug. MS suggests putting in a module - but you already figured that out. You could also just put it under a button click event.

or

Changing your for next statement to be

for a = 1 to (recordcount-2)

seems to make everything work also.

I've played a little, and found that the form doesn't crash when opened from a module (as in normal use) so I'm going to leave the problem for now, thanks.

ADezii: Thanks for the offer, but I can't really email it out at this stage, I don't think my employers would like it :-)

mshmyob: thanks, I'll try doing that in future. Is RecordCount buggy? Are there other methods I should be avoiding that can cause similar errors?

[mshmyob]

Not you ADezi it was my mistake. Must be my new laser surgery or I'm getting 2 old.

Hello msymyob, I'm a little confused about your point on RecordCount. In the current code context, it, (recordCount), is used as an Integer Variable as referenced in Lines 6 and 7, and has no relation to either the DAO or ADO Property (RecordCount) whatsoever. Probably just getting old and missing something as usual. (LOL).

[ADezii]

Not you ADezi it was my mistake. Must be my new laser surgery or I'm getting 2 old.

BTW, congratulations again on joining the Team, I know you'll be a fine addition!