G
Guest
I had a MS Access 2000 form that worked well. However due to certain
circumstances, the database has to be upgraded to 2002. After upgrading, the
recordset in the form's VBA worked erratically i.e. it would populate the
subform perfectly with an ID, then it wouldn't - returning "Error 7878 The
data has changed", then it would work again the next time I use the form.
I hope someone here will be able to tell me whether it's due to the
DAO.recordset coding i.e. differences between 2000 and 2002. Please see the
following code:
Private Sub StudentID_AfterUpdate()
'This will insert the newly-entered [Student Info].[StudentID] into the
subform's hidden StudentID
'control (i.e. in NewStudentCourseMarks table) so that the courses available
will be alloted
'to every new student entered in this form (Student Info) and its underlying
table.
If Me.NewRecord Then
If IsNull(Me.StudentID) = False Then
'Create recordset variable to hold subform's records
Dim rs As DAO.Recordset
Set rs = Me.Child22.Form.Recordset
'Goto the first record of the form recordset (to autofill form).
rs.MoveFirst
'Exit if you cannot move to the first record (no records).
If Err <> 0 Then Exit Sub
'Loop the procedure of inserting StudentID into subform's StudentID
until EOF
Do Until rs.EOF
'Set focus on subform control, Child22
Child22.SetFocus
'Insert new Student ID to NewStudentCourse/UnitMarks table
Child22!StudentID = Me.StudentID
'Move to the next record.
rs.MoveNext
Loop
'Lock StudentID control i.e. no data-entry
StudentID.Locked = True
'Backcolor is grey
StudentID.BackColor = 12632256
'Note that the LastName.setfocus has to be here, the cursor does NOT
show after docmd etc. code.
LastName.SetFocus 'Set focus to the next field, LastName
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendNewStudCourseMarks"
DoCmd.OpenQuery "qryDeleteAddedStudentCourse"
DoCmd.SetWarnings True
Me!Child22.SourceObject = "subfm_CourseMarks"
Me!Child22.LinkChildFields = "StudentID"
Me!Child22.LinkMasterFields = "StudentID"
rs.Close
'Enable cbSwitchSubform button
cbSwitchSubform.Enabled = True
'Change subform label
lbSubform.Caption = "Course Marks"
Else 'If IsNull(Me.StudentID) = True
MsgBox "The Student ID cannot be null!", vbCritical, "Missing
Student ID"
StudentID.SetFocus
End If 'Closes if IsNull(Me.StudentID)= False
Else 'If the Student ID value is changed in an existing record...
If MsgBox("Are you sure you wish to modify the Student ID?", vbYesNo,
"Confirm ID Change") = vbYes Then
'**********
'Find matching [Course Marks] records for old StudentID value
Dim RS_CMarks As DAO.Recordset
Dim RS_UMarks As DAO.Recordset
Dim strCurID As String
Dim dbcur As DAO.Database
Set dbcur = CurrentDb()
'Populate the recordset with the [Course Marks] table values.
Set RS_CMarks = dbcur.OpenRecordset("Course Marks", dbOpenDynaset)
'Populate the recordset with [Unit Marks] table values.
Set RS_UMarks = dbcur.OpenRecordset("Unit Marks", dbOpenDynaset)
RS_CMarks.MoveFirst
'Find for the first student ID record that matches the old student ID
RS_CMarks.FindFirst "[StudentID]='" & strOldSID & "'"
RS_UMarks.MoveFirst
RS_UMarks.FindFirst "[StudentID]='" & strOldSID & "'"
'If NO matching old Student ID value is found in [Course Marks]
table...
If RS_CMarks.NoMatch = True Then
DoCmd.RunCommand acCmdSaveRecord
LastName.SetFocus
Else 'If matching old ID found, requery form recs and open to the
modified rec
strCurID = StudentID.Value
Me.Requery
'Use this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209537
'to findrecord
'Find the record that matches the control.
Me.RecordsetClone.FindFirst "[StudentID] = '" & strCurID & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
'Lock StudentID control i.e. no data-entry
StudentID.Locked = True
'Backcolor is grey
StudentID.BackColor = 12632256
LastName.SetFocus 'Set focus to the next field, LastName
End If
'If matching old Student ID value is found in [Unit Marks] table...
If RS_UMarks.NoMatch = True Then
DoCmd.RunCommand acCmdSaveRecord
Else
Do Until RS_UMarks.EOF
'Insert new Student ID to [Unit Marks] table
RS_UMarks!StudentID = Me.StudentID
'Move to the next record.
RS_UMarks.MoveNext
Loop
End If
dbcur.Close
'Lock StudentID control i.e. no data-entry
StudentID.Locked = True
'Backcolor is grey
StudentID.BackColor = 12632256
LastName.SetFocus 'Set focus to the next field, LastName
Else 'If user clicked No on Msgbox confirming StudentID modification
StudentID.Value = strOldSID
'Lock StudentID control i.e. no data-entry
StudentID.Locked = True
'Backcolor is grey
StudentID.BackColor = 12632256
LastName.SetFocus 'Set focus to the next field, LastName
End If 'Close "If value = vbYes"
strOldSID = ""
End If
End Sub
circumstances, the database has to be upgraded to 2002. After upgrading, the
recordset in the form's VBA worked erratically i.e. it would populate the
subform perfectly with an ID, then it wouldn't - returning "Error 7878 The
data has changed", then it would work again the next time I use the form.
I hope someone here will be able to tell me whether it's due to the
DAO.recordset coding i.e. differences between 2000 and 2002. Please see the
following code:
Private Sub StudentID_AfterUpdate()
'This will insert the newly-entered [Student Info].[StudentID] into the
subform's hidden StudentID
'control (i.e. in NewStudentCourseMarks table) so that the courses available
will be alloted
'to every new student entered in this form (Student Info) and its underlying
table.
If Me.NewRecord Then
If IsNull(Me.StudentID) = False Then
'Create recordset variable to hold subform's records
Dim rs As DAO.Recordset
Set rs = Me.Child22.Form.Recordset
'Goto the first record of the form recordset (to autofill form).
rs.MoveFirst
'Exit if you cannot move to the first record (no records).
If Err <> 0 Then Exit Sub
'Loop the procedure of inserting StudentID into subform's StudentID
until EOF
Do Until rs.EOF
'Set focus on subform control, Child22
Child22.SetFocus
'Insert new Student ID to NewStudentCourse/UnitMarks table
Child22!StudentID = Me.StudentID
'Move to the next record.
rs.MoveNext
Loop
'Lock StudentID control i.e. no data-entry
StudentID.Locked = True
'Backcolor is grey
StudentID.BackColor = 12632256
'Note that the LastName.setfocus has to be here, the cursor does NOT
show after docmd etc. code.
LastName.SetFocus 'Set focus to the next field, LastName
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendNewStudCourseMarks"
DoCmd.OpenQuery "qryDeleteAddedStudentCourse"
DoCmd.SetWarnings True
Me!Child22.SourceObject = "subfm_CourseMarks"
Me!Child22.LinkChildFields = "StudentID"
Me!Child22.LinkMasterFields = "StudentID"
rs.Close
'Enable cbSwitchSubform button
cbSwitchSubform.Enabled = True
'Change subform label
lbSubform.Caption = "Course Marks"
Else 'If IsNull(Me.StudentID) = True
MsgBox "The Student ID cannot be null!", vbCritical, "Missing
Student ID"
StudentID.SetFocus
End If 'Closes if IsNull(Me.StudentID)= False
Else 'If the Student ID value is changed in an existing record...
If MsgBox("Are you sure you wish to modify the Student ID?", vbYesNo,
"Confirm ID Change") = vbYes Then
'**********
'Find matching [Course Marks] records for old StudentID value
Dim RS_CMarks As DAO.Recordset
Dim RS_UMarks As DAO.Recordset
Dim strCurID As String
Dim dbcur As DAO.Database
Set dbcur = CurrentDb()
'Populate the recordset with the [Course Marks] table values.
Set RS_CMarks = dbcur.OpenRecordset("Course Marks", dbOpenDynaset)
'Populate the recordset with [Unit Marks] table values.
Set RS_UMarks = dbcur.OpenRecordset("Unit Marks", dbOpenDynaset)
RS_CMarks.MoveFirst
'Find for the first student ID record that matches the old student ID
RS_CMarks.FindFirst "[StudentID]='" & strOldSID & "'"
RS_UMarks.MoveFirst
RS_UMarks.FindFirst "[StudentID]='" & strOldSID & "'"
'If NO matching old Student ID value is found in [Course Marks]
table...
If RS_CMarks.NoMatch = True Then
DoCmd.RunCommand acCmdSaveRecord
LastName.SetFocus
Else 'If matching old ID found, requery form recs and open to the
modified rec
strCurID = StudentID.Value
Me.Requery
'Use this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209537
'to findrecord
'Find the record that matches the control.
Me.RecordsetClone.FindFirst "[StudentID] = '" & strCurID & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
'Lock StudentID control i.e. no data-entry
StudentID.Locked = True
'Backcolor is grey
StudentID.BackColor = 12632256
LastName.SetFocus 'Set focus to the next field, LastName
End If
'If matching old Student ID value is found in [Unit Marks] table...
If RS_UMarks.NoMatch = True Then
DoCmd.RunCommand acCmdSaveRecord
Else
Do Until RS_UMarks.EOF
'Insert new Student ID to [Unit Marks] table
RS_UMarks!StudentID = Me.StudentID
'Move to the next record.
RS_UMarks.MoveNext
Loop
End If
dbcur.Close
'Lock StudentID control i.e. no data-entry
StudentID.Locked = True
'Backcolor is grey
StudentID.BackColor = 12632256
LastName.SetFocus 'Set focus to the next field, LastName
Else 'If user clicked No on Msgbox confirming StudentID modification
StudentID.Value = strOldSID
'Lock StudentID control i.e. no data-entry
StudentID.Locked = True
'Backcolor is grey
StudentID.BackColor = 12632256
LastName.SetFocus 'Set focus to the next field, LastName
End If 'Close "If value = vbYes"
strOldSID = ""
End If
End Sub