Access 2013 - Set a field value based on value of another field

Question

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!

Answer 1

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 the Property Sheet which isAlt+Enter if it isn't open, then select the Data tab and set up a Input 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:

Private Sub shortage_date_AfterUpdate()
      If Nz(shortage_date.Value, "") <> "" Then
         Status.Value = "Status"
      End If
End Sub

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 about Input Masks: https://msdn.microsoft.com/en-us/library/office/ff821336.aspx

Update: Changed to Input Mask instead of Validation Rule