Make a field required based on another field (1 Viewer)

[shenekas]

Hello,

I have a source table in Access 2016. This table has a field called Status and a field called Archived Date. The Status field is a Lookup field with 2 Values: Active and Archived. I would like to make the Archived Date field a "required" field if the Archived value is selected in the Status field. If the other value is selected, the Archived Date field should not be required to be populated. I used the following code:

If Me.Status = "Archived" Then

  If IsNull(Me.Archived_Date) Then

    MsgBox "You must enter a date when the Status is Archived.", vbOKOnly

    Cancel = True

  End If

End If

But after the message appears, it won't allow me to click in the Archived Date field (or anywhere else for that matter) in order to select the date. Is there anyway to have the cursor automatically go to the Archived Date field so that the date can be selected? Does anyone have any idea how to fix this?

Thank you!

 

[Isaac]

Where do you have that code?

 

[shenekas]

Where do you have that code?

In the form properties. I have it as an event procedure on that field.

 

[Isaac]

Okay, you're going to make me keep guessing.....Which event procedure? I recommend putting that type of validation in the Form's BeforeUpdate event

 

[shenekas]

Okay, you're going to make me keep guessing.....Which event procedure? I recommend putting that type of validation in the Form's BeforeUpdate event

My apologies. I’m not as well versed in the language as most probably are. But yes, it is in the form’s BeforeUpdate event.

 

[Isaac]

I am not sure why the form would lock up due to cancelling in the beforeupdate event, but you can also try SetFocus to the control where you want the cursor to go. Controlname.SetFocus

 

[shenekas]

I am not sure why the form would lock up due to cancelling in the beforeupdate event, but you can also try SetFocus to the control where you want the cursor to go. Controlname.SetFocus

I'm not sure either. I tried that, but then I receive this error.

 

[arnelgp]

you can use the Status' AfterUpdate Event together with the Form's BeforeUpdate Event (you need to delete your BeforeUpdate event on status):

Option Compare Database
Option Explicit

Dim bolCancel As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call Status_AfterUpdate
    Cancel = bolCancel
End Sub

Private Sub Status_AfterUpdate()
    bolCancel = False
    If Me.Status = "Archive" Then
        If IsNull(Me.Archive_Date) Then
            MsgBox "You must enter a date when the Status is Archived."
            Me.Archive_Date.SetFocus
            bolCancel = True
        End If
    End If
End Sub