Inserting rows based on dates

[Mebd]

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]

you can have a button there there that will insert the records:

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]

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]

see this simple demo. see the code on the "button"

 

[Pat Hartman]

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]

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]

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]

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]

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]

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]

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]

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]

you need to fix the index of Dues table:

 

[Pat Hartman]

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.