Validation Rule / Mandatory Field

[Banker_Sam]

From a form, I have a field labelled AMOUNT and a field labelled DATE. Is it possible to make DATE mandatory if data is entered into the AMOUNT field?

[pbaldy]

I can't think of a way with a validation rule, though I don't use them a lot. You could use the form's before update event:

http://www.baldyweb.com/BeforeUpdate.htm

[Missinglinq]

There's a number of approaches to checking whether or not a Control is populated, and Paul's is fine, and using it, something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Len(Me.Amount) <> 0 Then
   If Len(Me.DateField & vbNullString) = 0 Then
    MsgBox "You need to fill out the Date Field!"
    Cancel = True
    Me.DateField.SetFocus
   End If
 End If
End Sub

Note that if your Field/Control is actually Date you need to change that to something else as Date is a Reserved Word. I used DateField in the example.

Linq ;0)>

[pbaldy]

There's your fish.

[deepakg27]

Thanks for the code will be useful for me.

Regards
Deepak Gupta

[Banker_Sam]

Thanks for that, I applied the code to fields labelled CLAIM AMOUNT and DATE CLAIM SENT. If an amount is entered into CLAIM AMOUNT but DATE CLAIM SENT is blank then the error message appears.

Now for Stage 2...

I tried applying the came principle to fields labelled PAID AMOUNT and DATE CLAIM RECEIVED but it impacts another filed DIFFERENCE (CLAIM AMOUNT minus PAID AMOUNT). It results in a name error. Do i need to apply the code to DIFFERENCE instead?

[pbaldy]

What is your code and what exactly is the error?

[Banker_Sam]

What I’m trying to do is
If LMIClaimAmount is greater than 0 then LMI_Sent_Date must be greater than 0. If not, an error message is to appear.
If LMIClaimAmountPaid is greater than 0 then LMI_Received_Date must be greater than 0. If not, an error message is to appear.
The Difference is LMIClaimAmountPaid minus LMI Claim Amount
The LMI Delay is LMI_Received_Date minus LMI_Sent_Date

Below is the code I am using, but running these concurrently is impacting the fields Difference and LMI Delay, resulting in an error.


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.LMIClaimAmount) <> 0 Then
If Len(Me.LMI_Sent_Date & vbNullString) = 0 Then
MsgBox "Please complete the Date Claim Sent field located on the LMI Details tab!"
Cancel = True
Me.LMI_Sent_Date.SetFocus
End If
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.LMIClaimAmountPaid) <> 0 Then
If Len(Me.LMI_Received_Date & vbNullString) = 0 Then
MsgBox "Please complete the Date Paid field located on the LMI Details tab!"
Cancel = True
Me.LMI_Received_Date.SetFocus
End If
End If
End Sub


Any assistance is greatly appreciated.

[orange]

resulting in an error

Error description and number?

[pbaldy]

You can't have more than one sub for the same event, if that's not a typo. You can have more than one test in a single sub:

Private Sub...
  If Something Then
     Do Something
  End If

  If SomethingElse Then
     Do SomethingElse 
  End If
End Sub