Aging alert

[Ropesend]

Is it possible to place an aging alert on database entries?
I need to complete a tracking report for incomplete items and track these to
the date that they are completed. I've completed the tables and forms but
would like to:
(1) Track the number of days it takes to complete the item.
(2) Place alerts on items that exceed accepted ranges such as: 2 to 3 days;
4 to 6 days and 6 and above.

How can I accomplish this?

 

[Wayne-I-M]

Hi

Have a look at this

http://msdn2.microsoft.com/en-us/library/aa262713(VS.60).aspx

You can use it "as-is" or change the "stuff" it does from a message box to
something else - a pop-up form giving notice of the days, a flashing button,
or use something like this to provide other warnings.

 

[Ropesend]

Thank you for this.
My secondary question is how to stop the days aging relation to the date
completed?

 

[Wayne-I-M]

If it were me (I'm a bit lazy) I would not use anything too complecated.

Just have 2 textboxes (bound to field in your table). The 1st =Date() when
the record is created (or some other time that you decide) that 2nd would be
either actioned by a simply button eg. setvalue.....Date() or on some othe
event means the item is completed. You don't need to store the difference
between the 2 just use the OnCurrent event of the record to view it in the
form or used a query to supply the difference if you're creating a report.

HTH

 

[Arvin Meyer [MVP]]

I usually build a recordset that executes when I open the database and
checks whether anything remains to be done. I also have a checkbox, to mark
it completed if it is done. You can build 3 separate queries, or a single
one using the Partition () function to display the number of days each
record is outstanding. If you use separate queries, you can do a record
count to tell you how many records are outstanding in each category and
display them in a form or report if you like.

 

[Ropesend]

I think I understand. I'll record the date completed and tlet a query run on
outstanding items which will be in a report--something called "outstanding
items". I guess I can create a subtable to allow users to address those
also. I'll see if it works.

You're not lazy. Why raise the bridge when you could just let out the air in
the tires?
Both result in getting truck pass under the bridge.

Thank you for the information.

 

[Ropesend]

I'll have to look up how to create the recordset execution on the database
opening. I think that I have handle on the other suggestions. I create date
field for completed items; run a query to identify if any records have this
blank and create a form and report on the query. I can allow the report to
do the summary, can't I?

Thank you. You've given me a solution I can use.

 

[Arvin Meyer [MVP]]

The recordset code might look something like this. Let's assume that you
have a yes/no field in the table named Done. Also make sure you have set a
reference (Tools >>> References) to DAO 3.6)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim x As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From MyTable Where Done = False",
dbOpenSnapshot)

x = rst.RecordCount

If x >= 1 Then
'Run your queries
Else
MsgBox "Nothing to do", vbOKOnly
End If

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com