A Warning Query: Looks at whether two separate action fields are due in 2 months:

Action Due in 60 Days: [Actions by]-Now()<=60
Assessment Due in 60 Days: [Next Due]-Now()<=60

Another warning query looks at whether either of these conditions is met.

What I would like to do is flag up if these conditions are true e.g. “-1”. Ideally, on opening the database if the dates are due, I would like a screen to pop up warning the user that the dates are due.

I would be grateful for any advice regarding this matter.

Will this be on a per-record basis.
Easiest way is to use a If...Then on the FormCurrent

ie

Sub Form_Current()
Dim intActDue as integer, intAssDue as Integer, strMessage as string
intActDue = datediff("d" date(), me![Actions By])
intAssDue = datediff("d" date(), me![Next Due])

if intactdue<= 60 and intAssDue <= 60 then
strMessage = "Both the Actions and Assessments are due in 60 days or less"

elseif intActDue <= 60
strMessage = "The Action is due in 60 days or less"

elseif intAssDue <= 60
strMessage = "The Assessment is due in 60 days or less"
end if

if len(strMessage) > 0 then
msgbox strmessage
End if
End Sub

Thanks for the speedy reply...

The macro looks at each record.

i'm afraid I'm gonna need a bit of babysitting with regards to entering code. Where, exactly do I need to enter the code that you have written? Where is FormCurrent located?

Addtional Info: The Startup screen is a Form, which is run using the autoexec macro.

Sorry to be a pain!

The Current event can be found on a form's Properties under the Events tab.

It's the first event listed.

Click on the button with the ... marking and select Code Builder and insert Fizzio's code (excluding the first and last lines.)




As an aside, it's a better practice not to put spaces into your object and field names. Consider changing, if possible, Actions By to ActionsBy and likewise with NextDue.

You could just use the autoexec to open the form filtered and only show the records based on your conditions

Thanks for all your help...however, I am still having problems, when I am entering the code (I am copying and pasting the exact code from the posting) it doesnot work. It seems like there is a problem with the date() part; the field turns red when I enter it. I would give more details, but I am at my home computer, not my work one. I will provide more details tomorrow.

Thanks so far though!

Dan

On inputting the code “intActDue = datediff("d" date(), me![Actions By])” the text turns red and a pop-up box appears saying “Compile error Expected: list or separator or )” after clicking ok the word “date” is then highlighted.

Any ideas?

P.S. The lines highlighted if copied and pasted in are as follows:

intActDue = datediff("d" date(), me![Actions By])
intAssDue = datediff("d" date(), me![Next Due])
elseif intActDue <= 60
elseif intAssDue <= 60

Originally posted by d4nnyboy1
On inputting the code “intActDue = datediff("d" date(), me![Actions By])” the text turns red and a pop-up box appears saying “Compile error Expected: list or separator or )” after clicking ok the word “date” is then highlighted.

Any ideas?

P.S. The lines highlighted if copied and pasted in are as follows:

intActDue = datediff("d" date(), me![Actions By])
intAssDue = datediff("d" date(), me![Next Due])
elseif intActDue <= 60
elseif intAssDue <= 60


Fizzio has missed out the commas in the DateDiff function.

intActDue = datediff("d", Date(), me.[Actions By])
intAssDue = datediff("d", Date(), me.[Next Due])

Almost there! Thank you thank you thank you!

These two fields are still highlighted red…Any ideas?


elseif intActDue <= 60
elseif intAssDue <= 60

ok, i think i worked my last post, they were missing a then command. However, the nightmare goes on...

The expression looks ok, no red can be seen...but, when i try and run the form a box comes up saying the following error message:

"Run time error 2465 Micosoft Access can't find the field 'Actions By' referred to in your expression. You may have mispelled the field name, or the field may have been renamed or deleted"

Any ideas?!

There is no controle on your form named [Actions By]

There might be a problem with the spacing, ALSO try using me. instead of me!

Regards

The Mailman

Help, my brain is hurting!

If anyone could check the database out for me and see how they think I should do it, I would be really grateful.

Thanks in advance.

Dan

What DB?

Is there anyone willing to help on this matter, i can e-mail you the database. I cannot post it as it is too big even with just the structure.

Private message me if you would like to help.

Thanks

Dan