Quick Queries #85
By Richard Rost
19 days ago
The Feature That Causes More Problems Than It Solves In this Quick Queries episode, we discuss the pitfalls of Microsoft Access's Name AutoCorrect feature and why it often causes more problems than it solves. We will talk about launching global search forms with keyboard shortcuts in Access, inventory database design with movement tracking versus totals, issues with Excel notes, password management strategies and considerations for using Access, some challenges with VBA and module settings, and a few interface quirks. Additional topics include distributing Access applications, setting coding defaults, and updates about upcoming tutorials and events. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, Name AutoCorrect, global keyboard shortcuts, pop-up forms, inventory design, tracking inventory movement, Excel notes printing, password manager database, VBA interface quirks, AutoKeys macro, Quick Access Toolbar, barcode inventory tracking, SelTop property, Option Explicit default, 64-bit EXE compiling
Intro In this Quick Queries episode, we discuss the pitfalls of Microsoft Access's Name AutoCorrect feature and why it often causes more problems than it solves. We will talk about launching global search forms with keyboard shortcuts in Access, inventory database design with movement tracking versus totals, issues with Excel notes, password management strategies and considerations for using Access, some challenges with VBA and module settings, and a few interface quirks. Additional topics include distributing Access applications, setting coding defaults, and updates about upcoming tutorials and events.Transcript What if one of Access's so-called helpful features is actually breaking your database behind the scenes? Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today we're going to talk about the Microsoft Access feature that quietly causes more problems than it solves: Name AutoCorrect. I'll show you why it doesn't update everything like you'd think it does and why I usually recommend turning it off.
This episode also includes tips on global keyboard shortcuts with pop-up forms, inventory design and tracking movement versus totals, Excel notes and why they behave strangely, password managers versus building your own in Access, and a few VBA interface quirks you might run into.
Alright, let's get to it.
Leading off today, we've got Werner, one of my Gold members. He wants to know if there's a way to open a search form from anywhere in his Access application using a keyboard shortcut instead of putting buttons everywhere. He's tried macros and shortcut keys, but they aren't working, especially with this app setup using lots of pop-up forms.
This is one of those "why don't my shortcuts work" situations. Donald had a good suggestion using events like Keydown, but those only work when that specific form has focus, so they're not truly global. Honestly, Werner, you've run into a couple of Access quirks at once.
Yes, you can launch forms with keyboard shortcuts, but it's not always as simple as it sounds, especially once you start using pop-up forms everywhere.
Sandra and Raymond mentioned macros and the Quick Access Toolbar, which is honestly what I use, but again, pop-up forms, especially modal pop-up forms, can interfere with how Access handles focus and keyboard input. Darren's got a good suggestion. I like the Quick Access Toolbar myself.
In fact, those of you who know the TechHelp free template, I got a button right here on my Access Quick Launch Toolbar that I put there that all it does is open up the main menu. So I click on that, and it pops open the main menu. You could do the same thing, put a search box here if you wanted to, make another button that opens up your search form.
But the big gotcha here is those pop-up forms. When everything's pop-up, Access kind of loses its central command layer. So shortcuts, AutoKeys, toolbar tricks don't always behave the way you'd expect.
One of the best suggestions in the thread was Donald pointing out focus issues because sometimes the form is opening, it's just hiding behind something else. In general, if you want global shortcuts, you're usually looking at AutoKeys macros or a central non-pop-up form acting as your controller. Otherwise, you're just fighting Access. Pop-up forms are great, but they do come with trade-offs like this.
I would suggest, like I said, the Quick Launch Toolbar. You could put a search bar on your main menu. Then if you have a situation where you have to search from one of your pop-up menus, stick a little search button on that pop-up menu.
Next up, we got Gary, one of my Platinum members, who shared a great tip in Excel: if you turn on notes in your worksheet, you can actually print them either as displayed on the sheet or at the end of the sheet, which is really handy when you've got extra comments that don't fit in the cells. It's on the page setup menu. This is one of those, "oh yeah, I should have mentioned that in the video" moments. So thanks Gary, that's a great tip. He mentioned it as part of my Comments and Notes video. A lot of people don't realize Excel gives you those print options for notes, and I forget about it a lot of the time myself. But it's super useful when you've got extra context that doesn't belong in the actual data.
I agree with you on the VBA side too. Excel can feel a lot messier because you're constantly dealing with sheets, ranges, selections, and formatting. In Access, everything is more structured: tables, forms, records, sets. It's just cleaner. And yes, I've seen those giant Excel solutions too, entire applications built in spreadsheets. Some of them are impressive, but a lot of them probably should have been built in Access from day one.
The macro recorder is both a blessing and a curse. It's great for quick stuff, and I use it sometimes, but it definitely leads to some spaghetti code. I've converted a lot of solutions for people when I used to be a consultant. I go into a business and they've got everything built in Excel with buttons and scripts and macros that they recorded. I have to convert all that over to Access. That's one of the reasons I quit consulting. No, I'm just kidding, it wasn't that bad. But it's hard sometimes to get people to leave Excel - "from my cold dead hand" kind of thing.
Thanks for sharing.
Next up, we got Stephanie, a longtime student, who is building a linen inventory database for a small hotel and wants to track how many items are in storage, in rooms, in laundry, and on shelves while also keeping a total count. She's wondering if she should just store those quantities in fields or if she needs a more advanced design with additional tables.
This is a great example of where simple versus scalable design comes into play. Stephanie, what you're trying to do is totally doable. It just depends on how far you want to take it. Now if this is a small system like in your 50-room hotel, then the quick and easy approach is exactly what you describe. Just put those fields right in your product table: quantity in storage, quantity in rooms, quantity in laundry, and so on. It works, it's easy to understand, and it will get the job done.
Some of the guys suggested some other things, like I've got a full inventory video and seminars and all kinds of stuff. The more professional developer way to do it, which is what I recommend for long-term, bigger solutions, is to track movement instead of totals. In other words, every time linen moves from storage to rooms or from rooms to laundry, you record that as a transaction, and then your totals are calculated instead of stored in the tables.
It's the same thing with product inventory. You do a transaction table, and every transaction like you purchase goods from a vendor, they go into your inventory that way, and you sell them to your customer, they come out of your inventory that way. Your totals are all calculated based on those movements.
In some really, really big systems, to keep things fast, you do both. You've got your transactions, but then you can also store totals. Every now and then you reconcile those and make sure they're okay. It's kind of like tracking transactions in a check register. You have your individual transactions and then you also have the totals. Once in a while, like at the end of the month, you reconcile your checkbook and mark, "okay, are all these transactions adding up right?"
You can do a mixture of all three. It's whatever works for you. That's the benefit of using Access and that's why we build our own databases: to build a solution that works for you.
Is there a right or a wrong way to do it? No, it's whatever fits your business. That's why you build your own software.
But this is definitely one of the great points of Access, and this is why it's important to think ahead. If you ever want history or reporting or auditing - where did all my totals go? Don't let me stay at your hotel, or my wife, I should say. Your transaction table wins every time. But if you just need current numbers and that's it, that's all you care about, the simple method is fine.
You could even do it where, instead of calculating the totals, you could just update the total and then save the change in a change log. So you could go backwards. You could say, okay, if you've got, let's say, a hundred towels and you update it to 20, then it'll just save that in an audit log; let's say, "subtracted 20 from towels." Or whatever.
There's a million ways you can do it. Whatever works best for you. I think I've got videos showing all of these methods, so do some searches. I know the guys gave you some links, so check that stuff out too. I've got a barcode seminar. I know that you don't put barcodes on towels and linens and things of that nature, but it's the in and out tracking that's what's important here. The barcode part of it's actually a really small part of it. When you use a handheld barcode scanner nowadays, it's basically like you're typing in a code at the keyboard. That's what the barcode does, the barcode reader. The rest of it is the tracking and the movement of the inventory itself.
Speaking about tracking the movement of things in and out, before you are out of here, make sure you click like and subscribe. See what I did there? Seriously though, when you hit the like button, it tells the YouTube algorithm that people like my video and it shows it to more people. That helps me, it helps the channel, and it helps me keep these videos free. So if you like it, hit the like button. Thanks.
Next up, I get asked this once in a while. John, one of my Platinum members, wrote in asking if I've ever built a password generator database in Access. Something that can create strong passwords, rotate them, maybe even remind you when to change them. And of course, keep everything secure.
It's a good idea. I get asked this question a lot, and a few people have mentioned it on YouTube - you know, "hey, build a password generator."
Personally, I don't use that kind of thing myself in Access. I use Chrome's built-in password manager. It already generates strong passwords, keeps them all unique, and syncs them across all your devices: your browser on your desktop, your phone, wherever. It also helps protect you against phishing attacks too because it won't provide the password automatically if you're not on that website.
So if you click on an email that looks like it's coming from your bank, but it's not really from your bank and it takes you to a website that looks like your bank, you don't copy and paste the password from your Access database. Google Chrome just won't give you that password automatically because it recognizes that the domain name is different. You'd have to manually go and look it up, which should tell you that you're not in the right spot.
Now of course, nothing is 100 percent secure. If someone gets into your Google account, then that's the master key to everything. So you still need to lock your Google account down with good security, two-factor, all that stuff.
Could you build something like that in Access? Absolutely. But honestly, for something as critical as password management, I'd rather rely on tools that are built specifically for password management and that are constantly updated for security, and Google's got just about as good a security as I could think of.
Remember, storing data like that in Access isn't secure. You could password protect your backend table, but that's another password you've got to remember. Even Access's password security is not that strong. You'd really need to use strong encryption and SQL Server to keep those passwords secure, or encrypt the data in the table, basically scramble it with your own algorithm of some kind. I do have a seminar where I teach that.
In this seminar, I don't use it for whole databases, but if you've got one or two sensitive fields in your table, like credit limit or similar information, you can scramble it so it looks like that in the table. If someone does get the database, then they can't unscramble it without your password. But again, it's not perfect.
Now with all that being said, I have had this on my to-do list for a while because a lot of people ask me about it. So here's the thing, to everybody watching: if you want to see me build a password generator and safe storage database in Access, post a comment down below. If enough people are interested, maybe I'll put something together just for educational purposes.
I'll be completely honest: in my account balances database that I use, I actually do store some of my bank and credit card company passwords in there to log on because there are some sites that Google's password manager won't automatically fill in the password for you. It's probably due to something that the bank has locked down. So I have a field in here where I can just click the field and a copy is the password to the clipboard. I do do this myself. I know full well though that my Access database isn't secure, but I'm the only person here. Someone would have to break into my house and bypass my security system and break into my computer. I've got Windows secured. Then they'd have to figure out the password for the database. If they get past all that, they can get my bank password. But I do use Google password manager myself for most things.
If you want to see it, post a comment down below. If enough people are interested, then I'll do something. It could always make a good TechHelp video.
All right, heading over to YouTube now.
Meadhead1972. Now I love the Meadhead reference if that's an All in the Family reference. I used to watch it, and I was born in 1972. Pretty cool username there, dude.
Anyways, Meadhead is asking about Name AutoCorrect in Access. I mentioned it in my corrupted video, I believe, and whether renaming a table or field automatically updates everything like queries, forms, reports, and VBA code.
Great question, and I actually have this on my list to make a full TechHelp video about it to give everybody all the ins and outs about it. Let me just give you the basics real quick since you asked.
This is actually one of those features that sounds better than it is. I usually turn it off, especially in my older databases. Name AutoCorrect will update things like queries, forms, and reports, specifically the control sources and references to those fields. But it does not rename the controls themselves. So if you have a text box in your customer table and the name of the field is "First Name" with a space in the middle, and then you have a form with "First Name" on it, again, space in the middle, both the name and the control source have that space.
If you change the field in the table, it will update the control source, but not the name of the box, which causes more problems than it solves. Here, let me demonstrate.
We're going to go backwards here. I got my customer table that has "FirstName" in it with no space. I also have a customer form that has "FirstName" in it with no space. So the name of the box itself and its control source don't have the space in it.
Let's say later on I get delusional and I decide I want to change it and put a space in there. Now I save it and close it. Now the name of the field in here has the space in it. If you look at the form, the form still works. But now, if you open up the properties, it changed the control source so it matches the table, but the name of the box is still "FirstName." It doesn't update that.
That's one of the problems I have. It does update the references in some places, but not everywhere. Most importantly, it doesn't touch your VBA code at all. If you've got code referencing that field or that table name, you'll still have to go in and fix all that manually.
So honestly, I usually recommend turning Name AutoCorrect off because it can cause more confusion than it solves, especially in larger databases. I'll be doing a full video on this eventually. It's on my list. It's not very high on my list because it's not a major problem for most people. The only time it's a really big problem is if you have an old database with lots of stuff named wrong and then you decide to go through and fix it. Then you break stuff because you don't realize, "oh, the names of my fields haven't changed, but I'm referring to them." It's a nightmare.
Sometimes even then, it will still work, because now in a lot of places you can refer to both of these things. You can refer to "First Name" with the space in it, but remember to put brackets around everything because the control source exists in the underlying table and you can refer to "FirstName" the control. So in both cases, you'll sometimes get what you want and sometimes you won't. It's a pain, I know. But good question. Thanks for asking.
Next up, we got George Eau. He's asking if the SelTop property in Access actually hides the records above it, or if you can still scroll up and see them. Good question. SelTop and SelHeight don't hide anything. It just controls which records are selected. The other records are still there, and you can absolutely scroll up or down and still see the rest of them. Think of it like repositioning your view, not filtering or removing anything. If you actually want to hide records, that's where you apply a filter or change the record source. I think I showed how to do that in the previous video. He was asking about Quick Queries 83. I think I showed a demonstration of that.
Next up, a lot of you have asked me when SQL Server for Access Users Level 2 is coming out. I'm just going to say it's coming out soon. If you, like Neal, have the need for more cowbell in SQL, don't worry, you won't have to wait too much longer.
I'm heading out of town. I'm leaving tomorrow. The Microsoft MVP Summit is in Redmond, Washington, and we've got Access Day. That's coming up on Friday, the 27th. So I'll be in the Redmond/Seattle area all next week. I have queued up some videos to go out for next week.
Those of you who have been bugging me for more fitness videos, you got a bunch more of those coming, and some other cool stuff. I got stuff already ready to go for next week and then a couple days after I get back. I plan to start working on more SQL Server lessons on my return at the end of the month. Probably start working on those on the 30th or so. I never give dates. But I've gotten tons of feedback and a lot of signups too. I'm happy a lot of people have pre-ordered levels two and three, which are available on my website. I've got a bunch of people signing up as Silver members because they said they're interested in that. So yes, it's definitely coming. In a couple weeks, we'll have some more lessons. I like to sit down and do a bunch of them at once. So I'm going to try to do the whole beginner Level 2 at once, and then in the next week, maybe beginner Level 3, and we'll see how it goes.
Next up, Jose is saying that Excel notes can be really frustrating because they randomly resize, move around, or show up in weird places, which makes them tough to use for instructions. Welcome to Excel. You're not wrong. Notes and comments in Excel can definitely be quirky, especially when you start resizing rows or columns around them. They don't always behave the way you'd expect. That's one of the reasons I don't rely on them too heavily for anything critical. They're fine for quick reminders, but if you're building something more structured, especially something that other users depend on, you're usually better off putting that info somewhere more stable, like in a proper field or even moving the whole thing into Access, which is why I promoted that video in the first place. So it's not just you. It's just one of Excel's little quirky personality traits.
Like I always say, my rule is if it's more than one screen of data, it belongs in a database. I use Excel myself for a lot of small stuff, but as soon as it starts graduating into a hundred rows or 50-60 columns, then it's time to start looking at a database.
Next up, B Jones is replying to several months ago when I did a video on one of my little conversation starters that I do: "What features of Access do you think need improvement?" This isn't one that's an improvement; this is just he's asking for a feature that doesn't exist. He says a compiler to a 64-bit EXE file. Yeah, lots of people want that. I get it. You want that one-click standalone EXE that you get with a lot of other languages. Right now the closest thing we have is the Access Runtime, which still installs a stripped down version of Access on the user's machines. You've got to install that whole thing. So technically you can distribute your app, but it's not a true compiled EXE.
Personally, I'd love to see that too, but I don't think Microsoft is going to go that route. The Access team is already busy enough with the projects they're working on. Access has always been more of a rapid application platform than a compiled language.
Here's a hint to all the developers out there: if some third-party tool came along that could package Access up into a real EXE, that would be pretty awesome. I just wouldn't hold my breath for it, but that'd be pretty cool. Or something that could take an Access database and convert the front end over to VB.NET or something like that, that would be sweet.
I'm actually working on a tool myself to take Access forms and convert them over to web documents, HTML, ASP, that kind of thing with JavaScript. It's slow going and I've got a proof of concept working, but that's a ways away. But yeah, I feel you.
Next up, Richard is asking about my Option Explicit video. He says, how do you make Option Explicit and Option Compare Database the default in the VBA code editor so they show up automatically? Yes, I actually cover this in that video that you're commenting on. It's this video right here.
It's right on the screenshot. You go into the VBA editor, then Tools, then Options, and then you check "Require Variable Declaration." That will automatically add Option Explicit to any new modules that you create. It only applies to new modules going forward; it won't go back and fix your old modules, your old code, your form modules, your global system modules. You'll have to add it there into the existing modules yourself manually. Just copy and paste it.
For Option Compare Database, that one doesn't have a check box. Access usually adds it by default in new modules, but if it doesn't, you'll have to add it manually. The only time you'd ever change that, again, that's for how the database compares caseness: uppercase, lowercase.
Database means that lowercase Rick is the same as uppercase Rick as far as variables are concerned. There's Binary, there's Database, and then there's the third one - I'm drawing a blank - Text, I think. I almost never use them. The only time I will change it is if I want to make functions that are case sensitive, like checking passwords, like we talked about earlier - uppercase A is different from lowercase a - and there are functions you can use that are case sensitive too without making the whole module. If you know this module is going to be all about passwords and you're going to have several functions in it, you might want to switch to Option Compare Binary, for example.
Database uses the database's sort order. It respects your Access settings. That's usually what you want. Binary is case sensitive and compares based on raw character codes, and Text is case insensitive and uses standard text comparison.
Once again, hit that like and subscribe if you're enjoying my videos. When I mentioned Access Day earlier, I didn't realize that this video is actually going to release to the public on the day that Access Day is. So if you're a member, you're probably seeing this ahead of time, still got some time to get there. If not, this video is coming out on Friday, March 27th for everybody. So run down there right now and say hi, I'll be there.
Sometimes I forget that I pre-recorded most of this stuff. If I'm going away, normally I do try to do Quick Queries Fridays on Friday. That's why most of the time, like my regular videos I try to release them at 9 a.m.; I'll schedule them for the next day or two ahead. But when I know I'm going on a trip, I'll do them ahead of time, and I forgot that, yeah, this is releasing on Access Day.
I'll let you know how it went in next week's Quick Queries.
Don't forget to stop by my website and check out what's new. Lots of cool stuff came out this past week. While you're there, get on my mailing list so you get notified whenever I release new stuff. Make sure you check out my Captain's Log. It's a page on my website, and I've actually set up a YouTube channel just for it. You can give me some subscribes over there. This one YouTube channel keeps releasing these awesome Star Trek-related music videos. This one's fantastic.
I wrote a couple of articles last week. I don't remember if I mentioned them in the last Quick Queries or not, but the first one, "AI Slop is Flooding Tech Tutorials" - I literally have been finding YouTube videos that are not just a little wrong, they're 100 percent wrong. Like, I found one Access video that's like, no, that's not how that works at all. I'll put a link to this down below as well. Then, of course, another company that's trash talking Access because they're trying to sell expensive consulting services. So it never ends. But check out some of the Captain's Log.
Don't forget the merch store: get your mouse pads, coffee mugs, and all that stuff. Grab a copy of my book on Amazon. I'm actually putting together an SQL Server book for Level One. That's going to be cool too.
So the big takeaways today: if you want reliable shortcuts, use things like the Quick Access Toolbar or a central form instead of fighting popup focus issues.
For inventory, think in terms of tracking movement with transactions instead of just storing totals, unless that's what you want.
For Name AutoCorrect, honestly, I just turn it off. Manage changes yourself. It's not that hard to do. In those big old databases, sometimes you're better off just leaving the names the way they are. Deal with the spaces and the brackets.
Post a comment down below. Let me know what you thought of today's video and what questions you've got for next week's Quick Queries. If you're one of the people that wants to see me build a password manager, put a comment down below.
That's going to do it for your Quick Queries for today, brought to you by AccessLearningZone.com. I hope you learned something.
Live long and prosper, my friends. I'll see you next time. Enjoy your weekend.Quiz Q1. What is the main problem with Access's Name AutoCorrect feature?
A. It does not update references in VBA code or control names
B. It only works with table relationships
C. It automatically removes fields without warning
D. It only works with reports
Q2. What does Richard usually recommend regarding the Name AutoCorrect feature?
A. Always leave it on for all databases
B. Always turn it off to avoid issues
C. Only use it with forms
D. Use it only for VBA modules
Q3. When using pop-up forms in Access, what common issue can interfere with global keyboard shortcuts?
A. Pop-up forms interfere with focus and keyboard input
B. Pop-up forms disable all ribbon features
C. Pop-up forms automatically lock all data entry
D. Pop-up forms prevent all navigation
Q4. What is a good alternative to keyboard shortcuts for opening commonly-used forms in Access?
A. Using the Quick Access Toolbar buttons
B. Typing SQL commands into the Immediate Window
C. Rebooting Access each time
D. Using Windows Task Manager
Q5. In designing an inventory database, what is a scalable, professional method to track item quantities?
A. Tracking movements with transaction records and calculating totals as needed
B. Only storing current totals in a single record
C. Writing the numbers on paper
D. Ignoring historical movement for simplicity
Q6. Why does Richard prefer using Google's password manager over building one in Access?
A. It offers better security, auto-fills, and phishing protection
B. It is cheaper to use Access
C. Access provides stronger encryption than Google
D. Google's manager cannot store passwords
Q7. What is required to make Option Explicit the default for new VBA modules in Access?
A. Enable "Require Variable Declaration" in the VBA editor options
B. Edit the registry directly
C. Install a VBA add-in
D. Always use Access in safe mode
Q8. If you change the name of a field in a table using Name AutoCorrect, what does NOT get updated?
A. The name of the control on forms
B. The control source for forms
C. References in queries
D. Reports referencing that field
Q9. What does the SelTop property in Access affect?
A. Which records are selected, not which are hidden
B. The filter applied to the form
C. The sorting order of the records
D. The database encryption level
Q10. Where in Excel can you choose to print worksheet notes/comments?
A. In the page setup options
B. Only from the review tab
C. In the "Save As" dialog
D. From the Insert menu
Q11. What is the closest option to compiling a true EXE from an Access database?
A. Distributing with the Access Runtime
B. Exporting to a CSV file
C. Converting directly to a Word document
D. Using Windows Notepad
Q12. What does Option Compare Database do in VBA modules?
A. Controls how string comparisons are handled
B. Sets form background color
C. Creates new tables automatically
D. Assigns user permissions
Q13. What should you include around field names with spaces when referencing them?
A. Use brackets, for example [First Name]
B. Use parentheses
C. Use curly braces
D. Use quotation marks only
Q14. What is a major reason to consider moving from Excel to Access for a data solution?
A. When data goes beyond one screen or becomes complex
B. When you want fewer columns
C. To take better screenshots
D. To automatically generate EXE files
Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-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 from Access Learning Zone, I'm discussing how some of Access's so-called helpful features might actually cause problems behind the scenes, focusing especially on Name AutoCorrect. I'll explain what Name AutoCorrect really does, why it might not function as you'd expect, and why I usually recommend disabling it in most databases.
The episode covers several other topics as well, including how to create truly global keyboard shortcuts in Access applications that use pop-up forms, the best ways to design and track inventory movement versus storing totals, quirks with Excel notes and their print options, a comparison between password managers and building your own solution in Access, and a couple of VBA interface limitations you might encounter.
Let me walk through each of these topics in turn, starting with Werner's question about global keyboard shortcuts in an Access application loaded with pop-up forms. He was hoping for a shortcut that would bring up a search form no matter where the user is in the app. Unfortunately, while it is possible to open forms using keyboard shortcuts, pop-up forms and especially those that are modal can disrupt the focus and prevent truly global shortcuts from working reliably. Suggestions like using the Quick Access Toolbar or putting search buttons on main menus do help, but when every form is set to pop-up, Access loses central control, making consistent shortcuts challenging. AutoKeys macros or maintaining a non-pop-up "controller" form can provide more dependable results, but with pop-ups, you might have to get creative, perhaps by adding search buttons directly to the specific pop-up forms when needed.
Moving on, Gary shared a tip about Excel: by enabling notes, you can print them either as they appear on the sheet or at the end of the sheet, a handy way to include comments that don't fit within cells. This is controlled from the page setup menu in Excel - a feature that is often overlooked but very useful when you want to share context with others without cluttering your data. Gary also mentioned how messy coding with VBA in Excel feels compared to Access, thanks to all the selections and formatting you must handle. Access, in contrast, is much more structured, which makes development neater and more predictable.
Next, Stephanie raised an inventory design question about a linen management system for a small hotel. She wants to track stock in various locations (storage, rooms, laundry, shelves) along with a total count. For small systems, adding those fields directly to the products table works fine and is easy to implement. However, if you're aiming for something more scalable or need to keep history, you should consider transaction tracking. That means logging every movement from one location to another and then calculating your totals as needed. Some large systems even do both - store the totals for speed and log every transaction for accuracy and reconciliation. There's no "right" answer; it all depends on your business needs, which is exactly why we use Access to create custom solutions. But if you want richer reporting or require an audit trail, movement-tracking is the way to go. As for using barcodes in such a system, know that the critical piece is not the barcode itself but the in-and-out tracking of inventory.
John then asked about password managers in Access, specifically whether it's feasible to build a secure password generator and manager within Access itself. While you absolutely can build functional tools like this in Access, I personally prefer using something like Google Chrome's password manager. It's robust, generates strong unique passwords, syncs across devices, and is aware of phishing attempts, making it much safer than anything built in Access. Passwords stored in Access aren't truly secure unless you add strong encryption - standard Access password security is quite weak and can easily be bypassed. So while you could roll your own, in a real-world scenario you're better off relying on tools built for that purpose. That said, if there's significant demand for a password manager tutorial in Access, I'll consider making one for the sake of learning.
Meadhead1972 had a question about the Name AutoCorrect feature in Access. This feature claims to keep everything synchronized when you rename tables or fields. In practice, though, it will only update references in queries and the control sources on forms and reports - it does not rename the controls themselves, nor does it update your VBA code. This can lead to hidden problems, as the actual box names and the field names will become unsynchronized, or references in VBA will break without warning. For these reasons, I generally turn Name AutoCorrect off and prefer to handle any changes myself. If you have a large database with inconsistently named objects, relying on Name AutoCorrect is more likely to create confusion than to help. I will eventually make a full TechHelp video on this topic since it trips up so many users.
George Eau asked whether the SelTop property in Access hides records or just changes the selected ones. The answer is that it only changes the selection - the earlier records remain visible and scrollable. If you need to hide records, you need a filter or a change to the record source.
A lot of you have been asking when the SQL Server for Access Users Level 2 class will be out. It's coming soon. I'll be busy at the Microsoft MVP Summit in Redmond (and Access Day) for a bit, but SQL Server lessons will follow as soon as I return.
Jose chimed in about Excel notes randomly moving and resizing, which can be quite frustrating if you're trying to use them for instructions. Unfortunately, this is a well-known Excel quirk - notes are fine for short reminders, but anything more important should be documented elsewhere or moved into a more stable system like Access.
B Jones asked for an Access compiler to 64-bit EXE. That is a much-wanted feature in the community, but currently, the closest we have is distributing apps via the Access Runtime, which still requires Access components to be installed on end-user machines. There's no true standalone compiled solution yet, though I'd also love to see one and am even experimenting with converting Access front ends into web apps for deployment.
Richard had a question about how to make Option Explicit and Option Compare Database default in the VBA editor. For Option Explicit, you simply check "Require Variable Declaration" in the VBA editor's Tools menu, which affects new modules from then on but won't change existing ones. Option Compare Database usually appears by default in new modules, but if not, you'll need to add it manually. It's important for controlling case sensitivity and sort order when comparing strings in VBA.
Finally, don't forget to visit my website for the latest updates - including tutorials, articles, and my Captain's Log. You can join my mailing list so you'll always know when new content is released. The merch store is there as well, and new books - including one on SQL Server - are coming soon.
To sum up the main lessons: for reliable Access shortcuts, prefer the Quick Access Toolbar or a controller form; for inventory tracking, transactions win if you want audit trails, but totals are okay for simple needs; and Name AutoCorrect is often best turned off. If you want to see me create a password management solution in Access, let me know in the comments.
You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Using global keyboard shortcuts in Access with pop-up forms
Quick Access Toolbar as a workaround for launching forms
Focus issues with pop-up and modal forms and shortcuts
Comparison of keyboard shortcut methods in Access
Excel notes printing options from the page setup menu
Behavior of Excel notes and comments on worksheet formatting
Migrating large solutions from Excel to Access
Simple versus advanced inventory database design in Access
Using transaction tables for inventory movement tracking
Reconciling transaction and total fields in inventory systems
Adding search bars and buttons to Access menus and pop-ups
Security risks of storing sensitive information in Access
Using password managers versus building your own in Access
Name AutoCorrect feature behavior and limitations in Access
Effect of Name AutoCorrect on control sources vs. control names
Name AutoCorrect impact on VBA code references
Turning off Name AutoCorrect for database reliability
Access SelTop and SelHeight properties for record selection
Difference between filtering and repositioning in Access forms
How to enable Option Explicit by default in VBA editor
Difference between Option Compare settings in VBA
Manual addition of Option Compare Database where neededArticle Sometimes features in Access that are designed to be helpful can actually end up causing more trouble than they solve. One such example is Name AutoCorrect. Many people believe that if you rename a field or object in Access, this feature will automatically and perfectly update all references throughout your database, including queries, forms, reports, and even VBA code. However, that is not entirely true, and relying on Name AutoCorrect can actually introduce problems into your database that are difficult to track down and fix later.
Let's take a closer look at what Name AutoCorrect actually does. When you rename a table or field, Access will attempt to update references to that object in certain places. For example, if you change a field name in your table and that field is used as the control source in a form, Access will generally update the control source property of that control. However, it does not update the name of the control itself on the form. So if your form has a text box named FirstName and you rename your table field to First Name (adding a space), the control source property will update, but the text box itself will still be named FirstName, not First Name. This can cause confusion when you are trying to refer to that control elsewhere, in code or in expressions. Even worse, Name AutoCorrect does not touch your VBA code at all. If you have code that refers to the old field or table name, you will have to go into your code manually and update those references. This makes sweeping changes risky and can leave your project in an inconsistent state.
Because of these inconsistencies, I usually recommend turning off Name AutoCorrect in your Access options. This gives you more control over renaming and ensures you know exactly where changes need to be made. Managing changes yourself might take a little more time, but it helps you avoid those sneaky bugs that come up when something does not update the way you expect.
This kind of attention to detail becomes particularly important as your project grows, especially with legacy databases that might have many objects and references. While you can sometimes get away with inconsistent names by relying on brackets and flexible referencing (for example, [First Name]), you may find that things break unexpectedly, especially when both the field and the control on your form can have the same or similar names. For these reasons, making name changes in a larger system can quickly become more trouble than it is worth.
Global keyboard shortcuts in Access are another area that seem straightforward but can get complicated, especially if your application uses pop-up or modal forms. Normally, you can launch forms using keyboard shortcuts - either with macros or via the Quick Access Toolbar. However, pop-up forms disrupt Access's normal focus handling, which means global shortcuts and AutoKeys macros might not work as expected. Very often, events like KeyDown only work for the form that currently has focus, and with many pop-up forms, Access can lose that central point of control. For most users, the best solution is to use the Quick Access Toolbar to add buttons for commonly-used actions, like opening a main menu or a search form. If you want a search function available everywhere, consider adding a dedicated button on key forms, or use a non-pop-up form as your controller. While some workarounds exist, you will generally fight fewer battles by conforming to Access's intended behaviors and working within those design boundaries.
The design of inventory systems is another area with simple solutions for small projects and more advanced structures for scalability. If you just want to track the current number of items in storage, in rooms, in laundry, and on shelves, storing those numbers directly in a table works fine for smaller applications. You would simply put fields like QuantityInStorage, QuantityInRooms, and so on in your main inventory table. However, as systems grow or when auditing and historical tracking becomes important, a transaction-centered design is best. In that approach, every movement of an item - such as from storage to a room or from a room to laundry - is recorded as a separate transaction. You then calculate current totals as needed by querying these transactions. This method mirrors how checkbook registers work, giving you both a running total and historical records. In large-scale systems, some people combine both strategies - using transactions for auditing and updates, while storing calculated totals to display quickly, occasionally reconciling the two. The approach you take should match your business needs. If you need to track how inventory moves over time or need to answer questions like "where did that item go," always use a transaction log. If you just need today's quantities, basic field storage may be enough.
Excel's notes and comments also deserve mention. While it is handy to annotate cells, those notes can be surprisingly unstable - resizing, moving, and appearing unpredictably, especially after adjusting rows or columns. This quirk makes them unreliable for critical instructions or comprehensive documentation. For anything that needs to persist or be shared, it is better to keep your information in the sheet itself or move more complex data into a database like Access.
On the subject of password management, many wonder about building their own password generator or vault in Access. Technically, Access can be used to build such a tool, complete with password generators, reminders for rotation, and secure storage of passwords. That said, most experts advise using established password managers, such as the one built into your browser (like Google Chrome). These tools are regularly updated, take full advantage of encryption, and offer features like phishing protection - only filling in your password on the correct domain. Storing sensitive data like passwords inside Access is fundamentally risky; Access does not offer strong native encryption, and while you can scramble data or password-protect back-end tables, these measures are not bulletproof. For maximum security, keep such systems as separate and professionally-managed as possible.
Another frequently requested feature is the ability to compile an Access database into a true standalone executable file (EXE), preferably 64-bit. Unfortunately, this capability does not exist. The closest workaround is distributing your application with the free Access Runtime, which installs a trimmed-down version of Access on client computers. It is not a true compiled application, but for now, it is the best option for deploying Access solutions without requiring users to buy Access. Some developers hope for future tools to allow Access apps to be converted into more portable formats (like VB.NET or web apps), but that is a long-term dream.
A common best practice in VBA development is using "Option Explicit" at the top of your modules. This directive forces you to declare all variables before using them, which catches typos and helps prevent bugs. To make "Option Explicit" the default for new modules, open the VBA editor, go to Tools, then Options, and check "Require Variable Declaration." From that point forward, Access will automatically add Option Explicit to any new modules you create, but it will not update existing ones. For those, you need to manually paste the line in. As for "Option Compare Database," this is usually added by default to new modules and means that Access compares strings using your database's case sensitivity and sort order. You might change it to "Option Compare Binary" in special cases, like when you want case sensitivity, for example in password functions. If you want your module to treat "A" and "a" as different characters, use Binary; if you want them treated as the same, use Database or Text.
Finally, a note about working with selection in form datasheets. The "SelTop" and "SelHeight" properties let you move the selection cursor around, but they don't hide or filter records above or below the selected range. If you want to hide or show certain records, you will need to apply a filter or change the form's record source.
The bottom line for working with Access is to know its features and their limitations. It pays to manage important changes like names manually rather than relying on automatic features that are not truly reliable. When planning shortcuts, inventory tracking, or even sensitive data storage, always match your approach to the scale of your application and the security you require. And for non-database tips, keep in mind the quirks of tools like Excel so you do not get caught off guard.
If you enjoyed these database insights and practical advice, consider visiting AccessLearningZone.com for more training and resources. Keeping up with database best practices helps your projects run smoothly and saves you a lot of frustration in the future. |
