Solved Check Combo value then date value on form close, prompt user

[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.