Quick Queries #107
By Richard Rost
3 days ago
Do You Still Need Learn Access If AI Can Build It? In today's Quick Queries we will discuss why learning Access fundamentals still matters when using AI, along with troubleshooting form tab behavior, requeries for subform combo boxes, conditional report labels, date and time storage, flexible CSV imports, continuous forms, trusted locations, database distribution, TempVars, and newer Microsoft Access features. LinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, AI database design, AI generated VBA code, AI code validation, database normalization, learning Access with AI, Access form tab cycle, requery combo box subform, report Format event, CSV header mapping, ACCDE expiration date, TempVars multi-user
More InformationTranscript If AI can build your database, write your formulas, and generate your code, why spend time learning any of it?
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
As you can tell from the picture, I use AI heavily. I think it's a fantastic tool. But letting it do the work raises a different question. How do you know it did the right work?
To look at some quick reasons, the underlying skills still matter. Then we've got more questions and comments from YouTube, my website forums, email, and lots of other places.
All right, let's get to it.
Today's first question comes from Melissa in Boise, Idaho, one of my Gold members. Melissa says, "I've been learning Access to build a database for my business, but now AI can write queries and VBA code for me. It even tells me how to design my tables. If I can just ask AI for what I need, is it still worth spending time learning Access myself?"
Well, Melissa, of course, I still think it's important to learn the fundamentals, at least on your own. AI is a fantastic tool. I use it all day myself. But there are a lot of things you want to make sure that you understand completely before having AI just do everything for you.
So let's take a look at some reasons why.
Now, the first thing to understand is that AI can give you an answer that looks perfectly reasonable and is still wrong. And I'm not talking about something obvious, like our little robot here telling you that 2 plus 2 is 5. You're going to catch that one, hopefully.
I'm talking about a query that runs, brings back records, and leaves out some of the customers you needed. Nothing crashes. There's no error message. You just get the wrong list.
Or it gives you code that belongs in a different form event. It works when you test it one way, but not when your employees use it another way.
And if you already understand a little Access, you can look at that and say, "Hold on. That doesn't look right." And if you don't, you may not even know there's a question you should be asking.
So that's where learning the basics still pays off.
And by basics, I don't mean memorizing every function in Access. I don't expect you to sit there with flashcards learning every argument to every VBA command. You can look that stuff up. I do. AI can help you look it up.
But what I do want you to understand is how all the pieces fit together. You've got customers in one table, orders in another, and a relationship between them. Why do we do that? What does that CustomerID actually mean?
Because if AI hands you one giant table with the customer addresses repeated on every order, you want to be able to stop and question that design. You don't have to build the whole thing from memory, but you need enough understanding to recognize what you're being given.
And that also makes a big difference in what you ask for in the first place.
And yes, sometimes you have to know enough to know that information shouldn't repeat on every record and every table. Then sometimes you do need to know that it should, like the customer address example. You want to store customer addresses in the order table if you care where that order was shipped.
So it's knowing stuff like that that helps you build a better database.
Now, suppose you tell the AI, "Okay, build me an inventory database." Okay, inventory for what? Are you keeping track of office supplies in the closet? Have you got three warehouses shipping orders all day? Those are very different databases.
And what happens when somebody returns something? Does it go straight back into stock? Maybe it needs to be inspected first. What about items you've promised to a customer that haven't shipped yet? Are those still available for somebody else to buy?
AI can't read your mind. So if you don't tell it about those things, it has to leave them out or make assumptions. And the last thing you want is AI making assumptions.
The more you understand your database, the more you understand your data and your workflow, the better you can explain to AI what you need.
Now we've given it a much better question, but we still have to check the answer.
So let's stick with that inventory example. You got 10 widgets on the shelf and you sell three. You should have seven left. Start there. Give it a little test when you already know the answer.
Then try returning one. Try an order with a missing quantity. If two people are going to use this database, what happens when they both try to sell the last widget? That's the kind of stuff you want to find out before your customers do.
And this applies to other systems too. It applies to Excel or SQL Server. A formula giving you a number doesn't mean the number is what you wanted or that it's correct. You need to know what you're checking. Otherwise, you're asking the same AI that wrote it to assure that it did a wonderful job.
I'd rather see seven widgets on the shelf and know that there's seven widgets in the database.
Now, suppose the database passes your test. You've got a working database. That's great. But next month, somebody wants a different report, or you start using another warehouse, or a supplier changes the spreadsheet they send you. Now somebody has to make a change without breaking the parts that already work.
And this ties right into our last Quick Queries, where we talked about what happens when the person who built your Access database leaves. Whether that person wrote all the code or had AI help, you still need to know where the data is, how the important pieces work, and how to restore a backup.
You can have AI help you document it. It's really good at that. But saving the original prompt isn't the same as having a plan for maintaining the database, especially when something breaks.
When something does break, it's tempting to copy the error into AI and paste back whatever it gives you. And sometimes that works. Sometimes it fixes that error and creates two more.
Now you're going back and forth, changing things. Eventually, you don't remember what the code was supposed to do in the first place.
So slow down a little. Read the error. Find out which part is failing. Ask it to explain the change before you apply it.
If it says to remove a condition from a query, why was that condition there in the first place? Maybe that was the thing keeping canceled orders out of your sales totals.
AI can be a great debugging assistant. Sometimes even I'll do that. I'll give it a whole page full of code, and I'll be like, "It's 3 o'clock in the morning. Do you see the obvious syntax error? I can't figure out what's doing it." And it'll say, "Oh, you've got your thing backwards here."
Logic errors are the worst. I just did one the other day. It was late. I wasn't thinking. I had 100% of capacity, and I had a greater than this flipped that was less than. And I'm like, "Oh, that's why the backup is not running at 4 a.m. instead of it is running at 4 a.m."
AI caught that because I was late and I was tired. It's a great debugging assistant. But somebody still needs to understand what the correct result looks like. And that comes from knowing your business and knowing Access.
And as far as knowing your business goes, you've got to know those business rules too. When is an order complete? When you ship it? When the customer pays? When everything on the order has shipped? Even if it went out in three separate boxes?
Access doesn't decide that for you. And AI shouldn't be guessing. That's your business rule. You've got to explain it.
And this is also something I talked about in the last video too. The person in the office who knows how the business works brings something really valuable to the project. And learning some Access gives that person a way to turn what they know into tables and queries and forms and all that stuff.
AI can help with the building, but you still have to have someone who knows how to tell it what you're building and why.
And now you might be thinking, "Okay, but what if the tools change again? Am I going to spend all this time learning Access and then have to start over?"
Well, you might have to learn a different tool, but you're not starting from nothing. If you understand why customers and orders belong in related tables, that still helps you when you move your data to, let's say, SQL Server.
And if you understand how a loop works in VBA, learning a loop in another language is a lot easier. The spelling changes. Some of the rules change. But if you've already learned how to break a problem into pieces and work through it, that's useful no matter what you're using: Access, Excel, SQL Server, or if you're explaining the job to AI.
If you can think like a programmer, you'll get better results. So the time you spend learning this stuff can make those other tools easier to use.
And that's really where I want you to use AI. Let it take care of the tedious stuff so you can spend more time thinking about what the database needs to do.
I mentioned this back in Quick Queries 104. If I already know how a recordset loop works, I can ask AI to draft one for me instead of typing the same basic code over and over again. Then I can read it, make whatever changes I need, and test it.
And that's a useful time-saver. I use that all the time myself. I'll tell the AI, "Hey, I need a loop that does this and this. Write it for me." Then I'll read through it and make sure it's what I want.
It's like a calculator. I don't need to do long division on paper every time, but I still know how to do it if I need it. In case I'm out of batteries, I can still do it if I need to.
And more importantly, I still need to know whether I'm supposed to divide those numbers in the first place. The calculator doesn't know what problem I'm trying to solve.
If AI needs more direction, then people sometimes expect it to. And that brings us back to our pilot. You've got autopilot. Wonderful. Let it help fly the plane. But somebody still has to choose where you're going, watch the instruments, and know when to take over.
I wouldn't hand the whole job over to the inflatable guy and go take a nap.
So, coming back to the question we started with, yes, I think learning Access is still worth your time. And not just because I teach it and I'm trying to sell lessons.
You don't have to memorize every line of code. You don't have to do every bit of the work yourself. Use AI to help. But keep learning enough to understand what it's giving you and whether it does what you need.
And treat AI like a junior assistant, not a guru. It doesn't have all the answers. You still need to know what you're doing. Have it do tasks that are mundane or routine and keep the thinking part to yourself.
And the better you understand the subject, the more useful that assistant becomes. And that's true whether you're working with Access, Excel, VBA, SQL, Python, whatever.
All right. So I hope that answers your question. Let's see what else we got in the mailbag today.
All right. Next up, over to my forums. We've got some good troubleshooting questions in here. Some from Lee Shastid in the forums.
Lee says he's got a form where he tabs from field to field. But when he gets to the last field and he hits Tab, Access jumps back to the first record instead of moving to a new one.
Now, normally, the first thing I check is the form's Cycle property. If you want to tab through all the records, that should be set to All Records. The Tab Cycle property. I got a video on that.
But in Lee's case, it turns out there's a little more going on. So Donald jumped in with some excellent troubleshooting suggestions. He recommended checking the tab order, making sure the navigation buttons could create a new record, checking Allow Additions, and verifying that the form's underlying query can actually accept new records.
And then Lee confirmed that that was correct, and the navigation buttons would work, and then he could add records directly to the query.
And then Donald suggested checking the code for macros that could be moving the record pointer unexpectedly, along with, of course, a compact and repair or recompile. Recompile, decompile if you have to.
And then here's the interesting part. After trying the different suggestions, Lee finally rebuilt the form from scratch, and that fixed it.
And I've had this happen myself many, many times. Sometimes you can spend hours chasing a strange problem in an Access form, especially one that's been modified repeatedly over time. But the underlying data source works, the properties look right, and nothing obvious is causing the problem.
Creating a fresh form can be a perfectly reasonable troubleshooting step.
Now, we don't know exactly what was wrong with Lee's original form. It could have been a hidden setting, an event, or just some kind of corruption, but rebuilding it fixed the symptom.
And that's weird because it doesn't tell us what the cause was, but at least it works.
And the lesson here, I guess, is to troubleshoot systematically. Don't immediately rebuild everything, but don't spend all afternoon fighting one stubborn form either.
And I'm guilty of this myself. Something that doesn't seem right, so you try this, you try that, you compact, you repair, you do this. I've even restored from backups and then made the change again, and it still didn't work.
Sometimes, if there's corruption in there, it's just in there. It's like that spaghetti sauce. It's in there. You just got to start with a fresh form.
And a big thank you, as always, to Donald for helping Lee work through it. And that's exactly why I love having such a helpful community in the forums. You guys are awesome.
Next up, we got Ryan, who's got a project management database. He's choosing the people involved in a project from a combo box on a subform. If somebody isn't in the list, he opens a separate contact form and adds them. There it is. Looks pretty good.
But when he closes that form, it goes back to the project. The new person still isn't in the combo box. You just entered them where they go.
The contact can be saved in the table while the already open list is still showing its earlier results.
So Kevin suggested a requery that tells the combo to run its Row Source again and pick up the new contact.
You've got to give Access the right address. And there it is right there: Forms, parent form name, whatever it happens to be, bang subform name. The name of the subform control. Dot Form. That's a tricky part. And then combo box name dot Requery.
You start with the parent form. Go through the subform control, then through its Form property to the combo inside it. I've got several videos on that big long thing there.
And there's one of them, I think. No, that's something different. That's the IsLoaded function to make sure the parent form is loaded, but it should be.
And I think Ryan, yep, that worked. He forgot to reference the parent form.
And one thing to watch: the subform control can have a different name from the form loaded inside it. So it could have, like, it might be called OrderF, but you'd name the control OrderSubform. So watch out for that.
Again, thanks to Kevin for his timely reply and fix.
Next up, we got David. David wants to clean up a printed report. Some records have notes and some don't. So he doesn't want a lonely Notes label sitting there with nothing underneath it.
Hiding the label sounds simple enough, but the trouble was getting it to come back for the next record that actually had notes. And that's the sneaky part of this question.
You're thinking about separate rows on a page, where the code is changing the visibility of the same report control as Access formats those records.
And then we got Dan and both Kevins helping out here. We got Dan, we got Kevin, we got the other Kevin, we got John, lots of help on this one. Oh, and someone's beaming him too.
And this is the big one here. The key is to set the label's visibility both ways in the Detail Format event. If the notes are empty, hide it, visible as False. Otherwise, you've got to show it.
And otherwise is doing important work. If your code only says when to hide the label, then don't expect Access to read your mind and turn it back on later. It's what we talked about before. You've got to be specific.
And that's a much better test than printing one record and deciding you're done.
And Kevin's example also handles empty notes. And then I guess David confirmed he got it working. Yep. "By George, I think I got it."
Who's George?
And of course, I'm a nerd. I just had to look it up. "By George" probably refers to a king, but nobody knows for sure. It could also be Saint George.
The specific phrase, "By George, I think she's got it," was made famous in 1956 in the Broadway musical My Fair Lady, later adapted into the 1964 film.
So there's your nerdy trivia for the day too. I'm one of those guys where I have to look stuff up. If I see it in a movie or I read it somewhere, I'm like, "Why did I say that?" Then I got to look it up. I got to know. I don't like not knowing something.
But anyways, back to the question here. One more thing to watch: test this in Print Preview because the Format event doesn't always run in Report View. Otherwise, you could be staring at perfectly reasonable code and wondering why nothing happens.
So the general lesson is to define both outcomes whenever you're conditionally showing or hiding something. Got it? Good.
Next up, we got Kenneth, and he's tracking when tasks are completed and he wants to know whether the date and time should go into one field or two fields.
And it's an easy design decision. Say that 10 times fast. Design decision. Design decision. Design.
Anyways, a design decision to underestimate. Maybe you've got a date picker and a separate box for the time on your form, and so two table fields seem natural. Pick the date and then pick the time.
And then Donald pointed out that you can keep those separate controls while still storing the results together in one Date/Time field. And he linked to some cool videos of mine that covered how to do some of that.
And that's really the distinction here: how somebody enters the information doesn't have to determine how you store it.
Suppose a task starts just before midnight and finishes just after midnight. Looking at the times makes it look like the finish was earlier than the start.
So I always keep the date with the time. You've got the complete moment that you need to compare deadlines or compare how long something took.
And you can make separate stored fields work, but then put them back together whenever you need the complete value. I'm not a big fan of doing it that way.
But for something like this, like a task completion timestamp, I'd store them together. If you prefer separate controls on the form, that's fine. Combine their entries when you save the values together.
Just watch the difference between storage and formatting. Knowing just the date doesn't necessarily mean you've got the time. And showing the date doesn't necessarily mean the field has no time in it.
So thanks to Donald for the solution on this one.
Next up, we got one from Donald. I just love the subject on this one: "Text import turning into zombie feast." I love that.
Donald is importing CSV files, but the people creating them can choose different columns and put them in different orders. And some columns can change too. So an import that worked yesterday might not fit today's file.
I'll describe that as zombies feasting on his brain. You can see why.
You don't want everybody manually rearranging a spreadsheet just to make your import happy.
And we got some ideas here of some different imports. He's got some field mapping names. We've got Matt suggesting a field name lookup. And then Donald worked through aliases and required fields. Thomas emphasized checking the data before moving it into the final tables.
So lots of people chimed in on this one. This one goes on for a bit. There's a lot of good stuff in this one. You guys should read this one. I'll put a link to it down below.
What did I want to point out? There was something I wanted to point out in here.
Yeah, there's my solution. The approach that I'd use is to read the headers in and map them to fields in Access.
For example, you've got member number, member space number, or member with a number sign. Membership number. And they could all mean the same MemberNumber field in the database.
So store those aliases in a table. Then the column can move around. Someone could change it to a familiar alternate name, and it doesn't require a code change.
And if a header is unknown, flag it so an administrator can decide what it means. Just don't guess and put the data in the wrong place.
And you can also use the same table to determine if something is required or optional.
I'd also keep the original CSV data untouched so you can do the translation during the import, and you've got the source still available in case you need to go back and look at it.
So, yeah, that's a good idea. And I might make an interesting developer lesson out of this later. I haven't decided yet, but we'll see.
All right, heading over to the YouTubes.
First up, we got Noel. And he's asking about my faster DLookup Part Two video. In that video, I showed how to speed up lookups using a recordset instead of DLookup.
Noel wants to know if that same technique would work with an unbound control on a continuous form.
Well, the recordset lookup itself will work. But here's the catch: if you use VBA to put the result into an unbound text box, then Access isn't going to maintain the separate value for each row. You'll see the same value repeated throughout the continuous form.
So if you need a different lookup value for each record, my first choice would be to bring that information into the form's underlying query using a join. And that way, Access handles the lookup for all the records together.
You could also use a DLookup expression in the Control Source, but that brings us right back to the performance issues that we were trying to avoid in the original video.
So to answer your question, yes, the recordset technique works, but it's not a direct replacement for a calculated control in a continuous form.
So thanks for the question. Hope that answers it for you.
Next up, we got Jessica, who says that after her company switched to single sign-on, their Access database has started getting the active content blocked error.
They've enabled macros, tried trusted locations, and the databases are all on the network. Some work for one user, but not another.
So, Jessica, the first thing I check is the actual network path. Make sure everybody is accessing the databases using the same server name, not an IP address or a different network mapping.
I've seen cases where Access trusts one path, but not another.
Next, I would check the Trust Center on each user's computer. Make sure the network folder is actually listed as a trusted location and that network trusted locations are allowed.
But here's the big clue. You mentioned that this started after switching to single sign-on. That makes me suspicious of a company security policy change.
Your IT department, the IT guys, are great at this kind of stuff. They might have pushed out a new Office or Windows policy that overrides the settings you're trying to change.
And the missing Unblock checkbox isn't necessarily a problem. That's normally associated with files marked as coming from the internet. So a database created locally or stored on a network share might not have that checkbox at all. You usually get it when you download something.
So I'd have your IT department compare the security policies and exact database paths between a computer where it works and one where it doesn't. And that's where I'd start looking.
And good luck.
Next up, OSO. OSO, very close to Odo. So this is why the best two-digit year is a four-digit year. I agree. Ever since Y2K, I've been using four-digit years everywhere. Even when I'm filling out paperwork.
And while we're at it, I strongly recommend everyone switch to the ISO date format: year, month, day. It's unambiguous, and you don't have to guess if 3/4 means March 4th or April 3rd.
Four-digit years, people. We learned this 26 years ago. And ISO dates. I'm on a mission. Change everybody over to ISO dates. In my lifetime, I want to see this happen.
Next up, this viewer asked if there's a way to package an Access database as an EXE file so people can install it like a regular Windows application. They want to know if there's a way to make the database expire after a certain date.
Well, I get this question a lot. And the short answer is no. You can't compile an Access database into a standalone EXE. Access just doesn't work that way.
What you can do is distribute your database as an ACCDE file. And that compiles the VBA code and prevents users from modifying your forms, reports, and VBA code.
And your customers, or whoever you give the database to, can run it using the free Microsoft Access Runtime so that they don't have to purchase Access themselves.
Now, as far as making the database expire, absolutely. You can build an expiration check into your startup routine, store an expiration date, check it when the database opens, see if that date has passed, display a message, and then stop it from opening the database.
But just remember, it's not bulletproof copy protection. Access is not a secure licensing platform. And someone determined enough may find ways around your restrictions, especially if you save that date in a table or a text file, especially if you store it unencrypted.
I've got a registration seminar on my website where I teach you how to do something very similar where, essentially, I use a code that you can have the database give the user that they can either call you or email you, or whatever, and get the key code that unlocks it.
So there's lots of different ways you can do it. But for distributing a professional Access application to customers, an ACCDE with the free Runtime and some basic licensing logic, that's the way I'd go.
Next up, we got Nuts on Bikes. I love that name.
He left a comment on my video about making the Access Property Sheet easier to read. And they mentioned that there's a new property that lets the form window resize when you zoom.
And yes, you're absolutely right. Thanks for bringing this up.
I complained in that video that when you zoom into a form, it doesn't resize the form. So, for example, if I got my customer form here and I zoom in, let me see, if I zoom in, Control + Scroll Wheel, it doesn't resize the form.
But I mentioned something in the MVP email thread we have, and guess what they added? They added On Format. You can go to, where is it? The Form Resize Is Window right here. It's at that. Yes.
Hit Save, close it, close it. Now watch what happens. Watch this. Ready? Oh, look at that. Look at that. It's so nice. It's so nice.
The default is False. You have to turn it on. But if you use overlapping windows like I do, you're going to want to turn that on. It's a great feature.
Thanks to the Microsoft team for putting that together. And I was going to do a full video on this one, but I figured I might as well mention it now since I've been wanting this myself for a long time now.
Still waiting for forms zooming in Design View, though. Let's see. Sometimes they sneak stuff in here. Nope. Still can't zoom in here, though.
Sometimes they sneak features in and I don't even know about it until you guys say something.
So this is in the Current Channel. It was in the Beta Channel for a while. I don't usually mention stuff when they just add it to Beta. I've got another machine here running Beta, because Beta, you know how Beta versions are. Sometimes there's lots of bugs. Sometimes they put stuff in Beta, then it doesn't end up making the Current Channel and everybody's sad. And you're like, "Well, you said..." Well, I'm sorry. It was in Beta.
But this one's actually in the Current Channel. What am I on here? Account. I'm on Current Channel 2609. There's my build.
But yeah, that one's in there. That's like spaghetti sauce again. It's in there.
So thanks for pointing that out again. Was it Nuts on Bikes? I love it. I gotta go see your whole channel now. Hold on a second. This is glorious. Nuts on Bikes. I love it.
There's not many videos on there, but just that picture is amazing.
But yeah, this is yet one more sign that Microsoft is continuing to make investment and advancements in Microsoft Access. It's still very much alive and well.
Next up, I got Jen asking about whether TempVars behaves a certain way in a multi-user Access database. If you've got multiple users, each with their own front end, all connected to the same back end, will the TempVars reset when another user logs in?
And the answer is no. Each user has their own copy of the front end running their own Access session. So each user has their own separate TempVars collection.
If I'm logged in as Richard and you're logged in as Jen, my TempVars have nothing to do with yours. So even though we're connected to the same back-end tables, those TempVars aren't stored in the back end. In fact, they're not even stored in tables. They're stored in each running instance of Access, as in your system's memory.
Now, one thing to watch out for: if you have a login system where users can log out and other users can log in without closing Access, then you'll need to remember to clear or reset those TempVars yourself. They don't automatically disappear just because someone logged out of your login form.
And I use TempVars, for example, to store what user is logged on, so I don't have to keep checking the environment variables or whatever other user login scenario that you're running.
Now, I've got a separate video on building user-level security in Access where I demonstrate using TempVars to keep track of the logged-in user. So go watch this video if you want to learn more about that.
But separate front ends, separate TempVars. No worries about one user overwriting another user's values.
And hey, while I got you here, quick interruption. If this video's helping you out, hit Like and Subscribe, and click the bell so you know when I release new stuff, and you'll get all kinds of special prizes.
I'm just kidding about the special prizes, but you'll get my love. Is that enough?
Next up, we got Lucy asking about the new cascading combo boxes without VBA. And she wants to know when this feature will be available in the standalone version of Microsoft Access instead of Microsoft 365.
Well, Lucy, unfortunately, that's one of the drawbacks of buying the standalone version of Access. When you purchase the perpetual license, like Access 2021 or Access 2024, you're essentially buying that version as it exists when it released. It's frozen in time.
Now, you'll continue getting security updates and bug fixes while it's supported, but you're not going to get new features that Microsoft adds to Access 365.
So things like the new cascading combo boxes, or the form zooming, or the wide form support, they're not going to be showing up in your version of Access until Microsoft releases another major version.
So the last two versions were Access 2019, 2021, and 2024. Now, Microsoft has not yet announced when the next one is coming. So I wouldn't expect this feature to suddenly appear in your existing Access 2024 installation. It's just not.
You're probably going to have to wait until, I'm guessing, 2027. They might release another one since it's been three years since 2024, but I don't know. That's just my guess.
And that's one of the reasons why I strongly recommend getting a Microsoft 365 subscription. You get the latest features and the cool new toys as soon as Microsoft releases them.
And if you really like playing with new stuff, you can get the Beta version. And you also get the security updates, the bug fixes, and the rest of the Office applications as well: Word, Excel, PowerPoint, all that stuff.
I have the Microsoft 365 Apps for Business, I think it is, the one, and I'm paying less than $10 a month. And if you pay annually, it's even less than that.
I know that it could be confusing picking which version you need to buy, but this page explains all the different options and whatever. And I just updated it earlier this year.
Yeah, I've got the Microsoft 365 Apps for Business version. So that's the one I suggest.
But if you've currently got a standalone version and it's doing everything you need, there's nothing wrong with keeping it while it's supported. But if you want all the new features like I show in my videos, then Microsoft 365 is the way to go.
All right, folks, don't forget to Like and Subscribe. Make sure you stop by my website. Check out what's new. Check out the Captain's Log. You can see what's been rattling through my brain lately.
Of course, we got the merch store where you can get a T-shirt or a mouse pad or a coffee mug. I'm still looking for stuff for penguins. I will have penguins eventually.
Grab a copy of my book. If you got questions, post in the forums on my website. I got a fantastic group of people on my website that love answering questions. They do a great job, and I really appreciate everything that they do.
If you need a consultant, a developer, a tutor, help, whatever, visit my Access Developer Network.
And make sure you get on my mailing list because I love YouTube, but they're not exactly the best at notifying you when I release new videos. So get on my mailing list, and you'll get a notification every time I drop a new video, which is usually every day, but sometimes I take a day off once in a while.
So folks, there you go. It's going to be your TechHelp Quick Queries video for today. I hope you learned something.
Live long and prosper, my friends. I'll see you next time. Intro In today's Quick Queries we will discuss why learning Access fundamentals still matters when using AI, along with troubleshooting form tab behavior, requeries for subform combo boxes, conditional report labels, date and time storage, flexible CSV imports, continuous forms, trusted locations, database distribution, TempVars, and newer Microsoft Access features. Quiz Q1. Why is it still important to learn Access fundamentals when using AI to build a database?
A. AI cannot generate any Access queries or code
B. You need to recognize whether AI's output is correct and appropriate
C. Access requires every user to memorize VBA commands
D. AI only works with Excel, not Access
Q2. What is a dangerous type of AI mistake in a database query?
A. A query that displays an obvious syntax error
B. A query that crashes Access immediately
C. A query that runs but silently leaves out needed records
D. A query that opens in Design View
Q3. What is the main purpose of understanding related tables such as Customers and Orders?
A. To avoid using forms
B. To understand how data should be organized and connected
C. To eliminate the need for primary keys
D. To make every table contain the same fields
Q4. Why might customer address information sometimes be stored with an order?
A. To avoid creating an Orders table
B. To preserve the shipping address used for that specific order
C. To make CustomerID unnecessary
D. To prevent customers from changing their address
Q5. Why must a person provide detailed requirements when asking AI to build an inventory database?
A. AI automatically knows every business process
B. Inventory databases always use the same design
C. Different businesses have different inventory workflows and rules
D. Access cannot store inventory quantities
Q6. What is a good first test after creating inventory logic?
A. Test a transaction where you know the expected result
B. Immediately give the database to all employees
C. Delete all sample data
D. Ask AI whether its own code is correct
Q7. Why should a database be tested with situations such as returns, missing quantities, and simultaneous sales?
A. To make forms look more colorful
B. To find business logic and multi-user problems before customers do
C. To reduce the number of tables
D. To prevent users from entering dates
Q8. What should you do before applying an AI-suggested fix for an error?
A. Paste it in immediately without reading it
B. Ask AI to rewrite the entire database
C. Understand what is failing and ask AI to explain the proposed change
D. Delete the affected table
Q9. Why can removing a condition from a query be risky?
A. Conditions are never allowed in Access queries
B. The condition may be enforcing an important business rule
C. It automatically changes all table field names
D. It prevents reports from printing
Q10. What is a key business rule that AI should not guess?
A. The color of the Access application window
B. When an order should be considered complete
C. The name of every employee's computer
D. The font used in a report title
Q11. How can learning Access help if you later move to another database or programming tool?
A. The concepts of relationships, logic, and problem solving transfer to other tools
B. Access automatically converts every program into any language
C. You will never need to learn another tool
D. Other database systems do not use tables or relationships
Q12. What is an appropriate way to use AI for VBA coding?
A. Let AI make all business decisions without review
B. Have AI draft routine code, then review and test it yourself
C. Use AI only after deleting all existing code
D. Never use AI for coding assistance
Q13. What form property should normally be checked when Tab moves unexpectedly after the last field?
A. Record Source
B. Tab Cycle
C. Caption
D. Scroll Bars
Q14. If a form has strange behavior even though its properties and data source appear correct, what can be a reasonable troubleshooting step?
A. Rebuild the form from scratch after systematic troubleshooting
B. Delete the underlying tables
C. Turn off all relationships
D. Convert every field to Short Text
Q15. After adding a new contact in a separate form, why might the contact not appear in an already-open combo box?
A. The contact cannot be saved to a table
B. The combo box is still showing its previous Row Source results
C. Combo boxes cannot display contacts
D. The contact must be entered twice
Q16. What action updates a combo box list after a new related record is added?
A. Requery the combo box
B. Rename the combo box
C. Close the database permanently
D. Change the contact's primary key
Q17. When referring to a combo box on a subform, which name is especially important to use in the reference path?
A. The name of the subform control on the parent form
B. The caption displayed on the subform
C. The database file name
D. The name of the report section
Q18. When conditionally hiding a Notes label on a report, what should code do in the Detail Format event?
A. Only hide the label when notes are empty
B. Only show the label when notes exist
C. Set the label visibility for both empty and non-empty notes
D. Delete the label after the first empty note
Q19. Why is the Detail Format event important for report controls that change by record?
A. It allows visibility to be set as each detail record is formatted
B. It prevents records from being printed
C. It automatically creates a new table
D. It changes all report controls into unbound controls
Q20. Why is it generally better to store a task completion date and time in one Date/Time field?
A. Access cannot display separate date and time controls
B. A complete timestamp is easier to compare and calculate correctly
C. Date fields cannot be sorted
D. Time values cannot be entered on forms
Q21. What is the difference between data entry controls and data storage?
A. They must always match exactly
B. Separate date and time controls can still save into one Date/Time field
C. Tables can only store what is visible on a form
D. Forms cannot display dates from table fields
Q22. What is a good approach for importing CSV files with changing column names and column positions?
A. Require users to manually rearrange every CSV file
B. Import every column into the same database field
C. Map incoming headers to database fields using aliases stored in a table
D. Ignore the header row completely
Q23. What should happen when an imported CSV file contains an unknown header?
A. Guess which database field it belongs in
B. Flag it for an administrator to review
C. Automatically delete the entire database
D. Store it in the primary key field
Q24. Why should the original CSV data be retained during an import process?
A. So the source can be reviewed if the import mapping needs investigation
B. So the CSV file can replace the database
C. To avoid validating imported data
D. To prevent users from opening reports
Q25. Why is a VBA recordset lookup not a direct replacement for a calculated control on a continuous form?
A. Recordsets cannot read tables
B. An unbound control will show the same assigned value on every displayed row
C. Continuous forms cannot use any queries
D. VBA cannot run from forms
Q26. What is usually the best way to display a different lookup value for each row in a continuous form?
A. Bring the related value into the form's underlying query with a join
B. Use one unbound text box and assign values with VBA
C. Create a separate database for each row
D. Hide all bound controls
Q27. If one user can open a network database without active content warnings but another cannot, what should be checked first?
A. Whether both users are using the same database path and server name
B. Whether both users have the same favorite color
C. Whether the database has enough reports
D. Whether the affected user has printed a form
Q28. Why might Trust Center settings fail to resolve active content problems after a company security change?
A. Access no longer supports trusted locations
B. Company policies may override individual user settings
C. Trusted locations only work for reports
D. Security settings only affect tables
Q29. In a multi-user database with separate front ends, how are TempVars handled?
A. All users share one TempVars collection in the back end
B. Each user has a separate TempVars collection in their own Access session
C. TempVars are stored in a shared table automatically
D. TempVars are available only to administrators
Q30. What should an application do with TempVars when one user logs out and another logs in without closing Access?
A. Clear or reset the TempVars as part of the logout or login process
B. Assume TempVars automatically disappear
C. Delete all front-end forms
D. Compact the back-end database after every login
Q31. What is a common way to distribute an Access application to users who do not own Access?
A. Convert it into a standalone EXE file
B. Distribute an ACCDE file and use the free Access Runtime
C. Send users only the back-end tables
D. Export the application as a PDF file
Q32. What is an important limitation of expiration checks or licensing logic in an Access application?
A. It makes the database impossible to copy
B. It is not bulletproof protection against determined users
C. It prevents all data entry
D. It eliminates the need for backups
Q33. What is the benefit of treating AI like a junior assistant rather than a guru?
A. AI can handle routine work while you retain responsibility for decisions and validation
B. AI will never make any mistakes
C. You no longer need to understand your business rules
D. You can stop testing databases
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-A; 7-B; 8-C; 9-B; 10-B; 11-A; 12-B; 13-B; 14-A; 15-B; 16-A; 17-A; 18-C; 19-A; 20-B; 21-B; 22-C; 23-B; 24-A; 25-B; 26-A; 27-A; 28-B; 29-B; 30-A; 31-B; 32-B; 33-A
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, I am answering questions about whether learning Access still matters when AI can build databases, write queries, generate VBA code, and suggest table designs.
I use AI heavily myself. It is an excellent tool, and it can save a tremendous amount of time. However, AI doing work for you raises an important question: how do you know whether it did the right work?
AI can produce an answer that looks completely reasonable and still be wrong. It may give you a query that runs without errors but leaves out important customers. It may provide VBA code that works in one situation but fails when someone uses the form differently. Nothing necessarily crashes, and Access may not display an error message. You could simply get the wrong result.
That is why the fundamentals still matter. You do not need to memorize every Access function, every VBA command, or every possible argument. I look things up too, and AI can help you research those details. What matters is understanding how the pieces fit together.
You should understand why customers belong in one table and orders belong in another, what a CustomerID represents, and why relationships between tables matter. If AI suggests putting customer address information into every order record, you need enough database knowledge to question that design.
Of course, there are situations where an address should be stored with an order. If you need to know where a particular order was shipped, then storing the shipping address as part of that order makes sense. The important point is that you need to understand why you are storing data in a particular place.
AI also cannot read your mind. If you ask it to create an inventory database, it needs details. Tracking office supplies in a closet is very different from managing inventory across several warehouses that ship orders all day.
You need to consider business rules. What happens when an item is returned? Does it go directly back into available inventory, or does it need to be inspected first? What happens to items promised to customers but not yet shipped? Are those items still available for sale to someone else?
If you do not tell AI about these details, it has to make assumptions or leave important requirements out. You do not want AI making business decisions for you. The better you understand your workflow, your data, and your business rules, the better instructions you can give it.
Once AI provides a solution, you still need to test it. Start with simple situations where you already know the answer. If you have ten widgets and sell three, you should have seven remaining. Then test returns, missing quantities, unusual orders, and multi-user situations. If two people try to sell the last item at the same time, you need to know what happens before your customers find out.
This principle applies beyond Access. A formula in Excel may return a number, but that does not automatically mean it is the correct number. A SQL query may run successfully while producing incorrect totals. You need to know what result you expected so you can verify that the system is behaving properly.
You also need to think about maintenance. A database may work perfectly today, but next month someone may need a new report, another warehouse may be added, or a supplier may change the spreadsheet format they send you. Someone needs to be able to modify the database without breaking the parts that already work.
Whether a person built the database manually or used AI assistance, you should know where the data is stored, how the important forms and queries work, and how to restore a backup. AI can help document a system, but saving the original prompt is not the same as having a maintenance plan.
When something breaks, do not immediately copy the error message into AI and blindly apply whatever it suggests. Sometimes that solves one error but creates two more. Before long, you may not even remember what the original code was supposed to accomplish.
Instead, slow down. Read the error message. Identify the part that is failing. Ask AI to explain the proposed change before applying it. If it tells you to remove a condition from a query, find out why that condition was there. It might be the condition that prevents canceled orders from appearing in sales totals.
AI can be a very useful debugging assistant. I use it that way myself. If I have been staring at code for too long and cannot find an obvious syntax or logic error, AI may spot something I overlooked. It is especially useful when I am tired and have accidentally reversed a comparison or made a simple typing mistake.
However, AI cannot replace your understanding of what the correct result should be. You still need to know your business rules. When is an order complete? Is it complete when it ships, when the customer pays, or when every item on the order has shipped, even if those items went out in multiple packages? Access does not decide that, and AI should not be guessing. Those are decisions that come from your business.
The person who understands how the business works brings tremendous value to a database project. Learning Access gives that person a way to turn business knowledge into tables, queries, forms, reports, and automation. AI can help with the construction, but someone still has to explain what needs to be built and why.
You may wonder whether learning Access is worthwhile if the technology could change again in the future. The answer is yes, because the underlying concepts transfer. If you understand why customers and orders belong in related tables, that knowledge still helps when you move to SQL Server or another platform. If you understand how a loop works in VBA, learning loops in another programming language becomes much easier.
The spelling and syntax may change, but the ability to break a problem into logical steps remains valuable. The better you can think like a programmer, the better results you will get from Access, Excel, SQL Server, Python, or AI.
Use AI for tedious, repetitive work. If you understand how a recordset loop works, you can ask AI to draft one rather than typing the same basic structure repeatedly. Then you can review it, modify it as needed, and test it. That is a productive use of AI.
Think of AI like a calculator. You do not need to do long division by hand every time, but you should still know whether division is the correct operation for the problem. The calculator does not know what you are trying to solve.
AI is similar to autopilot in an aircraft. It can help perform routine tasks, but someone still needs to choose the destination, monitor the instruments, and know when to take control. Treat AI as a junior assistant, not as an unquestionable authority.
Learning Access is still worth your time. You do not need to memorize every line of VBA or build everything yourself. Let AI help you. Just learn enough to understand what it is giving you, determine whether it meets your needs, and recognize when something is wrong.
Another question involved tabbing through fields on an Access form. The user could tab from control to control, but when reaching the last control and pressing Tab, Access returned to the first record instead of moving to a new record.
Normally, I would first check the form's Tab Cycle property. If you want to move through all records, the setting should allow cycling through all records. However, there can be other causes.
You should check the tab order, confirm that the form allows additions, verify that navigation buttons permit new records, and make sure the form's underlying query is updateable and can accept new records. You should also check for VBA code or macros that may be moving the record pointer unexpectedly.
A Compact and Repair operation may help, and recompiling or decompiling can sometimes resolve odd behavior caused by corruption or compilation issues.
In this case, rebuilding the form from scratch resolved the problem. We may not know exactly what was wrong with the original form. It could have involved a hidden setting, an event procedure, an old property, or some form corruption. Recreating a form is not always the first troubleshooting step, but it can be a reasonable solution when the underlying data source works, the properties look correct, and the problem remains stubborn.
Troubleshoot systematically, but do not spend an entire afternoon fighting a damaged form when recreating it may solve the issue quickly.
Another question involved a combo box on a subform. A user selected people assigned to a project from the combo box. If the person was not listed, the user opened a separate contact form, added the person, and returned to the project form. The new person was in the table but did not appear in the combo box.
The issue is that the combo box was still displaying the list of values it loaded earlier. Saving a new contact does not automatically refresh that existing list.
The solution is to requery the combo box after the contact form closes. In the case of a combo box on a subform, you must reference the parent form, then the subform control, then the form contained within that control, and finally the combo box itself.
Be careful because the subform control name may not match the name of the form loaded inside it. This is one of the most common sources of confusion when working with subforms.
Another question involved hiding a Notes label on a report when a record had no notes. The challenge was that the label also needed to reappear for the next record that did have notes.
Report controls are reused as Access formats each detail record. If code only hides the label when notes are missing, then the label can remain hidden for later records. You need to explicitly define both outcomes in the Detail Format event. Hide the label when notes are blank, and show it when notes are present.
This is an important general principle. Whenever you conditionally show or hide something, define both states. Do not assume Access will restore a control's previous setting automatically.
You should also test report formatting in Print Preview. Some report events do not behave the same way in Report View, so code that appears correct may not run where you expect it to run.
Another discussion involved whether to store a task completion date and time in one field or two separate fields. Even if your form uses separate controls for entering a date and a time, I recommend storing the final value in one Date/Time field.
The way a user enters data does not have to determine how it is stored. Keeping the date and time together gives you one complete timestamp that can be compared, sorted, and used in calculations.
For example, if a task starts before midnight and finishes after midnight, comparing only the times can make it appear that the task finished before it started. A complete date and time value avoids that problem.
You can store dates and times separately, but then you have to combine them whenever you need the full timestamp. For something such as a task completion time, storing both together is generally the better design.
Another question involved importing CSV files where the people creating the files could choose different columns, put columns in different orders, or use different header names. An import process that worked yesterday might fail today because the source file format changed.
Rather than forcing users to rearrange spreadsheets manually, I would import and examine the header row, then map those headers to the appropriate fields in Access.
For example, "Member Number," "Member No.," "Membership Number," and similar headings could all refer to the same MemberNumber field. You can store these alternate names in a mapping table. That way, a column can move to another position or use an accepted alternate name without requiring a code change.
If an unknown header appears, flag it for an administrator to review rather than guessing where the data belongs. You do not want to place data into the wrong field simply because a column name was unfamiliar.
The same mapping table can also identify which fields are required and which are optional. I would preserve the original CSV data during the import process so that you can return to the source if there is ever a question about how the information was translated.
Another question involved using a recordset lookup to replace DLookup on a continuous form. A recordset can speed up lookups, but an unbound text box on a continuous form does not store a separate VBA-assigned value for each displayed row. If you set that unbound control with VBA, you will generally see the same value repeated throughout the form.
If you need a different lookup value for each record in a continuous form, the best approach is usually to include the value in the form's underlying query by using a join. Access can then retrieve the needed values for all records together.
You can also use a DLookup expression in the control source, but that may bring back the performance problems you were trying to avoid. A recordset lookup can work in some circumstances, but it is not a direct replacement for calculated controls on continuous forms.
Another question involved the "active content blocked" message after a company moved to single sign-on. The users had enabled macros and configured trusted locations, but the database worked for some users and not for others.
The first thing I would check is whether everyone is opening the database through the same network path. One person may use a mapped drive, another may use a server name, and another may use an IP address. Access may trust one path but not another.
Next, check the Trust Center settings on each computer. Confirm that the network folder is listed as a trusted location and that trusted locations on the network are permitted.
Because the issue began after a switch to single sign-on, I would also suspect a company security policy. IT may have deployed a Windows or Office policy that overrides the settings users are trying to change themselves.
The missing Unblock checkbox is not necessarily a problem. That checkbox is normally associated with files Windows identifies as having come from the Internet. A database created locally or stored on a network share may not have that option.
The best next step is to have IT compare the security policies and exact database paths between a computer where the database works and one where it does not.
I also want to reinforce the importance of using four-digit years and ISO date format. Use a four-digit year whenever possible, and use dates in year-month-day order. This format is unambiguous. You do not have to wonder whether 3/4 means March 4 or April 3.
Another question asked whether an Access database can be packaged as a standalone EXE file and whether it can expire after a certain date.
You cannot compile an Access database into a standalone EXE application. Access does not work that way. However, you can distribute an ACCDE file. An ACCDE compiles the VBA code and prevents users from modifying your forms, reports, and VBA modules.
People who do not own Access can run an ACCDE using the free Microsoft Access Runtime.
You can build expiration logic into your database startup routine. The database can check an expiration date when it opens, display a message if the date has passed, and prevent the user from continuing.
However, Access is not a bulletproof licensing platform. A determined person may find ways around basic restrictions, particularly if your expiration date is stored plainly in a table or text file. For normal distribution, an ACCDE file, the Access Runtime, and reasonable licensing logic is the standard approach.
Another viewer pointed out a newer Access feature that allows the form window to resize when you zoom. By default, zooming into a form does not resize the form window. A newer form property can change that behavior.
If you use overlapping windows, enabling the property that resizes the form window during zooming can make forms much easier to use. This feature is available in the Current Channel release of Microsoft 365 Access. It was initially available in the Beta Channel, but it is now available more broadly.
Microsoft continues to make improvements to Access, and features like this are a good reminder that Access is still actively supported and developed.
Another question involved TempVars in a multi-user Access database. If multiple users each have their own front end connected to the same back-end tables, one user's TempVars do not affect another user's TempVars.
Each user runs a separate Access session with a separate front end. TempVars are stored in that individual running instance of Access, not in the back-end tables. If I am logged in as one user and you are logged in as another, my TempVars collection is separate from yours.
However, if you have a login system where one person can log out and another person can log in without closing Access, you should clear or reset TempVars during the logout process. They do not automatically disappear just because someone signs out of your custom login form.
TempVars are useful for storing information such as the current logged-in user, especially when you do not want to repeatedly check other sources of login information.
Finally, another question involved when newer Access features will become available in standalone versions of Access rather than Microsoft 365.
When you purchase a perpetual version such as Access 2021 or Access 2024, you are purchasing that version as it existed when it was released. You will receive security updates and bug fixes during the support period, but you generally will not receive new feature additions.
Features such as newer cascading combo box options, form zoom improvements, and other interface enhancements are normally added to Microsoft 365 first. They do not usually appear later in an already released perpetual version.
Microsoft has not announced when the next standalone release of Access will be available. If your current perpetual version does everything you need, there is nothing wrong with continuing to use it while it remains supported. However, if you want the latest features as soon as Microsoft releases them, Microsoft 365 is the better choice.
I recommend Microsoft 365 for most Access users because it includes ongoing feature updates, security updates, bug fixes, and the other Office applications. If you enjoy testing new features early, you can also consider the Beta Channel, although Beta releases can include bugs and features that may change before they reach the Current Channel.
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 Why Access fundamentals still matter with AI
Validating AI-generated database designs and code
Testing database workflows and business rules
Maintaining and debugging AI-assisted databases
When rebuilding an Access form solves problems
Troubleshooting Tab navigation and new records
Requerying a combo box after adding a record
Referencing controls on a subform
Showing and hiding report labels per record
Using the Report Format event in Print Preview
Storing dates and times in one Date/Time field
Importing variable CSV columns with header mapping
Avoiding unbound values in continuous forms
Using query joins instead of repeated DLookup calls
Troubleshooting Access active content blocking
Distributing an ACCDE with Access Runtime
Adding database expiration and licensing checks
Resizing Access forms when zooming
Using TempVars in multi-user databases
Feature differences between Access editions Article AI can help you build databases, write formulas, design tables, create queries, and generate code. It can save a great deal of time, especially when you already know what you need and can recognize whether its answer makes sense. However, AI is most useful when you treat it as an assistant rather than as a replacement for understanding the work.
The main reason to learn Access, Excel, SQL, programming, or any other technical skill is not that you need to memorize every command or write everything from scratch. The reason is that you need enough knowledge to tell whether the result is correct.
AI can produce an answer that looks reasonable and runs without errors, but still gives you the wrong result. A query might return records while silently excluding customers you needed. A calculation might return a number that appears valid but uses the wrong assumptions. Code might work during a simple test but fail when a user performs the same task in a slightly different way.
The most dangerous problems are often not syntax errors. Syntax errors usually stop the program and make themselves known. Logic errors can quietly produce bad data, incorrect reports, or missing records. If you understand the basic purpose of a query, table relationship, form event, or calculation, you are much more likely to spot a result that does not belong.
You do not need to memorize every Access function or every VBA command. You can look up details when you need them, and AI can help explain them. What matters is understanding how the major pieces fit together.
For example, customers and orders normally belong in separate related tables. A customer can place many orders, so storing customer information once in a Customers table and linking each order to that customer helps prevent repeated and inconsistent data. The CustomerID field is not just a random number. It is the connection between the customer and that customer's orders.
At the same time, good database design requires understanding the business rules. You might normally avoid repeating a customer's address on every order, but you may need to store a shipping address with each order if you need to preserve where that specific order was sent. A customer can move after placing an order, and changing the customer address should not rewrite the historical shipping address on past orders.
AI cannot know these rules unless you explain them. Asking it to build an inventory database is not enough. Inventory for office supplies in one room is very different from inventory for a business that handles multiple warehouses, backorders, returns, inspections, reserved stock, and shipments.
You need to decide what happens when an item is returned. Does it immediately become available for sale, or must it be inspected first? Are items promised to customers still available for new orders? What happens when two employees try to sell the final item in stock at the same time? These are business decisions, not technical details that should be guessed by AI.
The more clearly you understand your own workflow, the better instructions you can give AI. Better instructions lead to better output. Even then, you must test the result.
Start with simple tests where you already know the correct answer. If you have ten items in stock and sell three, the remaining quantity should be seven. Then test a return. Test a missing quantity. Test an attempt to sell more than is available. Test what happens when multiple people use the system at the same time.
The same principle applies to spreadsheets, reports, and code. A formula returning a number does not prove that the number is correct. A report that prints does not prove that it includes the right records. You need to understand what result you expect before you can verify whether the result is valid.
AI is especially helpful for routine work. If you understand the purpose of a recordset loop, for example, you can ask AI to draft the general structure for a task. You can then read it, adapt it, and test it. This saves time while keeping you in control of the business logic.
A useful comparison is a calculator. A calculator can perform arithmetic quickly, but you still need to know which values to enter and whether multiplication, division, addition, or subtraction is appropriate. The calculator does not know the actual problem you are trying to solve. AI is similar. It can generate a lot of work quickly, but it does not understand your business the way you do.
When debugging, do not simply copy an error message into AI and blindly paste back whatever solution it suggests. Ask it to explain what is failing, what the proposed change does, and what possible side effects the change may have. If AI recommends removing a condition from a query, you should understand why that condition was there. It may have been preventing canceled orders from appearing in sales totals.
AI can be very helpful at spotting obvious syntax mistakes, mismatched names, reversed comparison operators, or missing references. It can also help explain unfamiliar error messages. But someone still needs to know what the program is supposed to do. Without that knowledge, you may fix one error while creating several more.
This matters even more when your database needs maintenance. A database that works today may need changes later because the business adds another location, changes its workflow, needs a new report, or starts receiving data in a different format. Saving the AI prompt that created something is not enough. You need to know where the data is stored, which forms and reports are important, what major queries do, and how to restore a backup.
Documentation is valuable, and AI can help create it. However, documentation should describe the system as it actually works. It should explain the tables, relationships, important forms, reports, queries, startup procedures, backups, and business rules. It should not merely be a collection of prompts and generated code.
Learning the fundamentals also helps you move to other tools later. If you understand why related tables matter in Access, that knowledge still applies when using a server-based database. If you understand loops, conditions, variables, and data validation in VBA, learning another programming language becomes much easier. The syntax may change, but the underlying problem-solving skills remain useful.
In Access, troubleshooting should be systematic. Suppose you are tabbing through controls on a form and pressing Tab at the last field sends you back to the first record instead of creating a new record or moving as expected. Start by checking the form's tab cycle setting. Then verify the tab order, whether additions are allowed, whether the navigation buttons permit new records, and whether the form's underlying query is updateable.
Also check whether any macros or event procedures might be moving the current record unexpectedly. Compacting and repairing the database, and recompiling the VBA project, can resolve some problems caused by corruption or stale compiled code.
Sometimes, though, a form has been modified so many times that a hidden setting, damaged property, or unexplained corruption remains. If the query works, the form properties appear correct, and the problem cannot be found after reasonable troubleshooting, creating a fresh form can be the practical solution. Do not rebuild everything immediately, but do not spend endless time fighting a form that may simply be damaged.
When a combo box should display a newly added value, remember that the combo box may still be showing its old list. For example, a user may be entering a project and selecting a contact from a combo box. If the needed contact is not in the list, the user opens a contact form, creates the new contact, and closes the form. The new contact is saved in the table, but the already-open combo box may not display it until the combo box is requeried.
The requery must target the correct control. When the combo box is on a subform, you need to refer to the main form, then the subform control, then the form loaded inside that control, and finally the combo box itself. A common source of confusion is that the subform control may have a different name from the form object it displays. Make sure you use the name of the control placed on the parent form when navigating to the subform.
Reports require careful handling when controls are conditionally shown or hidden. Suppose each record may have notes, and you want to hide the Notes label when there are no notes. The report formatting process reuses the same controls as it moves through records. If you only tell Access when to hide the label, it may remain hidden for later records that do have notes.
The solution is to explicitly define both outcomes during the report's formatting stage. If notes are empty, hide the label. Otherwise, show it. This ensures that each detail record gets the correct display state. Test this in Print Preview, because some report events do not behave the same way in Report View.
When storing dates and times, it is generally best to store the complete date and time together in a single Date/Time field when they represent one moment. A form can still provide separate controls for entering a date and a time if that is easier for the user. When the record is saved, the two entries can be combined into one stored value.
Keeping the full date and time together avoids ambiguity. If a task starts before midnight and ends after midnight, comparing time values alone can make the finish time appear earlier than the start time. A complete timestamp allows you to sort, compare, calculate durations, and identify the actual sequence of events reliably.
Importing CSV files can be difficult when outside users change column names, move columns into different positions, or omit optional fields. Avoid designing an import that assumes every file will always have the same layout. Instead, read the incoming header row and map each header to the appropriate field in your database.
A mapping table can store acceptable aliases for each field. For example, several header names such as Member Number, Membership Number, or Member ID might all map to the same database field. The import process should identify recognized headers, determine which fields are required, and flag unknown headers for review rather than guessing where the data belongs.
It is also wise to preserve the original imported file or its raw contents. That gives you a source record to inspect if there is a question about how data was translated. Validate incoming data before appending it to final production tables. Import it into a staging area first when practical, review problems, and only then move clean data into the main tables.
Continuous forms have their own behavior that can surprise developers. If you place an unbound text box on a continuous form and use VBA to assign a value to it, Access does not maintain a separate unbound value for every displayed row. The same value may appear throughout the form.
If each row needs a different related value, the best approach is usually to include that value in the form's record source query through a join or calculated field. This lets Access retrieve the correct value for each record as part of the dataset. A domain lookup expression can also work in some situations, but it can cause performance problems when many records are displayed.
In multi-user Access databases, each user should normally have a separate copy of the front-end database connected to a shared back-end database. This improves performance, reduces corruption risk, and keeps each user's forms, reports, code, and temporary settings separate.
TempVars are stored within each running instance of Access, not in the shared back-end tables. This means one user's TempVars do not overwrite another user's TempVars. If one user logs in under one name and another user logs in under a different name, each user has a separate TempVars collection.
However, if your application allows one person to log out and another person to log in without closing Access, you should clear or reset TempVars during the logout process. They remain in memory until they are changed, removed, or Access closes.
When distributing an Access application, you cannot convert it into a normal standalone executable file. A common approach is to distribute an ACCDE file. This prevents users from modifying the forms, reports, and VBA source code. Users who do not own Access can run the application using the free Access Runtime.
You can add licensing or expiration logic to an application. For example, the startup process can check an expiration date or validate a registration key before allowing the user to continue. The application can display a message and close or restrict access when the license has expired.
However, Access is not a secure licensing platform. Basic expiration checks can discourage casual copying, but determined users may find ways around them, especially if the expiration date or license information is stored in an obvious and unprotected location. Licensing should be treated as a reasonable business control, not as unbreakable copy protection.
Security issues involving blocked active content or disabled macros often depend on how the database is opened. If some users can open a database while others cannot, verify that everyone is using the same network path. A database opened through a server name may be treated differently from the same database opened through an IP address, a mapped drive, or another network alias.
Each user's Access Trust Center settings should be checked to confirm that the network folder is listed as a trusted location and that network trusted locations are allowed. If the problem began after a company-wide sign-in or security change, an organizational policy may be overriding individual settings. In that case, the IT department should compare the policies and paths on a working computer and a non-working computer.
Use clear date formats whenever possible. Four-digit years prevent confusion and avoid the old problems associated with two-digit years. A year-month-day format is especially useful because it is unambiguous and sorts correctly as text. A date written as year-month-day is easier to interpret consistently than a date where the month and day could be reversed.
Microsoft Access continues to receive improvements, but not every installation receives new features at the same time. Subscription-based editions receive ongoing feature updates, while perpetual-license editions generally receive security updates and bug fixes without newly introduced features. If a feature is important to your work, verify whether it is available in the edition and update channel you are using.
The most productive approach is to combine your own knowledge with AI assistance. Learn enough database design, query logic, form behavior, reporting, and programming fundamentals to define the problem and evaluate the answer. Then let AI help with repetitive tasks, explanations, drafts, documentation, and debugging.
AI can make you faster, but it cannot take responsibility for your data, your business rules, or your results. The person who understands the business and the system remains essential. Primary Topics AI-assisted Access development, database fundamentals, table normalization and relationships, validating AI queries and VBA, business rules and workflow design, troubleshooting Access forms, multi-user Access behavior, Access deployment and licensing Secondary Topics combo box requery after adding records, report control visibility, date/time field design, flexible CSV imports, continuous form lookup limitations, trusted locations and active content, ISO date formats, Microsoft Access feature availability |
