How to compare dates and generate error?

[joner89]

On my main form F_Orders i have two text box's. One contains and order date and one contains a required by date.

I want to create a macro or something that will check the validation of these entries before the order is confirmed.

In this case if the required date is earlier than the date ordered an error message is shown.

Here is my attempt so far.

Sub Date_Validation()
If Me.DateRequired.Value < Me.OrderDate.Value Then
MsgBox "The date required is ealier than the date ordered" & vbCrLf & _
"Please select a date required later than the date ordered"
End If
End Sub

The error i am getting is invaid use of Me keyword. I dont understand why this is wrong. Am i not selecting the field on my F_Orders form properly?

Any help would be great. Thanks in advance!

 

[John Big Booty]

I'm not sure where you currently have this code but I would probably put it in the Form's Before Update event, and change it slightly to;

If Me.DateRequired < Me.OrderDate Then
     MsgBox "The date required is earlier than the date ordered" & vbCrLf & _
          "Please select a date required later than the date ordered"
     [COLOR="DarkOrchid"]Cancel = True
     Me.DateRequired.Setfocus[/COLOR]
End If

 

[joner89]

The code is currently in a module. Apprently i have to have it in a module.

Where is the forms before update section?

Thanks for your feedback!

 

[John Big Booty]

I see no reason for it to be in a module. That is not to say however that it will not work in a module. You will however need to change it's structure somewhat. and then you will need to call that module in the form's Before Update event at any rate.

However for simplicity sake I'd put it straight in the form's Before Update event which you can find if you put your form into design view and then click in the small square in the top left hand corner of design window between the vertical and horizontal rulers, you will now you are dealing with the Form properties, rather than individual control properties as the small square will have a smaller black square inside it.

 

[joner89]

Thanks again John,

Well i have put it in the before update on the form anyway.

The only thing is how do i test it to see if it works? The whole reason i want it is so that i can validate the dates are coreect before i confirm the order. Can i assign a button to the before update? Or is there another way to do it.

Thanks again!

 

[John Big Booty]

If, as I trust you are doing, you are working with sample or a back up copy of your data simply start a new record and enter some dates that you know should get caught by you Before Update procedure.

 

[DCrake]

If you are using a bound form then to test the dates correctly you need to use the before update event on the control as opposed to the form event. This will then be validated for new and old records. You also need to extend the validation to both fields as a revised records can be checked as soon as a value has been entered

If date1 is not null and Date2 is not null then
    perform validation check
End if

Also only allow a date to be entered if the earlier date has been entered