Form and Subform error trapping

[usmcgrunt]

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.

[RuralGuy]

Three thoughts.
1) Placing the focus from a MainForm to a SubForm is a 2 step process. 1st put the focus on the SubFormControl and then put the focus on a control on the SubForm. ie:
Me!sfrmLCSupport.SetFocus
Me!sfrmLCSupport.Form.cboSupportSelect.SetFocus
2) Your mainForm will attempt to save the MainForm record when the user attempts to move the focus to the SubForm.
3) The SubForm will attempt to save the record when the user attempts to move the focus back to the MainForm.
That is how Access events work in this particular case. See why you are getting the results you are getting?

[usmcgrunt]

Allen, once again, thank you for responding to my post. My comments to your "Three Thoughts.

1) Understand the 2 step process to SetFocus from main form to sub form; I don't think I necessarily need to do this as long as I can create a custom message box that tells the user which fields are missing. Then the user can self-navigate to that field.

2 & 3) With my improved understanding of how access events work, I am fine with the record being saved but I just don't want the user to be able to navigate away from the record if the fields in the sub forms are NULL.

However, if the user decides that he/she does not want to continue then I want the entire record to be deleted. By setting the form up this way I can prevent the main form table from having records that have no associated records in the related sub form tables.

I am currently working different approaches but with each new possibility comes a slew of unknowns and error messages.

Bottom line at the end:

1) I want to prevent subform fields from being left blank.
2) I want a custom message that tells the user that they have left a subform field blank and that they must enter something in order to "save" (not really) or "Add" or move to another record.
3) I want the record to be deleted if the user decided to close the form and not enter information into the sub form fields.

Thanks, Sean

[RuralGuy]

Have you considered using a transaction for the MainForm? You could set yourself a flag indicating the SubForms have not started yet and then validate the SubForm records in the SubForm rather than the MainForm.

[usmcgrunt]

Allen, my current proficiency with Access might make using a transaction for the MainForm a bit difficult. More pointedly, I don't think I possess the knowledge to do what you say. With that said I will revert to my trusty little friend, "insert your favorite search engine here", and figure out what that entails.

To be continued......

[RuralGuy]

Sean,
Here's a link to a sample Transaction that you might find helpful.
http://www.rogersaccesslibrary.com/forum/topic398.html

[usmcgrunt]

Allen, Just my luck. The linked DB is coded with 32Bit code and I am on a 64Bit system. I cant watch the code in action but I can view it. SO I will start there. Thanks. It will take me some time to figure out how I can apply this DB code to mine.

Sean

[RuralGuy]

More examples of using Transactions:
http://allenbrowne.com/appaudit.html
http://allenbrowne.com/AppAuditCode.html

[RuralGuy]

BTW, you do not need those API's for a transaction. They are just for the Audit Function.