How to set a SubForm to latest record

[Kassy]

I’m not sure how to go about this or if this is possible. I have two tables, tblMembers and tblPayments. Members have an autonumber key field and Payments also has an autonumber key field. They are linked through MemberID., which is the foreign key in the tblPayments. A user can amend or add members on a Form which has the tblMembers as the table source. On the Main membership form is a SubForm – Payments which synchronises to the MembersID on the main form. Trouble is it always shows the first record of the Members Payments and users in the past have just entered over the top of the payment date instead of moving to a new payment record. I would like it to automatically set the focus to the last recorded payment for each member. If it is a new member obviously then a new record for the payment. Is there any way that it can be coded so that on the 31st of July every year it automatically resets to the next payment record waiting for new payment details. I have considered putting a Boolean ‘expired field’ in the tblMembers but again I would want it to automatically reset to true when the date = 31st July so that Memberships have expired and have to be renewed. There is one exception to this and that is for a Life Member who does not have to pay or re-apply. Any help would be appreciated.