Validating the date field using VBA code

[vs2k8]

Hello guys,

New to this forum and new to access programing, my issue is
I am comparing 2 date fields, I have to validate that Order Rcvd Dt should be less then Ord Comp date and Order Comp date cannot be null.
So I written a function to validate the field which is shown below,
If I write before update on Order_comp_date field then I get run time error message : You must save the field before you execute setfocus method

Private Sub Order_Dt_Comp_BeforeUpdate(Cancel As Integer)
If ChkFld = False Then
Cancel = True
End If

End Sub

This is my function
------------------------
Private Function ChkFld()

Dim isValid as Boolean
If (IsNull(Ord_Comp_Dt.Value)) Then
MsgBox "Order Complete Dt cannot be null,Please enter date"
Ord_Comp_Dt.SetFocus
isValid = False
End If
If (Ord_Rcvd_Dt > Ord_Comp_Dt.Value) Then
MsgBox "Order Complete Dt should be greater then Order Recv Date!"
Ord_Comp_Dt.SetFocus
isValid = False
End If

ChkFld = isValid

End Function

So my question is how I can overcome this error and I want user to enter the correct value and can't be null and even if just entering date and hitting save button it should also fire at save button too.

Thanks in advance

-visa

[hjozinovic]

hi Visa!

I would recommend using Validation rule property, unless you MUST use VBA for this.

a) Validation rule solution:
You can put in Ord_Rcvd_Dt's validation rule property:
Ord_Rcvd_Dt < Ord_Comp_Dt AND Len(Ord_Comp_Dt)>0

:::::::In Validation text property put a message like: "Order Complete Dt should be greater then Order Recv Date!"

b) VBA solution:
On form's before update event you can put a code:

If Me.Ord_Rcvd_Dt >= Me.Ord_Comp_Dt OR Len(Me.Ord_Comp_Dt)=0 Then
MsgBox "Order Complete Dt should be greater then Order Recv Date!"
DoCmd.CancelEvent

Hope this helps,
H.

[vs2k8]

Thanks H. for reply, I used your method but it seems Validation rule property is not working properly in access 2003, if I entered wrong date or kept null and hit the tab key it didn't fire the event, it works if i enter the date and remove the date and hit tab it says it cannot be null but it doesn't fire for wrong date.It should stop user there unless it enters right date.

Secondly I entered the code at form before update event,
it worked when I hit the save button but after the message it gives MS message 'You canceled the previous operation'
, i guess its bcos of DoCmd.Cancelevent, so how i can stop popping this message.
I am tired that my validation is not working, there are few more validation this one is easiere one its not working properly.

Hope to get the solution.

Thanks,
-visa

hi Visa!

I would recommend using Validation rule property, unless you MUST use VBA for this.

a) Validation rule solution:
You can put in Ord_Rcvd_Dt's validation rule property:
Ord_Rcvd_Dt < Ord_Comp_Dt AND Len(Ord_Comp_Dt)>0

:::::::In Validation text property put a message like: "Order Complete Dt should be greater then Order Recv Date!"

b) VBA solution:
On form's before update event you can put a code:

If Me.Ord_Rcvd_Dt >= Me.Ord_Comp_Dt OR Len(Me.Ord_Comp_Dt)=0 Then
MsgBox "Order Complete Dt should be greater then Order Recv Date!"
DoCmd.CancelEvent

Hope this helps,
H.

[hjozinovic]

Thanks H. for reply, I used your method but it seems Validation rule property is not working properly in access 2003, if I entered wrong date or kept null and hit the tab key it didn't fire the event, it works if i enter the date and remove the date and hit tab it says it cannot be null but it doesn't fire for wrong date.It should stop user there unless it enters right date.

Secondly I entered the code at form before update event,
it worked when I hit the save button but after the message it gives MS message 'You canceled the previous operation'
, i guess its bcos of DoCmd.Cancelevent, so how i can stop popping this message.
I am tired that my validation is not working, there are few more validation this one is easiere one its not working properly.

Hope to get the solution.

Thanks,
-visa

hey Visa,

i'm not sure why you get the message 'You canceled the previous operation'.
Obviously it's because of DoCmd.CancelEvent command but, I tried it myself and I got no message.

Attached here is my Test base. In MainForm's before update property you will find the code similar to the code you were using.

If bValue is less than 30 I get the message I asked.
After that I get no messages, only the record is still in editing mode as it should be, not allowing to save it before it meets the criterion set before.

Please try this and let us know the result.

h.

[vs2k8]

hey Visa,

i'm not sure why you get the message 'You canceled the previous operation'.
Obviously it's because of DoCmd.CancelEvent command but, I tried it myself and I got no message.

Attached here is my Test base. In MainForm's before update property you will find the code similar to the code you were using.

If bValue is less than 30 I get the message I asked.
After that I get no messages, only the record is still in editing mode as it should be, not allowing to save it before it meets the criterion set before.

Please try this and let us know the result.

h.

Thanks H for ur reply, I tried ur script beforeupdate, its still not working, so I used validation rule what u suggested before, instead Ord_Rcvd_Dt < Ord_Comp_Dt AND Len(Ord_Comp_Dt)>0 , I just tweaked it to < Ord_Rcvd_Dt and is not null, it works very well.

Now I have another question if you can help I have this combo box ord status, open (default) & closed, if user changes to close it should check other combo box status should be yes and one field should not be null,
Following is scenario user changes ord status = closed
combo box InOrdDoc = Yes and
InOrdDoc2 = Yes and field Product cannot be null,
I entered in Validation rule of Ord Status combo box
ord status = closed and InOrdDoc = Yes and InOrdDoc2 = Yes And Product is Not Null
Validation text: InOrdDoc and InOrdDoc2 must be yes and Product cannot be null.
I am trying to change Ord Status to Closed it fires continously,even I select open and my cursor sticks there and don't come out of my Order status field. So what event I should try or how to write in Validation rule.

Thanks in advance

-visa

[hjozinovic]

hey Visa,
If I understood you correctly then this is what you should do.
In form's before update event put a code:

If Me!OrdStatus = "Closed" Then
    If InOrdDoc=No OR InOrdDoc2=No OR IsNull Product Then
    MsgBox "InOrdDoc and InOrdDoc2 must be yes and Product cannot be null."
    DoCmd.CancelEvent
    End If
End If

btw, I don't understand why the VBA solution I suggested before didn't work for you?

H.

[vs2k8]

hey Visa,
If I understood you correctly then this is what you should do.
In form's before update event put a code:

If Me!OrdStatus = "Closed" Then
    If InOrdDoc=No OR InOrdDoc2=No OR IsNull Product Then
    MsgBox "InOrdDoc and InOrdDoc2 must be yes and Product cannot be null."
    DoCmd.CancelEvent
    End If
End If

btw, I don't understand why the VBA solution I suggested before didn't work for you?

H.

Thanks H. I don't know y its not working, do we have to change any properties of the field? I used ur code at form level beforeupdate, but nothing happened ,my cursor went to next field, it works only if I hit the save button, so I used the code at field level beforeupadte it works there if I hit tab it fires the trigger when criteria not matched, but issue is that it doesn't allow me to do any changes to be made in Indoc or Indoc2 or product field and continously it fires the message.So how I can overcome this pls let me know.
Below is the code
If Me!OrdStatus = "Closed" Then
If Me.InOrdDoc="No" OR Me.InOrdDoc2="No" OR IsNull(Me.Product) Then
MsgBox "InOrdDoc and InOrdDoc2 must be yes and Product cannot be null."
DoCmd.CancelEvent
End If
End If
Thanks again!

-visa

[hjozinovic]

Hi again.
The situation you described happens because you entered the code on field level.
You should have this code to run on form's before update property!
that way you will be able to adjust the values in specified fields and finaly save the current record after the criteria is met.
On field level it fires again and again because you need to exit current field in order to change other fields.
But you can't exit because it keeps firing the message!

Why is it not enough for you to check the fields before saving the record?
You keep insisting on field level checking, but that is not always easy...

H.

[vs2k8]

Hi again.
The situation you described happens because you entered the code on field level.
You should have this code to run on form's before update property!
that way you will be able to adjust the values in specified fields and finaly save the current record after the criteria is met.
On field level it fires again and again because you need to exit current field in order to change other fields.
But you can't exit because it keeps firing the message!

Why is it not enough for you to check the fields before saving the record?
You keep insisting on field level checking, but that is not always easy...

H.

Hi H,
Thanks for ur reply,I got it what u r saying, if Before update works at form level then it wouldn't be problem, user says we want that after entering the wrong value it should fire the trigger so I am trying to meet their requirement, can you tell me how I should convince user saying that this will work only at form level,while saving your record. I am not that expert in access so I dont know how to explain them.
One more thing I found that when I try to save it gives me following message
"The DoMenuItem action was cancelled" and it undo everything, I made in existing record, how I can trap this message and user can save existing record.

Thanks again.

-visa