I have a combo box (Status) which includes the following:
- Shortage
- Allocated
- Actioned
- Acknowledged
- Complete
I also have 5 other date fields which are as follows:
- Shortage_date
- Allocated_date
- Actioned_date
- Acknowledged_date
- Complete_date
However I want this status to be populated automatically based on what data has been entered in my previous fields.
For example, once shortage_date has been populated with a valid date (00/00/0000) I want the "status" to change to "shortage".
Once allocated_date has been populated with a valid date (00/00/0000) I want the "status" to change to "allocated".
I saw this bit of code online but I'm totally confused:
Private Sub Textbox1_AfterUpdate()
If Textbox1.Value = "1" Then
Textbox2.Value = "10"
End If
End Sub
I believe mine should look something like this but I dont know what I need to make sure it validates the date.
Private Sub shortage_date_AfterUpdate()
If shortage_date.Value = "(I want to valididate the date here)" Then
Status.Value = "Status"
End If
End Sub
Hope I make sense!
Firstly, I would set up an
Input Mask
on the field itself. You can do that by putting the form into design view, then select the field, then go theProperty Sheet
which isAlt+Enter
if it isn't open, then select theData
tab and set up aInput Mask
. That will handle your validation part so you don't have to in code.Then you should be able to just use the code:
The
If
statement is just to make sure that it doesn't reset the value back to the original every time the date is changed. Also here is link where you can read aboutInput Masks
: https://msdn.microsoft.com/en-us/library/office/ff821336.aspxUpdate: Changed to
Input Mask
instead ofValidation Rule