Required Field Based on Another Field in Access

[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!

 

[dhookom]

I would probably lock the status field and use a little code in the after update of the ArchiveDate to set the status to archive.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[xweyer]

Just to clarify are you saying that after you close the message you can't move the focus to any of the controls on the form? As if they were all disabled/locked? Also what Control and Event is the code running behind?