Auto adjust table data

[hawzmolly]

In my member db Table I need to change the [Current] field (Yes/No) and [Status] field from Active to Inactive automatically on my main table when 90 days past [DuesDue] is reached. Then after they pay their dues the table returns from No to Yes and Inactive to Active.

The [Current] filed is Yes/No, the [DuesDue] and [DuesPaid] are Date/Time and the [Status] is Text.

All of this should be done from the data entry form such that when I enter the [DuesPaid] date and effective period i.e., 1 Quarter, 1/2 year, or 1 year the 90 day clock starts again after a persons new [DuesDue] date occurs.

I know that setting up an expression [DuesDue]+91 will give me the correct date. What I don't know how to do is have the system change the [Current] field to No or the[Status] to Inactive automatically when I close or add using the data entry form.

I am sure this can be done when the db is opened using code but I am not sure where to start.

My data entry Form is based upon the following SQL

SELECT DatePart("q",[DuesPaid]) & "Q/" & DatePart("yyyy",[DuesPaid]) AS Qtr, DatePart("q",[DuesDue]) & "Q/" & DatePart("yyyy",[DuesDue]) AS Due, Members.LastName, Members.FirstName, Members.DuesPaid, Members.DuesDue, Members.HomeAddress, Members.HomeCity, Members.HomeStateOrProvince, Members.HomePostalCode, Members.EmailName, Members.Phone, Members.Current, Members.DuesAmount, Members.MemberTypeID, Members.Notes, Members.VotingMember, Members.Status
FROM Members
ORDER BY Members.LastName;




Any help would be greatly appreciated.

TIA

[Stingaway]

Initial thought - create a query to do the update you described, in the on open event of the form, run the query.

[hawzmolly]

I tried an update query. Problem is the query wants to update all the entries. I need to separate out those with [DuesDue] dates that are greater than 90 days overdue and I am not sure what code I should use in the query.

The SQL below gets me to the members who are not current by more than 90 days but the only reason this happens is because I have gone thru the DB and checked "No' for all of those who are not current whether by 1 day or 1 year. If I delete the Current = No then I get all entries and the dates that are +90. What I want is for the Table to update, change yes to no, based upon the +90 day criteria when I open the DB.

SELECT Members.LastName, Members.FirstName, Members.DuesPaid, Members.DuesDue, DatePart("q",[DuesDue]) & "Q" AS Qtr, DatePart("yyyy",[DuesDue]) AS QtrDue, [DuesDue]+91 AS Expr1, Members.HomeAddress, Members.HomeCity, Members.HomeStateOrProvince, Members.HomePostalCode
FROM Members
GROUP BY Members.LastName, Members.FirstName, Members.DuesPaid, Members.DuesDue, DatePart("q",[DuesDue]) & "Q", DatePart("yyyy",[DuesDue]), [DuesDue]+91, Members.HomeAddress, Members.HomeCity, Members.HomeStateOrProvince, Members.HomePostalCode, Members.Current
HAVING (((Members.Current)=No))
ORDER BY [DuesDue]+91;

[hawzmolly]

Thank you for the suggestion. From the Switchboard I use an update query that runs a macro when I open the form.