Access form dropdown multiple field validation.

[psamu]

I would appreciate anyone help on this. I have a dropdown field " Active", "Ready", and "Completed". There are two other fields for date, Ready Date and Completed Date. If user use the drop down and select "Ready" immediately "Ready Date" need to be by default Today' Date. If User select "Completed" without "Ready Date". It should stop and say "Completed" must be selected first. If already "Ready " selected and "Ready Date" populated in " Ready Date" field then without error "Completed Date" need to be populated by default Todays Date. Thanks

 

[psamu]

Thank you so much I appreciate your time and patience. I think it works, let me try. Different scenarios. Thanks again

 

[Micron]

You're welcome. I didn't write any error handling code - would not know where to put it for you.

 

[psamu]

Thank you. I changed the field names. Dropdown list are "Active", "Completed", and "Approved" . if selected "Completed" field name [Resolved Date] need to update and for "Approved" field name [Approved Date] need to update. Now I have below code.
<p>
Option Explicit:
Dim svOldValue As String

Private Sub Form_AfterUpdate()
Select Case Me.Status
Case "Completed"
If IsDate(Me.Resolved_Date) Then Me.Resolved_Date = Date 'must be in this order
If Not IsDate(Me.Resolved_Date) Then Me.Resolved_Date = Date
Case "Approved"
If IsDate(Me.Approved_Date) Then Me.Approved_Date = Date 'must be in this order
If Not IsDate(Me.Approved_Date) Then Me.Approved_Date = Date

If IsNull(Me.Resolved_Date) Or Me.Approved_Date = "" Then
MsgBox "You must enter a Completed Date before selecting 'Approved' status."
Me.Status = svOldValue
Exit Sub
End If
Case "Active"
With Me
.Resolved_Date = ""
.Approved_Date = ""
End With


End Select
svOldValue = Me.Status


End Sub

</p>

 

[psamu]

following need to correct.:
If I select Approved first without having Completed Date(Although gives warning, Approved date wont clear from Approved Date field)
Resolved Date and Approved Date field date should be editable if necessary (By default today's date)

 

[Micron]

You have put this code on the form AfterUpdate event, not the combo box AfterUpdate event.

You have 2 different controls being tested for null or empty string (""). Both tests are meant to be done on 1 control at a time.

If IsNull(Me.Resolved_Date) Or Me.Approved_Date = "" Then

Approved date wont clear from Approved Date field)

Use the examples above and write a line to set something to "" if that's what you need.

Resolved Date and Approved Date field date should be editable if necessary

So are they not editable? By this I presume you mean user should be able to type into them.

 

[psamu]

Thank you. Yes by default it should give today's date as per the selection but it is up to user to change different date. Now I can change the date but when I save it goes back to today's date.

 

[Micron]

Possibly because you have the code in the wrong event.