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

 

[Micron]

We would need the names of all of these controls you refer to. Also, why bother generating a message if someone just clicks on "Completed" but doesn't type anything in to it? Same possibility exists that they click on Completed Date without a combo selection but you don't specify that you want a message in that case.

 

[psamu]

Thanks. Drop down field is [status], [ReadyDate], and[CompltedDate]. If ready date is blank complteDate should not accept . Dropdown list are. "Active", "Ready", and "Completed". [ReadyDate] and [CompletedDate] will be blank for "Active"

 

[Micron]

One more question first. When the form opens, are the ReadyDate and CompletedDate fields populated if there is data in the underlying table/query or are they always blank at first? Please explain the possibilities.

 

[psamu]

Yes it will be blank until user select from drop down field (Status). Like user select "Completed" then it need to validate "ReadyDate" if empty or not. If empty should not accept. If ReadyDate available immediately "Completed " date need to populate. Thanks

 

[Micron]

I have most of it written, but I do not understand this part:

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.

If you select ready, and it automatically populates Ready Date, surely you do not want Completed Date to be Date() as well?
I think you are saying if user enters a ready date manually, THEN selects Ready status, you want Completed Date to be Date(). This means I have to check if Ready Date is already there before I decide whether or not to automatically make it Date().

 

[psamu]

If the drop down value is "Active" means ReadyDate and CompletedDate field should be blank even though some date entered earlier with status "Ready" in the drop down filed. Based on the drop down value both field need to be auto populate with today's date. But user should able to change, other condition if there is no date in the ready field completedfield should not accept a any date. Process is first status is "Active" then other user to"Ready". Then other user change the status to "Completed ". It is a process. In order to completed other user has to go to "Ready".

 

[Micron]

Upon reading your other posts, I think I have it. Put this at the top of your form module where it says Option Compare Database and SHOULD say Option Explicit:
Dim svOldValue As String

Use the property sheet to create an AfterUpdate event for the Status control and put this code into it. When done, it should look like this, but do not have the red parts in twice.

[COLOR=#ff0000]Private Sub Status_AfterUpdate()[/COLOR]

Select Case Me.Status
   Case "Ready"
        If IsDate(Me.ReadyDate) Then Me.CompletedDate = Date 'must be in this order
        If Not IsDate(Me.ReadyDate) Then Me.ReadyDate = Date
   Case "Completed"
        If IsNull(Me.CompletedDate) Or Me.CompletedDate = "" Then
             MsgBox "You must enter a Completed Date before selecting 'Completed' status."
             Me.Status = svOldValue
             Exit Sub
         End If
  Case "Active"
       With Me
           .ReadyDate = ""
           .CompletedDate = ""
        End With
End Select

svOldValue = Me.Status
[COLOR=#ff0000]End Sub[/COLOR]

Try it out and we'll see how close it is. Maybe you could figure out how to tweak it also.

 

[psamu]

Thanks. It works. But it let me enter a date with out changing to "Completed " in the "CompletedDate " field. And if I change to "Ready" from "Complted ". It won't clear the date in the "CompletedDate " field.

 

[Micron]

replace code lines in Status_AfterUpdate with this:

Select Case Me.Status
   Case "Ready"
        If svOldValue = "Ready" Then Exit Sub 'must be in this order
        If IsDate(Me.ReadyDate) Then Me.CompletedDate = Date
        If Not IsDate(Me.ReadyDate) Then Me.ReadyDate = Date
        If svOldValue = "Completed" Then Me.CompletedDate = ""
   Case "Completed"
        If Not IsDate(Me.ReadyDate) Then
            MsgBox "You must have a valid Ready Date before selecting 'Completed' status."
            Me.Status = svOldValue
            Exit Sub
        Else
            Me.CompletedDate = Date
        End If
  Case "Active"
       With Me
           .ReadyDate = ""
           .CompletedDate = ""
        End With
End Select

svOldValue = Me.Status

Put this at the bottom of the same form module:

Sub ChkComplDate()

If Not IsDate(Me.ReadyDate) Or Me.Status <> "Completed" Then
    MsgBox "Status must be 'Completed' AND a valid Ready Date is required before a Completed Date can be entered."
    Me.ReadyDate.SetFocus
End If

End Sub

Again, using property sheet, create ReadyDate After_Update event and insert this:

If Not IsDate(Me.ReadyDate) Then Me.CompletedDate = ""

Create two more events for CompletedDate; Enter event and Click event and add this line to BOTH:

ChkComplDate

Hopefully, I have it right, but the explanation of the requirements is a bit fuzzy given the level of complexity.