Quick Queries #82
By Richard Rost
34 days ago
Sum Calculated Fields, Combine Tables in Union Query In this video, we address common issues in Microsoft Access, such as why totals sometimes do not calculate in form footers and the reasons behind error messages. We discuss solutions for summing calculated fields, combining records from multiple tables using union queries, and troubleshooting differences between database behavior on different computers. Additional topics include handling file paths in VBA, managing rolling date cycles, distinguishing tables from queries, configuring Rich Text fields, and understanding the docking capabilities of forms. We also cover table-level validation rules, data macros, and answer various viewer questions related to Access database development. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, form footer sum error, #error in sum formula, union query combine tables, combo box multiple tables, combine customers vendors employees, unbound calculated control, dsum not working on client, docmd.outputto dynamic path, double double quotes vba, rolling three year date cycle, form window docking vba, data macros triggers
Intro In this video, we address common issues in Microsoft Access, such as why totals sometimes do not calculate in form footers and the reasons behind error messages. We discuss solutions for summing calculated fields, combining records from multiple tables using union queries, and troubleshooting differences between database behavior on different computers. Additional topics include handling file paths in VBA, managing rolling date cycles, distinguishing tables from queries, configuring Rich Text fields, and understanding the docking capabilities of forms. We also cover table-level validation rules, data macros, and answer various viewer questions related to Access database development.Transcript Why does Microsoft Access sometimes refuse to calculate a simple sum in a form footer, when it should be the easiest thing in the world? 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 Microsoft Access sometimes refuses to calculate a total in a form footer and throws a lovely little pound error instead. We'll look at some of the common reasons this happens and how to fix it so your totals actually work the way they're supposed to.
We'll also look at a few other interesting questions that came in this week, including why a database can work perfectly on your computer but fail on the client's machine, how to combine multiple tables into a single list using a union query, why a path that works fine when it's hard-coded suddenly breaks when it comes from a form control, and how to make a rolling three-year date cycle automatically move forward each year.
We got answers to questions from YouTube, my website forums, emails, and lots more, so let's get to it.
This is a special weekend edition of Quick Queries. Why a weekend edition? Well, as I wrote about yesterday in Macapton's log, I was feeling a little bit under the weather yesterday, probably from too much of the Trouble Jerky, so I spent most of the day yesterday in sick bay - just one of those common colds.
Which is funny because I remember in the original Star Trek series, McCoy talks about the common cold like it's something that he's suffering from - just the common cold - like it's still at that point something that you can't get rid of. Then by Next Generation it's like, "They used to have this thing called the common cold," so it's like sometime between McCoy's time and Crusher's time, they got rid of it. But yeah, that's what I've had, so that's no big deal. Just one of those scratchy voices, just bluffing. Not like a full-blown flu, but just enough that I don't want to be sitting in my chair looking at my monitor.
So what do I do? I lay in bed and look at the TV and still watch YouTube videos most of the time. So anyways, I couldn't get to it yesterday, but I'm feeling much better today. Better life through pharmaceuticals, right? Little DayQuil, little Sudafed, little Mucinex, little coffee. Right as rain. I got an hour in me at least to record a Quick Query, so all right, let's get to it.
Now, last week in Quick Queries 81, we had a question from someone who was having problems because they tried to make their union query do too much. They had all kinds of different stuff in it. They were trying to aggregate inside it, and I said, "Look, don't make your union queries that complicated." In some cases, staging tables - temporary tables - are better. Load up the temp table with all kinds of stuff. But sometimes a union query is the right solution, so let's take a look at this week's leading question.
We got a question from Teddy in Anchorage, Alaska, one of my Platinum members. Teddy says:
"I have separate tables for customers, vendors, and employees in my database. Sometimes I need to pick a person from a combo box, for example when assigning someone as a contact, selecting who a task belongs to, or choosing someone for a mailing label. The problem is that these people are stored in different tables. What's the best way to combine them into one list so I can select any of them from a single combo box?"
This is where union queries actually are a good idea, and in this video I show you how to do it step by step. Let's say you got a customer table and an employee table. You can make a union query to put those people together, as long as the fields match up - first name, last name, and ID - then you can put those together in a single combo box or a single list box, or a single whatever list you want. That's what a union query is good for, and you can do this with multiple sets. Just again, like I said last week, don't try to make them too complicated. Try to keep your union queries simple. If it's going to be a whole bunch of different stuff, different types of data, you might want to consider staging tables - a temporary table.
And also, one thing I like to teach people now in my beginner classes: this is fine, have a table for customers, have a table for employees, have a table for sales reps, vendors, whatever. But in all honesty, these are all just slightly different types of the same thing, right? And what's the thing? Well, the thing is a person, and most of these people - these persons - have all the same fields. You need an ID, a first name, a last name, a phone number, email address, and address - mostly the same stuff. So that's when you can just take all of your people types - your customers, your employees, your vendors, everybody - and put them in a person table. Then you just give them a person type ID, and this type ID will indicate what type of person they are, is this a customer, is this a vendor? Then you can have your different combo boxes and stuff just have a little criteria in them. So if you want to see just customers, then make a combo box that says, "Show me all the people where the person type ID is one," or if you really want to get high speed and you want to be able to put people in multiple categories, then you just use a junction table and you have a many-to-many relationship where many people can be in many categories. You can have a person who's both a vendor and a customer.
Of course, I got videos that explain all of this in more detail. Check out my relationships video, check out my many-to-many video, and I've got a relationship seminar that covers all of this stuff and much more in a whole lot more detail. I've actually got another TechHelp video coming out on something very similar I think next week, so stay tuned for more.
But bottom line, Teddy, if you want a quick fix and you don't want to redesign your entire database, yes, use a union query. It's nice and simple, you can put them together in a single list box or single combo box. The better design would be to use a single table and to identify which type of person it is, but that might involve redesigning your database a little bit. It all depends on how much work you want to put into it and what works best for you.
The best thing about building your own software with Access is you can decide how to build it the way you want. Whatever works for you works best. My job is to show you all the different options - you can put the Legos together however you want.
All right, head over to my forums on my website. Tom, one of my Gold members, wrote in with a situation that a lot of developers run into eventually if you're building software for other people, whether it's other people in your office or you're doing it as your consultant, whatever. He built an Access database with some form code that calculates a total using DSum. Everything worked perfectly on his computer, both as an ACCDB and as an ADE, but when he sent the file to his customer, it threw an error as soon as they updated a field on a form. Same database, same code, completely different behavior on the client's machine.
We ran through some stuff, and it turned out that he wasn't signed in to his Office account, and this is one of the most common headaches in Access development - something works perfectly on your machine but it breaks on someone else's computer. That's why I thought this was important to bring up for this week's Quick Queries.
When this happens, the problem is often not your code at all; it's something in the environment on the other machine: different version of Access, different Windows updates, missing references, security settings, trusted locations, antivirus software, even Office not being properly activated or you're not logged in. In the end, Tom discovered the problem had nothing to do with his code or his database in general - the client simply wasn't logged into their Office account, which Office doesn't like, so Access wasn't fully activated. Once they logged in, everything worked fine.
So remember, if it works on your machine but not someone else's, start looking at the environment before you start rewriting your code or start thinking that your database is the problem. Have them run through the troubleshooter. I got this whole troubleshooter on my website here - I'll put a link to it down below as a video you can watch. There's a whole list of things. I tried to put them in the order that I think you should check - the easiest stuff first. That tends to be a lot of the problems: restart the computer, compact and repair. There's a whole list of things in here. In fact, I don't have in here "make sure you're logged on to your Office account" - I'm going to add that to the list now.
Have them go down this because everyone's got different stuff, and that's the thing with Office. You might not have the same exact Office install - there are all kinds of things it could be, and chances are if your database works fine on your machine, it's probably not your database as well, it's probably something else.
Quick intermission: If you're enjoying my videos, if you like these Quick Queries videos, make sure you like and subscribe. It helps me out, helps the channel out, helps me keep these videos free, so I appreciate it very much. Thank you.
Next up, here's one that throws a lot of people. Joe, a Silver member, posted a question about using DoCmd.OutputTo with a dynamic file path. When he hard-coded the full path in his VBA, everything worked perfectly, but what he really wanted to do was store the path in a text box on a form and then pull it from there in the code, something like Me.FilePath or whatever. When he tried that, as soon as he ran it, the code stopped working even though it was the same path and it worked fine when he had it directly in the code.
This one usually comes down to how Access handles strings in VBA, especially when there are spaces involved in the file paths - it all comes down to those darn spaces. My Documents - a little space in there. When you put the path directly in your code, Access already knows it's a string because it's wrapped in quotes. When you put the value in a text box, sometimes the command that you're passing it to expects that path to be wrapped in quotes again. So if your text box contains something like that with a space in it, you have to wrap it in those double double quotes again.
This says take the full path - whatever your text box name is - and then in front of it put this - remember, this is a string, you've got open and close quotes for the string, and inside there you have double double quotes. Double double quotes inside a string becomes a single double quote in the string itself. I know it's confusing. I got a whole separate video that talks about those double double quotes. They're a bit of a pain. You can use a single quote too, but then you have problems with escaping for actual names that have apostrophes in them and all kinds of stuff like that, so I just prefer using the double double quotes.
This video will explain it in a little more detail, but that's the problem you're running into, most likely. It's the same thing whether you're using OutputTo or FollowHyperlink or Shell or any of those functions where you're sending it a full path name where you've got a space in there.
Or any kind of command or any of that stuff.
Next up, Matthias, one of my Silver members from Germany, he's trying to total up a percentage field in the footer of a continuous form - not endless form, but I get it. But the value he wants to sum up is coming from an unbound text box, so every time he tried something like Sum([thatControl]), he only gets his pound error. Of course, Kevin and Alex jumped in and tried to offer some support, but the real question became: can you total an unbound calculated control in a continuous form footer?
Generally, no. What you want to do is you want to put that calculation in a query first, then it becomes a bound control in your form, and then you can calculate it. It's a really common Access gotcha, and it gets a lot of people.
The Sum function in a form footer works great, but only when it's summing something that's actually part of the form's underlying record source. If the text box is unbound and you're doing math right there in the control, then Access basically says, "Nope, can't do it here."
Let me show you an example. We got a customer list here, right? We got first name, last name, and credit limit. Now these are all bound fields, so they're bound to fields in the actual customer table, and down here we got a sum of the credit limit - it's just Sum([CreditLimit]), very easy to do.
Now, let's say we have a calculated field in here. Let's say, let me get rid of some stuff so we have some room. Let's say we just got CreditLimit. Let's take this guy and let's make this - let's call this CreditLimit2, and we'll make its control source here - I'll zoom in so you can see it better - this will be =([CreditLimit]*2) or whatever calculation you want to put in there. That's fine, you can do that, but this is now a calculated value, which means this does not exist in the underlying table or query. This does not exist in the CustomerT. We're calculating it here, which means if we now try to do this - let's copy this guy, stick it here - and then instead of saying Sum([CreditLimit]), we'll say give me the sum of that CreditLimit2 field that I just made, right, this guy.
Okay, now when I try to open it, I get errors. Why? Because it's trying to sum up something that at the time this is calculating doesn't exist.
So how do you fix it? Well, you have to put this calculation in the underlying table or query. Now, since this is based on a table, this is based on the CustomerT, what I suggest is make a query. Don't try to do calculated fields in tables - yes, you can, I don't recommend it unless you got a very specific use case - and yes, I got whole separate videos on that. But very, very simply, just make a query based on the customer table, bring in everything, put your credit limit two in here - CreditLimit2: [CreditLimit]*2. Save this as MyCustomerQ.
Close all this stuff down. Now we have a query here, that's still a nice simple query, it's still editable, we can still add new records, we can still edit this stuff, but we have this as a calculated field in the query. So now I can say, okay, let's take this form, let's base this form off of CustomerQ. Now this guy, instead of being a calculated value, will get its value from CreditLimit2, and now this guy can do a sum of CreditLimit2, and it should work because it's in the underlying query.
See, don't try to do a sum in a form footer of a calculated field up here, that's the problem, and this is a huge gotcha. This gets a lot of people. So all your line item calculations should be in the underlying query, then you can add them into form footer calculations down here.
That's a very good question. It comes up a lot, so I hope this helps.
Next up, we've got Miguel, one of my beginner students, and the reason why I got these badges on the website is so I know someone's level of experience so I can answer the question differently - if someone's a beginner versus an expert versus a developer level student. Since Miguel's a beginner, we'll answer this at the beginner level.
Now what Miguel wants to do is he's got a table where he stores data in a three-year cycle - for example, January 1st, 2023 through December 31st, 2025 - and he wants those dates to automatically roll forward to the next three-year cycle when the period expires, something like January 1st, 2026 to December 31st, 2028, without him having to manually update the table.
Now, the important thing to understand here is that Access doesn't just wake up in the middle of the night and decide to update your data. If something's going to happen automatically, something has to trigger it. Now that trigger could be a button you click, an update query that runs, or later on when you get a little more advanced, some VBA code that runs when your database starts. Or if you're leaving it running on a loop, it could run in the middle of the night - I do a lot of that stuff myself so I don't have to sit there watching it.
Alex suggested a parameter query that you can run that does an update query - you type in the dates you want, it updates them. That's fine, you have to trigger that yourself, or you can tie it to a button. Donald suggested something that requires a little VBA - you put the logic in an OnOpen or OnLoad event of a form that opens regularly; that involves some programming, but basically it's the same thing: something's got to kick off that update query.
Unfortunately, if you want something to happen automatically, you have to have something that triggers it. Like Donald says, he uses an OnOpen or OnLoad event. You could do this with a macro, without VBA programming - you could have a macro that runs an update query and that kicks off in your form's open event. There are lots of different ways you can do it, and Donald gave you some great tips here, too. Let us know if that works for you.
Just keep that in mind anytime you say it needs to automatically do something - well, what's going to make it automatically do that? Something's got to make it happen.
It's kind of like what I used to tell clients when I used to build databases: every time you say the word "automatically," I want you to hear the little cash registers going off. I'm just kidding. "Automatically" has to be triggered by something - whether it's an event, you clicking a button, or a timer that goes off in the middle of the night.
Like your backup software - there's a timer, it's running constantly, and then it's got to know when to launch some software. So when do you want that automatically to happen? When you open the database? When you click a button? All that stuff.
Next up, got an email from Gary. We're going to file this one under the cool stuff that I probably knew 30 years ago but have completely forgotten. He says he uses the little VBA code editor button to switch back to Access, and I'm like, "Oh yeah, I completely forgot about this thing."
Let me show you what I'm talking about. When I'm working with VBA editor stuff and I'm doing a video with you guys, I keep the window just perfectly here at the Access border. What I do is I leave it off the side a little bit so I can click over here. If I'm back in the database, you don't see this little part over here because it's outside the recording window, but I just click here and then I click back and forth this way.
When I'm working personally, I use Alt-Tab a lot because you can use Alt-Tab to flip to the previous window, but Gary reminded me there's a little button right there. Click on it and it brings you back to Access. I forgot all about that little button. I put this guy here on the Quick Launch toolbar - I got a whole separate video how to do that - and that lets me jump back to this guy, or if this isn't even open, you can click on this and it opens that window for you.
But I totally forgot about this little guy right there: the "View Microsoft Access" button. Click on that, it brings you right back here. Cool. There are so many buttons on here that I never use. Like someone reminded me a long time ago about the comment block buttons you can put on here. You get in the habit of doing things a certain way for literally decades that you forget all the little tiny nooks and crannies of this thing that you never use. So thanks again, Gary, and if you guys have any little shortcuts, tips, tricks, whatever, send them in and I'll give you full credit for them. I might steal them a little bit, but I'll still give you full credit for them.
Got another beginner question from Peggy. Peggy says: "I may be premature in asking this, but how do I know when to use a table and when to use a query?"
Well, they're two totally different things and don't feel embarrassed to ask a question. I make sure that my forums are a safe space for everybody - everyone started off as a beginner somewhere - so don't ever feel embarrassed to ask a question no matter how simple you think it is.
Now, I do spend a lot of time in my Beginner Level 1 class going over the difference between a table and a query, but just in case you missed it: a table is where you actually store your data. A query is how you can view that data in a different way - queries by themselves don't store anything, there's nothing saved in a query, but you can interact with the data in your table through a query. Queries can do things like filter your data and sort it and show it in different ways.
Think of a table like the pantry in your kitchen - that's where the actual food is stored. The cans, the boxes, the ingredients all live in your pantry, and in Access that's your raw data: your customers, orders, products, employees. The table is where the real stuff lives - the information. Now a query is like a recipe - the recipe doesn't store food. Instead, it tells you what to pull out of the pantry and how to combine it. The recipe might say, "Get tomatoes, garlic, pasta, and ignore everything else," or maybe it says store the ingredients in a certain way or calculate the totals. The recipe tells Access how to look at or work with what's in the pantry.
So the rule of thumb is: tables store data, queries ask questions about that data.
Lots of analogies I could give you, but that's basically how you can remember that. Tables are where the data is stored; queries are just different ways of looking at - different recipes. Your customer table might have a million customers in it, your query might say, "I want to just see the customers from Florida," or "Just the customers with credit limits of a thousand dollars or more." That query itself doesn't have any data in it, it's just displaying the customer data in a different way.
I hope that helps you visualize it better.
All right, time for YouTube questions.
Bob says: "Very nice job in the food portion of the database. I was looking to integrate the physical exercise, but I would think that Excel may be a better choice."
Yeah, exercise is coming. I know I stalled on the fitness database for a while, started doing some other projects. It's on the back burner, but it's not forgotten about. I'm going to get back to it very soon. I want to be able to take time - I want to have three or four days where I can just sit down and record a whole bunch of lessons, because when I do a little bit of this then put it aside and do something else, I have to spend an hour or two just getting my mind back to where I was. I want to have a couple of days where I can just focus on just that.
I still want to build out the exercise portion of it for myself too, because I'm still using a whiteboard. I got all my exercises, I got my garage in my gym - I have a gym set up in my garage - I got a big whiteboard, and I'm doing all the food on my computer because, like you said, the food section is great. But I do want to build the exercise portion of it still, too. So it's coming, I haven't forgotten about it.
Now, as far as Excel goes - yeah, I started a lot of this stuff off in Excel. That's what I showed you guys at the beginning of the fitness database, that it starts off in Excel and then from there I migrated over to Access. That's how I build a lot of my databases. A lot of things, you'll start in Excel and realize that Excel is good enough, you might not need a whole database for something, but I do plan on building a database portion for the exercise stuff. So I have not forgotten about it, it's coming soon.
Next up, Muharem says he's following along with one of my train examples, but right away he gets an error when clicking a button. Access says, "There was a problem communicating with an OLE server or ActiveX control and the OnClick expression may not be valid."
That one can look scary, but most of the time this is not your code. When Access throws this kind of message before your code even runs, it's usually an environment issue. The first things I would check are: go into your VBA editor, do a Debug - Compile, make sure it compiles properly. Go under Tools - References and make sure that nothing is missing, any missing references. Double check make sure your button's OnClick property is set to Event Procedure and it's not pointing to something that doesn't exist anymore. Nine times out of ten it's one of those, so check it out.
Next up, we've got Jamshid. He had a client using an Access application with about ten users, worked fine for years - back end file stored on a physical Windows 2003 server. Then one day they moved the back end to a Windows Server 2022 virtual machine running VMware, and then the database kept corrupting.
Thanks for sharing. I've seen similar situations when environments change, especially when moving a backend into a virtual server like that or a different type of storage system. Access itself usually isn't the problem, but things like network latency, caching, how locking in the VM handles file shares - a whole lot different - and your corruption risk will go up if that's the case. If the database worked fine again when moved back to the original server, that definitely points to something in the VM or the network configuration rather than Access itself. Remember, Access is a simple file-based system. It doesn't like cloud storage, it doesn't like VMware, it doesn't like all that weird stuff that people try to run. It's built for a simple LAN configuration. If you want anything else, use SQL Server.
Next up, we got Richard from Australia - cool name. He says he's using the rich text notes trick that I show in that video and he's confused because sometimes his long text field shows the HTML tags like the b and /b for bold and other times it shows just formatting, and he's entering the data the same way and can't figure out why it behaves differently.
Usually this comes down to how the field is configured. If your long text field is set to Rich Text in the table and the control on the form is also set to Rich Text, then Access is going to hide the HTML tags and it just shows the formatting. But if either one of those is set to Plain Text then you'll see the actual tags that you don't want to see, you won't see the formatting. Make sure both the table field and the form control are set to Rich Text if you want the formatting to display properly. Sometimes I'll even put both - I'll put two controls on a form, one Rich Text and one Plain Text - so I can see the tags if I want to debug them.
Like here we've got this Rich - well I think this is just set to Plain Text now, but if I go into the customer table, find the notes field - set this to Rich Text, say yes. Now what you can do, if I come into here, now this control has to also be set to Rich Text. Let me get rid of this stuff so we got some extra room in here to play. What I'll do sometimes if I want to see this stuff, I'll set this guy to Rich Text under Data, and then go to Rich Text here, and now you'll be able to see all that formatting. If I select some stuff, I can go bold, I can go red. But you can also make a copy of this guy, make this like Display, and then set this to Plain Text - say yes - and now when you look at it you've got one of each. Here it is with the actual tags in it. It uses strong instead of bold, that's fine - same thing, the tags, it's a limited set of tags by the way. I don't think you can come in here and put like CSS and JavaScript and all that - no, it's a very limited set of HTML you can use inside of Access. They always encapsulate everything in a div. But you can come in here and make changes and see what they are down here. That's why you might be seeing the bold sometimes. If you got a field somewhere that you don't have set to Rich Text, you'll see these little tags.
Next up, TaxFree - I like that, TaxFree - asks, "Is it possible for a form to pop up and dock to the right of the window, similar to the property sheet?"
Not that I'm aware of, no. The property sheet and the navigation pane are special built-in windows; Access knows how to dock those. Regular forms don't dock like that. The closest you could get would be to use a pop-up form and position it with VBA so it appears where you want it along the right side of the screen. It'll look similar, but technically it's just floating there, it's not truly docked. I show how to move and position and resize windows - well, forms, basically - in this video, so check that out. That's the best you could do, you can't really dock it.
Next up, N269 asked whether table-level validation rules in Access can only be done with expressions or if you can use VBA that returns true or false. Then he realized, oh yeah, triggers - triggers are SQL Server, and Access doesn't really have those.
But Access does have something called data macros. They're not quite as powerful as SQL Server triggers, but you can do some pretty cool stuff with them. They run directly on the table for events like Before Change or After Insert. If you need more advanced logic at the table level, that's where data macros come in. Otherwise, with Access applications, I usually handle stuff like that with VBA at the form level. Check this video out and see, maybe it'll do what you need.
All right, so that's about going to do it. Make sure you hit that like and subscribe - did I say light? I said light and subscribe. Too much DayQuil, folks. Make sure you get on my mailing list on my website so you get email notifications. I don't send spam; just every once in a while.
Don't forget, Access Day is coming up, it's getting close - what is it, about a little over two weeks away now. So make plans if you can.
Stop by my website, see what's new. I just released Access Developer 53 last week - that's a big one - and in that one, I finished up a file drop system where you can take files from your desktop and just drop them in your Access database. Really cool stuff.
Check out the Captain's Log, my posts about whatever I happen to talk about, like Trouble Jerky. Did a little article on scammers a couple days ago. Merch store is still open, got t-shirts and coffee mugs. I got some new stuff I'll be adding soon too, so check it out. You can grab a copy of my Access Beginner Level 1 book on Amazon.Quiz Q1. Why does Microsoft Access commonly refuse to calculate a sum in a form footer for certain controls?
A. The Sum function only works with bound controls from the underlying record source
B. The Sum function only works with unbound calculated controls
C. The Sum function can be used with any field, regardless of its source
D. The Sum function requires the field to be a primary key
Q2. What is the recommended method for combining records from multiple tables into a single list for a combo box?
A. Use a union query to join similar fields from each table
B. Use an append query for each table
C. Create a relationship between the tables and use a subform
D. Use a make-table query to copy all records into one table
Q3. What is one advantage of storing all types of people (customers, employees, vendors) in a single "person" table?
A. It makes it easier to filter and categorize people by type
B. It eliminates the need for relationships
C. You no longer need to use primary keys
D. You must only use text fields
Q4. What is likely the cause if an Access database works on your computer but fails on a client's machine?
A. Environmental differences such as Office activation or references
B. Database corruption from excessive record locks
C. Poorly written queries in the back end
D. Too much data in the tables
Q5. Why might DoCmd.OutputTo fail when using a file path stored in a form text box rather than hard-coded in VBA?
A. The path may require additional quotes for spaces when passed from a control
B. The path must always be hard-coded
C. VBA cannot read data from text boxes
D. There is a limit on the length of the file path in VBA
Q6. How can you sum a calculated value in a form's footer if the calculation is based on other controls?
A. Place the calculation in the underlying query, then sum that field in the footer
B. Use an unbound text box for the calculation and sum it directly in the footer
C. Use an aggregate function directly on the unbound control
D. Only sum fields created in the table design view
Q7. What must happen to trigger automatic updates in Access, such as rolling a date range forward every three years?
A. A user or process must trigger the update, such as clicking a button or form event
B. Access automatically runs updates at midnight
C. Scheduled tasks run in the background without setup
D. Linked Excel files make this unnecessary
Q8. What is the proper distinction between an Access table and a query?
A. Tables store data, queries ask questions and display/filter that data
B. Both tables and queries store data
C. Queries are required to back up tables
D. Tables cannot be filtered or sorted directly
Q9. When moving an Access back end from a physical server to a virtual machine, what issue might occur?
A. Increased risk of corruption due to network or VM configuration
B. Access will optimize performance automatically
C. Queries run faster on VM storage
D. There are no differences between physical and virtual hosting
Q10. If a long text field displays HTML tags instead of formatted content in Access, what is a likely cause?
A. The field or form control is set to Plain Text instead of Rich Text
B. The data contains too many characters
C. Access does not support formatting in long text fields
D. The field must be a primary key
Q11. Can regular forms in Access be docked to the right of the window like the property sheet?
A. No, but forms can be positioned via VBA to simulate docking
B. Yes, any form can be docked like the property sheet
C. No, forms must always be centered
D. Yes, but only in the latest Access version
Q12. What mechanism in Access provides event-driven logic at the table level similar to triggers in SQL Server?
A. Data macros
B. VBA in form events only
C. Append queries
D. Table relationships
Q13. If running a VBA command in Access gives an error about OLE server or ActiveX control, what is NOT likely the problem?
A. The code is incorrect and compiles but has a syntax error
B. Missing references in VBA
C. The OnClick property points to a non-existent event
D. The button is set to Event Procedure
Q14. Why might Access be unsuitable as a back end for certain server or cloud configurations?
A. Access is a file-based database and may be affected by network latency and file sharing on VMs or cloud systems
B. Access supports cloud storage natively without issue
C. Access requires a constant internet connection to function
D. Access has no compatibility with Windows servers
Q15. What is the best approach if you want to see both formatted and raw HTML content from a Rich Text field in a form?
A. Place two controls on the form: one set to Rich Text and another to Plain Text
B. Always use Plain Text to see both formats
C. Use only the table view for rich text fields
D. Rich Text fields cannot show raw HTML content
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I'm going to discuss a handful of common Microsoft Access problems and answer several interesting questions from students this week.
One of the issues I want to address is why Access might refuse to calculate a total in a form footer and display an error, even though it seems like a simple task. We'll explore the reasons behind this, cover some possible fixes, and make sure you understand what needs to be in place for totals in your forms to work the way you expect.
I will also touch on other topics including scenarios where a database works perfectly on your computer but fails when used by a client, combining separate tables into a single list using a union query, dealing with dynamic file paths passed from a form, and setting up rolling three-year date cycles so that they update automatically.
Before heading into the questions, let me share a quick update - I recently had a little downtime thanks to a common cold. You might remember that back in the old Star Trek series, Dr. McCoy still suffered from the common cold, but by Next Generation, it had disappeared. Anyway, after a day of recovery and some pharmaceuticals, I'm back and ready to record a fresh batch of Quick Queries.
Let's jump right into the first question, which is about combining people stored in separate tables. You might have tables for customers, vendors, and employees, and you occasionally need to select a person from any of these groups in a combo box. To accomplish this, union queries are a very practical solution as they allow you to stitch together rows from different tables into one unified list, provided they have similar fields (like ID, first name, and last name). With a union query, you can fill a combo box or list box with names from all tables.
It's best to keep union queries as simple as possible. If things become too complex or involve many types of data, consider creating a temporary or "staging" table instead. However, for just pulling together lists of different people types, a union query is ideal.
As I'm teaching more often now, there's also a smarter approach: store all your people in one master table and use a "person type" field to categorize each record as a customer, vendor, employee, and so on. This lets you control which type of people appear in which combo box with simple criteria and also allows for more flexibility in reporting and searching. If you want people in multiple categories at once, use a many-to-many relationship with a junction table. While redesigning like this requires more upfront work, it's much simpler to maintain in the long run.
If you want a quick fix, use union queries. For a better design, consider restructuring your tables. I'll have more detailed videos on related database design topics available on my website, which you can consult for guidance.
Next, I want to discuss an issue that crops up often when you share a database with someone else and it fails to work for them, even though it runs fine on your computer. In a recent situation, a member built a database with code using the DSum function to calculate totals. It worked for him, but when he sent it to a client, it threw an error every time a form was updated. As it turned out, the problem wasn't in the database or the code at all - instead, the client's Office account wasn't properly signed in and Access wasn't activated. Logging into Office fixed the issue. This is a reminder that problems are frequently related to different computer environments: varying Access versions, missing references, untrusted locations, or even activation status can all impact how your database runs. So if something goes wrong on another user's PC, check the environment and reference my troubleshooting guide, which you'll find on my website.
Now, let's tackle a question about dynamic file paths. If you hardcode a file path directly into your VBA and it works fine, but it fails when you try to pull the path from a form control (such as a text box), the culprit is usually spaces in the file path and how the string is constructed in VBA. When pulling the value from a control, you may need to handle quotes differently so Access parses the value correctly. This double-quote issue frequently catches people off guard. I have a separate video covering the nuances of double double quotes, which you may find helpful as this applies to OutputTo, FollowHyperlink, Shell, and similar functions.
On to another frequent stumbling block: summing values in a continuous form footer when the value is based on a calculated (unbound) control instead of a bound field coming from your form's data source. Suppose you calculate a value within a text box in your form (like doubling a credit limit). If you then try to sum this unbound control in the form footer, Access generates an error because it's not actually in the underlying data source. The fix is to move your calculation into the form's record source - create a query based on your table and add the calculated field there. Once the field is available in the query, you can point your sum calculation at it, and the errors will disappear as Access can now total a field that does exist in the data source.
A beginner's question came in next about automatically updating a three-year date cycle. The main takeaway here is that Access does not process things truly "automatically." For updates to roll forward, there must be a trigger, like pressing a button, opening a form, or running a macro or VBA script when certain events occur. You can set up an event to check dates and update them as needed, but something still needs to initiate the process. For more granular automation, you'll need some programming or at the very least, a macro tied to a form open event.
I also received a tip about the VBA code editor - if you want to quickly switch between the editor and Access, there's a button right there that does just that. Sometimes, simple built-in features get overlooked if you've been doing things a certain way for years, like relying on Alt-Tab. It's a handy reminder that the interface has lots of shortcuts, and if you have tips like this, feel free to send them my way.
Peggy, another beginner, asked about the difference between a table and a query. Simply put, tables permanently store your data; queries let you view, filter, or calculate different versions of that data but do not store data themselves. A good analogy is that tables are like the pantry where the real food is, and queries are like recipes that tell you what food to use and how to present it.
Moving into some rapid-fire Q&A:
Bob asked about integrating exercise tracking into the fitness portion of the database and wondered if Excel might be better suited. I started with Excel for food tracking, but as the needs grew, Access became a better fit. Exercise tracking is still on the to-do list and will get built out further.
Muharem ran into an OLE server or ActiveX control error when clicking a button. If Access throws such errors before any of your code runs, it's almost always an environmental issue, such as broken references or the wrong event procedure. Use Debug - Compile and check your references first.
Jamshid's Access database kept corrupting after moving the backend to a Windows Server 2022 virtual machine, even though the database worked fine for years on a physical server. This kind of corruption is usually related to network latency, file locking, and how virtual storage interacts with Access, not a problem with Access itself. Access is happiest on a simple LAN with traditional file shares, and anything else dramatically raises your risk of corruption. For complex environments, moving to SQL Server is the correct solution.
Richard in Australia had trouble with the rich text notes feature unexpectedly showing HTML tags. This behavior is controlled by the field and control settings for rich text versus plain text. Both the table field and form control need to be set to Rich Text in order to display formatted text correctly. Otherwise, you'll see the raw HTML tags.
TaxFree wanted to know if it's possible to have a form pop up and dock to the side of the Access window like the property sheet does. Standard Access forms can't dock that way; you can pop up and position a form with VBA, but true docking is not available to regular forms.
N269 asked if validation rules at the table level in Access can use VBA to return true or false, similar to triggers in SQL Server. In Access, table-level validation rules must use expressions. However, Access does support data macros, which provide some trigger-like behavior for table events, although they aren't as robust as SQL Server triggers. For advanced validation logic, handling it in VBA at the form level is still the most powerful choice.
That wraps up today's batch of questions. Don't forget to check out my website for even more resources and the troubleshooting guide mentioned earlier. Access Day is approaching, and I've just released a new Access Developer course covering features like a desktop file drop system. Visit the Captain's Log for more database insights and some fun behind-the-scenes discussion as well.
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 Summing values in a form footer and fixing errors
Combining data from multiple tables using union queries
Storing people types in a single table with type IDs
Handling Access databases that work locally but fail on client machines
Troubleshooting environmental issues in Access deployments
Using dynamic file paths in VBA OutputTo commands
Managing spaces in file paths with VBA string handling
Summing calculated fields in continuous form footers
Creating calculated fields in queries for use in forms
Implementing rolling three-year date cycles with update triggers
Triggering automated actions with form events and macros
Switching between Access and VBA editor windows with toolbar buttons
Difference between tables and queries in Access
Configuring rich text formatting for long text fields
Positioning and resizing forms to mimic docking behavior
Limitations of table-level validation and use of data macros in AccessArticle Sometimes, Microsoft Access appears to struggle with what should be a simple task: calculating a sum in a form footer. You might expect that adding up numbers would be straightforward, but often you are met with an error in the footer - usually that infamous pound error message where your total should appear. It turns out there are good reasons for this behavior and, more importantly, reliable ways to resolve it.
Let's talk through some of the most common scenarios that cause problems when you try to sum up data in a form footer. The most usual suspect is trying to sum a calculated value directly from an unbound text box in your form. In Access, the Sum function in a form footer only works on fields that are part of the form's underlying record source - that is, fields that exist in the table or query the form is based on. If you add a text box to your form that is not bound to a field in your data but instead has a formula like =[SomeValue]*2, Access won't let you sum this at the footer level - it just cannot see those values for aggregation because they don't exist in the data source.
If you try to use something like =Sum([UnboundTextBox]) in the footer, Access will display an error because at the time it is calculating that summary, those values do not have a field to refer to in the underlying table or query. The solution is to move your calculation into the record source itself. This usually means making a query based on your table, bringing in all the fields you need, and adding your calculation as a new field in that query. For instance, suppose your table has a field called CreditLimit and you want to sum up double the credit limit for all records. You would create a query with a field like CreditLimit2: [CreditLimit]*2. Save this query and then set your form's record source to that query. Now, in the form footer, you can use =Sum([CreditLimit2]) and Access will handle it with no problem because CreditLimit2 is now a legitimate part of the data set.
Another common sticking point involves union queries. You might have separate tables for customers, employees, and vendors, and want to select from all of them in the same combo box. Union queries are perfect for this sort of thing as long as the fields you want to combine are identical across tables - such as ID, FirstName, and LastName. You can write a query like:
SELECT ID, FirstName, LastName FROM Customers
UNION
SELECT ID, FirstName, LastName FROM Employees
UNION
SELECT ID, FirstName, LastName FROM Vendors;
Just make sure the field names and order match, and you can use that union query as the row source for your combo box. However, don't try to make your union query overly complex - complex aggregations are better handled with staging tables or by processing your data with simpler individual queries first. The most flexible and robust database design would actually involve creating a single People table with an added field to indicate whether someone is a customer, employee, or vendor, but if you need a quick solution, a union query is practical.
Sometimes, code works perfectly on your own computer but breaks when you move the database to another machine. This is often due to environmental differences: different versions of Access, missing references, untrusted locations, or even simply not being logged into an Office account. If everything works for you but not a client, always check the environment first - make sure the client is logged in, Office is activated, and all updates are applied before spending time rewriting code.
Handling dynamic file paths in VBA can also cause issues, especially when using commands like DoCmd.OutputTo with file paths that have spaces. When you hard-code the path in your VBA code and it is in quotes, Access is happy. But if you get the path from a text box control on your form, the path needs to be concatenated correctly as a string. For example, if your text box is named FilePath, you might need to write:
DoCmd.OutputTo acOutputReport, "MyReport", acFormatPDF, Me.FilePath
If that fails, make sure your text box value does not have leading or trailing spaces and is being treated as a string. If necessary, wrap the path in additional quotes or use double double quotes if passing as a command argument, especially if spaces are involved.
Access cannot truly "automate" updates unless something triggers them. For example, if you want a table's date range to roll forward automatically every three years, then you need to decide what event will kick off that update. This could be a button click, running an update query, or some VBA code set to run OnOpen or OnLoad of a form. For most users, this kind of update is easiest to accomplish with a simple macro or an update query that's tied to a button. The key thing to remember is that events in Access only run when triggered; nothing will happen automatically unless you set it up that way.
For beginner users, knowing when to use a table versus a query is important. Tables are where your actual data lives. They are the foundation and store everything permanently. Queries are just views, or "recipes," that tell Access how to pull data out of the tables, combine it, filter it, or calculate new values. Think of a table as your pantry of ingredients, while a query is a recipe that tells Access which ingredients to use and how to prepare them.
If you encounter OLE server or ActiveX control errors when using buttons on a form, often the problem is in missing references or in the OnClick property of your button. Always check that you have no missing references in your VBA editor (Tools > References) and ensure your button's OnClick property is set to [Event Procedure] and points to the correct code.
Another network-related problem arises when you move your Access backend file - say, from a physical server to a virtual server using software like VMware. Even though it seems like nothing has changed, Access is a file-based system and is sensitive to things like network latency, sharing settings, and how the file system is managed by virtualization software. Access works best on a simple local area network. Complications are nearly always environmental, not Access itself, so be careful when changing your infrastructure.
If you are using Rich Text fields in Access and notice that your notes sometimes display with visible HTML tags and sometimes with formatting, check the properties both in your table and in your form. Both the table field and the form control must be set to Rich Text. If either is set to Plain Text, Access will display the raw HTML.
Finally, some users want forms to "dock" to the right of the Access window the way the property sheet does. Unfortunately, regular forms cannot be docked like the property sheet. The best alternative is to use a popup form and use VBA to manually set its position and size on the screen.
Table-level validation in Access is limited to expressions. You cannot use VBA directly in a table's validation rule. For more advanced scenarios, you can try using data macros, which are closer to database-level triggers, but for most logic, using VBA code attached to form events is still the standard approach in Access.
By understanding these points and using the correct objects in Access, you can avoid common errors, troubleshoot more effectively, and streamline your database development process. |
