Update Table

[NISMOJim]

Hello Access Gurus,
I'm adding on to a database for employee permits I made a few years back, and could use some ideas.
tblPermit has fields such as PermitNo (PK), LName, FName, IssueDate. I added a checkbox field named Revoke.
I added a new table, tblRevoke, with the fields RevokeID (PK), PermitNo, RevDate and RevDays.
If someone's permit gets revoked, the user opens the Permit form to that employees record, which displays all the information from tblPermit, and clicks the "Revoked" checkbox. This opens another form to enter the date revoked, and the number of days revoked (Permit Number is autofilled from the Permit form).
I would like the Revoked checkbox in tblPermit to automatically be unchecked when the Revoked Date plus the number of Days Revoked is less than the current date, so we don't have to manually uncheck these records, yet still have tblRevoke as a record of past offenders. I'm concerned that a report of revoked permits will be run, and the report will include people who are not currently revoked, but were never unchecked when their time came up.
I was thinking of putting code in the On Open property of the main menu so the records are updated each time the database is opened, but I'm not sure how to properly write such code.
Thank you all for any suggestions.

[June7]

The check field is now calculated data. Don't save this calculated data. Calculate the status when needed. The field is superfluous.

Can a permit be reinstated? Can it then be revoked again?

[NISMOJim]

Yes, permits will be reinstated after a determined number of days, and yes, for the slow learners, they may be revoked again. tblRevoke stores the records that will allow us to look at the repeat offenders (no calculations, just a Revoked Date and the Number of Days Revoked, along with the Permit Number), while tblPermits just holds the checkbox to flag if a permit is currently revoked, based solely on if someone bothered to uncheck the box when their permit is reinstated.
The code I'm looking for would say "if the [Revoked Date] plus the [Number of Days Revoked] from the records in tblRevoke is before today's date, then uncheck the [Revoked] checkboxes in tblPermit that are currently checked, where the records from both tables have the same [Permit Number]. There will probably never be more than 3 or 4 revoked permits at any one time.
Hope that makes sense!

[June7]

I am still suggesting the field is now unnecessary. Calculate the status when it is needed. Any query/process that would be required to determine what records to update could just report the result, not save to table.

[NISMOJim]

I think I understand what you are saying. Delete the Revoked field from tblPermits, and only have the checkbox checked on the form based on the calculation of DateRevoked plus Number of Days? I think that would be some code in the On Current property of the form, but not sure how to write it.
I also have a form that searches records based on user-input criteria. On this form, I will need be able to search for currently revoked permits. Again, code being one of my many weaknesses, I'm a bit lost.

[June7]

Pulling the date of latest revoke record is easy with expression in ControlSource: DMax("DateRevoked","tblRevoke","PermitNo='" & [PermitNo] & "'")
Pulling the [Number of Days] value from the latest record gets tricky.
DLookup("[Number of Days]", "tblRevoke", "PermitNo='" & [PermitNo] & "' AND [DateRevoked]=#" & DMax("DateRevoked","tblRevoke","PermitNo='" & [PermitNo] & "'") & "#")

A query using TOP N could pull the latest revoke record for each permit, review:
http://allenbrowne.com/subquery-01.html#TopN

Then the expressions could reference that query:
DLookup("LatestDate", "QueryName", "PermitNo='" & [PermitNo] & "'")
DLookup("LatestNumbDays", "QueryName", "PermitNo='" & [PermitNo] & "'")


Is PermitNo a number or text field? If number, remove the apostrophe delimiters.

[NISMOJim]

That works good, but I made a small change. One additional hidden textbox pulls the max of the RevokeID of the matching permit number. Then, the other boxes pulling the date and days with the matching RevokeID is always the most recent infraction for each person.
Thank you for the help!