User Profile
RichardRost
MVP
Joined Mar 01, 2020
User Widgets
Recent Discussions
Re: VBA
That error should not happen when you make a brand new database. If you are seeing it right away, it usually means there is something wrong with your Access installation, not the database itself. The first thing I would try is doing a repair of Office from the Control Panel. If that does not fix it, you might need to completely remove Office and reinstall it, since sometimes older versions or corrupted installs can leave things behind. Is this a brand new computer, or have you had Office on it for a while? Sometimes the history of installs makes a difference. I give my students a full troubleshooting checklist for problems like this, but in short the big steps are: repair, reinstall, and make sure you are up to date with the latest patches. If none of that works, then start looking deeper. Or just buy a new machine. LOL. JK. LLAP RR115Views0likes0CommentsRe: Adding sub form for a new year
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.91Views0likes0CommentsRe: Adding sub form for a new 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.71Views0likes0CommentsRe: Adding sub form for a new year
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 RR53Views1like0CommentsRe: If Statement on a list box?
The list box's AfterUpdate event, yes. And I'd rename the list box to something like KeyList, and name those text boxes something better as well. So you should end up with something like: If IsNull(Key1) Then Key1 = KeyList Elseif IsNull(Key2) Then Key2 = KeyList Else Key3 = KeyList End If2KViews0likes1CommentRe: If Statement on a list box?
Data2link Your syntax looks OK, provided you have the correct spacing and that's not all just on one line: If X=1 Then doThis Elseif X=2 Then doThis Else doThat End If Also, don't put an = sign in front of that. See these free videos for more help: Intro to VBA AfterUpdate Richard Rost MVP 2014-2015 Access Learning Zone1.9KViews0likes3Comments
Recent Blog Articles
No content to show