Quick Queries #75
By Richard Rost
31 days ago
Using Subforms Instead of Tab Controls, & Lots More!
In this video, we'll talk about common problems with tab controls in Microsoft Access forms and better alternatives for managing multiple views. We'll discuss issues with the SendKeys function and security dialogs, fixing #Name errors, why cloud sync is not a true backup, and how to think about flexible database design. You'll also hear tips on useful keyboard shortcuts, web scraping considerations, handling parameter errors, and strategies for building Access databases or finding help. Plus, I answer viewer questions from YouTube, forums, and email in this Quick Queries session.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, tab controls, DLookup, SendKeys, pound name error, web scraping, database design, cloud sync, keyboard shortcuts, ODBC login prompts, DSN-less connection, backup strategies, continuous forms, subform navigation, Association video series, parameter error, custom database software, Document Object Model, self-referencing many-to-many, bundling products, data recovery, merch store, Navigation Pane, VBA window, recordset loop, product groups, Copy Web Page Data, normalizing tables, system backup, incremental backup, meal bundles, calculated fields, client settings, system images, API connection, Access Day 2026
Intro In this video, we'll talk about common problems with tab controls in Microsoft Access forms and better alternatives for managing multiple views. We'll discuss issues with the SendKeys function and security dialogs, fixing #Name errors, why cloud sync is not a true backup, and how to think about flexible database design. You'll also hear tips on useful keyboard shortcuts, web scraping considerations, handling parameter errors, and strategies for building Access databases or finding help. Plus, I answer viewer questions from YouTube, forums, and email in this Quick Queries session.Transcript If you've ever built an Access form that slowly turned into a tab nightmare, this video is going to save you a lot of pain.
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 why tab controls in Microsoft's Access can cause more problems than they solve. We'll talk about a cleaner technique that I personally use that avoids a lot of these headaches.
Plus, we answer your questions from YouTube, my website forums, emails, and much more, including why SendKeys fails on Windows login prompts or on database login prompts; how to fix those dreaded pound name errors that show up everywhere in datasheets; when web scraping makes sense and when it doesn't; how to think about database design when there is no single right answer; why cloud sync is not the same thing as real backups; a few of my favorite keyboard shortcuts; and where to go when you want to learn how to build custom database software yourself or if you want to hire someone to do it for you. That and more in today's Quick Queries. Let's get to it.
Looking off the bat today we got Maurice who says he was using a DLookup statement and spent three hours trying to figure out what was wrong. Went to look at one of my videos and only had to see the screenshot before he realized what was wrong. I don't want to show Kevin's reply just yet, but here's his DLookup statement. Do you see what's wrong in there? Anybody? Kevin got it: missing quotes around the criteria. That's got to be inside quotes.
The DLookup is looking for three strings, and in fact, I posted in a response. If you look at the pop-up Intellisense for it, the DLookup says "Expression As String." That's what you're looking for. In this case: "User," "Password," "Domain" as a string, which is the table or query you're looking the data up in. And then it just says "Criteria," but that is also a string. And I tested that, because if you throw it in a statement like this and I just put "1=1" in here, which any equality or inequality will work, if you come and debug-compile, it will compile. But as soon as you try to run it, you'll get a type mismatch because that's not a string. So it's definitely looking for a string, which is why I'm surprised this compiles. If it was a variant, it would allow that and then just yell at you once it ran and say it's not a valid thing, or, worse yet, it would be a runtime error and you just wouldn't have any clue why it's not returning a record. But at least you get a type mismatch. Just keep that in mind. I like to remind people of stuff like this because if I do it, if Maurice does it, if other users do it, you'll probably do it too. So it's just something to keep in mind. The more you see it, the more it sticks in your noodle.
Next up, we've got Gary, one of my Platinum members, saying he's trying to automate a file import. But when the database opens with a form OnOpen event, there's a message box saying you want to open with a timer event. And if there's no user intervention, then it continues to run code that begins an import script connecting to a different database with ODBC, which generates a Windows logon and password message box that he'd like to be able to populate with SendKeys.
Now, I just did a video a couple days ago that teaches you how to launch a web page and then use SendKeys to copy the data and then you can bring it back to Access and paste it in. I'll put a link to this down below. But the problem is, first of all, Alex is right: SendKeys is very temperamental. If it's ODBC, there's got to be a different way to connect instead of using SendKeys. We went through some other stuff in here and he shared some screenshots, but essentially, yeah, this idea makes perfect sense. But like I said here, Windows is intentionally blocking it. The short version: SendKeys usually doesn't work with Windows security dialogs. Once you hit that ODBC login box or a Windows credential prompt or anything user logon related, you get what's called "protected desktop." Access can't see it, can't focus on it. It was intentionally blocked SendKeys from interacting with it to prevent malware from doing exactly what you're trying to do. That's why it works with a web browser, but it's not going to work with a Windows logon box. It's not Windows being flaky; that's just security. SendKeys can work on Windows you own, like Access forms, a browser window you launched yourself. That's why my scraping trick works. But security prompts: hard stop. Especially after Windows updates.
So, like Alex says, the correct fix is to avoid that prompt entirely. That usually means saving your login credentials. If the DSN allows it, you can use Windows authentication, switch to a system DSN, have a user DSN for unattended runs, use a DSN-less connection string and supply the credentials in code. There are all kinds of different ways. But the key takeaway here is don't fight Windows with SendKeys. If you're seeing a password prompt, the solution is to find another workaround for the authentication. You can't usually automate those dialogs. So hope that helps.
In the Captain's Log this week, I showed a couple of keyboard shortcuts. I used to do keyboard shortcut videos, but they're too short. I'm just kidding. If you don't know already, Control+semicolon inserts the date and Control+Shift+semicolon inserts the time. You have to do them together. You go Control+colon and then space and then Control+Shift+colon and you get the date and time. Control+apostrophe can copy the value from the same field in the record above it if you're in a continuous form.
I asked everybody, hey, what do you like to use? Donald shared his Control+comma, Control+period switching views. I think he's mentioned that before. Control+plus is handy if you're at the top of a long continuous form to go to a new record. That's a good one. I'm going to put a lot of these together, maybe a mouse pad or something with all my favorite shortcuts on it, and add it to the merch store.
Joe says F11 for the Navigation Pane. Alt+F11 opens the VBA window. Shift+F2: I use this one constantly. Shift+F2 or Control+Shift+F2 in code to go back to the previous spot you were editing in. And, yeah, I've been thinking about putting together a desk mat. I bought a Star Trek Next Generation desk mat with a globe border on it. It's really cool. I should see if those people can do something for me. But, yeah, great keyboard shortcuts. If you have any to add, post them down below.
Next up, one of my Access veterans and Platinum member Donald said that he put in a long work session on an Access project and thought he was being careful, made multiple local copies of his database, copies found to USB drives, and relied on OneDrive and Google Drive for syncing. Then later that day, he discovered that not only was that morning's work gone, but the database everywhere had rolled back about a week. After virus scans and digging through logs, it appears the copies never fully completed. Windows rebooted during an update. And the cloud tool simply synced the last version that they actually saw.
There's a lot of back and forth about this on the website. I'll post a link if you guys want to read it all. But essentially, this story is all too real. A lot of us think this can't happen once you've got lots of different backups going on. What it really comes down to, though, is don't make assumptions. Verify your drag and drop copies. Cloud sync tools aren't necessarily true backups. And, of course, Windows decides to reboot on its own. I've got ways you can turn that off, but I think Windows Update is super annoying myself. I have it turned off on all my machines. I make sure I update manually once in a while.
I had this happen to me and I told the story on the website here. I had a hard drive like this; we're talking about like 1998. It was a massive two gigabyte drive, which cost me like $1,000 back then. But I had my entire business on it. My database, customer info, everything. All backups. I was not backup crazy back then. I'd make a backup maybe once a year. But, yeah, the drive died. I spent a couple days pulling my hair out. I seriously considered going to one of those data recovery companies, where for like $1,000 they can try to get back the stuff that's on your drive. Because sometimes what happens with hard drives, especially the old school ones that actually have moving platters on them, magnetic platters, is the drive motor dies or the logic on the board. I think one of the circuit boards in it dies, but the data is still on the platter. So what they do is, in a clean room, they can take those platters out, put them in a brand new drive, and then hope it comes back to life. And sometimes it does, but that's an expensive process, which for me at the time, $1,000 was most of my monthly income. So I wasn't about to do that then.
But fortunately, I was able to rebuild most of it. Like I said, I had a couple of month old copy of the database. I had some old floppies lying around with some data on them, old emails I could resurrect, paper invoices. Yes, I had paper invoices at the time. It was the 90s, folks, but that was a brutal week.
So, ever since then, I've been obsessed with backups. For me, it's been like I've got a nightly backup that runs automatically, of course. And once a quarter, I'd make system-wide full backups. And I would manually copy data up to a cloud storage drive. And I would manually copy my website and my SQL Server and copy those to different drives. But recently I decided, I spent the whole day a couple days ago automating everything. I'm using Macrium Backup now, which makes a full system backup on the three primary machines here in my office and then a nightly incremental. Then I wrote a script that copies that up to my cloud storage drive, verifies it. If you guys want to see a more in-depth video series on good backup strategies, not just for your Access database, because I've covered it for Access before and I've got multiple videos and even some templates on my website for doing just backups of your database. But if you want to learn more about backing up your entire system and cloud-based copy, system images, all that stuff, let me know. I'm happy to make more videos about it. I just don't know how many people are interested in that kind of stuff. So post a comment down below if you'd like to see more.
Next up, it's a question from Ben, one of my Learning Connection and Developer members. Ben asked a really thoughtful question about database design. He's working on a system with products that are purchased, manufactured, bundled, and sold. He's struggling with how to structure the tables. But the bigger question he asked was this: when there are multiple ways to design something, how do you know which way is the right way versus something that just happens to work now?
Lots of good stuff brought up in this thread. I'm not going to read the whole thing for you, but just digest it for you. This is one of those questions that never really goes away, even after my 30 plus years of doing this. The short answer is that there usually isn't one single right way. There's database theory that you teach in a classroom, and then there's real world experience. Most good design lives somewhere in between the two. You can learn all about properly normalizing tables and rules and this and that, but in the end, what works for you is what works best.
What I focus on first is getting something working that I actually understand. It works. It's good. It might be slow, but it's working. Then I step back and ask about tradeoffs. Will this scale? Is performance going to be an issue? Is this still going to work six months from now? If it's a recordset loop, can I make it faster with SQL statements? But I get it working first, then we back it up. Once we've got our working solution, then we look at how we can tweak it and make it better.
Take this from me: you're going to learn something a year from now that you don't know today. Even me, I'm still constantly learning stuff, and I've been doing this, like I said, for 30 years. Next month, I'm going to learn something new, and then I'm going to look at my database and go, oh man, I can change it and do this and add that. So you're never going to get past that point. Perfection is the enemy of good enough.
When I first started doing videos, my videos had to be perfect and it would take me six months to release a video. Now I just do good enough, like these Quick Queries, and have fun. It's the same thing with database design. You're going to learn more stuff even if you're not a professional developer. You're going to learn some new trick and you're going to go, oh, wait a minute, that's how that works. Then you're going to want to go back and redesign your whole database. Just do what works well today. If it needs to scale up, if it needs to get faster, you can always tinker with it and make it better tomorrow. Don't worry about making sure that your database is perfect right now.
Now, in this specific case to answer this question, Access Developer 25 is where I cover this scenario. I'll put the screen up with the QR code. In this video, I show the proper way to model products that are made from other products. It's a self-referencing many-to-many relationship and I cover that in this class. We build true bundles, kits, or assemblies, whatever you want to call them. Then you can say, okay, these different parts make up this thing. I used to do the same thing with computers. I used to sell computer equipment back in the day. Back in the 90s, I used to sell PCs and you'd say, okay, this hard drive, this motherboard, this whatever, we're going to bundle this thing called the Alpha PC package or whatever. Then the Beta package, which is not quite as good, and it had a huge 500 megabyte hard drive. So that's like making bundles.
Now, that's slightly different than I've also built databases before where you take raw materials and build those into a product. You can do the same thing, but that involves quantities of stuff mixing together. I've done it all, but this one covers product groups, which is grouping packages together like that.
You know where else I cover something like this? My fitness database series. See, for everybody who's complaining that there's too much time in the fitness database series, I cover all kinds of good stuff in this fitness database series. I teach you how to take food items and combine them into meals. What's a meal? It's a bundle of food items. It's the same concept. We got our food list; here's all the foods that we got, vegetables and oils and fruits and whatever. Those can be bundled together into meals. My standard cereal-coffee, my fish-rice-and-veggies, these are all meal bundles. Hard drive motherboard whatever in a computer, those are bundles, whatever kind of package deals you want. Then when you go to put them in your food log, you come down here, you type in meals, it'll show you all the meals, and now I can add the fish-rice-and-veggies meal. I hit plus and boom, there it is. I'm going to delete it now because I didn't eat that, so let's get rid of that stuff. That would throw my calories off for the day. But that's exactly what that does.
But going back to Ben's question, the key takeaway is: if it works and you understand it (or if you mostly understand it) and it meets today's requirements, it's good design. Where you are right now, it's good design. Is it perfect? Maybe not, but refinement comes later. You're always going to learn something new, so don't worry about it. Just do the best you can today. You can figure it out later.
I've had this happen, too. If you feel like you built your tables kind of wrong and you think maybe there's a better way to do it, there might be. If you learn a better way, that's fine. You can always rebuild the tables later and move the data over with some update queries or whatever. There's always ways to massage your data to fix it. I had some really bad databases that I built when I first started out. Bad things like an order table with 10 product spots, because I didn't know how to do an order table with a related order detail table. So you got an order, you put up to 10 products. I'm never going to sell more than 10 things on one order. Until you sell 11. So you make 10 order spots on it. Then I had to learn how to take all that data from two or three years worth of orders (which wasn't much back in those days) and transfer that to properly normalized tables. You can do it. I think I even have a video on how to do that. But just do the best you can today. And keep watching my videos and you'll learn more.
Next up, one of my lifetime members, Michael, said that about my arrow key navigation, where I did the video that taught you how to use the arrow keys on a continuous form so you can go up, down, left, right. You can control that yourself with a little coding. He's like, yeah, it's under File - Options, Client Settings, Continuous Form Navigation, Record Keys, etc. You're absolutely right; it's in there. This was probably added, I don't know exactly when it was added, and honestly, I don't feel like Googling it, but I'm going to guess somewhere around Access 2007 or 2010. So, it's probably that I learned how to do it my way before the feature was in Access. I mention this because it's a great example of how Access evolves over time. They're constantly adding new features that make things you learn how to do obsolete. I'm constantly running into stuff that I missed that they've added since, and people come up and they're like, hey, what about this setting? I'm like, oh, that's new. So, I'm still learning stuff myself.
Access is still a living, evolving creature. It might be slowly evolving like the rest of us, but it's definitely still being worked on and improved upon. All that being said, learning how to control the keys yourself is still very valuable. Some users like that behavior, some don't. Hard-coding it into client settings that affect your entire application could be a good or a bad solution depending on how you look at it. If you handle it in code, you can turn it on or off per form, per control, even per user. You can use the built-in option if it fits your needs, but I like to be able to control the keyboard behavior myself so it gives you flexibility for custom situations. So, thanks for bringing this up, Michael. And to anybody else: if you find something that I don't know, I want to hear about it. I love learning new stuff all the time. My students teach me new things constantly. I don't always have time to play with the new versions, and I don't always upgrade to the latest version of Access myself. I intentionally use an older version of Access because I don't want to have to roll back again like I've done a billion times. I want my databases to run stable, so when I'm teaching, I don't have to worry about fussy behavior. Like the copy-paste issue that's still annoying me.
All right, let's head over to YouTube. First up, we got Nick commenting on my Copy Web Page Data video that I talked about earlier. In the extended cut, I talk about some different ways that you can take that data that you've pulled off of Amazon or Walmart or whoever's web page, you brought it over into Access. Now how do you get the product data out of there? How do you get the price that you want? Well, you could spend all kinds of time figuring out how they've structured the text and look for keywords and all that. I do that in my account balances database. We look for certain keywords like "posted" or "available balance" or whatever. Then we find two landmarks in the text so we can pull data out from between. Or, one of the techniques I show in the extended cut, you can just use AI. You can give that text to AI and say here, I want you to find the price in this big page of data. The last price was this. Look for something probably close to that. That is exactly one of the things that I cover in the extended cut.
So, very good. That's where AI is a great tool. I don't use it for writing whole programs, maybe sometimes a specific function, but AI is great for taking a whole bunch of data and structuring it for you. Like I'll copy and paste a whole bunch of text and say, can you structure this for me in a comma-separated, one-item-per-line format? It's great at stuff like that. Or finding data in a big page of mess. I have it clean up emails because I have it pull in emails directly from my mail server and there's usually all kinds of weird text and stuff around it or the line breaks aren't right. I just send it to the AI and I say, hey, clean this email up for me so I can read it. There you go. Perfectly readable text. I do the same thing sending emails back. I tell the AI, reply to the customer, tell him that his membership is good until the 30th, and if he wants to renew, go ahead and it'll write the email for me. I don't have to type it out or read it word for word. Those are the things that AI is great for.
Next up, I got a form with a subform in datasheet view. It keeps changing the record to #Name or #Name? in every row in every column every time I press Escape key several times. That's weird. This is almost always a broken expression somewhere. You got a formula with a problem in it. A #Name error usually means that Access can't resolve a function, a field, a control name, a calculated field, something like that. Whether you're in datasheet or continuous forms, the error is going to show up in every row because it's recalculating constantly and pressing Escape just triggers it again. So compile your VBA, check any calculated fields, check any query fields, check any controls for renamed or missing references. I do have a video here that talks more about the dreaded #Name error. Go check this out.
My personal preference: I like to use continuous forms instead of datasheet. It shouldn't change what you've got here, but I feel like you have more control over it. The only thing that datasheet view is better for is that you can more easily resize the columns, which you can still do with some tricks in continuous forms, but it's easier in datasheet view. I find that continuous forms are a whole lot easier to work with. I hope that helps.
Next up, EasySpeak 101: be useful to either connect to another type of records or, if not allowed, access and scrape data off web pages, which are several linked pages deep for one record. I think I understand what you're asking. The short answer is to always use a proper data connection if one exists. An API for a website is best. If they give you a programmatic way to talk to it, like OpenAI does, I've done several videos on that. Or, if it's your database, an ODBC connection or a direct data feed. One thing I tried to stress in that video is web scraping should be a last resort when there's no supported or easy way to get the data. I'm just using it to pull a price off of Amazon's page or Walmart's page for my weekly shopping. I'm not scraping their whole database or trying to pull down 10,000 products. I want to know what bananas and blueberries cost today. That's the kind of simple stuff. Scraping works for simple cases, but once you're dealing with pages that are several links deep or extremely dynamic content, it gets fragile very fast. I show the scraping technique in this video because sometimes it's the only option. The big websites like Amazon, they're not going to mind if you scrape 20 or 30 pages to get some product pricing. You're going to do that by hand anyway. I'm going to order these 10 or 15 things; I'm going to do the same thing manually. It's just the same as if I was sitting there at the browser. But if you can connect to the data source by a vendor-approved method, that's always better. That's a better long term solution, too. This is just something to get you by when you can't.
All right, next up, I get comments like this a lot. This user's asking if I can build a custom Access database for a real estate booking and inventory company, including invoices, letterhead, logos, printable booking forms, basically a complete ready-made solution.
Again, this comes up a lot, but I want to be clear: I don't do custom consulting anymore. I don't build databases for hire. If your goal is to learn how to build this yourself, then that's exactly when you come to me. I have thousands of hours of Access lessons and developer-level courses on my website. And yes, after going through my course, you could absolutely build a system like this on your own. Now, if you want someone else to build it for you and give you a finished template, then that's where you want to check out my Access Developer Network where you can hire individual developers. There are lots of developers that you can hire on this page, including my friend and associate Sammy Shama, who runs Shama Consultancy. He's got a lot of developers working for him and they do fantastic work. I've referred a lot of business to him. Lots of other Access veterans and consultants and people on this page, you can check them out. That's where you go for help.
That's not to say that I don't take projects once in a while and turn them into video series, like my Accounts Payable database, my Calendar Seminar, a lot of the cool projects and seminars that I have on my website started off as client requests. In fact, a few of them were from back when I used to do custom consulting jobs, and I'm like, you know what, I'm going to build this for you, but I can also turn this into a seminar. If you're cool with that, talk to the client, maybe give them a little break. I'm building it for them if I can turn it into a video too.
But my plate is very full right now. I've got lots of other stuff I'm working on. I still have to finish the fitness database, still working on my ABCD, still working on my Access SQL Server seminar or course that I'm putting together. So I have way too much going on right now to start something new, plus keeping up with my daily TechHelp videos. I'm a busy person. So talk to Sammy or one of the other guys on that page if you want help building a database.
Next, we got PublicLips or is it PublicClips or is it PubLipClips? I don't know. That reminds me of the website Experts Exchange. If you change it a little bit, it's... never mind. Anyways, PublicLipClips says,
Another question is we have a main form on tab one (this is my tab control video), and a subform on tab two and a subform on tab three. I've tried updating the links with only a parameter error.
A parameter error tells me one thing: if you're getting the "Enter Parameter Value," that means you're asking for a field that doesn't exist, or it doesn't know where it's spelled wrong. Anytime you see "Enter Parameter Value," you've probably got something spelled wrong or you're referring to a field wrong or something like that. Watch this video for information on that.
With that being said, one of the things that I try to emphasize in that video is I'm showing you how to use tab controls, but I don't like tab controls myself. Access can do all that stuff that you mentioned, but with tab controls, I never liked tab controls. They were super buggy when they first came out many moons ago, and I just never really warmed up to them. They're awkward to code around. Parameter errors are even worse when you start putting subforms on tabs inside of forms. It's just crazy to refer to them. It's probably why you're getting the error: Access wants that in a specific way. It's like referring to a field in a subform: Forms!formname.Form!subformname, it just gets crazy. In this video, I teach you how to get the value from an open form, but if you're trying to get a value from a subform, here's a regular form. If you're looking for a subform, it's that. And a tab control can make it even more difficult: Forms!parentformname!subform.Form!field. It's a nightmare.
So, I don't like using tabs, especially when you try to refer to controls on them.
Now, what do I prefer? I prefer using a single subform object and then just swapping out what subform is displayed in there. You can do it with little labels or buttons or whatever you like. I show you how to do that in my Association video series, Part 8. What's Association? Well, it's making a database to store association information, whatever kind of associations or groups. I tried to keep it a generic name, but it could be for your membership, your club, your church, whatever you want to store information for. It stores information on people, families.
Here's the little subform object thing, and there are little buttons here that you can click on to go to contacts or demographics. Instead of dealing with tabs, I use these. I developed this system a long time ago, and it's so much better. It just swaps out the subform that is displayed in here, and you get multiple subforms. It's much, much easier to work with, I think, but that's just me.
Next up, we got RoadsterLover Media. See, there's no ambiguity with his name because he capitalized all the right letters. Again, talking about my Copy Web Page video. This video did spawn a lot of comments, so thank you. I love when you guys comment on my stuff. He says, tell me how web scraping is not a copyright issue. Okay, I will. Tuning in for response in tomorrow's video. So, quite sure of yourself that you're going to make a video. Well, yeah, you did make the video. It's a good question. Can the same thing be done via the DOM (which means Document Object Model) to get away from SendKeys?
Let's address the copyright thing first. Web scraping by itself is not a copyright issue. Copyright is about how you use that data, not the act of reading or copying what's already publicly visible on a website. They want you to see that information. If you're scraping a handful of pages for personal use, for price comparisons or your own research, nobody cares. Amazon deals with billions of page views a day or whatever some crazy number. They don't care if you're scraping 20 or 30 pages so you can get a better price on blueberries on Saturday when you go shopping.
Where you would run into trouble is if you decided to scrape thousands or millions of their pages and then you decided you're going to redistribute or sell that data as a file that people can import in their databases and sell that as your own product. Then they'd have a problem with that. You'd get a knock at the door from some people in suits or a very kindly worded email: cease and desist. But that's a very different situation than what we're doing here. Amazon or Walmart, most companies, even I wouldn't care if you're scraping 20, 30, 40 pages, no big deal.
As for the Document Object Model instead of SendKeys, yes, that can definitely work and it can be cleaner, but it's also much more fragile. You're relying on element names, IDs, page structure, and that can change any time. As I mentioned in the video, they do tricks where they have a placeholder for an ID for where the price is going, and then after the page loads in the browser, they use JavaScript to background read it and stick it in there so that people that are trying to do what we're doing with code can't see the value. They have to wait for your actual web browser to finish loading the page, which is why my method works because it waits until the page is downloading. So the simple "grab the page text" approach that I show: it's less elegant, but it's also more durable long term. It may not be perfect, but it's far less likely to break six months from now when Amazon decides to change the page structure just a little bit.
So I hope that this answers your legal concerns and addresses your DOM question. Why such a DOM question? And I hope this reinforces why my approach, even though it's not the best. I'm not a huge fan of SendKeys, but like I said in my other video, it's the king of the "good enough" functions. When it works, it's great. I wouldn't use SendKeys for anything like inside of Access that you can program yourself, or even other Microsoft Office applications like Word and Excel, where you can get to their application programming level. You can do automation, you can control a document, you can control a PowerPoint presentation or a spreadsheet. Use SendKeys when there's a better alternative, but for scraping text off of a web browser, I think this is fine for a page. It's not simple. Oh, someone else is beaming in.
Now, the only reason I resorted to this was because I tried the better methods first. I tried using just an XML HTTP pull and that doesn't work because the price doesn't exist as I showed in the first video. You can't use the Edge browser either; same thing. You got to resort to what you got to resort to. So I'm all about adding tools to the box. Give me another tool for my toolbox. I'll do what I got to do to get what I need to get. I'm saving that seven cents a pound on bananas whether they like it or not.
All right, before you go, don't forget Access Day 2026 is officially on the calendar, March 27, 2026 in the Redmond, Washington area. I went to last year's event, had a great time, lots of solid presentations, some great conversations, and even some folks from the Microsoft Access team showed up to talk about what they're working on. Just to be clear, I'm not a presenter. I'm attending like everyone else, hanging out, learning, and talking Access. Armin is still finalizing the speaker list and they're also planning an optional dinner afterward so everyone can get together and talk nerdy Access stuff. So if you're in the area or you can make the trip, definitely mark your calendar. I'll share more details as I get them.
Make sure you stop by my website to check out what's new. I put lots of stuff on the website that doesn't necessarily show up here on YouTube. So if you want to get all the good goods, this is where it's at. Don't forget to get on my mailing list. YouTube used to send out email notifications whenever creators created new videos and stuff, but they don't anymore. So if you want to make sure you don't miss anything, get on my mailing list. It's free. Sign up.
Don't forget to check out my Captain's Log where I post my thoughts about whatever I happen to be thinking that day. Sometimes it's about Access, sometimes science, sales, whatever happens to be running through my brain. Yes, I did a money management project post and a backup day post and I used the same image. I just had the AI change what's on the background screen behind it. Check it out.
All right, head over to the merch store. Get your sweatshirts and your hats and your mouse pads and all that cool stuff. If you have a copy of my Access book, it's on Amazon: go get one. Read it on your Kindle.
Before we wrap up, big ideas from today:
Tab controls look good, but they often create more problems than they solve. There are cleaner and more flexible ways to handle multiple views in Access.
SendKeys has its place, but Windows security dialogs are hard to work with, like database logon prompts. Don't try to use SendKeys for that kind of stuff or for websites that you can use an API to get their data. Use that instead.
#Name error is almost always a broken expression.
Cloud sync is not real backup. Make sure you verify your backups.
When it comes to database design, there isn't always one perfect answer, one classroom textbook way to do things. It's whatever works best for you. Like I always say, you want to not store things in your database tables that you can calculate on the fly, but there are exceptions to that. If you're running reports more than you're putting data in the table, you might want to just store the calculated value. I have whole videos on that topic too.
As always, post a comment down below. Let me know what you thought about today's video, what helped you the most, what you'd like to see covered next. These Quick Queries videos are all about your comments. I take the best ones or the ones that I happen to read and that's what goes in the video. If you want to get into video, post a comment.
But that's going to do it. That's going to be your Quick Queries video for today, brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you next time.Quiz Q1. Why does the instructor recommend avoiding tab controls in Microsoft Access forms?
A. Tab controls can restrict user navigation in forms.
B. Tab controls often cause coding complications and parameter errors.
C. Tab controls are not supported in newer versions of Access.
D. Tab controls limit the number of subforms you can use.
Q2. What is the main advantage of swapping subforms with buttons or labels instead of using tab controls?
A. It makes the form look more professional
B. It allows displaying more data at once
C. It provides a cleaner and more flexible technique for handling multiple views
D. It reduces the amount of form objects in the database
Q3. What common mistake did Maurice make in his DLookup statement?
A. Using an incorrect table name
B. Forgetting to use string quotes around the criteria argument
C. Referencing a non-existent field
D. Using a numeric value instead of a string in the Expression parameter
Q4. When does SendKeys NOT work reliably in automating tasks?
A. When automating Access forms
B. When populating data in a web browser window you launched
C. When trying to interact with Windows security dialogs or login prompts
D. When navigating inside Excel workbooks
Q5. What is the primary reason cloud sync tools like OneDrive or Google Drive are NOT considered true backups?
A. They use too much storage space
B. They only work with Microsoft products
C. They only keep the latest version seen, which may cause data loss if files are not synced properly
D. They are slower than local backups
Q6. What approach does the instructor recommend for database design when there is no single right answer?
A. Follow strict textbook normalization regardless of needs
B. Always optimize for performance first
C. Get something working that you understand and meets current requirements, then refine later
D. Avoid using any calculated fields
Q7. What is the likely cause if you see the "Enter Parameter Value" prompt in Access?
A. There is an incorrect or missing field or object reference in a query or form
B. You have too many tabs in your form
C. The database has exceeded its record limit
D. There is a formatting issue with dates
Q8. What is a #Name error in Access usually caused by?
A. Corrupted database files
B. A broken expression or incorrect reference to a control, field, or function
C. Incorrect user permissions
D. Having too many forms open at once
Q9. What is the recommended way to obtain data from another website if possible?
A. Scrape the web page every time you need to update
B. Use the SendKeys function to automate copying the data from the browser
C. Use a vendor-approved method like an API or database connection
D. Manually type in all the data
Q10. According to the video, what is the best use case for web scraping?
A. Large-scale data extraction from websites with dynamic content
B. Pulling a limited amount of public information for personal use or research, like current prices
C. Building and selling a commercial product from scraped data
D. Creating website backups
Q11. Why can using the Document Object Model (DOM) approach for scraping web data be problematic?
A. It is illegal in most countries
B. DOM element names and structure can change frequently, breaking your code
C. It cannot extract text data at all
D. It is slower than using SendKeys
Q12. What is the key advice regarding database design improvements over time?
A. Never change your database structure after the first design
B. Learn and improve as you go; do the best you can today and refine as you learn more
C. Outsource all design work to a professional
D. Avoid using subforms to keep it simple
Q13. Why does the instructor prefer handling keyboard navigation in code rather than using global client settings?
A. It allows for form-by-form or control-by-control customization
B. It is easier to maintain
C. It makes the application run faster
D. It disables default Access navigation features
Q14. What do keyboard shortcuts like Ctrl+semicolon and Ctrl+apostrophe help you do in Access?
A. Save the form or report
B. Insert the current date or copy values from the previous record
C. Open a new blank form
D. Refresh table connections
Q15. What is the instructor's general approach toward achieving a balance in database design?
A. Strive for perfection before going into production
B. Build a working solution first, then consider optimization and scalability later
C. Avoid performance considerations
D. Use as many calculated fields as possible in all tables
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-A; 8-B; 9-C; 10-B; 11-B; 12-B; 13-A; 14-B; 15-B
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, we're tackling some of the most common and quirky issues that come up in Microsoft Access, especially when it comes to tab controls and database usability. I'm Richard Rost, your instructor, and I'm here to walk you through some smarter ways to structure your forms, approaches to backup, authentication headaches, and plenty more.
To start, let's look at why tab controls in Access can often become more trouble than they're worth. If you've ever tried to build a form with a bunch of tabs, you may have quickly run into a mess of confusing references, awkward code, and buggy behavior, especially once subforms are in play. Instead of relying on tab controls, I prefer to use a single subform object and swap out the displayed content using buttons or labels. This approach is less error-prone, easier to maintain, and practically eliminates many of the annoying problems with subform references and parameter errors.
Now, let me address a handful of the questions that came in through YouTube, website forums, and emails:
First off, Maurice struggled for hours with a DLookup statement, only to realize—thanks to a video screenshot—that he had neglected to put quotes around his criteria. DLookup expects its expression and criteria parameters as strings, and forgetting the quotes results in a type mismatch error when the code runs. It's easy to overlook something like this, but it happens to all of us from time to time. The more you see and think about such details, the less likely you are to repeat the mistake.
Next, Gary wanted to automate a file import but was getting hung up on using SendKeys to interact with ODBC login prompts. The key issue here is that Windows intentionally blocks SendKeys from interacting with security-related dialogs to prevent malware from automating logins. While SendKeys can be handy for automating your own forms or browser windows you've opened yourself, it's simply not going to work with Windows login prompts, ODBC authentication, or other protected dialogs. The right solution is to handle authentication directly by supplying credentials in your code, saving them in a DSN, or using connection strings. Don't try to circumvent Windows security with SendKeys—find a clean authentication workaround instead.
I also showed off a few of my favorite keyboard shortcuts. For example, Control+semicolon inserts today's date, Control+Shift+semicolon adds the current time, and Control+apostrophe copies the value from the same field in the record above. Several community members chimed in with their own favorites, like Control+comma and Control+period to switch views, F11 to show the Navigation Pane, and Alt+F11 for the VBA window. Keyboard shortcuts can really speed up your workflow, and I enjoy collecting and sharing new ones.
Donald shared a cautionary tale about backups. Despite making multiple local copies, saving to USB drives, and relying on cloud sync with OneDrive and Google Drive, a Windows update undid a day's work by rolling the database back a week. The moral here is clear: don't just assume your files are backed up—verify it. Cloud sync tools are not true backups, and unexpected reboots can lead to lost work. Ever since I experienced my own hard drive disaster years ago, I've adopted a robust backup routine that runs automatically, uses tools like Macrium Backup, and includes both local and cloud-based copies. If you're interested in learning more about comprehensive backup strategies, let me know and I'll consider making a more in-depth video series.
Ben asked a classic database design question: when faced with multiple possible ways to structure your tables, how do you know which is the best? The truth is, there's rarely a single "right" answer. Good database architecture is a balance between theory and practical needs. My approach is to implement a solution that works, even if it's not perfect, and refine it as I learn better techniques. Over time, you'll discover improvements and be able to correct your structures—don't let the pursuit of perfection prevent you from making progress. In Access Developer 25, I cover how to model products made from other products, such as bundles, kits, or assemblies using self-referencing many-to-many relationships. I also touch on similar ideas in my fitness database series, where individual food items are bundled into meals. If your current design works and meets today's requirements, that's good design. You can always adjust later as your needs change.
Michael pointed out that Access added an option in Client Settings for handling continuous form navigation with arrow keys. This is a great example of how Access is an evolving tool and why it's important to keep learning. Sometimes features get added that make previously complex programming obsolete. Even so, knowing how to handle navigation in code can give you even more flexibility for custom scenarios.
I also received many questions about web scraping. In a recent video (and its extended cut), I explained practical ways to extract data from web pages when APIs or direct data connections aren't available. For simple use-cases like checking prices or copying a handful of records, web scraping is fine. Of course, if a proper API exists, always use that first—scraping should be your last resort. One user asked about the legalities of scraping; generally, simply reading data from public pages for personal use isn't an issue. Problems arise if you're pulling huge amounts of data or republishing it, but for everyday tasks like copying prices for your shopping list, it's not a concern.
Sometimes, scraping can be made easier by using AI to find and organize the specific pieces of information you need from messy web page text. For example, you can ask AI to extract prices, clean up email text, or structure information for you, saving time over trying to parse everything manually.
Regarding database errors, if you encounter the dreaded pound name error (#Name?), it almost always points to a broken expression, such as a mistyped field or control name, especially in datasheet or continuous form views. When this happens, carefully check your calculated fields and queries for errors or renamed references.
I occasionally get queries about building complete, custom Access solutions for clients. These days, I don't take on consulting work, but my website offers a wealth of developer-level courses that can teach you to build such systems yourself. If you'd rather hire someone, check out the Access Developer Network page on my site for skilled professionals who can take on your projects.
Back to tab controls: I see lots of students running into parameter errors when using subforms on different tabs—usually due to confusing or incorrect references. This is one reason I'm not a fan of tab controls; they complicate your form design and often create more headaches than they solve. Instead, I recommend using one subform object and changing its source as needed. This system is easier to code, understand, and maintain.
To round out the legal and technical concerns, web scraping is not inherently a copyright violation; it's all about what you do with the data. Relying on the Document Object Model (DOM) is an alternative to SendKeys but is often more fragile, as page structures and element IDs can change. I stick to more durable approaches where possible, and resort to scraping and SendKeys only when better options are unavailable.
Before wrapping up, a quick reminder: Access Day 2026 is scheduled for March 27 in Redmond, Washington. It's a great opportunity to connect with fellow Access enthusiasts and learn from the best in the community. Make sure to check my website regularly for updates, get on my mailing list so you don't miss any announcements, and consider picking up my book or some Access-themed merchandise while you're at it.
Key takeaways from today's session:
- Tab controls often create more complexity than they solve. Consider simpler alternatives.
- SendKeys cannot automate Windows security prompts – use programmatic authentication where possible.
- Pound name errors are usually due to broken expressions or invalid field references.
- Cloud syncing doesn't guarantee a backup—always double-check your backups.
- Database design rarely has a single perfect solution; start with what works and refine as you go.
- When working with external data, use APIs or direct data connections rather than scraping, unless there's truly no other option.
As always, feel free to post your questions and comments. I'll continue to pull topics from the most interesting and useful comments for future videos. 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 tab controls in Access can cause issues
Cleaner alternatives to tab controls in Access forms
Proper use of quotes in DLookup criteria
Troubleshooting DLookup type mismatch errors
Automating file import with Access forms and timers
Limitations of SendKeys with Windows security dialogs
Alternatives to using SendKeys for ODBC logins
Recommended Access keyboard shortcuts
Continuous forms vs datasheet view in Access
Fixing Name errors in datasheets and subforms
Best practices for Access backups vs cloud sync
Database design decisions with no single right answer
Balancing database normalization and practical design
Refining Access database design as you learn
How to restructure Access tables as you improve design
Controlling arrow key navigation in continuous forms
Difference between code and built-in settings for navigation
Dealing with parameter prompt errors in subforms on tabs
How to refer to controls in subforms and tab controls
Using a single subform with buttons instead of tabs
Swapping subforms programmatically for better design
When to use web scraping for data import
When APIs or direct connections are better than scraping
Risks and limitations of web scraping complex sites
Legal considerations for web scraping
Pros and cons of Document Object Model vs SendKeys for scraping
Finding and fixing broken expressions in Access forms
Options for hiring developers to build Access databases
Referring to Access support networks for custom projectsArticle If you have been building Microsoft Access forms and are running into some of the common headaches, this guide will help you understand several practical aspects, from form design choices to debugging and automation strategies. Let's get into some real-world Access advice and examples that many users—even experienced developers—come across.
One issue that comes up for almost everyone is trouble with DLookup statements. For example, if you try to use DLookup without wrapping the criteria argument in quotes, it won't work as intended. DLookup expects strings for both the "expression" (the field to return) and "criteria" (the condition for finding the record). If you forget to use quotes, Access will throw a type mismatch error when you run your code. Here is the general structure you want:
DLookup("FieldToReturn", "TableOrQuery", "CriteriaString")
For instance, if you want to check if a username and password exist:
DLookup("ID", "Users", "UserName = 'john' AND UserPassword = 'secret'")
Notice how the condition is a single string in quotes. Even if your code compiles, you might get type mismatch errors at runtime if the criteria is not quoted right. Always pay careful attention here, and if you do get tripped up, remember you are in good company; mistakes like this happen to everyone.
If you are working with automating file imports or database logins, another pain point is trying to control login dialogs with SendKeys. SendKeys can be very fragile. For example, if you connect to another database via ODBC and a Windows logon prompt appears, SendKeys often fails because Windows treats these dialog boxes as secure "protected desktop" areas. Access can't interact with them, by design, to prevent security risks. SendKeys will only work on windows that belong to your process, like your own Access forms or maybe a browser window you launched, but not on protected dialogs.
The best way around this is to avoid triggering the prompt. Try to pre-save your login credentials or use connection strings that provide the username and password directly. There are alternatives, such as DSN-less connections or setting up authentication that does not require user interaction. In summary, SendKeys is not a good fit for automating Windows or database logins, and you will need to solve authentication through proper configuration, not by simulating keystrokes.
While working inside Access, keyboard shortcuts can save you loads of time. Some useful ones include Control+semicolon to insert the date, Control+Shift+semicolon to insert the time, and Control+apostrophe to copy the value from the field above into your current field if you are in a continuous form. Control+comma and Control+period switch between views. Control+plus moves quickly to a new record at the end of a continuous form. Knowing and using these shortcuts can speed up your data entry and navigation, and if you use Access regularly, these can become second nature.
Backup strategy is another topic that often gets overlooked until it's too late. Simply copying your database to a USB drive or relying on cloud sync platforms like OneDrive or Google Drive is not a true backup. Synchronization tools can sometimes overwrite recent work if they have not finished copying or sync an older copy if a process is interrupted, such as by a Windows reboot. To be safe, set up dedicated backup software that runs on a schedule, verifies the backups, and ideally stores them in multiple places (local and cloud). Always check your backup files manually now and then to ensure they are valid and recent. Recovering from a hard drive failure or accidental deletion is much easier if you have reliable backups.
A common and important question for Access and database design in general is: "What is the right way to structure my tables?" The truth is, there is rarely only one right answer. Database theory can guide you to best practices such as normalization, but real-world needs might lead to practical compromises. Start by building a structure you understand and that functions as required. As you use your database, you will learn more about what does and does not work, and you can refine your design over time. The key is to implement something workable, then review and optimize it after you have verified it performs and is maintainable. For example, if you are tracking products that you manufacture, sell, or bundle (such as computer parts in kits), you will likely implement a self-referencing many-to-many relationship to model product bundles. This idea pops up in many systems, such as bundling inventory parts, grouping food items into meals, or creating kits in sales systems, and it is an example of learning and refining your structure to match your needs as you gain experience.
If you realize later that your tables could have been designed better, it is always possible to move data to new, restructured tables using update or append queries. As your knowledge grows, so will your ability to adjust and improve your system. Do not let the pursuit of perfection stop you from getting a working solution today.
Many Access users run into issues with keyboard navigation and form behavior. Some features are built into Access now, such as continuous form navigation options found under File > Options > Client Settings. These settings control things like how arrow keys move between records. Still, controlling navigation and form behavior with VBA code gives you additional flexibility, as you can enable or disable features per form or per user instead of setting it globally in the client settings.
If you are troubleshooting errors like "#Name?", especially when they appear everywhere in a datasheet or continuous form, it is usually an indication that there is a formula or expression somewhere referring to a non-existent or misspelled field or control. Compile your VBA modules, check all calculated fields and queries, and verify that all control references are correct. Switching to continuous forms from datasheet view can sometimes help, because you have more control over layout and behavior, though datasheet view allows for easier column resizing.
Another important topic is web scraping. If you need to pull small amounts of data from web pages (such as prices for a few items), web scraping can be helpful. However, if there is any kind of official data connection, an API, or export option, always use that method first. Web scraping is fragile because web page structures change, and large-scale scraping can violate terms of use. However, grabbing a handful of public pages for personal, non-commercial use is generally fine and does not raise copyright concerns unless you are redistributing or selling the scraped data. For small, personal projects—like comparing prices—it is low risk, but you should use vendor-supported methods whenever possible.
SendKeys is sometimes suggested as a way to automate copying data from web browsers, but this is really just a workaround when better options (APIs, DOM access) are not available. It is less reliable and more likely to break with future website changes, but for one-off or simple repetitive tasks, it can sometimes do the job.
A final note on tab controls in Access forms: while tab controls appear to make organizing your form easy—putting different subforms on tabs for a tidy look—they can create major headaches. Managing references to subforms gets complicated, leading to parameter errors or broken navigation, especially as you nest forms or try to refer to controls deep inside tab pages. If you are seeing "Enter Parameter Value" errors, check your references and field names carefully. The recommended approach is to use a single subform control and dynamically swap out which subform is displayed based on button or label clicks. This creates a more flexible interface, is easier to manage in VBA, and dramatically reduces reference problems. Building your navigation with buttons or labels to show different subforms gives you full control and is far easier to debug than trying to wrangle a complex tab control.
If you ever need custom database work, know that many developers and consultants are available to hire, but if your goal is to learn, there are lots of online resources and courses where you can get up to speed and build your own systems step by step.
In conclusion, some of the big lessons for Access users are: avoid tab controls when possible and use subforms that you swap dynamically for easier management; do not use SendKeys for secured Windows prompts; always back up your work with dedicated backup tools instead of relying only on sync services; accept that database design is iterative and you will always discover better ways with experience; use keyboard shortcuts to speed up your workflow; and choose the best data retrieval method available, reserving web scraping for when there is no better option.
As always, keep learning, stay open to new techniques, and do not be discouraged by mistakes—they are part of the process, and everyone encounters them. Take what works for you today and improve it as you go. If you have questions, keep exploring, and reach out to the community for ideas and support. |
