By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,487 Members | 1,747 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,487 IT Pros & Developers. It's quick & easy.

Runtime error on form

P: 20
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. 'Ups the grades of all students
  4.  
  5. 'count records
  6. Dim recordCount As Integer
  7. recordCount = DCount("[ID]", "tblStudents")
  8. DoCmd.GoToRecord acDataForm, "frmGradeUpdater", acGoTo, 1
  9.  
  10. For a = 1 To recordCount
  11. DoCmd.GoToRecord acDataForm, "frmGradeUpdater", acGoTo, a
  12. If Form_frmGradeUpdater.chkAlumni.Value = "0" Then GradeUp
  13. Next a
  14.  
  15. Form_frmKeyData.flgUpdated = -1
  16. DoCmd.Close
  17. End Sub
  18.  
  19. Private Function GradeUp()
  20.  
  21. Dim maxGrade As Integer
  22. maxGrade = Form_frmKeyData.cmbHighestGrade.Value
  23.  
  24. Dim response As String 'for alumni question
  25.  
  26. 'check if the grade value is null, and throw up a warning message if so.
  27. If (IsNull(Form_frmGradeUpdater.Grade.Value)) Then
  28.     MsgBox ("No grade data for: " & Form_frmGradeUpdater.txtForename.Value & " " & Form_frmGradeUpdater.txtSurname.Value)
  29.     Exit Function
  30. End If
  31.  
  32. Dim currentGrade As String
  33. currentGrade = Form_frmGradeUpdater.Grade.Value
  34.  
  35. Dim nameStore As String
  36. nameStore = (Form_frmGradeUpdater.txtForename.Value & " " & Form_frmGradeUpdater.txtSurname.Value)
  37. If currentGrade = "Grade 8" Then
  38.     If maxGrade = 8 Then
  39.      response = MsgBox("Should " & nameStore & " become an alumni student?", vbYesNo)
  40.      If response = 6 Then Form_frmGradeUpdater.chkAlumni = -1
  41.     End If
  42.     currentGrade = "Grade 9"
  43. End If
  44. If currentGrade = "Grade 7" Then currentGrade = "Grade 8"
  45. If currentGrade = "Grade 6" Then currentGrade = "Grade 7"
  46. If currentGrade = "Grade 5" Then currentGrade = "Grade 6"
  47. If currentGrade = "Grade 4" Then currentGrade = "Grade 5"
  48. If currentGrade = "Grade 3" Then currentGrade = "Grade 4"
  49. If currentGrade = "Grade 2" Then currentGrade = "Grade 3"
  50. If currentGrade = "Grade 1" Then currentGrade = "Grade 2"
  51. If currentGrade = "Kindergarten" Then currentGrade = "Grade 1"
  52. If currentGrade = "Preschool" Then currentGrade = "Kindergarten"
  53.  
  54. Form_frmGradeUpdater.Grade.Value = currentGrade
  55. Exit Function
  56. End Function
  57.  
  58.  
Feb 8 '08 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,679
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. 'Ups the grades of all students
  4.  
  5. 'count records
  6. Dim recordCount As Integer
  7. recordCount = DCount("[ID]", "tblStudents")
  8. DoCmd.GoToRecord acDataForm, "frmGradeUpdater", acGoTo, 1
  9.  
  10. For a = 1 To recordCount
  11. DoCmd.GoToRecord acDataForm, "frmGradeUpdater", acGoTo, a
  12. If Form_frmGradeUpdater.chkAlumni.Value = "0" Then GradeUp
  13. Next a
  14.  
  15. Form_frmKeyData.flgUpdated = -1
  16. DoCmd.Close
  17. End Sub
  18.  
  19. Private Function GradeUp()
  20.  
  21. Dim maxGrade As Integer
  22. maxGrade = Form_frmKeyData.cmbHighestGrade.Value
  23.  
  24. Dim response As String 'for alumni question
  25.  
  26. 'check if the grade value is null, and throw up a warning message if so.
  27. If (IsNull(Form_frmGradeUpdater.Grade.Value)) Then
  28.     MsgBox ("No grade data for: " & Form_frmGradeUpdater.txtForename.Value & " " & Form_frmGradeUpdater.txtSurname.Value)
  29.     Exit Function
  30. End If
  31.  
  32. Dim currentGrade As String
  33. currentGrade = Form_frmGradeUpdater.Grade.Value
  34.  
  35. Dim nameStore As String
  36. nameStore = (Form_frmGradeUpdater.txtForename.Value & " " & Form_frmGradeUpdater.txtSurname.Value)
  37. If currentGrade = "Grade 8" Then
  38.     If maxGrade = 8 Then
  39.      response = MsgBox("Should " & nameStore & " become an alumni student?", vbYesNo)
  40.      If response = 6 Then Form_frmGradeUpdater.chkAlumni = -1
  41.     End If
  42.     currentGrade = "Grade 9"
  43. End If
  44. If currentGrade = "Grade 7" Then currentGrade = "Grade 8"
  45. If currentGrade = "Grade 6" Then currentGrade = "Grade 7"
  46. If currentGrade = "Grade 5" Then currentGrade = "Grade 6"
  47. If currentGrade = "Grade 4" Then currentGrade = "Grade 5"
  48. If currentGrade = "Grade 3" Then currentGrade = "Grade 4"
  49. If currentGrade = "Grade 2" Then currentGrade = "Grade 3"
  50. If currentGrade = "Grade 1" Then currentGrade = "Grade 2"
  51. If currentGrade = "Kindergarten" Then currentGrade = "Grade 1"
  52. If currentGrade = "Preschool" Then currentGrade = "Kindergarten"
  53.  
  54. Form_frmGradeUpdater.Grade.Value = currentGrade
  55. Exit Function
  56. End Function
  57.  
  58.  
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.
Feb 9 '08 #2

mshmyob
Expert 100+
P: 904
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.
Feb 9 '08 #3

P: 20
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?
Feb 12 '08 #4

ADezii
Expert 5K+
P: 8,679
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).
Feb 12 '08 #5

mshmyob
Expert 100+
P: 904
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

Expand|Select|Wrap|Line Numbers
  1. for a = 1 to (recordcount-2)
  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?
Feb 12 '08 #6

mshmyob
Expert 100+
P: 904
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).
Feb 12 '08 #7

ADezii
Expert 5K+
P: 8,679
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!
Feb 12 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.