Cancel action if fields in form are empty

[awmohrjr]

I have tried to develop code that would ensure that all fields on a
given form are completed before an action occurs. Unfortunately with
the code below I am able to create message boxes that display if the
field is empty, but the action still occurs. In other words, the even
is not cancelled if any fields are null on the form.

I would appreciate any help you could provide to correct this problem.

Thanks.

-----------------------------------------------------
Private Sub Command75_Click()
On Error GoTo Command75_Err

DoCmd.Echo True, ""

If (IsNull(Forms!frm_CFAAdd!Originator)) Then
Beep
MsgBox "The Originator must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!TechnicalContact)) Then
Beep
MsgBox "The Engr/PM must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!KAM)) Then
Beep
MsgBox "The KAM must be completed before proceeding.""",
vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Purpose)) Then
Beep
MsgBox "The Purpose must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CustID)) Then
Beep
MsgBox "The Customer must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Program)) Then
Beep
MsgBox "The Program must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Description)) Then
Beep
MsgBox "The Description must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!ExpandedDescr)) Then
Beep
MsgBox "The Expanded Description with Required Actions must
be completed before proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CustRefNo)) Then
Beep
MsgBox "The Customer RFQ No. must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!DrawingRef)) Then
Beep
MsgBox "The Drawing reference must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CADRef)) Then
Beep
MsgBox "The CAD reference must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!TypeofChange)) Then
Beep
MsgBox "The Type of Change must be completed before
proceeding.""", vbOKOnly, "Action Stopped"

End If

[CFANo] = [CFA]
Forms!frm_CFAAdd.CFANo.Visible = True

Command75_Exit:
Exit Sub

Command75_Err:
MsgBox "All fields must be completed before proceeding.""",
vbOKOnly, "Action Stopped"
Resume Command75_Exit

End Sub

 

[Allen Browne]

Use the BeforeUpdate event of the *form* to validate the record. That event
fires regardless of how the record is going to be saved.

You do realize you can also open your table in design view, and set the
Required property of each field to Yes if it must be entered? Normally you
would do that for only a few fields though. If you are requiring all fields
just to try to avoid handling Nulls, see:
Nulls: Do I need them?
at:
http://members.iinet.net.au/~allenbrowne/casu-11.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have tried to develop code that would ensure that all fields on a
given form are completed before an action occurs. Unfortunately with
the code below I am able to create message boxes that display if the
field is empty, but the action still occurs. In other words, the even
is not cancelled if any fields are null on the form.

I would appreciate any help you could provide to correct this problem.

Thanks.

-----------------------------------------------------
Private Sub Command75_Click()
On Error GoTo Command75_Err

DoCmd.Echo True, ""

If (IsNull(Forms!frm_CFAAdd!Originator)) Then
Beep
MsgBox "The Originator must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!TechnicalContact)) Then
Beep
MsgBox "The Engr/PM must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!KAM)) Then
Beep
MsgBox "The KAM must be completed before proceeding.""",
vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Purpose)) Then
Beep
MsgBox "The Purpose must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CustID)) Then
Beep
MsgBox "The Customer must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Program)) Then
Beep
MsgBox "The Program must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Description)) Then
Beep
MsgBox "The Description must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!ExpandedDescr)) Then
Beep
MsgBox "The Expanded Description with Required Actions must
be completed before proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CustRefNo)) Then
Beep
MsgBox "The Customer RFQ No. must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!DrawingRef)) Then
Beep
MsgBox "The Drawing reference must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CADRef)) Then
Beep
MsgBox "The CAD reference must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!TypeofChange)) Then
Beep
MsgBox "The Type of Change must be completed before
proceeding.""", vbOKOnly, "Action Stopped"

End If

[CFANo] = [CFA]
Forms!frm_CFAAdd.CFANo.Visible = True

Command75_Exit:
Exit Sub

Command75_Err:
MsgBox "All fields must be completed before proceeding.""",
vbOKOnly, "Action Stopped"
Resume Command75_Exit

End Sub

 

[AWM]

Thank you Allen however I am a newbie and need more specific
information to proceed. I have checked the newsgroup to find "before
update code validate" but cannot find any examples that I could use to
start developing the code. Could you provide more detail?

Thanks, Art
---------------------------------------

Use the BeforeUpdate event of the *form* to validate the record. That event
fires regardless of how the record is going to be saved.

You do realize you can also open your table in design view, and set the
Required property of each field to Yes if it must be entered? Normally you
would do that for only a few fields though. If you are requiring all fields
just to try to avoid handling Nulls, see:
Nulls: Do I need them?
at:
http://members.iinet.net.au/~allenbrowne/casu-11.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have tried to develop code that would ensure that all fields on a
given form are completed before an action occurs. Unfortunately with
the code below I am able to create message boxes that display if the
field is empty, but the action still occurs. In other words, the even
is not cancelled if any fields are null on the form.

I would appreciate any help you could provide to correct this problem.

Thanks.

-----------------------------------------------------
Private Sub Command75_Click()
On Error GoTo Command75_Err

DoCmd.Echo True, ""

If (IsNull(Forms!frm_CFAAdd!Originator)) Then
Beep
MsgBox "The Originator must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!TechnicalContact)) Then
Beep
MsgBox "The Engr/PM must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!KAM)) Then
Beep
MsgBox "The KAM must be completed before proceeding.""",
vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Purpose)) Then
Beep
MsgBox "The Purpose must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CustID)) Then
Beep
MsgBox "The Customer must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Program)) Then
Beep
MsgBox "The Program must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Description)) Then
Beep
MsgBox "The Description must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!ExpandedDescr)) Then
Beep
MsgBox "The Expanded Description with Required Actions must
be completed before proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CustRefNo)) Then
Beep
MsgBox "The Customer RFQ No. must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!DrawingRef)) Then
Beep
MsgBox "The Drawing reference must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CADRef)) Then
Beep
MsgBox "The CAD reference must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!TypeofChange)) Then
Beep
MsgBox "The Type of Change must be completed before
proceeding.""", vbOKOnly, "Action Stopped"

End If

[CFANo] = [CFA]
Forms!frm_CFAAdd.CFANo.Visible = True

Command75_Exit:
Exit Sub

Command75_Err:
MsgBox "All fields must be completed before proceeding.""",
vbOKOnly, "Action Stopped"
Resume Command75_Exit

End Sub

 

[Allen Browne]

1. Open the form in design view.

2. Open the Properties box (View menu. Make sure the title of the Properties
box says, "Form" so you are looking at the properties of the form, not those
of a text box.

3. On the Event tab of the Properties box, set the Before Update property
to:
[Event Procedure]

4. Click the Build button (...) beside this. Access opens the code window.

5. Between the "Private Sub..." and "End Sub" lines, set up the code to look
like the example below. You are blocking the save when you set the Cancel to
True.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.Originator) Then
Cancel = True
strMsg = strMsg & "Originator required." & vbCrLf
End If

If IsNull(Me.TechnicalContact) Then
Cancel = True
strMsg = strMsg & "Technical Contact required." & vbCrLf
End If

'repeat the above for other fields.

If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid data"
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen however I am a newbie and need more specific
information to proceed. I have checked the newsgroup to find "before
update code validate" but cannot find any examples that I could use to
start developing the code. Could you provide more detail?

Thanks, Art
---------------------------------------

Use the BeforeUpdate event of the *form* to validate the record. That event
fires regardless of how the record is going to be saved.

You do realize you can also open your table in design view, and set the
Required property of each field to Yes if it must be entered? Normally you
would do that for only a few fields though. If you are requiring all fields
just to try to avoid handling Nulls, see:
Nulls: Do I need them?
at:
http://members.iinet.net.au/~allenbrowne/casu-11.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have tried to develop code that would ensure that all fields on a
given form are completed before an action occurs. Unfortunately with
the code below I am able to create message boxes that display if the
field is empty, but the action still occurs. In other words, the even
is not cancelled if any fields are null on the form.

I would appreciate any help you could provide to correct this problem.

Thanks.

-----------------------------------------------------
Private Sub Command75_Click()
On Error GoTo Command75_Err

DoCmd.Echo True, ""

If (IsNull(Forms!frm_CFAAdd!Originator)) Then
Beep
MsgBox "The Originator must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!TechnicalContact)) Then
Beep
MsgBox "The Engr/PM must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!KAM)) Then
Beep
MsgBox "The KAM must be completed before proceeding.""",
vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Purpose)) Then
Beep
MsgBox "The Purpose must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CustID)) Then
Beep
MsgBox "The Customer must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Program)) Then
Beep
MsgBox "The Program must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!Description)) Then
Beep
MsgBox "The Description must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!ExpandedDescr)) Then
Beep
MsgBox "The Expanded Description with Required Actions must
be completed before proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CustRefNo)) Then
Beep
MsgBox "The Customer RFQ No. must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!DrawingRef)) Then
Beep
MsgBox "The Drawing reference must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!CADRef)) Then
Beep
MsgBox "The CAD reference must be completed before
proceeding.""", vbOKOnly, "Action Stopped"
ElseIf (IsNull(Forms!frm_CFAAdd!TypeofChange)) Then
Beep
MsgBox "The Type of Change must be completed before
proceeding.""", vbOKOnly, "Action Stopped"

End If

[CFANo] = [CFA]
Forms!frm_CFAAdd.CFANo.Visible = True

Command75_Exit:
Exit Sub

Command75_Err:
MsgBox "All fields must be completed before proceeding.""",
vbOKOnly, "Action Stopped"
Resume Command75_Exit

End Sub