First, I apologize for the long post. Please do not let this discourage you from responding.
Bottom Line Up Front: I am trying to ensure that all fields in the mainform and subform are not left blank.
I have a form with numerous tabs. Each of the tabs has fields that the user must fill in. I have placed 'Is Not Null' validation rules on all of the fields in the main form with associated messages telling the user that they cannot leave the field blank. All of these fields work fine.
I have 5 subforms within this mainform that I am trying to do the same thing to. However, since I cannot assign a 'Is Not Null' in the subform properties, I have tried to use some code in the mainform BeforeUpdate to accomplish the same task. It is not working. See code below.
Relationships
1. All of the fields on the mainform are from one table.
2. All of the subforms link to other tables via the mainform primary key.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!sfrmLCLHours.Form!LHours_Lecture) Then
MsgBox "Lesson Hours Error", vbOKOnly, "Missing Lesson Hours"
Me!sfrmLCLHours.Form!LHours_Lecture.SetFocus
DoCmd.CancelEvent
Exit Sub
End If
If IsNull(Me!SfrmLCRef.Form!RID) Then
MsgBox "Lesson References Error", vbOKOnly, "No References"
Me!SfrmLCRef.Form.RID.SetFocus
DoCmd.CancelEvent
Exit Sub
End If
If IsNull(Me!SfrmLCLearnOut.Form!cboLOutcomeSelect) Then
MsgBox "Lesson Learning Outcomes Error", vbOKOnly, "No Learning Outcomes"
Me!SfrmLCLearnOut.Form.cboLOutcomeSelect.SetFocus
DoCmd.CancelEvent
Exit Sub
End If
If IsNull(Me!SfrmLCEdObj.Form!cboEdObjSelect) Then
MsgBox "Lesson Educational Objectives Error", vbOKOnly, "No Educational Objectives"
Me!SfrmLCEdObj.Form.cboEdObjSelect.SetFocus
DoCmd.CancelEvent
Exit Sub
End If
If IsNull(Me!sfrmLCSupport.Form!cboSupportSelect) Then
MsgBox "Lesson Support Requirements Error", vbOKOnly, "No Support Entry"
Me!sfrmLCSupport.Form.cboSupportSelect.SetFocus
DoCmd.CancelEvent
Exit Sub
End If
End Sub
Here is what happens.
1. After I fill in all of the mainform fields I immediately get the first ISNull error message "Lesson Hours Error" before I even have a chance to fill in the subform field.
2. I press 'OK' on the message box but it wont allow me to fill in the field in the subform. When I try to click on the field in the subform the message box pops back up again.
Now if I remove the 'DoCmd.CancelEvent' line in the code and run the form again, number 1 above still happens but now when I press 'OK' my cursor immediately jumps to the next subform control and I am no longer able to prevent the user from leaving a subform field blank.