VBA Code 3

[Lavenderchan]

Hi I was wondering I have a check box that I want to be check if a date is passed a certain amount of days. I have the past due days already in table but I'm not sure how to write the code to get this function to work. Would it be before update or after update? I'm a little confused. Any help would be helpfull.

Thanks,
Keri

 

[SkipVought]

hi,

"if a date is passed a certain amount of days"

"Would it be before update or after update? "

How did the date in question get there? From the query? From a user entry? WHEN?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lavenderchan]

The date is put there by a user. I think the code should go afte upafter update but I wasn't sure.

Keri

 

[Lavenderchan]

here is my code
Private Sub Is_Past_Due__BeforeUpdate(Cancel As Integer)
If Len(Trim(Me.Past_Due)) > Me.Due_Date Then
Me.Is_Past_Due_ = True
End If
End Sub

My toggle box is not filling in based on the critiea. Have I written it wrong.
Thanks,
Keri

 

[Remou]

Due_Date is a date? Past_Due is a number?

So

[tt]If Due_Date>=Date()+Me.Past_Due Then[/tt]

YesNo?

http://lessthandot.com

 

[Lavenderchan]

They are both date fields.

 

[Remou]

Then just compare them. When you use Len(Trim(Me.Past_Due)) you get a number, not a date.

http://lessthandot.com

 

[Lavenderchan]

I'm not able to get the code to work should I put it as a part of the query cretiea?

 

[Remou]

See my note above.

http://lessthandot.com

 

[Lavenderchan]

Here is my new code:
If Due_Date >= Date + Me.Past_Due Then
Me.Is_Past_Due_ = True
The check box is still not checking based on this cretiea. Am I doing something wrong?
Thanks,
Keri

 

[Remou]

Let's go back to my question, is Past_Due a number or a date? Perhaps you could post the line you have written (If Due_Date >= Date + Me.Past_Due Then) showing what you might expect the values to be?

http://lessthandot.com

 

[Lavenderchan]

Past Due is a date I want the check box Is Past Due to automaticly check when the past due date is greater than the due date.
Thanks,
Keri

 

[Remou]

So why do you have date in the line, just compare the two dates!

If Due_Date >= Date + Me.Past_Due Then

http://lessthandot.com

 

[MichaelRed]

If past due is a date, ...

Me.Is_Past_Due_ = Past_Due > Date()

But please delete the underscore highlighted in red,(in the expression AND in the name of the 'control) it may not actually do any harm, but it is also poor practice ...




MichaelRed

 

[ck1999]

Just a thought but should this code not go better in the
me.past_date after update procedure. The checkbox will never trigger the before_update without trying to update it.

ck1999

 

[Lavenderchan]

I want to trigger this code to fill the check box once a record becomes past due. How do I get it to run every time I open the form. Its not working should I put the code else were?
Thanks,
Keri

 

[PHV]

I want the check box Is Past Due to automaticly check when the past due date is greater than the due date
In the Current event procedure of the form and the AfterUpdate procedure of Past_Due:
Me![Is_Past_Due_] = (Me![Past_Due] > Me![Due_Date])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lavenderchan]

I would like to say THANK YOU very much for your help. the response have help clear up my confusion.

Keri

 

[ck1999]

You may also want to have the enabled property set to false so that the user cannot uncheck or check the checkbox.

If you leave it enabled then a user could uncheck or check the box then run a report and your underlining table would not have the correct value.

ck1999

 

[Lavenderchan]

Hi i added the false property and now the code isn't working proparly. Here is my code Me.Is_Past_Due = Me.Past_Due < Me.Due_Date.

I need it to check if the due date is greater than the past due date. then it would be over due.

Thanks,

Keri