Solved Check Combo value then date value on form close, prompt user (1 Viewer)

[allen675]

I have the following code which checks the combobox to ensure it matches a certain value, and if it does the code then goes onto do a date validation check for me. If the date validation check fails I want a YesNo message box to appear to prompt the user to go back and enter/update the date. Code so far:

Private Sub Command60_Click()

Dim sStatus As String

sStatus = Me!ClientStatus & ""
If sStatus <> "Trying to Contact" And sStatus <> "Appointment Booked" And sStatus <> "Appointment Completed" And sStatus <> "DIP Completed" And sStatus <> "Sign Up Booked" And sStatus <> "Signed Up" And sStatus <> "Contacted - Keeping in contact" Then
    DoCmd.Close
    [Forms]![MainMenu]![FollowUpList].Requery
    Exit Sub
End If

If (CDate(Me.FollowUpDate) <= Date) Then
MsgBox ("The follow up date has not been changed to a date in the future. Do you want to update this now?")
Exit Sub
DoCmd.Close
[Forms]![MainMenu]![FollowUpList].Requery
End If
End Sub

The first part works. It manages to check all the values of the 'ClientStatus' combobox and if a value matches it happily exits the sub. The problem I'm having is with the second section. I'm getting invalid use of Null on the If (CDate(Me.FollowUpDate) <= Date). I'd like to also be able to handle a no date value too i.e. if the date is null or <= to, then please input/update date

Trust that all makes sense?

 

[theDBguy]

Maybe try using Nz()?

If CDate(Nz(Me.FollowupDate, Date())) <= Date() Then

 

[allen675]

Maybe try using Nz()?

If CDate(Nz(Me.FollowupDate, Date())) <= Date() Then

Perfect thank you worked a treat

How do I personalise the message box title without in this situation please?

 

[theDBguy]

Perfect thank you worked a treat

How do I personalise the message box title without in this situation please?

MsgBox() has a few arguments.

MsgBox(Prompt, Buttons, Title)

Try adding the third argument.

 

[allen675]

I get an error '= expected'

 

[theDBguy]

I get an error '= expected'

If you want the user to select Yes or No, then try it this way?

If MsgBox("Are you sure?", vbQuestion + vbYesNo, "Please confirm") = vbYes Then

 

[allen675]

I want it to work with Ok, don't want to give them an option as date must be updated or entered

 

[allen675]

Don't worry fixed it myself!

 

[theDBguy]

Don't worry fixed it myself!

Congratulations! Glad to hear you got it to work. Good luck with your project.

 

[Pat Hartman]

I see that you closed the thread but your validation is not working correctly because it is not in the correct event. Validation code does NOT belong in a click event of a command button. The code belongs in the form's BeforeUpdate event so that it ALWAYS runs regardless of what caused the form's record to be saved.

What happens to your validation of you just close the form or close Access? How about if you scroll to a new record? Or click into a subform if you have one?

Private Sub Form_BeforeUpDate(Cancel As Integer)

Dim sStatus As String

sStatus = Me.ClientStatus & ""
If sStatus <> "Trying to Contact" And sStatus <> "Appointment Booked" And sStatus <> "Appointment Completed" And sStatus <> "DIP Completed" And sStatus <> "Sign Up Booked" And sStatus <> "Signed Up" And sStatus <> "Contacted - Keeping in contact" Then
    DoCmd.Close
    [Forms]![MainMenu]![FollowUpList].Requery
    Exit Sub
End If

If CDate(Nz(Me.FollowupDate, Date())) <= Date()Then
    MsgBox ("The follow up date has not been changed to a date in the future. Do you want to update this now?")
    Cancel = True
    Me.FollowUpDate.SetFocus
    Exit Sub
End If
End Sub