Forms and subforms - before update (1 Viewer)

LSL

New member
Local time
Today, 12:31
Joined
Apr 1, 2022
Messages
6
I have a database for members of a dog training club. I am very inexperienced in Access and have taught myself via the internet.

I have created the tabbed form below to keep each members details. The members pay an annual fee and the date of payment is entered into the membership paid field. I also have a table called payment and have placed a subform on the renewal tab to record dates the following years payment is made.

What I would like to be able to do is when the member makes a renewal payment is to edit the date in the membership paid field on the general tab so that this will show the most recent payment date and have the old date automatically populate the payment table and show on the subform on the renewal tab.

Is this possible? I was thinking of a before update event on the membership paid field but i've tried numerous ways and cannot get this to work.

Any help and advice would be appreciated.


mdform.png

renewal.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:31
Joined
May 7, 2009
Messages
19,169
how do you intend to do that?
what i can see on Renewal tab are but dates.
do you need a Button to automate it?

on the click of the button, it ask for the Renewal date.
if date is future date, that is, greater than the last renewal date,
1. save the that date to a variable (var1)
2. save the old membership paid date to a variable (var2)
3. replace the membership paid var1
4. save var2 to payment table.
 

LSL

New member
Local time
Today, 12:31
Joined
Apr 1, 2022
Messages
6
how do you intend to do that?
what i can see on Renewal tab are but dates.
do you need a Button to automate it?

on the click of the button, it ask for the Renewal date.
if date is future date, that is, greater than the last renewal date,
1. save the that date to a variable (var1)
2. save the old membership paid date to a variable (var2)
3. replace the membership paid var1
4. save var2 to payment table.
I am manually entering the dates into the renewal tab, but would like to automate it so if I edit the membership paid date on the general tab it will automatically enter the old value (before editing) into a new record on the renewal tab
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:31
Joined
May 7, 2009
Messages
19,169
you can store the OldValue of your "membership paid date" to a variable.
add code to the BeforeUpdate event of your "membership paid date"

private sub membership_paid_date_BeforeUpdate()
dim dte as variant
dte = me!membership_paid_date.OldValue
currentdb.Execute "insert into payment_table (pay_date) select " & Format$(dte, "\#mm\/dd\/yyyy\#") & ";"
end sub
 
  • Like
Reactions: LSL

LSL

New member
Local time
Today, 12:31
Joined
Apr 1, 2022
Messages
6
you can store the OldValue of your "membership paid date" to a variable.
add code to the BeforeUpdate event of your "membership paid date"

private sub membership_paid_date_BeforeUpdate()
dim dte as variant
dte = me!membership_paid_date.OldValue
currentdb.Execute "insert into payment_table (pay_date) select " & Format$(dte, "\#mm\/dd\/yyyy\#") & ";"
end sub
Great! Thanks, I’ll give this a go
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:31
Joined
May 7, 2009
Messages
19,169
on second thought it would be best to use the BeforeUpdate event of the sub-Form.
 

LSL

New member
Local time
Today, 12:31
Joined
Apr 1, 2022
Messages
6
on second thought it would be best to use the BeforeUpdate event of the sub-Form.
This worked great on the BeforeUpdate of the main form and is adding the correct value into a new record in the payment table. However the new record has no member ID. (Fields in table are MemberID and date_paid) It's also not showing on the subform as obviously its not attached to that Member record. Could you please tell me what I need to add to the code to ensure the entry is being made to the correct member. Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:31
Joined
Feb 19, 2002
Messages
42,970
I edit the membership paid date on the general tab it will automatically enter the old value (before editing) into a new record on the renewal tab
It is poor practice to store the same piece of data multiple times.

Always update using the renewal form. If you want to see the latest renewal data on the membership tab, use a control bound to a domain function:

=dlookup("PaidDT", "tblDues", "MembershipID =" & MembershipID)
 

LSL

New member
Local time
Today, 12:31
Joined
Apr 1, 2022
Messages
6
It is poor practice to store the same piece of data multiple times.

Always update using the renewal form. If you want to see the latest renewal data on the membership tab, use a control bound to a domain function:

=dlookup("PaidDT", "tblDues", "MembershipID =" & MembershipID)
Thanks for that. Where do I put this code
 

Mike Krailo

Well-known member
Local time
Today, 08:31
Joined
Mar 28, 2020
Messages
1,030
Instead of using a regular bound control, make a calculated control. Put the dlookup line directly inside the control. Make sure to take the tab stop off of that control so it gets skipped over. Then you don't need that field in your main table anymore. As Pat said, it is redundant.

1649085633629.png
 
  • Like
Reactions: LSL

Users who are viewing this thread

Top Bottom