Forum Discussion
Adding sub form for a new year
I am new to access. I have a new job to work on the database. I am attaching a copy of the page where there is the membership record and below there are several subforms. I would like to add a new subform of "2025-2026 Dues". There are forms for the previous years and I can copy them, but I don't know how to get them at the bottom ofthe page I have shown below. Can someone help me?
13 Replies
I just realized that when I copied the files into one table from the many yearly tables, the records are reordering themselves by the record number. How do I prevent the reordering. I want them by "Year Dues Date."
When you look directly at a table in Access, the records do not have a guaranteed order. Access will usually just show them in the order they were created, or by the autonumber field, but that can change. Tables are meant for storing the data, not for controlling how it looks.
If you want your records to always show by Year Dues Date, the way to do that is with a query (or a form or report based on that query). Just create a query on your table, add the Year Dues Date field, and then sort by that field. That way you will always see the data in the order you want.
I also strongly recommend that you do not use spaces in your field names or table names. It might seem fine now, but it will cause you problems later when you start writing queries or VBA code. Use something like YearDuesDate instead of Year Dues Date.
It might also help to go through a good beginner tutorial or book on Access. There are plenty of free resources on YouTube, or you can find books that walk you through the basics step by step. That foundation will make it much easier to build your database the right way from the start.
I have been watching your videos, playing, and learning a lot. I realize that in the many dues tables (one for each year) the previous person used an autonumber for the primary key. The primary key counted the number of dues paid for each year which I don't need except that it is a unique number. When I copy several years into a new table, they get new primary key numbers for the dues table. The foreign key number is the membership key number and it does not change. Does this cause any problems in relating the dues to the proper member in the membership table?
I really appreciate all of your help. I am going to put all the individual years into one file which I can do. However, I have a problem. There is an "Autonumber" field that when I put two years together I get a continuous running number. I want to count the number of payments received by year. I have a field "Year Dues Paid" that I can use to count payments. The date changes from year to year, i.e. 2024, 2025, etc. How can I start the count over each year.
You generally don’t want to store that count in your table. That’s something you calculate on the fly. For example, you can use DCount in a query, form, or report to count the number of payments for a given year, or just use the aggregate Count function in a totals query. There are lots of different ways to do it, but storing calculated values like this in the table is not considered good database practice. See this video for more.
The reason you are running into trouble is the design itself. Having a separate subform and usually a separate table for each year is what creates the problem. I see people do this all the time with things like sales tables - Sales2024, Sales2025, and so on - but that really should all be one table. The same applies here with your dues.
What you really want is one Dues table with fields like MemberID, Year, AmountPaid, DatePaid, MailingFeePaid, and so on. That way every year's dues go into the same place. Then on your main Membership form you only need one subform that lists all the dues for that member. If you want to see a specific year, just add criteria to filter by Year. That makes the database simpler, easier to maintain, and it will keep working indefinitely without you having to add a new tab every year.
If you already have tables for each year, you can copy them into one combined table, just adding the year as a field. Once it is set up, you never have to redesign the form again. It is a one-time fix that saves a lot of headaches down the road.
I actually did a TechHelp video on something very similar to this because my students ask me about it all the time. The question I usually get is "how do I reset the database for a new year?" The answer is the same idea - you do not create a brand new table or form for each year, you design the database once with the year as a field and then just filter the data you want to see. Here it is: Reset an Access Database for the New Year.
Let me know if that helps.
LLAP
RRI see a some things in this screen shot that make me want to know more about the history of this particular database.
First, the image and design of this form remind me what we used to see in the early 2000's, with Access 2003 era databases. Is this one that old?
Second, the record selector shows 1 of 1843. I assume that would refer to the number of members, which is rather small for a database as old as this appears to be.Third, the implication of having tabs for different years of dues is that the underlying design might not be appropriate.
Does this database have different tables for each year's dues, in addition to the subforms? As Tom said, all those subforms is going to lead to their own problems, and if there are also multiple tables, it's even more sketchy.
All in all, the impression created by the screen shot and desription is that this is an older database in need of tender loving care to make it more viable.Show us, please, the table relationships in this database.
I like the idea of one dues form for all years, but I am not sure how that would work and where to begin to make the change.
You open the form in design view, add a new tab by right-clicking the tab control, then copy the previous subform, select the new tab, and paste.
That said, think what this will look like in 10 years. So many tabs I would get dizzy. Plus, what if you win the lottery in year 5 and there is nobody available to do this annual maintenance? It would be much better to have a single tab for Dues, listing all dues. Perhaps there would be two text boxes to allow filtering for a date range. The big advantage is that this will work indefinitely, without any annual maintenance needed. Highly recommended.Thank you so much. Great idea. Now I need help in how to implement your idea. This is my 3rd time trying to reply to your post. It disappears.
I like your idea. Thank you. However, I have no idea how I would do that. Any help would be greatly appreciated.
Design the form, and inspect the subform for the most recent year. What is its Recordsource property? Is the Filter property set?
But before we go down this road: please lay out all your tables on the Relationships diagram (see Database tools tab in the Ribbon), and post a screenshot here. We want to convince ourselves that you have a solid database design.
