Validation <=3

[teiben]

I have 2 fields datein and duedate. Duedate must be 3 days or more from the datein. For example if today is 9/23/04 the due date must be 9/26/04 or later. I added some code to the before update on the duedate field

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = acDataErrContinue
End If
End Sub

The message worked but user were still back dating the duedate. So AT THE table level, at duedate I made the default value =DateAdd("d",3,Date()) and the validition rule: <=3

Now when the user tries to change the default date on the form to say 10/27/04, the user is unable to move out of the duedate field and the database ends up locking up. Is the validation rule wrong? Is there a sample db out there (I'm using 97) w/examples of how to use date validation at the table level? Help would be greatly appreciated.

 

[Rusty]

Yep there are a number of problems here that are leading to the lock up.

First of all the default value of the [duedate] field at the table level makes it add in the date 3 days after today, i.e. 26/09/2004.

The validation rule (even when it's own without the above) will not do what you want it to do, i.e. the duedate must be 3 days or more from the datein.

I think the way to go is in the code on the form - I'll see what I can come up with and try to get back to you.

Rusty

 

[PierreR]

Teiben,

Sorry for all the trouble you're having. The code I gave you works perfectly in Access 2000. There is no way the user gets out of the textboxes with a wrong newly entered date. I also converted to 97 format, but I'm not sure it is a perfect conversion. Did you check every letter in yours against the code I've given you before? Or do you know that it does not work because you have '97?

TRY THE 97 FORMAT ATTACHED.

Pierre.

 

[teiben]

Thanks. I tried the db ~ am able to open it. I can enter a bad date. Today's solution until someone else tells me different, was to remove the default date & default value. Instead I, at the form level on duedate I added >=Date()+3 as a validation rule. I tested it adding the wrong date, correct date. So I'll just back and see how much my phone rings later.

 

[Rusty]

Try using the code below in the BeforeUpdate Event of the [duedate] field.

Private Sub duedate_BeforeUpdate(Cancel As Integer)
If DateDiff("d", [datein], [duedate]) < 3 Then
MsgBox "You can't do that..."
Else
End If
End Sub

This fires off a messagebox if the duedate is less than 3 days after the datein. This can be adapted to suit your needs via some additional commands, e.g. undo, etc.

Hope this helps,

Rusty

 

[ecniv]

I am guessing you are using bound controls, if so use Rustys example.

If not, you can place validation checks on the Save button, which won't let the user save anything unless it passes all validations. This is harder to implement but you can make one sub to do the checks and it can affect the colours of textboxes to assist the users in where problems lie. Just an option for future development you may wish to consider. BTW to do this you'll need some good VBA coding


Vince

 

[PierreR]

May I know in what way Rusty's code is different to mine? I cannot see a difference.

Pierre.

 

[Rusty]

May I know in what way Rusty's code is different to mine? I cannot see a difference.

Pierre.

Dunno mate, there may not be a difference - I didn't have a chance to look at your attached solution before posting mine.

However, teiben said that he was unable to open your Db - so he wouldn't have seen your solution in action anyway.

Rusty