Make a field required based on another field

shenekas

New Member
Joined
Mar 25, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Why not just one field, archiveDate, that identifies the record as active or not based on whether there's a value in it?
 

shenekas

New Member
Joined
Mar 25, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Sep 25, 2012
Messages
249

ADVERTISEMENT

Would this not be easier:
SQL:
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:
SQL:
Private Sub Status_AfterUpdate()
Archived_date = now()
End Sub
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,828
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jan 11, 2009
Messages
1,181
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top