Inserting the entered date from a field in one form to a similar field in another form / table

[StuartR]

Can anyone advise me the best way to do this please ...

I have a members_table with a one to many link to a subscription_table. The subscription table records all payments received from all members. I want however to save the latest_payment_date for any member so that I can track which members are overdue with their subscriptions



I did think about having a field on the members table which would hold the date of the last_payment_made, with this field being over-written / updated by any payment made by that member in the subscription table / data entry form. I have however not managed to achieve this.

I was then going to run a query to list members who have not paid for more than 1 year, (ie in arrears with their subscription) but I am sure there will be a more efficient way of achieving this. Access 2016 may even have a standard solution to this of which I am unaware.

Any help or guidance will be appreciated.

[Bulzie]

Might try using DMax function to get the last date per member, then display that on the main form.

DMax("[Last_Payment_Made]", "Yourtable", "[MemberID] = " & Me.MemberID)

If you want to check it for overdue maybe for 6 months:

If DateAdd("m",6,DMax("[Last_Payment_Made]", "Yourtable", "[MemberID] = " & Me.MemberID)) > Date() then me.Overdue = "Overdue"

[StuartR]

Thanks Bulzie

I'm not too sure where you are suggesting I insert the DMax function.

Stuart

[Bulzie]

You can create a field on the Main form and in the Control Source add =DMax("[Last_Payment_Made]", "Yourtable", "[MemberID] = " & Me.MemberID)

[StuartR]

Getting there I think, but not quite there yet ...

Actual Table & Field names are ...

Member_Names with Member_ID field with one to many relationship with MemberIDFK field on Income table
Member_Names table has a field called Last_Payment

I created a table called Member_Names_DateTest which has a control for the Last_Payment field

I inserted the following code using the Expression Builder into the Control Source for this ...

=DMAX("[Date of Payment]","Income","[MemberIDFK] = " & Me.Member_ID)

This is accepted as not having any errors, but when you view the Member_Names_DateTest form the Last_Payment field shows #Name?, and not the required last date.

Hope you can follow this, and thanks for your help.

PS Is the problem perhaps to do with the fact that we are searching for and passing Dates, and not text, and does this affect the " " etc. ???

[aytee111]

Try: =DMAX("[Date of Payment]","Income","[MemberIDFK] = Forms.formname.Member_ID")

[StuartR]

Success ~ That did it. Thanks for your help aytee111 and Bulzie. Much appreciated.