checking if fields on form are blank

[p4nny]

Hi,


I would like a piece of code to check whether fields within a form are blank/null.

On event button click,

If they are blank/null then MessageBox, "Please enter info into..." the relevant fields returning the Control Source and cancel the event of add record to table

Otherwise proceed to add record to table.

Hope that make sense

Many thanks

[alansidman]

Here's an example. Change up the control name as needed.




Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Field1.Value) Then
MsgBox "Enter a value in Field1"
End If
End Sub

[HiTechCoach]

Hi,


I would like a piece of code to check whether fields within a form are blank/null.

On event button click,

If they are blank/null then MessageBox, "Please enter info into..." the relevant fields returning the Control Source and cancel the event of add record to table

Otherwise proceed to add record to table.

Hope that make sense

Many thanks

Data validation is best handle in the form's Before Updater event. This even always fires before a record is saved and can be cancelled. Closing a form, record navigation, Dirty=False ,save button, etc ill all fire off the Before Update event for a dirty record. There is no way a user can get around this event.

Example:


Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = False


' perform data validation
If IsNull(Me.CompanyName) Then

MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
Me.CompanyName.BorderColor = vbRed
DoCmd.GoToControl "CompanyName"

Cancel = True


End If

' check additional controls here



If Not Cancel Then
' passed the validation process

If Me.NewRecord Then
If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
Cancel = True
Else
' run code for new record before saving

End If


Else
If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
Cancel = True
Else
' run code before an existing record is saved
' example: update date last modified

End If
End If

End If


' if the save has been canceled or did not pass the validation , then ask to Undo changes
If Cancel Then

If MsgBox("Do you want to undo all changes?", vbYesNo, "Confirm") = vbYes Then
Me.Undo

End If

End If



End Sub