Quick Queries #77
By Richard Rost
7 days ago
Avoiding a Common Table Design Mistake (Easy Fix)
In this video, we discuss some of the most common Microsoft Access questions from viewers, focusing on why tracking monthly payments with one field per month is a classic database mistake and how adding a second related table can solve reporting headaches. We also talk about working with tabbed documents vs. overlapping windows, Power Query merge performance in Excel vs. Access joins, locking fields in edit mode, hiding tables with VBA, the continued relevance of Access in 2026, combo box list caching, report footer formatting issues, and more.
Yonatan from (a Developer Student) : Is it possible in Microsoft Access to use overlapping windows by default but have some forms open as tabbed documents at the same time?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, beginner table design mistake, related payments table, tabbed documents vs overlapping windows, navigation form tab control, end of day deposit report, Excel Power Query merge performance, lock fields in edit mode, hide tables VBA, Access in 2026, combo box list caching, report footer gap fix, Debug Compile empty subs, SQL Server for Access users
Intro In this video, we discuss some of the most common Microsoft Access questions from viewers, focusing on why tracking monthly payments with one field per month is a classic database mistake and how adding a second related table can solve reporting headaches. We also talk about working with tabbed documents vs. overlapping windows, Power Query merge performance in Excel vs. Access joins, locking fields in edit mode, hiding tables with VBA, the continued relevance of Access in 2026, combo box list caching, report footer formatting issues, and more. This is part 77.Transcript If you're tracking monthly payments in Microsoft Access in a single table with one field per month, you're making a very common beginner mistake, and that's okay. You're probably used to Excel.
Welcome to another TechHelp Quick Queries brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Today we're going to talk about a classic beginner database mistake and why adding a second table can make your database so much better.
We'll also cover questions from YouTube, my website forums, emails, and more, including tabbed documents versus overlapping windows and if you can use them at the same time, Excel Power Query merge performance, locking fields in edit mode, hiding tables with VBA, whether Access is still worth learning in 2026, combo box list caching, fixing report footer gaps, and a lot more.
Let's get to it.
We're going to start off today with a question from one of my students in the forums, Yonatan. One of my developer students, he says, great question. Is there a way to have Access use overlapping windows by default, but still have some forms open as tabbed documents?
The short answer is no, not really, at least not in the built-in Access sense. Tabbed documents versus overlapping windows is a database-level setting, so it applies to basically all regular forms. You can't have some forms open as tabs while others are overlapping windows at the same time.
Now the workaround is, of course with everything else in Access, you can fake it. You can build your own tabbed interface using a navigation form, a tab control, or my trick where I use a subform object and swap the source object to load different subforms. Then for forms you want floating, if you want to have the default be the tabbed interface, you can then use pop-up or modal forms, and then you get the best of both worlds that way.
You just can't do it one or the other with the Access database settings, but you can finagle it to make it look that way if you want. You can have either all tabs and then use pop-up windows, or you can do overlapping windows and make your own tabbed interface. Great question, though.
Next up, got an email from Alvita. She's building a billing form. Try saying that ten times fast: building a billing form. I can't do it. Building a billing form where each month has its own payment field. She asked if someone pays their bill today, can I run an end of day report that totals up today's deposits?
Let me show you. She sent me a screenshot of what her database looks like. There's her form, and I blacked out the customer's information. I can see here you've got one form and one table in the database. Then you've got the beginning balance, the amount paid, and then you've got separate fields in here for each month.
The way you're set up right now with a separate field for January, February, March, and so on, which is a super common beginner approach, makes reporting harder than it needs to be. Because now if you want a deposit total for today, you need a separate date paid field for each month too.
So, right over here, put a January paid date, February paid date, March paid date, and so on. Then you've got 12 month fields, and then you've got 12 date paid fields. I see this is just a paid or not, which, if this is a text box with the word "paid" in it, you might want to consider changing that to a checkbox, a yes or no value, to indicate whether it's paid or not. It will be easier to query later.
Now also, you're going to have to have an amount paid for each month too: January amount paid, because what if they pay less than what they're supposed to pay? February amount paid, date paid, March amount paid, date paid, and so you see how you get all these fields. Now you've got 36 fields in one record.
That's why this is better off put in a separate related table. It's a little more advanced, but it makes a database much better.
Can you do it this way with what you've got? Absolutely, sure you can. Then you'd make a query over here that just showed all of the payments that came in on today's date using the date parameter.
But the better way to do it is to set up a separate table. It's got the customer ID, the payment date, the amount paid. If you want, you could put what month that applies to; you could put a date like it applies to December, 2026, or whatever. An end of day report is much, much easier.
This approach here, which, like I said, a lot of beginners do, especially if you're coming from Excel, is common. Because you do something like this in Excel, you just make a different column or a different row for every month. Databases, you think a little bit differently though.
This would be like if you had an order form. Let's say you've got the customer, you've got the order date, all the stuff about the order. But then for your line items on there, you had, let's say, 10 rows or 10 fields. That's all the orders you could put on it. So you're limiting yourself that way.
Yes, I know that with months, it's always exactly 12. But what happens when next year rolls around? You've got to make another record for the same customer. Then you're going to have duplicated information.
What I'd recommend for you, first off, is watch my Access Beginner 1 class. It's about four hours long. Take some time, go over all the basics, and learn about how to properly set up your tables and stuff. Then watch my relationships video, where I teach you that thing I talked about with making multiple related tables. That should get you started.
If you have any questions, let me know. Post a comment down below.
Next up, got another email from Sean. He emailed me asking about Excel Power Query, specifically the merge function. He's using it like an SQL join to compare a couple of local files after unpivoting repeating columns. But merge takes forever in Power Query while Access joins run way faster. Yes, Sean, you're not crazy. You're thinking about it the right way.
Power Query's merge is basically a join, but Excel isn't a true relational database. Power Query is awesome for reshaping data like unpivoting all those fields, those columns. But once you start doing big joins and comparisons on large data sets, it can get slow pretty fast.
Access, on the other hand, is literally built for joins. It's optimized for relational operations. It can index your join fields, and it's way more efficient at those SQL-style merges. So, if you're doing lots of compare-merge work, it often makes sense to do the cleanup in Power Query or Excel if you want, and then put the merged data into Access and let Access do what Access does best.
I'm no Power Query expert. I've used it, but I don't do courses in it because I don't use Power Query that much, because I'm an Access guy and I can do all that stuff in Access.
If you're already an Access user, this is exactly the kind of task where Access shines. Import the sources, index the join fields, and then use the queries to compare them.
Next up, over on YouTube, lackcat left a great comment about the classic Northwind database saying it's what sparked a lot of people's imaginations back in the early days of Access. He remembers learning from the manuals and the examples.
Yeah, me too. I loved it. Northwind 2.0, the original, the one that came with Access 2.0, is the first one that I started working with. I don't know if they're calling the new version Northwind 2.0. I think they are. Yes, they're calling the new one Northwind 2.0.
So, I used the one that was Northwind, I guess 1.0, it came with Access 2.0. The old, the old old one that came on floppy disks. I've talked about the new Northwind at length in previous quick queries. It's a good database. I don't personally use it myself. It's great for showing off what Access can do, so it's a nice piece of software.
But lackcat, you're basically right. Back then, those sample databases and those chunky printed manuals were basically how we all learned back in the day. I did the same thing. I tore Northwind apart, reverse-engineered it piece by piece.
So, I'm with you. I appreciate the kind words. The memes will continue.
I did a little search for nostalgia; I was doing a search for the old Northwind database to find some screenshots of it. Ila Dog's has a nice page set up because it is memory lane. There it is. Look at that old thing. This is before my day. This is Access 1.0. This is before I started using it. I think I started in 2.0.
He's got a lot of cool stuff on here. Access 2. This is the one. This is what I started with. Love it. There's Northwind. I ripped this thing apart. Studied every bit of it. 95. It's a great page if you want some nostalgia for those of you who have been using it for a while. I'll put the link down below. Thanks to Colin at Ila Dog's for maintaining this page. This is really awesome.
Next up, we got Spence. Spence commented on my RU Sure function and showed a shorter version that just returns true or false directly based on whether the user clicks yes, plus they added a beep. Got to love the beep. I put beeps in everything. That's a great suggestion.
The compact version is totally valid. I'll be honest, though, personally I like to write code that's a little more spelled out just because it's easier to read and maintain later, especially for teaching. The computer doesn't really care either way, performance-wise. It's basically the same. The difference is microscopic. But future me cares when I'm debugging it six months from now. So I like readable code.
But I love seeing clean alternatives like this. It's like using a full if-then-else-end if block versus trying to cram it all together on one line, even though you can. Sometimes I like the more readable version. It's just easier to maintain. This is awesome work.
Next up, we got Danny. She says she's new to Access and built a workflow for sales reps. She wants the forms to feel like they tab to the next form. She also wants to carry data from one form to the next, like copying an address over so she can calculate mileage.
A couple of quick points. First, you can't literally tab from one form to a totally different form like that in one big continuous page. But you can make it feel that way by putting a Next button on the form that opens the next form for data entry and even closes the previous one. The easiest way to do that is with the Command Button Wizard. Or, if you don't mind a little VBA programming, you can use DoCmd.OpenForm.
Second, if you want to feed values from form A to form B, there are a couple of options. The classic approach is to open the next form and pass the info into it, and then have the new form read it and fill in the fields. The key skill here is learning how to reference form values cleanly: Forms!FormName!FieldName.
Or you can pass arguments when you open the form; that's a different way and it's a little more complicated.
If you want to calculate mileage between two addresses, Access doesn't do that itself, but there are lots of external map services you can use and you can absolutely structure the workflow to capture the addresses so you're ready for that step.
Here are some videos that might help you out. Command Buttons will teach you how to make that Next button that opens another form. This video will show you how to get the value from another form that's open, so you can open form two and then read the values from form one. It basically looks like this: Forms!FormName!FieldName.
Start with those two videos and if you still need help, post another comment or contact me and we'll figure it out.
Next up, Tommy says he's tried my lock fields by tag code in my edit mode video, but it hangs and throws a runtime error 438 on the Control.Locked line. 438 is, I think, "object doesn't support that property." In this case, it means that your loop is hitting a control that can't be locked. A lot of times, it works fine in the database exactly as I built it in class, but then you try running it on your own database and it doesn't work. You might have a control on your form that cannot be locked.
Maybe you've assigned a tag to something like a label. If you put that lock me tag in a label, it can't lock that control. That's the problem. So check all your controls, remove them one at a time, and that will tell you which one is the bad one. Labels don't have a Locked property. If you tag a label as lock me, Access is going to choke right there.
Make sure only text boxes and other lockable inputs have the lock me tag, or check so you only set Locked on control types that support it. That's a little more work, though. Or throw a tiny bit of error handling around that one line. Just put an On Error Resume Next and it will just ignore it if it can't do it. Good question though.
Next up, we got Habiblah. I believe I have pronounced that correctly. He said he hides tables via VBA so they can't be seen, and they also can't be accessed from another file unless they're unhidden with VBA.
You can definitely hide tables in Access and VBA can do that too, but just to clarify, hiding tables doesn't really secure them. If someone has access to the database file, they can still link to hidden tables from another Access file or turn on hidden objects and view them. Hiding is great for keeping things tidy or keeping the looky-loos out so they can't poke around and find stuff, but it's not real protection.
If you've got a method where the tables are still usable but they're not truly accessible or linkable unless they're unhidden, I'd honestly love to hear how you're doing that, because that's where SQL Server comes in. You can't secure tables in a database in Access by itself and still allow users to work with the data in those tables. I'm not aware of any way to do that, so please educate me. I'd love to hear about it.
All right, here we go again. Pancage asked if learning Microsoft Access in 2026 is still useful because he keeps hearing that Access is old and obsolete. I get this every couple of weeks. Yes, absolutely, it's still useful in 2026. I'll tell you why: Access is still the best tool on Earth for quickly building a desktop database application, especially for small businesses or for internal tools for bigger companies. The rapid development is just unparalleled.
People who say it's obsolete usually mean because it's not a modern web application platform or you can't build phone apps for it. That part is true. Access isn't designed to run your database in a browser or on your Android or your iPhone. That's not what it's meant for.
But here's the magic combo: Access is the front end and SQL Server is the backend. That gives you the speed and productivity of Access with true enterprise-grade database power underneath. Once your data is in SQL Server, you can connect it to anything: websites, Power Apps, mobile apps, whatever. But you still use Access on your desktop PC or on your laptop.
So no, Access isn't dead. It's still actively supported. It's still an incredibly practical tool when used the right way. We still have conferences every year. We still meet with the team at Microsoft during the MVP Summit, and we talk with the guys that are building Access and supporting it and fixing the bugs and all that stuff. So it's still actively being worked on.
Is it as flashy as some of these new tools that are out? No, but it will run your company reliably for decades and not complain. I've had clients that I built databases for in the late 90s or 2000s that are still using some variation of that original Access database that I built way back when.
Here's my original video. I recorded the first version of it in 2023 and I haven't updated the video since. I've made some notes on the page every year, but it's probably due for a re-record soon when I get some free time. But everything I said in this original video is still perfectly valid. It was valid in 2023, 24, 25, and now in 2026. There's no plans to retire Access. We're still working on it. It's still a great program.
That's where I stand, not just because I teach it, but because I love it and I still use it myself every day, and lots and lots of people do. There we go. I updated it.
Next up, we've got a question that reminds me of Mxyzptlk. As he says, they have a ton of combo boxes pulling the same list of values and they're wondering if it's possible to query that list once and reuse it across all those combos and whether that would be faster or slower.
Great question. I get this a lot, and it depends on where the data lives. If everything's local, reusing the same resource isn't a big deal. But if those combo boxes are pulling lists across the network or from a backend file, then you can absolutely optimize it.
What I would do is cache the list locally (or "cache" depending on how you pronounce it). When the database opens, or when the user logs in, or when they open the form, however often you need to refresh that data, you pull those lookup values down once into a local temp table. Then all your combo boxes point at the local temp table instead of hammering the network repeatedly. You can refresh it on a schedule, once a day or whenever, or have the user manually click a refresh button. It just runs a query, pulls all those records down, and there you go.
So yes, you can share that data, and when the source is remote, caching it locally can make the whole app feel snappier. Then you take that, and with some of the other tricks I've shown you, like the smart combo boxes, you can really make your database run faster.
If you use SQL Server, you can just query the server for only the data that you need instead of all the customers. Limit it somehow, for example, give me just the list of customers that are active or have purchased something in the last year or whatever. So yes, great question.
Next up, we got Thushara. It's tough with screen names to guess what to call people. Like if I see "Mike," yeah, okay, but I don't know. Anyway, they're asking about Access reports. When a report spills onto a second page, they get a big ugly blank gap between the detail section and the page footer, and they want the footer to move up so page two looks clean.
I know exactly what you're talking about, and unfortunately, that's mostly how Access reports work. The page footer is locked to the bottom of the page, so if your detail section doesn't fill the whole page, Access doesn't automatically pull the footer up to sit right under your last record. The empty space is basically just unused page space, and the footer goes on the bottom.
There are a couple of workarounds, but the easiest one is don't use a page footer for that content. If it's something that you want right after the data, put it in the report footer. Yes, the report footer shows up above the last page footer. It's weird, but that's how it works. You've got the report header, then the report footer, and then there are a whole bunch of different parts. I cover these in my different videos. If it's only supposed to appear on the last page, the report footer is usually the right place to put it. If it has to truly appear on every page, then it's got to go in the page footer and you're stuck with it being at the bottom.
There are VBA tricks, programming tricks you could do to move it, but messing around with reports in VBA can be kind of tricky. Also make sure the Can Grow and Can Shrink are set properly for the sections as well as the controls themselves, so they're not artificially reserving space. Because if you make a big detail section that's bigger than the data needs to be and its Can Shrink property is set to false, it's going to take up all that space whether you want it to or not. There's a video on Can Grow and Can Shrink if you're not familiar with that one.
Finally tonight, we got rtat1. We're going to file this one under I love it when you guys teach me something new. He says that I don't have to actually delete the empty default event procedures like Form_Load because if there is no code in them, then doing Debug Compile will automatically remove those empty subs.
Wait, what? I've been using Access for 30 years and I never knew this. I have to try this. This reminds me of those videos, oh yeah, you tell me I'm 40 years old and it took me 40 years to figure out what? Anyway, let's see here.
Okay, Design View. Give me an OnClick event. I'm in here. I'm going to leave it empty and then Debug Compile once in a while. Oh, wow, when did they add that? That is super cool. I can distinctly remember some of my old databases going back to like, I don't know, 1997, 2000, whatever. I would go through literally and clean up all of those empty subs. They must have added that. My mind is exploding now.
I love it when you guys teach me these little tricks because the Access team, they sneak little things in there that I don't always catch. That's amazing. But you're right, and if only you could think of a good phrase that rhymed with Debug Compile once in a while, I would put it on a t-shirt. Or a mouse pad. Can you see that one? I don't know if you can see that one. Anyway, he adds on there, yeah, thanks. That's really cool.
Get on my mailing list, subscribe, like and all that stuff. It helps me out. It helps the channel out. Get me up to five million subscribers by the time I'm 365 years old. What am I at? A quarter million now? I did the math. I'm going to be like 80 some years old by the time I hit a million subscribers, but that's okay. Slow and steady wins the race.
We got Access Day coming up if you're in the Redmond, Washington area or can make the trip. It's Friday, March 27, 2026. Check it out. Registration is now open. I'm registered. I'm just going as an attendee, not speaking.
They just posted a list of some of the speakers. Who's on here? Let's see. We got Mike Wolfe. Mike Wolfe was supposed to be at the one last year. I was supposed to meet him and he didn't show up, so I didn't get to meet him. But I'm looking forward to meeting Mike. He does the Weekend Review every week for Access stuff. It's really cool.
We got Rochelle Long. I've never met her. That looks interesting too. We got these guys. There's Juan Soto, going to be there. Cool. Juan was supposed to be there last year too. I didn't get to meet Juan. Looking forward to that. The Access team was there last year. They went over some of the stuff that they were working on for Access. Looking forward to it.
Stop by my website. Check out what's new, what's coming up. I did just make an announcement that my SQL Server for Access Users course is about ready to start coming out. That's going to be coming out pretty soon. Don't forget to check out my Captain's Log where I post thoughts about whatever's on my brain noodle. Get a copy of my book. It's on Amazon. Check it out. Visit the merch store for your t-shirts and all that good stuff.
That wraps up today's Quick Queries. The big takeaway today is avoid that common beginner table mistake of putting one field per month and instead use a proper related payments table, so things like end of day deposit reports become easy.
We also talked about how to work around tabbed documents versus overlapping windows by faking tabs with navigation forms or subforms, or using a pop-up form. And how to fix that runtime 438 error in edit mode by making sure you're only trying to lock controls that actually support the Locked property.
Don't forget, post a comment down below. Let me know how you liked today's video and how you plan to use any of this stuff in your own database.
That's your Quick Queries video brought to you by accesslearningzone.com. I hope you learned something.
Live long and prosper, my friends. I'll see you next time and enjoy your weekend.Quiz Q1. What is a common beginner mistake when tracking monthly payments in Microsoft Access?
A. Creating a separate table for each month
B. Using one field per month in a single table
C. Creating a form with no tables attached
D. Saving all payment data in a single text field
Q2. Why is having one field per month in a single Access table problematic?
A. It makes it easier to run queries
B. It requires less storage space
C. It makes reporting and querying more difficult
D. It automatically updates payment dates
Q3. What is the recommended approach for tracking individual monthly payments in Access?
A. Add more fields for each new month
B. Store all payments in a memo field
C. Use a related payments table with fields for customer ID, payment date, and amount paid
D. Track payments only in Excel and import once a year
Q4. What Access form/window behavior can be set as a database-level setting but not mixed within the same database?
A. Overlapping windows only
B. Tabbed documents only
C. Mixing tabbed documents and overlapping windows for individual forms using built-in settings
D. Using pop-up windows exclusively
Q5. How can you "fake" a mixed tabbed and overlapping windows experience in Access?
A. By changing database settings for each form dynamically
B. By using navigation forms, tab controls, subform objects, and pop-up or modal forms as needed
C. By running an external script for each form
D. By switching to Excel for some forms
Q6. If you have a payment table with separate fields for each month, what might you have to add to track dates and amounts accurately?
A. Only a single date field for the year
B. No additional fields are needed
C. Separate fields for each month's paid date and amount paid
D. One memo field for notes
Q7. What benefit does moving payment history to a separate related table provide?
A. It makes it impossible to generate reports
B. It complicates the database unnecessarily
C. It simplifies queries, reporting, and avoids duplicated data
D. It uses more storage space
Q8. Why does Excel Power Query perform slower at merging large datasets compared to Access?
A. Power Query lacks support for SQL joins
B. Access is a relational database optimized for joins and indexing
C. Excel cannot work with large files
D. Power Query only supports text data
Q9. What is a suggested workflow for heavy data merging tasks between Excel and Access?
A. Do everything in Power Query
B. Use Excel formulas exclusively
C. Clean data in Excel, then merge and compare using Access
D. Only use Excel for data storage
Q10. What lesson was learned about hiding tables using VBA in Access?
A. Hiding tables is a security feature that fully protects data
B. Hiding tables only keeps them out of sight, but does not provide real security
C. Hidden tables cannot be accessed by any user through any means
D. Hiding tables encrypts them
Q11. In Access, what property error may occur if you try to lock all controls on a form?
A. Property not found error for textboxes
B. Runtime error 438 if the control (such as a label) does not support the Locked property
C. Access runtime crashes immediately
D. The Locked property affects all controls equally
Q12. What is the recommended solution for error 438 when locking controls by tag?
A. Only tag lockable controls, like text boxes
B. Apply error handling with On Error Resume Next
C. Both A and B
D. Ignore the error and proceed
Q13. Is learning Access in 2026 still considered useful according to the video?
A. No, Access is completely obsolete
B. Only for hobbyists
C. Yes, especially for building desktop database applications and as a front end to SQL Server
D. Only if you plan to work for Microsoft
Q14. What approach is recommended for combo boxes that pull the same list across multiple forms, especially if the list comes from a remote source?
A. Query the network or back end every time
B. Hard-code the combo box values
C. Cache the list locally in a temp table for faster access
D. Use drop-down macros
Q15. When placing content that should appear right after the data, which report section is usually best in Access?
A. Page Footer
B. Detail Section
C. Report Footer
D. Page Header
Q16. What advantage does the Report Footer provide over the Page Footer in Access reports?
A. It shows up only on the first page
B. It appears above the last page footer, right after the detail section on the last page
C. It is always at the top of every page
D. It allows merging of multiple reports
Q17. What tool can help avoid unnecessarily reserving space in Access report sections?
A. Disabling the section
B. Setting Can Grow and Can Shrink properties properly
C. Increasing the font size
D. Using only page footers
Q18. What useful new behavior in Access VBA for empty event procedures was discussed?
A. Empty event subs are kept indefinitely
B. Empty event procedures are automatically deleted upon Debug Compile
C. Access hides empty event subs unless you enable them
D. They cause compile errors
Q19. What is one reason the instructor prefers more spelled-out VBA code instead of compact one-line return statements?
A. Compact code performs better
B. Compact code looks more professional
C. Spelled-out code is easier to read and maintain, especially for teaching and debugging
D. Access requires spelled-out code
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-C; 8-B; 9-C; 10-B; 11-B; 12-C; 13-C; 14-C; 15-C; 16-B; 17-B; 18-B; 19-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I want to address a classic mistake many people make when moving from Excel to Microsoft Access. If you are keeping track of monthly payments using a table that stores a separate field for each month, you are not alone. This is a very common approach for beginners, but it does create a lot of issues when it comes to reporting and managing your data later on.
I also want to answer some questions that have come in from YouTube, my forums, emails, and more. We will talk about topics like tabbed documents versus overlapping windows in Access, Excel Power Query merge performance compared to Access, locking fields by tag in edit mode, hiding tables using VBA, whether learning Access is still worthwhile in 2026, combo box list caching, fixing report footer gaps, and several other useful tips.
Let's get started with a question from the forums about Access window management. A student wanted to know if you can set Access to use overlapping windows by default, but have some forms open as tabbed documents. The short answer is that you cannot mix tabs and overlapping windows using Access's built-in settings. The entire database has to use one or the other. However, you can simulate a tabbed interface using tools like navigation forms or tab controls. My favorite trick is using a subform object and swapping out the source object, which gives you tab-like navigation. If you want floating windows, use pop-up or modal forms. While you can't mix these settings directly, you can build an interface that gives you the flexibility you need by combining these techniques.
Next, I received an email about building a billing system with one payment field per month. For example, you might have fields like JanuaryPaid, FebruaryPaid, MarchPaid, and so on, with a separate field to indicate whether that month is paid. While this setup works in Excel, it creates a number of challenges in Access. If you wanted to run an end-of-day deposit report, you would need twelve additional fields for the date each month's payment was made, plus possibly twelve more to store the actual amount paid if the payment is variable. This can quickly balloon to 36 or more fields in a single table, making your database unwieldy and hard to work with.
A much better approach is to create a related table where each payment is a separate record. This table would include fields like CustomerID, PaymentDate, and AmountPaid. You could also add a field to indicate which month the payment applies to. With this structure, generating reports or querying for payments made on a specific day becomes much simpler. Switching from a flat-table approach to a properly related table takes a little extra work at first, but it saves significant time and headaches down the road, especially when your reporting needs become more complex.
If you are still new to database design, I strongly suggest watching my Access Beginner 1 course to get a solid foundation in tables and relationships. There are additional videos on my site that walk you through building related tables and setting up proper relationships as well.
Moving on, I got a question about Excel Power Query's merge function. If you are familiar with SQL joins, you may notice that Power Query merge can be slow, especially after unpivoting large source files. Access, on the other hand, is built for efficient joins and is highly optimized for relational data. When you have large datasets and need to compare or merge records, Access often does so much faster than Excel Power Query, particularly if your join fields are indexed. If you need to do heavy lifting with large datasets, use Power Query to reshape your data if necessary, then load it into Access to perform the joins and merges.
On a nostalgic note, there was a mention of the Northwind database - the original example database that shipped with early versions of Access. Many people, myself included, learned a lot by dissecting that sample database. It was a great introduction to the kinds of things Access can do. For those interested in a little Access history, I found a web page with screenshots of the early Northwind versions for a fun walk down memory lane. If you want to see how Access used to look, check out the link in the video description.
Another student wrote in with feedback about my RU Sure function, providing a shorter version with a direct True/False return and a beep for confirmation. While compact code is perfectly valid, I tend to write things out in a more expanded way for clarity and easier maintenance, especially when teaching. Both approaches are good - the key is using what works best for you in your particular situation.
For those building multi-step workflows, a common question is how to move from one form to another and carry data along between them. You cannot literally tab from one form directly to another in one continuous sequence, but you can put a Next button on each form that opens the next one in line, optionally closing the current one. The Command Button Wizard makes this easy. If you need to pass values, for example, copying an address to calculate mileage, you can use references like Forms!FormName!FieldName to pull data from one form to the next. I have videos that dive into these techniques, and I recommend starting with those for smoother workflow design.
A troubleshooting topic came up regarding my Lock Fields by Tag code. If your code fails with a runtime error 438, it is likely because you have tagged a control that does not support the Locked property, such as a label. In that case, Access can't lock it and throws an error. The fix is to make sure you only tag controls that can actually be locked, like text boxes or combo boxes. Alternatively, you can add a little error handling to skip controls that do not support the Locked property.
Next, some people use VBA to hide tables in Access, thinking this will secure them from outside access. Hiding tables with VBA is an effective way to keep your database interface tidy, but it does not provide real security. Anyone with access to the database file can view hidden tables by changing settings or even link to them from another Access database. Real security, especially for sensitive data, requires using SQL Server as your backend, which allows you to control precise user permissions.
A perennial question is whether Microsoft Access is still worth learning as we move into the future, such as 2026. The answer is absolutely yes. Despite what you may hear, Access remains the best tool for building desktop database applications quickly and efficiently, especially for small businesses or internal use. Access may not be designed for web or mobile apps, but paired with SQL Server as the backend, you have a very powerful setup that can be connected to modern systems as needed. Access is still actively developed and supported, and many applications built decades ago are still running today. Everything I discussed in my original "Is Access Dead?" video still stands today.
A technical question came in about combo box lists. If you have many combo boxes sourcing from the same set of values, is it possible to query that list once and reuse it? Yes, especially if your source data is remote or on a slow network. The best way is to cache the lookup values in a local temporary table when needed, and then point all your combo boxes at this table. This avoids repeated network requests and speeds up your forms. You can refresh the cache on a regular schedule or whenever the data changes.
For reports, someone asked about the empty space that can appear between your detail section and the page footer when a report spills onto additional pages. Access anchors the page footer at the bottom, even if there is a large gap above it. If you want information to appear directly after your data, use the report footer instead of the page footer. The report footer appears after the last record and before the final page footer at the bottom. Make sure to check the Can Grow and Can Shrink settings for your controls and sections, so that space is not reserved unnecessarily.
A tip from a viewer taught me something new: if you leave empty event subs in your code, running Debug Compile will automatically remove those empty subs for you. I never realized this happened, so I tried it out myself and it works. It's a small timesaver that helps keep your code tidy.
On a final note, Access Day is coming up in Redmond, Washington. I will be attending and hope to meet many of you there. Be sure to visit my website for updates on courses, including my upcoming SQL Server for Access Users class and regular thoughts in my Captain's Log. I also have a book available and some merchandise if you are interested.
To summarize: avoid the trap of designing tables with one field per month. Use a properly related payments table instead for easier reporting and better database design. We also discussed how to navigate between tabbed and overlapping windows, tips for troubleshooting errors, efficient combo box list handling, and other helpful advice for Access users at any level.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Tabbed documents versus overlapping windows in Access
Faking tabbed interfaces with navigation forms or subforms
Using pop-up or modal forms for floating windows
Problems with tracking monthly payments in one table
Drawbacks of using one field per month for payments
Benefits of using a related payments table
How to structure a payments table with customer ID, date, and amount
Querying payments for specific dates with proper table design
Converting text "paid" fields to yes/no checkboxes
Difference in merge performance: Excel Power Query vs Access
When to use Power Query versus Access for large joins
Importing sources and indexing join fields in Access
Referencing values between open forms
Passing data from one form to another in Access
Using DoCmd.OpenForm to launch and pass data between forms
Fixing runtime error 438 when locking controls in edit mode
Ensuring only lockable controls use the Lock property
Error handling for locking controls via VBA
Hiding tables in Access with VBA and its security limits
Caching combo box lookup lists locally for performance
Refreshing temp tables for shared combo box sources
Fixing report footer gaps in Access reports
Using report footer versus page footer for content placement
Setting Can Grow and Can Shrink on report sections
Automatic removal of empty VBA procedures using Debug CompileArticle If you are using Microsoft Access to track monthly payments and have designed your table with a separate field for each month, you are making a common beginner mistake. This usually happens because many of us start out using Excel, where it is typical to represent months as separate columns. However, in Access - because it is a relational database - this structure creates problems for reporting and ongoing data management.
Here is why the single-table, one-field-per-month approach is problematic: Imagine your table has fields like JanuaryPaid, FebruaryPaid, MarchPaid, and so on, possibly with corresponding fields for the date of payment and the amount paid for each month. If a customer can pay a different amount each month, you then need fields like JanuaryAmountPaid, JanuaryDatePaid, FebruaryAmountPaid, FebruaryDatePaid, and so forth. With just a year's worth of monthly payments, you quickly end up with dozens of fields for just one record. When a new year comes, you either add even more fields or start duplicating the customer information in a new record, which is very inefficient.
If you want to run an end-of-day report showing all deposits made today, this flat table design means you need to check every date-paid field across all months to see if any match today's date, then sum the corresponding amount paid for each. This is cumbersome to query and maintain, especially as your data grows.
The correct way to design this in Access is to use two tables with a relationship between them. The first table holds your core customer data, and the second table records each payment as a separate record. Each row in the payments table would have a CustomerID, PaymentDate, AmountPaid, and (optionally) a Period or Month field if you need to categorize payments by month. Now, running a report to total today's deposits is simply a matter of querying the payments table for records where PaymentDate equals today's date and summing the AmountPaid. This is what is known as a "one-to-many" relationship - one customer can have many payments.
For those transitioning from Excel, this design may feel unfamiliar, but it is foundational to good database practice. Here is an example of how you might define your two tables:
Table: Customers
- CustomerID (Primary Key)
- CustomerName
- Any other static customer information
Table: Payments
- PaymentID (Primary Key)
- CustomerID (Foreign Key, links to Customers table)
- PaymentDate
- AmountPaid
- ForMonth (optional - could be a Date, or a short text value like "2026-01")
To set up your database this way in Access, you should start by creating both tables, defining the CustomerID field in Payments as a foreign key to Customers, and then using forms for data entry that will automatically associate each payment with the correct customer. When you need a report - like seeing all payments received today - you can build a simple query on the Payments table filtering by PaymentDate.
Here is a very basic example of how a query might look in SQL for today's payments:
SELECT Payments.CustomerID, Payments.PaymentDate, Payments.AmountPaid
FROM Payments
WHERE Payments.PaymentDate = Date();
And if you want the total deposits for today:
SELECT Sum(Payments.AmountPaid) AS TotalDeposits
FROM Payments
WHERE Payments.PaymentDate = Date();
Adopting this approach avoids the complexity and maintenance headaches of having many similar fields in one table. Instead, you have a flexible structure that can easily handle new months, multiple payments per month, partial payments, or future changes like weekly or biweekly payments.
If you are just starting out with Access, I recommend investing some time in learning about table relationships and the basics of normalization (the process of structuring your tables to reduce redundancy and improve consistency). This might involve taking a beginner course or following a tutorial on relationships - this one skill will make a big difference in how effectively you can use Access.
A related question that comes up in Access is the difference between tabbed documents and overlapping windows. Access offers two modes for opening forms: either as separate, overlapping windows or as tabbed documents within a single main window. This is a global setting and you cannot have some forms using tabs and others overlapping in the same session. However, you can mimic the effect of tabbed navigation by using navigation forms, tab controls, or by embedding forms as subforms and switching the subform source object. For forms that need to appear as pop-up or dialog boxes, you can use the PopUp or Modal properties. While you can't truly mix Access's built-in tabs and overlapped windows at the same time, you can design a user interface that provides the best of both worlds.
Some users run into issues when trying to programmatically lock fields for editing using VBA. You may have VBA code that loops through controls and sets their Locked property based on a tag value:
For Each ctrl In Me.Controls
If ctrl.Tag = "lockme" Then
ctrl.Locked = True
End If
Next ctrl
If you get a runtime error 438 ("object doesn't support this property or method"), it usually means that your code is attempting to set the Locked property on a control type that doesn't actually support it - such as a label. Only controls like text boxes, combo boxes, and similar inputs can be locked. Double-check your tags so only lockable controls are included, or enhance your loop to check the type of control before trying to lock it. Alternatively, you could add error handling to skip incompatible controls:
For Each ctrl In Me.Controls
On Error Resume Next
If ctrl.Tag = "lockme" Then
ctrl.Locked = True
End If
On Error GoTo 0
Next ctrl
This ensures the code continues even if it runs into a label or other non-lockable item.
For those working with many combo boxes that are all pulling the same values (perhaps from a lookup table on a network), performance can be improved by caching the data in a local temp table. When the database opens, or when you load the form, you retrieve the list once and store it locally. All your combo boxes then refer to this local data. This avoids multiple network calls and makes the interface snappier. You can refresh this cache as needed - daily, on demand, or at login.
Another question that comes up often is about Access's future. Despite rumors, Microsoft Access is still actively developed and maintained. It is not obsolete, and it remains the fastest, most cost-effective tool for building desktop database applications, especially for small businesses and internal company tools. The key is to use Access as a front end and connect it to a robust backend like SQL Server when you need to scale.
Report design in Access can be frustrating, especially when you want a section (like a custom footer) to appear directly below your data on shorter pages. The Page Footer section is always printed at the bottom of each page, so if your report detail does not fill the page, you get an ugly blank space. The workaround is to use the Report Footer for things that should appear immediately after the last record, but be aware that the Report Footer only appears at the end of the entire report (not on every page). Adjust your section heights and use the Can Grow and Can Shrink properties on both the detail section and controls to help minimize white space.
A neat tip from long-time Access users: if you create an empty event procedure (like a blank Form_Load sub) and later remove all code from it, Access (in recent versions) will automatically remove the empty sub when you perform Debug > Compile. This feature helps keep your code cleaner without needing to manually delete empty event handlers.
For those looking to further their skills, look for resources covering Access table design, relationships, forms, query optimization, and report formatting. There are many excellent classes and tutorials out there to help you move beyond beginner pitfalls.
The key takeaway is to avoid placing repetitive data in multiple fields within the same table. Instead, use properly related tables to open up the full reporting and automation power of Access, save yourself a lot of future headaches, and make your applications scalable well into the future. Use design best practices right from the start, and you will find Access to be a powerful, efficient tool for your business needs. |
