Make a field required based on another field

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

 

[JonXL]

Why not just one field, archiveDate, that identifies the record as active or not based on whether there's a value in it?

 

[shenekas]

Why not just one field, archiveDate, that identifies the record as active or not based on whether there's a value in it?

I have to show an "Active" or "Archived" status based on the customer needs and for reporting purposes. And if it is "Archived," then the Archived Date has to be recorded.

 

[JonXL]

You can do all of that with just the one field.

If the users want a Y/N flag showing somewhere on a report, you calculate it at runtime based off that date field.

 

[petertenthije]

Would this not be easier:

If Me.Status = "Archived" Then
If IsNull(Me.Archived_Date) Then
Me.Archived_date = now()
End If
End If

Edited to add: if you are using a form to update the table, then you can use the "After update" event:

Private Sub Status_AfterUpdate()
Archived_date = now()
End Sub

 

[xenou]

Maybe post your full code. It looks like that is a FORM_before_update, and as suggested above you might prefer to just set the value rather than present a dialog. I think it would have to be FORM_Before_Update, not Form_After_Update, so that the data is saved with the update (but admittedly I haven't done this in a while so I might remember wrongly). Nothing in the original post suggests why no fields would be clickable or editable.

 

[james_lankford]

I have to show an "Active" or "Archived" status based on the customer needs and for reporting purposes. And if it is "Archived," then the Archived Date has to be recorded.

and that is exactly what an archiveDate field would record -- the date it was archived
if it has a value in it then its been archived
if it doesn't have a value then it wasn't archived