I am trying to check there are no null values in Sub Form (Using Forename)...I have tried the code below and it is not working, any help would be greatly appreciated. I have a Pupil Table with a separate table for contacts (subForm) and added this code to a 'Save' command button
UG added code Tags
Code:
Private Sub cmdSave_Click()
'Ensure a Name has been selected
If Me.frmPupilContact_subform.Forename Is Null Then
MsgBox "You must enter a name", vbOKOnly
Else
'Otherwise save
DoCmd.Save
MsgBox "The registration has been saved", vbOKOnly
'Close and open the form to clear it
DoCmd.Close
DoCmd.OpenForm ""
End If
End Sub
If IsNull(Me.frmPupilContact_subform.Form.Forename) Then
MsgBox "You must enter a name", vbOKOnly
Else
Me.Dirty = False
MsgBox "The registration has been saved", vbOKOnly
End If
The validation code DOES NOT belong in the save button. Validation code such as this belongs in the FORM's BeforeUpdate event. In that event, if the validation code finds an error, you can cancel the save and therefore prevent the bad data from being saved. The code, where it is now, can be bypassed and unless you have validation rules on the table, the bad data will be saved.
Code:
Private Sub cmdSave_Click()
On Error GoTo ErrSub
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Endif
ExitSub:
Exit Sub
ErrSub:
Select Case Err.Number
Case 2501
Resume Next
Case Else
Msgbox Err.Number & "--" & Err.Description
Resume ExitSub
End Select
End Sub
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Ensure a Name has been selected
If Me.Forename & "" = "" Then
MsgBox "You must enter a name", vbOKOnly
Ne,Forename.SetFocus
Cancel = True
Exit Sub
End If
End Sub
Private Sub Form_AfterUpdate()
MsgBox "The registration has been saved", vbOKOnly
'Close and open the form to clear it
DoCmd.Close
DoCmd.OpenForm ""
End Sub
The validation code MUST be in the subform, NOT in the main form. When focus shifts from the subform to the main form, Access AUTOMATICALLY SAVES the subform record so you do not have the control you think you have because you are using the wrong events.
Also,
There is something wrong with your flow if you have to actually close a form and reopen it. You might want to post the database so we can help to move the code to the correct events.
Access gives you all the control you need, you simply have to understand the purpose of each event to understand what code needs to go where
You're welcome. Despite all the forum postings on this topic, there is still confusion on how bound forms work and why your validation MUST be controlled in the FORM's BeforeUpdate event or sometimes in an individual control's BeforeUpdate event. ALL other events can be bypassed and allow bad data to be saved. In my apps, all validation code goes into the FORM's BeforeUpdate event. The only time I might deviate is if I want to raise an error message as the data entry is happening. For example, on entry of an SSN, I would want to check for duplicates immediately to prevent further data entry rather than have to reject the record save at the end.
The FORM's BeforeUpdate event is like the flapper at the end of a funnel. It is the last stop before a record gets saved and NOTHING gets past it regardless of what caused the record to be saved.
