Inserting rows based on dates (1 Viewer)

Mebd

New member
Local time
Today, 23:32
Joined
Jun 30, 2021
Messages
14
Hope some can help here?

I have a membership database and I want to create a form that records membership dues. What I need to happen is when we create a member, based on the date of their membership and current year, it will insert correct number of rows. For example, a member joins in 2018 then I should have 4 rows based on it being 2021, someone how joined in 2000 would have 21 rows, etc.

Each row should have the column heading, Year, Fee Amount, Date Fee Paid, Receipt Number, Collector, Comments

Year Fee Amount Date Fee Paid Receipt Number Collector Comments
2018 £27 21/08/2018 123 Mebs
2019 £27 12/08/2019 234 Mebs By Cheque number 0000123
2020 £27
2021 £27

Can this be done?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:32
Joined
May 7, 2009
Messages
14,607
you can have a button there there that will insert the records:
Code:
private button_Click()
dim lngCount

For lngCount = Year(Me!JoinDate) To Year(Date())
   currentdb.execute "insert into yourTable ([year]) select " & lngCount
next

end sub
 

Mebd

New member
Local time
Today, 23:32
Joined
Jun 30, 2021
Messages
14
Hi Arnelgp, Thanks for coming back. I'm new to all this so need step by step help. Where do I paste code suggested code?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:32
Joined
May 7, 2009
Messages
14,607
see this simple demo. see the code on the "button"
 

Attachments

  • membership.accdb
    552 KB · Views: 111

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:32
Joined
Feb 19, 2002
Messages
33,711
I'm assuming this is a conversion and you are trying to create missing detail records but where are you getting the information aside from membership year from? Has the fee been the same forever? How would you know when the fee was paid or the other information?
 

Mebd

New member
Local time
Today, 23:32
Joined
Jun 30, 2021
Messages
14
Hi Pat,

Yes this is a conversion based on paper records. The fee amount does change but not each year. The 'how do I know when the fee was paid' question is my next stage that I will post on.
 

Mebd

New member
Local time
Today, 23:32
Joined
Jun 30, 2021
Messages
14
Hi Arnelgp, apologies for the late response and thanks for the demo and I will try to replicate this on my database. will let you know how I get on
 

CarlettoFed

Member
Local time
Tomorrow, 00:32
Joined
Jun 10, 2020
Messages
36
The unique index "yr" of the "Dues" table is wrong as it is set only on "Year" while it must include "Year" and "MemberID".
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 23:32
Joined
Sep 12, 2006
Messages
14,689
I would be more inclined to have a members table with a start and finish date. Do you then really need to have a membership table with details for individual years? You could determine which memberships were current based on the dates in the members table, and then you don't need a table for membership at all.

ie, Member X joined on 18/4/2018, and there is no termination/resignation date - therefore X remains a member for 2018, 2019, 2020, 2021, without any further tables being required.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:32
Joined
Feb 19, 2002
Messages
33,711
You can create a table with the membership fee for that year. Then as you are creating records, you can get the fee from the years table.
 

Mebd

New member
Local time
Today, 23:32
Joined
Jun 30, 2021
Messages
14
Hi Arnelgp, you demo file worked a treat. That is exactly what I needed. Now how do I take your code and enter it into my database. Remember that I am new to all this, thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:32
Joined
Feb 19, 2002
Messages
33,711
Arnel's code doesn't do what you need it to do although I'm sure he can make it work as a conversion.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:32
Joined
May 7, 2009
Messages
14,607
you need to fix the index of Dues table:
member.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:32
Joined
Feb 19, 2002
Messages
33,711
arnel, this is a one time conversion. I doubt the OP wants to use the form to go one at a time t each member and generate the records. He might be able to convert it with some guidence.
 

Users who are viewing this thread

Top Bottom