how to set a macro to show a message at a certain date

[Jessica]

Hello,
For my company, thirty day reviews have to be completed on each client every
thirty days and treatment plans have to be completed two weeks after
authorization start date. What would be the best way to set a macro to
somehow alert me when treatment plans and thirty day reviews are due for each
client.
I am stumped. I initially thought to create a message box to appear but the
message would change for each client.

Thanks Jessica

 

[Steve Schapel]

Jessica,

The general approach I would use here is to make queries to return
records of overdue reviews. The specifics will, of course, depend on
the structure of your data, but I guess it will be something like where
a client's most recent review is more than 30 days ago.

Then, the next question is when you want this message box to be
activated. For example, whenever you open the database? Anyway,
whenever it is, you can use a macro with an OpenForm action, to open a
form that shows these overdue reviews. And in the Condition of the
macro, you can specify, using a DCount() expression, that the form will
only open if there are in fact any such records.

 

[Jessica]

Very helpful thanks a lot.

 

[Jessica]

That was very helpful but I am kind of stumped as to how to exactly write a
formula with dates. I created the query and now I would like to show results
with authorizations 14 days after the start date. I assume if I would need
to find records with authorizations thirty days after the authorization start
date, I would create the same query and set criteria for thirty days instead
of 14.

 

[Steve Schapel]

Jessica,

I would need to know specific details about your data (fields/tables),
with examples, before I could comment specifically. But in general,
yes, you can subtract one date field from another, or from today's date,
and use a criteria of >30.

 

[Jessica]

The table is called BA authorizations. The fields are: Client ID, BA start
Date, BA end date, total units authorized, units remaining

I would like to work with the "BA Start Date" in my query to show those
records which thirty days has past . Eventually, I will run it for those
with 14 days past as well as 60 and 90 but help with 30day time frame will
help be develop the others.

Jessica

 

[Steve Schapel]

Jessica,

If I understand you correctly, you would just put this in your query, in
the Criteria of the BA Start Date field...
<Date()-30
I.e. this will give you all records where the BA Start Date is before 30
days ago.