Required fields

[Guest]

I need to set up some VB code to alert the inputter that they need to
complete a couple of required fields depending on the value of a third field.
At present I have some code (below) attached to the BeforeUpdate event of the
form that checks for one of the fields but I need to extend it to the 2nd
field and I don't know how to.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.[NYSPA Received] And IsNull(Me.Date_NYSPA_Received) And
Me.New_Cease = "New" Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not be
saved"
Cancel = True
End If
End Sub

The 2nd field is called Service Level and it also becomes mandatory if
New/Cease = "New". I will need to change the message that displays to reflect
both fields unless there is a way of individually identifying which field is
not completed.

I'm fairly new to VB so please be gentle with me!

 

[Guest]

You can use the same code twice for each field

If Me.[NYSPA Received] And IsNull(Me.[Service Level]) And
Me.New_Cease = "New" Then
MsgBox "Service Level is required" & vbCrLf & "Record will not be
saved"
Cancel = True
Exit Sub
End If
If Me.[NYSPA Received] And IsNull(Me.Date_NYSPA_Received) And
Me.New_Cease = "New" Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not be
saved"
Cancel = True
End If
*******************
Or, to make it more tidy
If Me.[NYSPA Received] And Me.New_Cease = "New" Then
If IsNull(Me.[Service Level]) Then
MsgBox "Service Level is required" & vbCrLf & "Record will not be
saved"
Cancel = True
Exit Sub
End If
If IsNull(Me.[Date_NYSPA_Received]) Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not
be saved"
Cancel = True
End If
End If

*******************
If it check the first field, if it's Null it will prompt the user and then
exit the sub
If the first field is not Null then it will check the next field, and prompt
if it Null.

Can also set the focus for each field if it's Null in each case, using:

Me.[TextBoxName].SetFocus

 

[Guest]

Many thanks for this - it worked brilliantly.
Just a quick question - If I wanted to use SetFocus where abouts in the code
should it be slotted. My guess is after the Cancel = True.

You can use the same code twice for each field

If Me.[NYSPA Received] And IsNull(Me.[Service Level]) And
Me.New_Cease = "New" Then
MsgBox "Service Level is required" & vbCrLf & "Record will not be
saved"
Cancel = True
Exit Sub
End If
If Me.[NYSPA Received] And IsNull(Me.Date_NYSPA_Received) And
Me.New_Cease = "New" Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not be
saved"
Cancel = True
End If
*******************
Or, to make it more tidy
If Me.[NYSPA Received] And Me.New_Cease = "New" Then
If IsNull(Me.[Service Level]) Then
MsgBox "Service Level is required" & vbCrLf & "Record will not be
saved"
Cancel = True
Exit Sub
End If
If IsNull(Me.[Date_NYSPA_Received]) Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not
be saved"
Cancel = True
End If
End If

*******************
If it check the first field, if it's Null it will prompt the user and then
exit the sub
If the first field is not Null then it will check the next field, and prompt
if it Null.

Can also set the focus for each field if it's Null in each case, using:

Me.[TextBoxName].SetFocus


--
Good Luck
BS"D

I need to set up some VB code to alert the inputter that they need to
complete a couple of required fields depending on the value of a third field.
At present I have some code (below) attached to the BeforeUpdate event of the
form that checks for one of the fields but I need to extend it to the 2nd
field and I don't know how to.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.[NYSPA Received] And IsNull(Me.Date_NYSPA_Received) And
Me.New_Cease = "New" Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not be
saved"
Cancel = True
End If
End Sub

The 2nd field is called Service Level and it also becomes mandatory if
New/Cease = "New". I will need to change the message that displays to reflect
both fields unless there is a way of individually identifying which field is
not completed.

I'm fairly new to VB so please be gentle with me!

 

[Guest]

It can be done after the MsgBox for each field

--
Good Luck
BS"D

Many thanks for this - it worked brilliantly.
Just a quick question - If I wanted to use SetFocus where abouts in the code
should it be slotted. My guess is after the Cancel = True.

You can use the same code twice for each field

If Me.[NYSPA Received] And IsNull(Me.[Service Level]) And
Me.New_Cease = "New" Then
MsgBox "Service Level is required" & vbCrLf & "Record will not be
saved"
Cancel = True
Exit Sub
End If
If Me.[NYSPA Received] And IsNull(Me.Date_NYSPA_Received) And
Me.New_Cease = "New" Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not be
saved"
Cancel = True
End If
*******************
Or, to make it more tidy
If Me.[NYSPA Received] And Me.New_Cease = "New" Then
If IsNull(Me.[Service Level]) Then
MsgBox "Service Level is required" & vbCrLf & "Record will not be
saved"
Cancel = True
Exit Sub
End If
If IsNull(Me.[Date_NYSPA_Received]) Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not
be saved"
Cancel = True
End If
End If

*******************
If it check the first field, if it's Null it will prompt the user and then
exit the sub
If the first field is not Null then it will check the next field, and prompt
if it Null.

Can also set the focus for each field if it's Null in each case, using:

Me.[TextBoxName].SetFocus


--
Good Luck
BS"D

I need to set up some VB code to alert the inputter that they need to
complete a couple of required fields depending on the value of a third field.
At present I have some code (below) attached to the BeforeUpdate event of the
form that checks for one of the fields but I need to extend it to the 2nd
field and I don't know how to.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.[NYSPA Received] And IsNull(Me.Date_NYSPA_Received) And
Me.New_Cease = "New" Then
MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not be
saved"
Cancel = True
End If
End Sub

The 2nd field is called Service Level and it also becomes mandatory if
New/Cease = "New". I will need to change the message that displays to reflect
both fields unless there is a way of individually identifying which field is
not completed.

I'm fairly new to VB so please be gentle with me!