Search code examples
vbams-accessbeforeupdate

Keeping old values of a field in ms access database

I have a database for members of a club. I have a table member details with a field membership paid, which holds the date the annual membership is paid. I want to keep a record of this date in a table called payment and overwrite the membership paid field with the date in subsequent years. I am new to access and vba and have taught myself with the internet. Can anyone please tell me if it’s possible to do this using a before update event? I have a member details form and a payment subform. I was hoping to be able to see the latest date on the member details form and payment dates for previous years on the payment subform. Any help greatly appreciated.

Solution

  • Wrong approach. Start with a normalized table structure. Create tables and hook then together using the relationships tool to get something like:

    enter image description here

    try entering some mock data in the tables. Then on the toolbar on the left side of the screen under tables select the Member table and then on the ribbon under the create tab hit create form to get:

    enter image description here

    Access is all about rapid development. Use forms to enter data. Do not enter data directly in the tables. If you feel like it, practice using the default form to enter more mock data.
    Then put the form in design mode (after saving it) and delete everything in the highlighted header section of the form. For clarity select the MemberID textbox (not the label) and under the other tab on the textbox properties change the name to txtMemberID

    While still in design mode, on the ribbon under Form Design Add a textbox (it comes with a label) and name it txtLatestPayment. Then under txtLatestPayment's properties under the Data tab set the control source to the following

    =DMax("PaymentDate","MemberPayments","MemberID = " & [txtMemberID])

    your done, play around again:

    enter image description here