Quick Queries #39
By Richard Rost
8 months ago
Compact Runtime, Normalization, Nested Subforms
In this Microsoft Access tutorial, I will answer a range of viewer-submitted questions about topics like running Compact and Repair operations in the runtime version, best practices for table normalization and handling optional item attributes, setting up and using a shared VBA library across multiple databases, adjusting the zoom box size and font, filtering reports based on filtered forms, enforcing appointment times on the hour, understanding the IsMissing function, and referencing nested subforms.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Compact and Repair in runtime, Compact and Repair VBA, custom Compact and Repair button, normalization RPG database, item attributes table design, item table vs name-value pairs, shared library database VBA modules, Access frontend deploy shared library, zoom box size reset bug, zoom box font size issue, Access update fixes bugs, rolling back Office update, print list box collection report, list box text align right, DISTINCT vs DISTINCTROW query, filter report by form query, force appointment times on hour, IsMissing optional parameter Access, referencing nested subforms, Access t-shirt online store.
Intro
In this video, I answer a variety of Microsoft Access questions from viewers, including how to use VBA to compact and repair databases in runtime, different ways to store item properties in tables, when to use local versus shared Access library files, and how the zoom box remembers its size and shape. We'll also talk about aligning list box columns, the difference between DISTINCT and DISTINCTROW in queries, filtering reports based on form criteria, restricting appointment times to specific minutes, using IsMissing for optional parameters, and how to reference controls in nested subforms.
Transcript
It's Friday. Welcome to another Quick Queries TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Quick Queries are answers to queries about queries and other stuff Microsoft Access related. These are questions that don't normally need a whole video on their own, so I throw them all together into a Quick Queries video.
Let's head to the mailbag. Well, it's not so much really a mailbag as it is stuff in my forums and such. Anyways, first one comes from Richard Garber.
He says he doesn't understand why you can't use the Compact and Repair feature in the free runtime, and Sammy came back with, while your end users should not be doing Compact and Repair, that should be something that's your, the developer's, job, which I agree with.
Then Richard came back with, why can't an end user, because he's planning on distributing this to other people who aren't necessarily going to have an administrator in their office, and he wants to know why you can't just let them do a Compact and Repair themselves.
Basically, you can, but you have to do it with VBA. You do not have access to the Compact and Repair button in the GUI, that is, in the ribbon, from the runtime edition.
So you'll have to make your own button, maybe put it on a menu somewhere, that will run the Compact and Repair VBA code. It will compact and repair. It's still in there. It will still work with the runtime edition and with an ACCDB file, because your backend, where your tables are, should be an ACCDB file.
You just can't do it from the ribbon, and I have a video that explains how to do it with VBA. Here's the video on that. You can see me make a little compact backend button and it does its thing.
Next up, a question from Michael. He asks, aside from continuing through this course (because he's currently on Access Expert Level Two), how does one gain wisdom when it comes to normalization, or is it just through the school of hard knocks?
He's building a database for an RPG, cataloging items, and all items have some common basic properties: name, description, weight, that kind of thing. But he's got one-off things, like weapons that have damage, damage type, to hit bonus. Potions have an effect and duration.
He's like, how do you put this all together? I said, when it comes to table structure, I personally lean toward keeping all of the items in the same table.
Remember, you've got a whole bunch of fields that aren't necessary for every item. For example, effect and duration wouldn't be applicable to swords, just to potions and maybe wands and such.
So on the form, you can display these fields using a little bit of VBA logic. If the item type is a potion, then show these fields, and so on like that.
It doesn't hurt to keep them in the table. They're not taking up any extra space if there's no data in those fields. Just a tiny, teeny, tiny little bit of space. It's negligible.
That's the beginner or even the expert-level solution. If you want to go more advanced with it, you could have a second table that's got name-value pairs in it.
For example, you have an item table and a data table. The data table will have all the data that's optional for your items. Here's your item table: you've got a sword and a potion of healing. You'd have fields that are common to all the items. Every item is going to have a cost, usually, notes, and a weight, perhaps, if you're doing encumbrance.
Then here's the data table over here. You can see these are the data items for item number one, the data points: damage, which not all items have. For the ones that do, you pick damage, damage type, and handedness.
This all applies to the sword, item one. These things down here apply to item two: you have effect and duration, which a sword wouldn't have. Where do you get this list?
You can get more complicated with this stuff. You can then have another table that's got the list of variable items for each of these. For the sword, in that table, you might have damage, damage type, handedness, and whatever other attributes are just for that sword.
You pick the same thing for any type of weapon: dagger, warhammer, and so on. Then you have a list of optional ones based on the type of weapon. You can get a lot more complicated than this.
I actually have on my list to do a full video on something similar to this--obviously not with D&D items--but I've got a customer-related one coming up soon. This is a more advanced way to handle it, and it's a little more flexible.
Again, there's nothing wrong with putting these different fields straight in the item table, especially if you're talking about a reasonable size database. We're not talking about hundreds of thousands of records here. You're talking about, what, a couple hundred items, maybe? So I would just throw it on the same table and only display the fields for the items that you need.
You have to remember, when you're working with small and mid-sized databases, like small company databases or hobby databases like this, it doesn't have to be designed perfectly. You don't have to use perfect normalization for every single table. It's okay to make little shortcuts sometimes just to save yourself some work.
You're not building a Fortune 500 enterprise database here. If you are, well, then you can afford my full course and you can learn how to do stuff like this properly.
Michael, I hope that answers your question.
Next up, Rocky Williams wants to know if a local copy is necessary for a shared library database.
What he's talking about is covered in this video. It's basically where you can take all the functions and subroutines that you want to have in multiple databases, and you don't want to have to copy them between the different databases.
For example, I made a function in here called DoubleIt. It basically takes a number and returns its double. Okay, great. It's a simple example, but you want to use this same function in all of your 20 different databases. You don't want to have 20 copies of this thing floating around because if you want to update it and change it, you have to change 20 of them.
So you can make a shared library file. Its only job is to hold a few modules that contain stuff you want to be able to use in all of your databases.
But Rocky's question is, do you have to have this shared library file set up locally as well, or can you just have it referenced, like in your server folder? I said you should have it on each user's machine, just like you do with the front end.
If you have users that are accessing a shared file like that, aside from your shared table file, if it's front end stuff--and VBA functions and subs are basically front end stuff--I would have it copied down to the local user's machine too.
You have less chance of running into any kind of file corruption or record locking problems. Even though there are no records in it, I've seen it happen. Less chance of corruption.
If you have a batch file or something that copies down front end updates, I would also copy down this file as well. Keep a local copy on your users' workstations and that way everything's kept up to date, which is what my Access Updater does when it copies down your front end files if you make an update.
Next up, Eric from Australia says one thing he can't figure out is how to get the font size in the zoom box to default to something like 16 or so. It will change for that session, but when he restarts the database, it defaults back to 11 again.
The problem that Eric was having--we discussed this, and he told me in a follow-up email--turned out to be an issue with Access, maybe a bug. He did an Access Office update and it fixed the problem.
I just wanted to bring this up because people have asked me the same thing in classes before, and in comments and such, and I just want to show you something.
So if you've got your zoom box--I use it a lot in class--here it is. If you move it and resize it like that and then either hit Cancel or close it, it won't remember its size and shape. See, it comes right back to where it was.
But if you do move it and resize it, let's do this, and hit OK, now when I open it up again, it keeps its same size and shape. It will still center itself.
If I make it bigger like this and then hit OK and then open it back up again, you get the same size. The font does not behave that way, though. If I change the font to 72 point and hit OK, even if I hit Cancel, it still keeps that font size. So font doesn't behave the same way.
I'm going to put it back to 24 where I like it. So just the dimensions of the box itself will be remembered. Also, the data in the box too: if you do this and then hit Cancel or close the box, it doesn't save it back here. You have to hit OK.
You have to resize the box where you like it, make your changes, and then hit OK, and then it'll bring that data back here and save the dimensions of the box. The font operates differently, but like Eric said, he did an Office update and that problem went away because he would change the font, then restart the database, and the font was back to where it was before. So that was probably just a bug. Little bugs happen.
Sometimes the Access team will add one thing and fix something over here, and it breaks something over there. I've had databases like that too. I've had software like that. I've done programming my whole life.
I used to build all kinds of stuff in C and Visual Basic, and you make one little change that you don't expect to affect anything and all of a sudden you break half the database. That happens.
When in doubt, if something's not behaving itself, run down my troubleshooter. I'll put a link to that down below. Always try an Office update. See if maybe that problem has been fixed.
Worst comes to worst, you can just roll back to a previously known good version. Here's a video on rolling back. I've had to do this a couple of times myself, so I don't Office update very often.
Next up, let's head over to YouTube. Rick Sheldon says he just finished a Beginner Level One course and found it very useful. As a reminder to everybody else, it's free on my website and on YouTube. Go watch it.
He says he's used Access over the years to track his coin collections, cost, date acquired, condition, type, and so on. He uses it to print lists to take to his coin shows.
Why not just take a laptop and show off your database? That would be awesome.
The memory of details is not a useful skill of mine. Yeah, mine either. I don't remember details either.
He says that he'd be interested in my take on his using Access. Well, I can't see your database. I'd love to see some images. Feel free to visit my website and post some screenshots in the forums.I'd love to see what you've got going on there, but the important thing, and what I said in my reply, is that whether you're running a business or managing a coin collection, the beauty of Access is that it adapts to your needs.
Even if you're a basic, just-getting-started beginner, you can build a cool little database without any programming. You can track your stuff, set up your tables and your forms. You don't need reports if you're not printing anything out.
If you've got really simple lists, like you want to print out just a query view or a table view to take to your coin collecting event, that's all you need. You don't have to make anything really complicated.
The one thing that I've found, and this is true for me and for many of the students that I've taught over the years, is that once you learn a little bit more, once you stretch your brain just a bit and you learn some more things that you can do, you're like, "Oh my god. Oh, that's cool. Now I got to go do that in my database." So you're changing it. Even stuff that I've built, like right now, I'm currently revising my calendar database that I built 15 years ago.
I know so much more now about Access than I did 15 years ago when I built the thing. When I first took it apart, I'm like, "What was I thinking?" Now that's going to be even cooler because I know a whole lot more about it.
Just learn a little bit every day. That's why I make these TechHelp videos, to teach you one new thing every couple days. That's how you stretch your brain. Your brain behaves just like a muscle. The more you put it under a little bit of stress, force it to learn something new, then it'll behave better for you.
Like memory. I'm awful with memory too. I try to memorize stuff but there's just so much crap up there. Not a lot of room in my attic for new stuff.
Oriel Ramirez wants a bend diagram with Starfleet colors. I think maybe you meant Venn diagram. I'm not familiar with a bend diagram. I even Googled it, I couldn't find anything. Sorry for the messages coming in. I usually mute everything on my machine when I'm recording but I forgot to unmute my messenger. So I apologize for that. If this was a real class, I would have re-recorded that segment, but we're friends here. Quick queries is, we're all just friends.
Anyhow, unless Ben is some new Starfleet officer I haven't met yet, I'm pretty sure I asked ChatGPT to throw together a quick Venn diagram with the Star Trek crew. It gave me that, so we got our three Starfleet colors. I don't know who's orange on the ship, but oh well.
Felipe says, "I found your video right in time. It came to me," and he asked how to align text to the right in a list box. Yeah, I cover that in this video here: list box column align right.
Great, I'm glad I helped you. My question is, were you searching for that? As I tell everybody, if you ever search for something that you're trying to do in Microsoft Access, if you're searching on Google or Bing or YouTube and one of my videos doesn't come up, what are you supposed to do? You let me know, you send me your keyword phrase you used, and I will make a video on it. That's my goal: anyone searches for anything Access related, I want to be there.
Going to my distinct versus distinct row video. I'll put a link to this down below.
Herbunk asks: Wait, hold on, someone's beaming in! He asks, "What if you added a field from the order table to the query, such as order date? Would both of Richard Rost's orders come up in a separate record in the query?"
Okay, so this depends on whether you're working with DISTINCT or DISTINCTROW. If you're doing that last example that I gave with DISTINCTROW, where you've got the customer over here and the order over here and you just bring down the customer table fields, then you'll only see a list of unique customers. If you bring down an order table field, well, you know what, this usually falls into my try it and find out for yourself rule. You'll learn more if you figure this out on your own.
You don't want me to spoil this for you when you can just very easily try it yourself. You'll learn better if you try it yourself. If you only have stuff from the customer table and you run it, you get just those unique customers. As soon as you add something from this table, look what happens. See, now the DISTINCTROW is going to give you distinct rows.
In the future, try that yourself. Bring this back over here, try this yourself. Don't ask me, just try it. You'll learn better. Dig into it, experiment, play with it. I've got a whole page and a whole video just on this topic. Go watch this.
Yes, I'm here to teach you, but I hate spoon-feeding everything. I like to show you guys new stuff, but if you watch a video and you're curious about something, just try it, give it a shot. If you try it and you play with it for 10-15 minutes and you still can't figure it out, then ask me, because then I know at least you tried it. Trust me, that's the best way to learn.
When I was learning this stuff, I didn't have a YouTuber or Google or any of that stuff. I had to go dig through books or spend three hours trying to figure it out myself. That's the best way to learn.
I don't even know how I'd pronounce that, but how do you filter a report if the form is filtered by a query? Well, if the form is based on a query and the query is where the parameters are set, the criteria are set, then if you base the report off the same query, it should get the same set of records.
This video here was to show you how to take a form that you filtered in the form and apply that same filter to a report. If you just open a form and that form is getting its criteria direct from the query, then any report that you base on that should also get its data from the same query.
I do the same thing in my invoicing video. When we open up the invoice report, it's based on the data in the form, and the query gets the data from the form. And yes, you can have a loop that does that too. The form has a field that provides data to the query, and the query opens up the report so that this stuff can bounce around too.
Oh, and I just wanted to take a moment to let some of you guys know that I read all of the comments you guys post. I don't always have the time to reply to them all. Sometimes you'll just get the little Vulcan hand, or a "You're welcome" for a thank you with that kind of stuff. I just don't have the time to write responses to all of these, and the ones that I think will make good answers for the video, I'll definitely put in the video. But sometimes, as I'm just going down, I mean I read them all, but sometimes I just hit the button and just here's your thing because I just don't have time to go through and write responses to everybody.
I do spend more time on the stuff on my website and the forums there, but YouTube comments I try to at least go down them a couple times a week. Just because I gave you the Vulcan hand doesn't mean that I didn't read your comment or that I'm just trying to blow you off. And you are welcome.
Gregory asks, in my before update and after update video, I show you how to check to make sure that you don't already have an appointment at that time. Gregory says he wants to know how you could force compliance with appointments only starting on the hour, preventing the user from inputting a time like 10:05.
That's a fantastic question, and I hate that I didn't think of that while I was recording the video. It's very easy to solve. All you have to say is, in your before update event, just check to see if the minute entered equals zero, or 15, or 30, or 45 if you want to do on the quarter hour. If that's not the case, then just tell them, "Hey, you can only put in appointments on the hour." That's really easy to do and that's a great question.
I'd probably throw it right after the year check.
Right here, check appointment time. I'd say, instead of your appointment time, say minute. If the minute appointment time is not zero, and the minute appointment date time is not, let's say you're doing it every half hour, then you just put in here minutes.
Appointments must be on the hour or half hour only. You can add other things in here too, 15, 45, whatever you want. Debug compile once in a while. Then when we come back out here and open our appointment form, if I try to put something new in here at today at 14, right? Hour and half hour only. OK, so I get 6:30. Then I continue to send the confirmation email. No. And that's how you do it. Great question. I love that.
DMP04 said, "I started using Access in 1995 until three years ago. I think I used IsMissing maybe one time. IsMissing is something you can use to see if an optional parameter is there or not." Yeah, I don't think I've ever used it in a real product, in a real distribution database. I've used it myself a couple of times, but I think the thing is that you know when you need it and when you don't need it. As long as you, the developer, are the one working with it, you can decide whether to use it or not. I don't know. I've never really had a real need for it either, so there's a lot of stuff in there that you probably will use maybe once or twice, but it's there if you need it. That's the nice thing.
Next up, Ernie says, "What is the complete path for nested subforms?" These get complicated. I have a whole separate video on this coming out where I'm going to go into it in detail. But basically, let's say you took your order form and dropped it into your customer form, so now the customer has each of his orders down here, and inside the order form, you have order details.
Let's say you wanted to get that guy right there, which is some extended price. I made a button over here that does it and it looks like that, and here's what the full name of it looks like:
It's Forms!CustomerF!OrderF is the name of the subform control in the CustomerF. Dot Form, that means we're going into another form. What's the form name? It's OrderF, right? Now in that form we're going to go to the OrderDetailF. Dot Form, and in that guy we're going to look for some extended price. You just keep going dot form dot form down the line until you get to what you're looking for.
Again, I got another video coming out on this where I'm going to go over it in more detail. There are some caveats as to things you have to watch out for, but that's the basics of it. And of course, if you want to learn more about nested subforms, there you go.
Before I let everybody go, I just want to let you know I set up a couple of online stores because some people were bugging me. I set up a store at T Public where you can get some t-shirts with the Access Learning Zone on it. I had some hats, some baseball jerseys, a throw pillow. I put together the "Save it, Close it, Open it".
What else we got? We were thinking about putting together a MAGA hat, where it says something about Access like, "Make Access Good Always" or something. Someone said, "Make Access Great Again," but it doesn't need to be made great again. Access has always been great.
What else did I do? Oh, I also put together the "Debug, Compile Once in a While," that's coming, and "If you set it, you gotta forget it." That's on a mouse pad.
What else do I have?I'm thinking you put it together in a large desk pad with maybe some access shortcuts on it and stuff like that. But yeah, check it out. I'll put links down below. I have two online stores set up right now. Yeah, get yourself some swag.
I'm not doing it to make money, folks. I get like a dollar each time I sell one of these. It's just cool stuff. I just think it's neat.
All right, that's going to do it, folks. That is your Quick Queries for today. That's number 39. I hope you learned something. Live long and prosper, my friends. I'll see you next week.
TOPICS:
Using VBA to compact and repair databases in runtime
Storing item properties in a single table versus multiple tables
Implementing a flexible item attribute system with name-value pairs
Separating common and variable item attributes using related tables
Displaying form fields conditionally based on item type using VBA
Advantages of storing optional fields within the main item table
Consequences of local versus shared placement for Access library files
Remembering zoom box size and shape in Microsoft Access
Office update fixing persistent zoom box font size issue
Aligning list box column text to the right in Access
Difference between DISTINCT and DISTINCTROW in queries
Effect of adding related table fields to DISTINCTROW queries
Filtering a report using a query with criteria from a form
Restricting appointment times to only allow certain minutes using VBA
Using IsMissing to check for optional parameters in Access
Referencing controls in nested subforms using dot notation
COMMERCIAL:
In today's video, we're talking through a bunch of viewer questions about Microsoft Access, covering topics like why Compact and Repair is not available in the runtime edition, how to handle table design for items with different properties, whether you need a local copy of shared library files, and quirks with the Zoom box font settings. We'll also discuss right-aligning text in a list box, how DISTINCT versus DISTINCTROW works in queries, filtering reports when your form is based on a query, forcing appointment times to land on the hour, why IsMissing is rarely used, how to reference controls in nested subforms, and more. If you want practical answers to common Access questions and a few laughs along the way, you'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. Why is the Compact and Repair feature not available to end users in the Microsoft Access runtime edition?
A. The functionality is missing from the runtime entirely
B. The Compact and Repair operation requires administrator privileges
C. The GUI button for Compact and Repair is not present in the runtime, but it can be accomplished using VBA
D. Compact and Repair only works with MDB files, not ACCDB files
Q2. What is generally advised regarding Compact and Repair operations in distributed Access databases?
A. End users should run Compact and Repair regularly
B. Compact and Repair is unnecessary in modern versions of Access
C. The developer should handle Compact and Repair, not the end user
D. Access automatically compacts and repairs on database close
Q3. If you want runtime users to be able to perform Compact and Repair, what solution is recommended?
A. Show them how to access the ribbon through a shortcut
B. Distribute the full version of Access with the database
C. Create a button that runs Compact and Repair via VBA
D. Use third-party tools only
Q4. What is the most straightforward way to handle different types of items with unique attributes in a small RPG database?
A. Make a separate table for each item type
B. Place all items in one table, using additional fields for attributes that only apply to certain types
C. Only store items with identical attributes in the same table
D. Normalize all item attributes into separate tables, even for small databases
Q5. What is an advantage of using a name-value pair data table for item properties in a database?
A. It allows unlimited storage without table size limits
B. It enables more flexibility and custom attributes for different item types
C. It guarantees faster searches for item data
D. It is the only way to store optional attributes
Q6. For a database used by a small company or hobbyist, what is an acceptable design trade-off?
A. Absolute normalization for all tables
B. Prioritize perfect design regardless of size
C. Allow a few shortcuts to make design and use easier
D. Avoid using additional fields in the main table
Q7. Why does the instructor recommend storing shared library databases locally on each user's machine?
A. Because network access is always faster than local access
B. To minimize file corruption and record locking issues
C. Because VBA modules can only run on servers
D. To avoid violating licensing terms
Q8. What happens regarding the zoom box size in Access when you resize and close it using OK versus Cancel?
A. Both OK and Cancel save the new size
B. Only Cancel saves the new dimensions
C. OK saves the size and shape, Cancel does not
D. Neither OK nor Cancel saves the new dimensions
Q9. What should you do if Access is not behaving as expected after you change a setting (like zoom box font size)?
A. Ignore it and hope it fixes itself
B. Check for and install Office updates first
C. Immediately reinstall Access
D. Assume your database is corrupted
Q10. What is a key benefit of using Microsoft Access for even basic or hobby database tasks?
A. Access always requires programming
B. Access adapts to your needs and can be used with no programming
C. Access is only for professional developers
D. Access cannot print simple lists
Q11. According to the instructor, what is the best way to keep improving your skills in Access?
A. Memorize every feature by reading the help file
B. Change your database only once, then leave it alone
C. Learn a little every day and experiment on your own
D. Only use templates provided by Microsoft
Q12. When experimenting with DISTINCT and DISTINCTROW in a query, what did the instructor emphasize as the best learning method?
A. Only use provided examples, never experiment
B. Try it out yourself in Access to see the effect
C. Stick with DISTINCT only, as DISTINCTROW is obsolete
D. Always ask the instructor before changing queries
Q13. If you want a report to show exactly the same records filtered by a form that is based on a filtered query, what should you do?
A. Manually copy the filter to the report each time
B. Base the report on the same query as the form
C. Create a new table for the report each time
D. Use macros instead of queries
Q14. How can you enforce that appointments in an Access calendar database only begin on the hour (or quarter hour)?
A. Remove the minutes field from the table
B. Set up a before update event to check the minute value and prompt the user as needed
C. Limit the hours available in the database
D. Use only unsigned integer fields for time
Q15. What is the complete path format to reference a control in a nested subform within Access VBA?
A. Forms!SubformControl.SubformField
B. Forms!MainForm!SubformControl.Form!NestedSubformControl.Form!ControlName
C. Forms!ControlName!Form
D. Forms!MainForm.Control.NestedControl
Q16. What is the reported purpose of the online stores mentioned toward the end of the video?
A. To generate substantial profit for the instructor
B. To provide a way for fans to get fun Access-themed merchandise
C. To sell proprietary Access databases
D. To offer exclusive database features
Q17. What is the suggested first troubleshooting step if you encounter a strange bug or unexpected behavior in Access?
A. Ignore it because bugs are normal in Access
B. Immediately rebuild your database from scratch
C. Run the instructor's troubleshooter and/or try an Office update
D. Purchase a third-party troubleshooting guide
Answers: 1-C; 2-C; 3-C; 4-B; 5-B; 6-C; 7-B; 8-C; 9-B; 10-B; 11-C; 12-B; 13-B; 14-B; 15-B; 16-B; 17-C
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 collection of smaller questions about Microsoft Access that do not require a full video of their own. Quick Queries serve as a way to address various database questions that come up on the forums or in the comments.
One of the first questions is about the Compact and Repair feature in Access Runtime. A student wondered why end users can't access Compact and Repair from the free runtime version. The answer is, the button is simply missing from the ribbon in the runtime edition. However, you can still trigger Compact and Repair by using VBA code. You can create your own button or menu to run this code so your end users have this feature, even if the graphical option is unavailable. Just remember, your backend tables should be in an ACCDB file. I have a dedicated video that demonstrates adding a button for Compact and Repair via VBA.
Another question came up about database normalization, specifically how to manage an items database for an RPG game where different item types have distinct properties. The student asked whether each item should be in the same table or if there's a better way to handle varying fields such as sword damage, potion effect, or weapon bonuses. My recommendation for most small or hobby projects is to keep all items in one table and simply allow some fields to remain empty for certain item types. It's not wasteful if unused fields remain blank—it only uses minimal space.
However, if you want a more advanced solution, you could create a related data table using name-value pairs for optional or unique properties. This involves creating an item table for general fields (like name, cost, and weight) and a second table to capture specifics like damage or effect, linked by item ID. This structure adds flexibility but is more complex to build. Unless you're managing a massive dataset, the simpler single-table approach is often best.
Next, Rocky asked about setting up a shared library database across multiple users. He wanted to know if his shared library file (which holds reusable VBA functions and subroutines) has to be copied locally or can stay on the server. I recommend keeping a local copy of the shared library file on each user's machine, just like the front end, to minimize risk of file corruption and locking problems. If you have a batch file that copies front end updates, use it to update your shared library file as well.
Eric from Australia had a question about the font size in the Access zoom box, which kept reverting to its default after closing and reopening the database. This turned out to be a bug that was resolved by installing an Office update. On a related note, the zoom box in Access does remember its size and position if you press OK instead of Cancel, but the font size typically reverts unless a fix or update addresses the problem. If you run into persistent issues, try running an Office update or check my troubleshooter instructions. In dire situations, rolling back to a previous version can help.
There was a comment from Rick, who shared how he uses Access to track his coin collection. He prints lists to take to events, and he asked for thoughts on this approach. My advice is that the beauty of Access is in its adaptability. Whether managing a business database or a hobby project, beginners can get great results with simple tables and forms, no programming needed. As you learn more about Access, you naturally find ways to improve and expand on your original designs. Just keep learning day by day and tweak your databases as your needs and your knowledge grow.
Oriel asked for a "bend" diagram in Starfleet colors. I suspect he meant a Venn diagram. For fun, I even created a quick Star Trek-themed Venn diagram but, as for orange, that one is a mystery.
Felipe needed to know how to align text to the right in a list box. I have a video covering exactly that. If you ever search for a specific Access solution and none of my videos come up, let me know what you searched for and I will create a video to address it. My goal is to have a resource for every Access-related question.
A question about DISTINCT versus DISTINCTROW in queries came up. If you bring down only fields from your customer table, DISTINCTROW will return only unique customers. But if you add a field from the related orders table, you'll get one record per unique combination (such as each order date for that customer) instead. The best way to learn is to try this out yourself, experiment, and see how the results change. I encourage students to play around with Access, and only ask for help after giving it a real shot.
Someone else asked about filtering a report when the form is already filtered by a query. If both form and report use the same query, any criteria set in the query will pass through to both. This is the same approach I use in invoicing examples, where the current form provides data to the query, which in turn feeds both the display form and printable reports.
I always try to read all comments and questions, especially those on my website and forums. I may not have time for lengthy replies to everyone, but if I don't answer fully, know that I still read your messages and I appreciate them.
Gregory wanted to ensure that users can only schedule appointments on the hour, not at arbitrary minute intervals. You can enforce this by checking in your form's Before Update event that the Minutes part of the time is zero (for hour-only appointments) or a small set of acceptable values (such as 0, 15, 30, or 45 for quarter-hour slots). If the user enters an invalid time, you can gently prompt them to use approved intervals.
A note from a viewer highlighted the rare usage of IsMissing, a function to check for optional parameters—something most Access developers seldom need but is available just in case.
Ernie asked for the correct syntax to reference a field in a nested subform. In Access, this gets lengthy. You chain references for each form and subform with .Form, appending the control names as you move down each level. I plan on making a detailed video guide on nested subforms, but this is the fundamental approach for referencing deeply-nested controls.
Lastly, I have set up a couple of online stores with Access Learning Zone swag, including t-shirts, hats, desk pads, and more, all just for fun. If you want some Access merchandise, links are available on my website.
That wraps up this Quick Queries session. If you want step-by-step guidance on anything you saw here, I have a complete video tutorial available on my website at the link below. Live long and prosper, my friends.
Topic List
Using VBA to compact and repair databases in runtime
Storing item properties in a single table versus multiple tables
Implementing a flexible item attribute system with name-value pairs
Separating common and variable item attributes using related tables
Displaying form fields conditionally based on item type using VBA
Advantages of storing optional fields within the main item table
Consequences of local versus shared placement for Access library files
Remembering zoom box size and shape in Microsoft Access
Office update fixing persistent zoom box font size issue
Aligning list box column text to the right in Access
Difference between DISTINCT and DISTINCTROW in queries
Effect of adding related table fields to DISTINCTROW queries
Filtering a report using a query with criteria from a form
Restricting appointment times to only allow certain minutes using VBA
Using IsMissing to check for optional parameters in Access
Referencing controls in nested subforms using dot notation
|
