Quick Queries #91
By Richard Rost
19 hours ago
Using Queries as Record Sources for Forms and Reports In Quick Queries 91, we answer a variety of Microsoft Access questions, including whether forms and reports should be based on tables or queries. We will discuss hidden controls in datasheet forms, the process behind new Access features, techniques for hiding the Access interface, strategies for sending and analyzing online questionnaires, and troubleshooting common errors like the Enter Parameter Value prompt. Additional topics include filtering objects in SQL Server, avoiding naming conflicts in VBA, and the current state of dark mode in Access environments. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, forms vs queries, hidden controls, datasheet forms, modernization, hide interface, online questionnaires, file path in subform, calculated fields, subforms, enter parameter value error, object explorer, filter tables, SQL Server backend, VBA variable name conflict, Me keyword, Google Forms import, dark mode, VBA editor themes, ACCDE packaging, create shortcuts in VBA
Intro In Quick Queries 91, we answer a variety of Microsoft Access questions, including whether forms and reports should be based on tables or queries. We will discuss hidden controls in datasheet forms, the process behind new Access features, techniques for hiding the Access interface, strategies for sending and analyzing online questionnaires, and troubleshooting common errors like the Enter Parameter Value prompt. Additional topics include filtering objects in SQL Server, avoiding naming conflicts in VBA, and the current state of dark mode in Access environments.Transcript Should your Microsoft Access forms and reports be based on tables or queries?
Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. Today, we are going to talk about that.
Plus, hidden controls in datasheet forms, Access modernization, hiding the Access interface, sending online questionnaires, and why Microsoft is adding some of the features that it is to Access, and lots more. We have got questions from my website for us, from YouTube, from Reddit, and from emails. Let's jump right in.
I just want to start off today with a quick mention. I posted this in my captain's log earlier today. I have seen some people complaining online. They are saying things like, why is Microsoft wasting time with Access on things like cascading combo boxes and form zooming when there are bigger problems to solve?
But here is what a lot of people do not realize. According to the Access team themselves at the MVP Summit, at Access Day, and at some of the other seminars they have attended, many of the features you see being released started as side projects during Microsoft's internal innovation week. They give their employees a week every year to work on side projects, whatever they think is interesting. So the developers get time to experiment with ideas that they think would be useful. Sometimes those ideas turn into really cool features.
So no, they are not pulling resources away from critical bug fixes, security updates, and compliance issues, which is what they spend most of their time working on. They are just building neat stuff that they have wanted for years and putting it into Access.
I put a link to the full captain's log below if you would like to read more. But the Access team is a small team at Microsoft, and they are doing the best they can. So let's try to give them some support and show them some love. Sometimes some of the best features start off as side projects.
Like this little thing I had an idea to do like 20-some years ago, which was doing little video tutorials on CD. Well, that worked out pretty well for me.
All right, heading over to the forums on my website. Tom, one of our gold members, wanted to attach receipt files to expense records in a subform. You need a way to store the file path with each record and then let users click something later to open the receipt.
Here is what he posted, something like that. There is a really handy trick that Donald, one of the platinum members, suggested: storing the receipt file path in a hidden control on the subform, even if it is a datasheet form. A lot of people do not realize that datasheet forms still have form headers and footers. You can use them to hold hidden controls that users cannot accidentally unhide.
Once you have the path stored, opening the receipt is easy. Just use FollowHyperlink. I have covered that a million times. Do not forget to make sure it exists.
Another thing Donald suggested is you can actually use the page header or page footer. He says he uses some code there to stop the form from being printed, which you can just cancel if the user tries to print it, but you can also hide stuff there. Remember, the page header and page footer do not show up in form view when you are looking at the form on the screen. They only show up when you print a form, which we never do. We only print reports. You can absolutely hide values there if you want to. I personally would just use TempVars, but I can use that too. It is another tool for your box.
Next up, Andrea, one of our gold members, is normalizing her database by moving mileage values into a separate mileage table. Her question was whether it is okay to base data entry forms on a query so she can calculate things like miles driven as she is entering records. Then she never minded it and said, I figured it out. My only question is now, do I base my form on the query now?
I chimed in a little bit. This is one of the biggest misconceptions in Access. A lot of people think forms should only be based on tables. In reality, many of my forms are based on queries. In fact, that is often the better approach. Either a query or you could directly base a form on an SQL statement too, which is essentially a query.
If your query is updatable (meaning Access still knows which table to write the data back to), then you can edit records and add records just like normal, and also display calculated fields at the same time. This is perfect for things like your mileage differences, totals, or other values that you do not want to store redundantly in the table.
Like in my TechHelp free template, we have an Orders table here, where we have a product, the quantity, and the unit price, and this guy over here is a calculated value. This form here, the subform--yeah, do not have exclusive access, that is fine--is based on OrderDetailQ. OrderDetailQ is this simple guy. It is just the OrderDetail table with that one calculated field in there. As long as it is simple like this, you can still edit this record. So now you have a query with all the same data as the table plus your calculated value. That is how I would handle it.
Just do not make your query really complicated with lots of other tables and joins, aggregates, and so on, or it will not be updatable anymore. But you are doing it the right way.
All right, Andrea, that is exactly what I recommend. If you analyze it first, then migrate one piece at a time, that is much safer than trying to rebuild the whole database in one weekend and hoping the warp core does not explode. Good luck.
Next up, we have Tony asking a question about SQL Server. In my SQL Server Beginner Level 1 course, which is free by the way (you should go check it out), he is asking if there is a way where there is an Object Explorer similar to Access where you can filter your tables and queries and stuff.
Of course, Kevin chimed in and there it is right there. Let me show you guys. Here is SQL Server, and this is the database that I am using in class. There is only one table in it so far, but let us pretend you had a whole bunch. You can just right-click on Tables, go to Filter, then Filter Settings, and then Name right here contains--just type in like CUST for customer, hit OK--and it will filter and just show you the ones where there is the word customer in or cust or whatever you type. It is really easy to do. It is just in different places than in Access. It is not like the navigation pane. It is a little more advanced, but once you know how to work around it, it is easy to figure out.
If you are curious about SQL Server and what it is, if you are SQL curious, check out this course. It is absolutely free. I walk you through setting up SQL Server, which is also absolutely free. It is a much better back end for your Access database. You do not have to lose your Access database. Just take your tables, migrate them up to SQL Server, and then you can continue working with your forms, queries, and reports just the way you have them now if you want. There is also lots of better stuff you can do, so check it out.
Next up, Monica, one of our gold members, added a new tab to a form and everything worked perfectly, except every time she closed the form, Access popped up one of those lovely Enter Parameter Value errors, asking for a field that definitely exists.
You have all seen this guy before. This is a great example of Access acting like it is possessed. The problem turned out to be a subform inside another subform that was still referencing her field BeadTypeID, even though it did not actually need that field. So when the main form started closing, Access tried to evaluate that reference one last time and could not find it.
One interesting thing to keep in mind when you are working with forms that have subforms, especially multiple subforms, is that Access builds the form hierarchy from the inside out. The deepest subforms are loaded first, and then their parent forms are loaded around them. That means once everything is open, a subform can safely refer to its parent because the parent is now fully available. When you close the form, the process happens in reverse. The parent form unloads first, and then controls on the child forms might still try to evaluate expressions, queries, or row sources one last time during their shutdown. If those expressions contain hard-coded references to something else on a different form, Access may no longer be able to find that form because it is already in the process of closing. That is when you get the infamous Enter Parameter Value prompt.
Using Link Master and Link Child fields is much more reliable because those references stay with the active form hierarchy instead of depending on a top-level form that could already be halfway out the door. I talk about this a lot more in this thread. I will put a link to this thread down below.
She eventually found the problem, and her debugging technique is spot on. She made a copy of the form and started deleting pieces until the error disappeared. I love that--exactly how you should do it. Just start slowly erasing things and see what happens. That is one of the best troubleshooting methods you can use in Access.
So if you want to read this whole thread, I will put a link to it down below. It is pretty fascinating. Of course, I have a full video on the Enter Parameter Value pop-up. That is usually because someone typed something in wrong, or you copied and pasted something and it does not exist on the new form, or you deleted something you needed. I would say nine times out of ten when I see people complain about this, they spelled something wrong.
One thing you should not spell wrong is the word like. So make sure you hit that Like button right now and subscribe to my channel if you are not already. That helps me, helps the channel, helps more people find my videos, and I appreciate it. Thank you.
Next up, Chuck, one of our silver members, was trying to display the current computer and Windows username in a text box using the environment variables. I have covered this in a few different videos. But no matter what he did, the text box stayed blank and Access kept getting confused about whether ADUser was a variable or a form control. This is a classic beginner VBA gotcha. A local variable and a text box are two completely different things.
For example, if you have got a field on your form, a text box control called FirstName, and then somewhere in your code, you dim FirstName as a string in that subroutine or function, if you say something like FirstName = "Rick", that is going in that variable. It is not going to show up on your form.
This is one of those cases where you need to specifically use the Me keyword. You could say Me!FirstName = "Joe", and that will show up on the field. That is how Access can differentiate it. But the more important thing is, try not to dim variable names that are equal to your form field names. You could call the string value something like "ValueString" or call the field name "Text" or something different to differentiate them.
In fact, in the thread, that is one of the things I suggested: dim it as "UserText" or something. Fortunately, we got him down and it works perfectly now. Sometimes just starting over works. Sometimes you just get a bug somewhere in there, and it is best to start over. Backing up is important and having restore points is important.
I do not know how many times I have pulled my hair out over a specific problem and decided to start over from scratch and do pretty much the same thing, but then the problem went away. No clue why. Sometimes bugs disappear with a fresh start.
Heading over to Reddit now, I stumbled across a Reddit thread in a very, let's say, anti-Microsoft subreddit where someone asked if there are any real alternatives to Microsoft Access. Naturally, I had to put my flame-resistant suit on and I jumped right in. Even in a group dedicated to criticizing Microsoft, several people admitted that there really is not anything that matches what Access does.
If you need to build real Windows business applications with forms, reports, automation, and a good database behind it, and you want to do it fast, Access is still in a class by itself. Sure, there are alternatives, SQLite, FileMaker, web apps, but none of them hit that same sweet spot of power, speed, and easy development. Access just quietly keeps doing its job while everyone else argues on the internet.
So, yeah, hate Microsoft all you want, but Access has been cranking out databases and running companies for 30 years. Every time I see something like this online, I am like, just go sit down.
Moving over to YouTube now, I bring up my "Changing Clocks Is Still Dumb" video all the time with Access-related stuff because it is database-related too. I started this because I hate changing clocks twice a year, but it is also a nuisance for databases. If you have to calculate time differences and it happens to go over that 2 a.m. time shift, you have to be able to deal with it. I have done several videos on that, but I am glad people still agree with me and comment on my daylight saving stance.
Anyway, this person says statistically more violence occurs in the winter. With more darkness comes more violence. I do not know if that is true, but I will take your word for it. That sounds like one of those stats that should be right. It feels right. The amount of SAD, seasonal affective disorder, goes unnoticed because--yeah, when I used to live in Buffalo, I used to have really bad SAD, seasonal disorder, during those dark winter months. When you see the sun for, what, four hours a day, and even then it is behind thick gray clouds. I was always borderline depressed up there. I did not realize it. I had super bright lights in my office that I would crank up during the day just to keep myself from being in the dark the whole time.
One of my arguments is I can kind of understand up north where this makes a little more sense when you want to shift the daily hours. But down here--I live in Florida, much closer to the equator--we do not need it down here, and we have already voted as a state to get rid of it, but it is still sitting in the do-nothing Congress. Anyway, I could go on, but thanks for the comment. I appreciate that, and I agree.
Next up, we have Hamid asking how to hide the Microsoft Access Windows, so users only see your application and not the Access interface running in the background. Well, yes, of course, Hamid. A lot of developers do this to brand their databases as their own or because some clients still think that Access is just a toy and not a real database.
There is only so much you can hide, but you can make it look very professional. In my "Hide Access" series, which is two videos, I show you how to replace the icons, create a splash screen, hide the navigation pane and the ribbon, minimize the Access window, and basically package everything into an ACCDE file. To most users, this will look like a custom application, and they will not even realize it is running Access.
Now, obviously, someone who knows what they are doing can still go into Task Manager and see MSACCESS.EXE running. You cannot get around that, but you can hide it to most users who are not computer experts. You can make it look like it is your own, so go check this video out.
Next up, Sanch is talking about my "Access Not Opening" video that I just did a couple days ago. It is where you double-click on an Access shortcut or the database file and it just does not open. That is because the shortcut or the file you are trying to open is just a link to the database ACCDB file itself, instead of running MSACCESS.EXE, which will then open the database in its own process. It is a very fascinating video--watch this. I will put a link to it down below.
He wants to know if I can do a second developer part to do it programmatically. I am not sure what you mean by programmatically. If you are talking about creating those special shortcuts with VBA, then yes, that can absolutely be done. You can create a Windows shortcut that just points to MSACCESS.EXE and then passes your database file as the argument. That will then force it to open in its own Access process.
If you mean detecting or controlling Access process behavior after it is already running, that is a lot trickier. It is still possible with some Windows API scripting, but I do not know if we need to go into that much trouble. If anyone else wants to see how to do this--how to create those Access shortcuts in VBA--let me know. I will put a separate video together on it. Just post a comment down below.
Next up, we have Stateless Atheist, who says if Microsoft modernized it more, it would stop more people leaving it. I hear this all the time. Honestly, I think Microsoft has a hidden gem in Access that they do not give enough appreciation to. For building desktop business applications, it is incredibly productive. If they put a bigger team behind it and invested more heavily, it could be an even stronger platform.
That said, you still do not have to abandon Access just because your company wants web or wants mobile apps. If Access is your desktop front end, move your data to SQL Server, and then connect whatever modern tools you want on top of that. That is exactly the direction I am going to be focusing on more in the future. I am not leaving Access anytime soon, but I am going to be doing a lot more with SQL Server, getting your tables up into SQL Server, and then you can attach whatever other apps you want to it. That is where the real power is.
As I said at the top of the video, the Access team is small and they are doing the best they can to just keep up with bug fixes and compliance issues. But if Microsoft is listening--yeah, invest more heavily in Access and you can have a fantastic platform. You have already got millions of people using it to run their businesses. Focus more there.
I get emails all the time from people saying, "We really love your videos, but our company is moving into this online platform or to that app and blah, blah, blah, blah, blah." It is like, no, just keep Access, but just add more stuff to it.
Next up, we have Kartik asking two great questions. He is talking about my questionnaire video that I did. He asked, how do you send a questionnaire to other people, and once they fill it out, how do you analyze the results?
This is the video he is talking about. This one was all about just managing questionnaires on your computer. It had nothing to do with sending it to other people, unless you have other people using your Access database on your network or whatever.
How do you send it to other people? There are a lot of options. One thing you could do is, if you want to send it out to customers or employees, I usually recommend Google Forms. I did a video a couple of years ago on how to use Google Forms. You email them a link, they fill it out online, and then you import the results right into Access or Excel for reporting and analysis. That is the easiest way if you want to send a questionnaire out to a bunch of people.
Your other option is to set up SQL Server online, and then you can build a data entry form in whatever your web design language of choice is. I use ASP and basic HTML and CSS. I have a full seminar on setting up Access to work with SQL Server. You can even build some simple web pages and stuff like that to do the survey online. That is always an option.
But the short answer is: collect the data however you want. You can even send out just emails with plain text in them, as long as it is formatted right. Just send out a plain text email with the fields that you want and tell your users to type in their answer right next to it. Then you can just read that email back in or copy and paste it into Access, and then you can analyze it that way. There are a million ways to do it. But the short answer is: collect the data however you want, and then let Access do what Access does best, which is crunching the numbers and generating reports.
Finally today we have Fuego Esmeralda: "What happened to dark mode on the code editor? And did you speed yourself up for a bit there?" Let me answer your second question first. Sometimes for the vertical videos that I do (the shorts), if I have got a regular video that is like three and a half minutes long, I might run it forward at a faster speed, like 1.1x, just so I can get it under that three-minute window. That is what YouTube cuts off shorts at.
If you do a cell phone format and you know, vertical video and you want to make it a short, you have to have it under three minutes. Once in a while I will speed those up a little bit. I try not to do it too much so I do not sound like a chipmunk, but that might be what you are hearing. Every now and then I forget to turn that setting off in my video recorder or the editor that I use. So you might hear a segment that is slightly faster, but I do not think I have done that lately.
Now for the first part of that: What happened to the dark mode on the code editor? I did a video a little while ago about making dark mode in your Access databases. I focused primarily in the TechHelp video on just the database itself. We made it so we could switch our forms and stuff between light mode and dark mode. Windows already has a light mode and dark mode. Office already has a light mode and dark mode and you can switch those yourself. But what about the forms themselves in your database? That is what that video focused on.
Now, Fuego is asking about the code editor. I did not cover the code editor in that video because I covered it in this video. I got to "Switch the VBA Editor Themes." This is actually a three-part series and you can load and save those as well. So you can easily combine them at the same time if you want to. This actually turned into three whole developer classes.
In these developer classes, I cover setting up the dark mode for your forms. We make it so you can click one button and it will set all your forms to dark mode. It will set Windows to dark mode, including your taskbar, your background, all that stuff. Whatever other Windows applications also listen to dark mode will switch to dark mode, like your browser and so on. Microsoft Office's theme will switch to dark mode. Your VBA editor will switch to dark mode. It is all built in. We start covering that in Access Developer 57. If you want a complete dark mode system, check that out. I will put a link down below.
Before you go, do not forget to stop by my website and check out what is new. I am always adding new videos, updates, templates, and random bits of Access Goodness from time to time. It is worth a quick look to see what you might have missed.
Do not forget to check out my captain's log where I post my thoughts about whatever, including that article I mentioned earlier about the best features that started as side projects. I did a couple of articles on AI and how it has hit a knowledge wall lately. That is a pretty interesting read. Also, how overreliance on AI can bite you, even if you are a penguin.
Do not forget to stop by my merch store, pick up a cup and a mug and a t-shirt and all that good stuff. Grab a copy of my book on Amazon. Stop by my forums if you want to post a question. Yes, there is a visitor forum where anybody can post a question. If you need help, check out my Access Developer Network, lots of consultants, developers, and tutors. Get on my mailing list so you get notifications whenever I release new videos.
Today we learned when to use queries as form record sources, how to hide the Access interface, how to collect survey data online, and lots more. Post a question down below. Let me know how you liked today's video and what questions you would like to see answered in next week's quick queries.
That is going to do it for your Quick Queries video for today, brought to you by AccessLearningZone.com. I hope you learned something.
Live long and prosper, my friends. I will see you next time. Enjoy your weekend.Quiz Q1. According to the video, what is often the best choice as the record source for an Access form?
A. Query, if it is updatable
B. Only tables, never queries
C. SQL Server tables only
D. Excel spreadsheets
Q2. Why does Microsoft Access sometimes get new features like cascading combo boxes and form zooming?
A. Microsoft is ignoring more important issues
B. They are side projects from internal innovation week
C. External contractors demand them
D. User complaints force Microsoft to add them immediately
Q3. What is the main benefit of basing forms on updatable queries rather than tables?
A. Allows for calculated fields and data entry in one place
B. Queries always run faster than tables
C. Tables cannot be linked to reports
D. Forms based on tables cannot be used for data entry
Q4. What can prevent a query from being updatable in Microsoft Access?
A. Having many calculated fields
B. Using too many text fields
C. Overly complex queries with multiple joins, aggregates, or non-updatable sources
D. Saving the query with a short name
Q5. How can you store hidden values or controls in a datasheet form?
A. Using form headers and footers
B. Hiding fields in table design view
C. Locking the form for editing
D. Disabling all controls on the form
Q6. Why is it better to use Link Master and Link Child fields for subforms in Access?
A. They update references automatically as the form hierarchy loads and unloads
B. They allow you to skip table normalization
C. They disable all validation rules
D. They prevent Access from ever closing a form
Q7. What troubleshooting method was recommended for fixing form errors like the "Enter Parameter Value" prompt?
A. Delete and recreate the database
B. Copy the form and delete pieces until the error disappears
C. Rename all your fields randomly
D. Uninstall and reinstall Access
Q8. When using VBA, how can you prevent confusion between variable names and text box controls?
A. Use the Me keyword to reference controls
B. Always use Option Explicit
C. Never use variables in Access
D. Only use numeric variables
Q9. What is a good method for sending questionnaires to users outside your Access network?
A. Use Google Forms to collect responses and import results
B. Mail paper surveys
C. Use only built-in Access reports
D. Require users to install Access on their computers
Q10. What is a primary advantage of moving your Access tables to SQL Server?
A. It provides a more reliable and scalable back end for Access
B. SQL Server automatically upgrades your forms
C. Only SQL Server can produce reports
D. Access cannot read data from SQL Server
Q11. According to the video, why does Access remain popular despite criticisms?
A. It is fast, powerful, and lets users build complete business apps quickly
B. It is the only Microsoft product supported
C. Other database systems are obsolete
D. It is free for everyone
Q12. What steps can be taken to make an Access application look more professional and hide the Access interface?
A. Hide the navigation pane and ribbon, use custom icons, create a splash screen, and package as ACCDE
B. Change the table colors only
C. Use only datasheet forms
D. Require users to log in as admin
Q13. What is the function of the Me keyword in Access VBA code?
A. To refer to form controls directly
B. To change the database password
C. To back up the entire database
D. To delete variables from memory
Q14. What general advice was given regarding trying to fix persistent bugs or errors in Access?
A. Sometimes starting over fresh resolves strange bugs
B. Never use backup files
C. Only work online; never local
D. Reinstall Windows
Q15. For implementing dark mode in Access, what did the video mention about VBA code editor themes?
A. There are videos covering how to switch the code editor and forms to dark mode
B. It is not possible to apply dark mode to the editor
C. Only the ribbon changes colors
D. You need to reinstall Access for dark mode
Q16. When making forms for calculated values like mileage differences, what is the recommended database design approach?
A. Calculate in a query and not store redundant data in the table
B. Always calculate manually in Excel
C. Store both raw and calculated values together in tables
D. Never use queries for calculations
Answers: 1-A; 2-B; 3-A; 4-C; 5-A; 6-A; 7-B; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-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 answer a variety of questions on Microsoft Access, from forms and queries to handling the interface and collecting data from users.
I want to start with an observation about Microsoft's development process that many users may not know about. Some people criticize Microsoft for spending time on features like cascading combo boxes or zooming on forms instead of tackling bigger issues. However, many features added to Access actually begin as side projects during the company's internal innovation week, where developers get to work on whatever interests them. These projects often lead to useful features being rolled out to users without taking away from the team's main focus on bug fixes and security. The Access team is fairly small, and they're balancing priorities as effectively as possible, so let's try to support their efforts.
Over on the forums, Tom asked about attaching receipt files to expense records in a subform. If you want to store the file path for each receipt and open it later, you can use a hidden control on the subform - even on datasheet forms, which do have headers and footers you can use for this purpose. Once the path is stored, you can open the receipt using the FollowHyperlink function. Another way is to put hidden controls in a page header or footer, since these aren't visible in form view, only when printing, which is rare for forms. Personally, I prefer TempVars, but the other method works just as well.
Andrea had a great question about normalizing her database and whether it's okay to base data entry forms on queries, especially if she wants to include calculated fields like miles driven. This is a common confusion for Access users: many people think forms should only be based on tables. In reality, most of my forms are based on queries, and that's often preferable. As long as your query is updatable - meaning Access can still tell where to write the data - you can edit and add records and display calculated fields without duplicating data in your tables. Simple queries with calculated fields work great for this. Just don't make your queries too complex, with lots of joins or aggregates, or you may lose the ability to update records on your form.
Tony had a question about SQL Server, wondering if there's an Object Explorer like Access's navigation pane. There is, though it works a bit differently. You can filter tables, queries, and other objects in SQL Server Management Studio by right-clicking and applying filters. If you're interested in learning about SQL Server and using it as the back end for your Access databases, check out my free beginner course. You don't have to give up Access - you can link your existing forms, queries, and reports to tables on SQL Server for better performance and security.
Monica ran into a problem after adding a new tab to a form: every time she closed it, she got the infamous "Enter Parameter Value" error. This ended up being due to a subform inside another subform still referencing a field that was missing. The tricky part of Access form/subform architecture is that subforms load before their parents, so after everything's open, a subform can refer to its parent. When closing, the process works in reverse, and lingering references can cause errors as parent forms unload first. Using Link Master and Link Child fields is more reliable for keeping references tied to the correct place in the hierarchy and avoiding hard-coded references to controls that may no longer exist when closing. Monica solved her issue using a solid debugging strategy - making a copy of the form and removing parts one by one until the problem went away. That's one of the best ways to troubleshoot in Access.
Chuck wanted to display the current computer and Windows username in a text box using environment variables, but ran into trouble because Access was confusing variables with form controls. This is a common issue for new VBA users. If you have a variable with the same name as a control, setting its value in code doesn't update the form. You have to explicitly specify that you're referencing a control, often with the Me! notation. It's a good practice to avoid naming variables and controls the same - choose distinct names to make your code easier to manage.
Over on Reddit, I noticed a discussion about alternatives to Access in a group that generally dislikes Microsoft. Even in that setting, people admitted that nothing really compares to Access when it comes to quickly building Windows business applications with forms, automation, and robust reporting. There are other tools out there - like SQLite, FileMaker, or various web apps - but none offer the same combination of features and development speed. Despite its age, Access is still running businesses quietly after all these years.
On YouTube, my video about the hassle of changing clocks generated comments about daylight saving time and its effect on databases. If you're dealing with time calculations that cross the 2 a.m. time shift, it can get complicated. As a former resident of Buffalo, I know how the long, dark winters can affect mood, and I understand why some regions want to change the clocks while others don't.
Hamid asked about hiding the Microsoft Access interface so users only see the application and not Access itself. It's possible to make your database look very professional by hiding the navigation pane, ribbon, and other Access elements, adding custom splash screens, and even replacing icons. This involves making a packaged ACCDE file and using techniques I cover in my Hide Access videos. While it isn't possible to completely hide that you're running Access to a savvy computer user, you can make your application look fully customized to most end users.
Sanch mentioned my "Access Not Opening" video about why double-clicking on a shortcut or database might fail to open Access. The issue often comes from shortcuts that point directly to the ACCDB file instead of launching MSACCESS.EXE with the database as a parameter. This ensures the database opens in its own process. It is also possible to create such shortcuts programmatically with VBA if needed, and I can make a video on that if there's interest.
Stateless Atheist brought up the idea that modernizing Access even further would retain more users. I agree - Access is a powerful, productive environment for building desktop apps, and with more investment from Microsoft, it could be made even stronger. And you don't have to give up Access if your company wants to move to web or mobile apps; you can migrate your data to SQL Server as a back end and use other front-end tools as needed, while still benefiting from all the investment you've made in your Access forms and reports.
Kartik asked how to send a questionnaire to others and analyze the results once they fill it out. My earlier video focused on managing questionnaires on your own computer, not distributing them. To send a questionnaire out, Google Forms is a great solution - send people a link, let them answer online, and then import the results into Access or Excel. Alternatively, you can set up SQL Server online and build web data entry forms in your preferred web language. You could even collect responses by email and process them in Access as long as you keep the format consistent. The main idea is to gather the data however you find easiest, then let Access handle the analysis and reporting.
Finally, Fuego Esmeralda asked what happened to the dark mode in the VBA code editor, and whether my video was sped up for a short. Sometimes, to fit a video into YouTube's "Shorts" format, I increase the playback speed, but I try not to overdo it. Regarding dark mode, I have a three-part series where I show how to enable dark mode for both Access forms and the VBA editor. This includes toggling Windows and Office themes too. You can find all this starting in my Access Developer 57 series.
Before you go, take a look at my website for new videos, templates, updates, and articles - including thoughts from my Captain's Log about interesting Access features and AI developments. Visit the forums if you have a question, browse my merch store, and subscribe to my mailing list so you don't miss out on new content.
In this video, we covered when to use queries as form record sources, how to hide the Access interface, collecting and analyzing survey data online, and much more. If you have a question or feedback, post a comment below and let me know what you'd like covered in future Quick Queries.
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 Storing file paths for receipts in subform controls
Using hidden controls in datasheet form headers and footers
Storing form data in hidden page headers or footers
Basing Access forms and reports on queries vs tables
Using updatable queries as form record sources
Displaying calculated fields in forms through queries
Troubleshooting subform references causing parameter prompts
Understanding form and subform load/unload sequence
Using Link Master and Link Child fields for reliable references
Avoiding naming conflicts between form controls and variables
Properly referencing form controls in VBA with the Me keyword
Filtering tables in SQL Server Object Explorer
Hiding the Microsoft Access interface for end users
Creating splash screens and branding Access applications
Minimizing and hiding Access ribbon and navigation pane
Packaging and distributing ACCDE files for security
Creating Windows shortcuts to open Access databases
Sending questionnaires using Google Forms and importing results
Collecting and analyzing external survey data in Access
Implementing dark mode for Access forms and user interface
Enabling dark mode in the Access VBA code editorArticle When building forms and reports in Microsoft Access, one common question is whether to base them on tables or queries. Many people believe that you should always use tables as the data source, but in practice, using queries can offer more flexibility and power. In fact, most experienced Access developers prefer using queries as the foundation for their forms and reports, especially when you want to include calculated fields or combine data from multiple tables.
A key advantage of using queries is the ability to display calculated values without storing redundant data in your tables. For example, imagine you have an Orders table and you want a form to show the total price for each order line (which is just quantity multiplied by unit price). Rather than storing that total in the table, you can build a query that includes the original data along with a calculated field for the total. This approach keeps your database normalized and prevents data inconsistencies.
When you base your form on a query, as long as the query is "updatable" (meaning Access can figure out how to write edits back to the source data), you can edit and add records in your form just as you would with a table. Access can handle simple queries that have calculated fields or pull in data from related tables through straightforward joins. However, if your query becomes too complex, with lots of joins, aggregate fields, or groupings, it may no longer be updatable, and you will lose the ability to make edits directly in the form.
To give a simple example, let's say you have an OrderDetails table with fields for ProductID, Quantity, and UnitPrice. You want a form that shows these fields and also calculates the LineTotal for each item. Create a query like this:
SELECT ProductID, Quantity, UnitPrice, Quantity * UnitPrice AS LineTotal FROM OrderDetails;
Base your form on this query, and Access will still let you edit Quantity and UnitPrice. The LineTotal field will update automatically and is calculated on the fly. You do not need to store it in the table.
It is only when you start joining multiple tables or using aggregate functions like SUM or GROUP BY that you must be careful. These queries may not be updatable, which means edits in your form or report will not be saved back to the source tables. So for data entry and editing, keep your queries simple.
Reports, on the other hand, can be based on any query, even non-updatable ones, because reports are for viewing and printing data and do not require edits. This is a good place to use more complex queries that summarize or group data from multiple tables.
Another useful Access feature is the ability to add calculated or hidden controls on your forms. For example, if you have a datasheet subform where you want to store or use a file path (such as to a scanned receipt), you can use a hidden text box in the form's header or footer. Datasheet forms do support headers and footers, even though users do not see them, and you can use these sections to hold controls for behind-the-scenes functionality.
When working with VBA, be cautious about naming conflicts between variables and form controls. If you have a text box named UserName and declare a variable named UserName in your code, Access can become confused about which one you mean. Use the Me keyword to refer to controls on your form. For example, Me!UserName = "Bob" will set the value of the UserName control, not a variable. It is good practice to avoid using the same names for both variables and controls to prevent this kind of error.
Many people want to make their Access applications look professional by hiding as much of the Access interface as possible. You can customize the look by hiding the navigation pane and ribbon, replacing icons, creating splash screens, and compiling your database to an ACCDE file. While you cannot hide everything (advanced users can still spot Access running in the background), these techniques can make your application feel like a stand-alone program to most users.
Access remains a standout tool for building Windows business applications quickly, especially when you need forms, reports, automation, and a reliable database. While alternatives exist, none provide the same blend of power and ease for rapidly developing complex applications. Even if your business wants to use modern web or mobile platforms, you can keep Access as a front end by moving your data to SQL Server and accessing it from various applications.
If you need to collect data from users online, there are a few ways to handle this with Access. You can use tools like Google Forms to send questionnaires via links, and then import results into Access for analysis. For more advanced scenarios, set up a SQL Server backend and create web forms using technologies like ASP, HTML, and CSS, allowing people to submit data over the web. Whatever method you choose, Access excels at importing, summarizing, and reporting on data gathered from a variety of sources.
If you want to automate Access or customize the user experience further, you can use VBA to create shortcuts, change themes, or even script changes like switching to dark mode for your forms and editor. Remember to keep your variable names clear and avoid conflicts with control names. Use the Me prefix to remove any ambiguity when coding interactions with form controls.
In summary, base your forms and reports on queries instead of tables whenever you need calculated fields or want to combine data sources, as long as your queries stay updatable for editing purposes. Keep queries simple for data entry, but feel free to use more complex ones for reports. Use hidden controls and careful naming to keep your applications organized, and consider customizing the Access interface for a professional appearance. With these strategies, you will build flexible and robust Access applications that leverage the strengths of both tables and queries. |
