How to automatically update Yes/No field based on date

[KerryL]

I have a database that is used to track assignments. When an assignment is entered, a "Received Date" and "Due Date" are entered. Also in the table there exists a field called "Overdue" which is used on reports to signify whether an assignment is overdue. It is a Yes/No field; the default is "No."

I would like to automatically have the "Overdue" field changed from the default of "No" to "Yes" when the "DueDate" of a particular record becomes less than today's date.

I've added code that accomplishes this when the data entry/edit forms are opened, but if the database is used and those forms aren't opened, the "Overdue" field for each record is not affected.

My question is this:
Each time the database is opened, how can I compare the &quot;DueDate&quot; of each record to that day's date and, for those records where DueDate < Today, change the &quot;Overdue&quot; field from the default of &quot;No&quot; to &quot;Yes?&quot;

Thanks in advance for your help.
KerryL

 

[vb6novice]

KerryL,

If your database has a startup form that opens automatically when the database is opened, then place your code in the OnOpen event for that form.

If your database does not have a form that automatically opens on startup, put the code in a module, and have the OnClose event of every form call the procedure.

 

[mikey69]

The first of VB6's suggestions would be my choice. It's easy to bypass the start-up options if you need to, so this would be fail-safe. A strat-up form is a good idea anyway.

 

[KerryL]

Yes, there is a start-up form and I understand your advice and it makes sense to me. But let me ask a dumb question.

I was under the impression that I couldn't reference or update a field unless it existed on an open form. If the start-up form doesn't have any of those fields on it, how do I write the code to affect the correct fields in the table?

Database name = Tracking
Start-up form = frmMainMenu
Table name = tblData
Fields involved = DueDate & Overdue

Using the parameters above, can I write something like this in the OnOpen event of frmMainMenu?

If (Now) > [Tracking]![tblData]![DueDate] Then
[Tracking]![tblData]![Overdue] = True
Else
[Tracking]![tblData]![Overdue] = False
End If


It seems to me I need to do more than this--like define the table as a dataset and increment the code through each record--or at the very least this will update all the Overdue fields. Sorry for my confusion, but thank you for your help.

KerryL

 

[mikey69]

Don't know which version of Access you are using. assuming it is 97 or 2000, you need to use Data Access Objects (DAO) which also work in 2002. You need a few lines of code in the event to activate the database, open the recordset, read the record amend and update it. You should search the help file for more details, or look in the book called Building Applications with Microsoft Access, which should be on your CD. At first, DAO seems frightening - there's a mass of detail. But It's worth persisting. Like most things to do with computers, it is logical, even if the logic takes some understanding.

Good luck.

 

[KerryL]

I'm using Access2000, Mike.

I think I understand your reference to DAO and the need to open the table as a recordset. I just can't seem to get the syntax right so it will work.

It also seems kind of slow and cumbersome to open the recordset and compare the due date to Now() for each record. There must be a better way to update the overdue fields, I just haven't figured it out.

KerryL

 

[vb6novice]

KerryL,

Unless you have a gazillion records, looping through the recordset once should be fairly quick and if you do it on the Main form's open event, you probably won't notice the time it takes. Your code should use a SELECT statment to open a recordset that only has the Overdue field. That will improve speed. If you are trying to stay away from code, you could create an update query that compares the Due Date to current date and updates the Overdue field. Just run the query in a macro on startup.

Hope this helps.

 

[mikey69]

Hi KerryL, if you e mail me, I'll send you some VBA code to open a recordset. You should use a query as the rcordset, which has slightly different syntax, My e mail address is youmike@mweb.co.za