Quick Queries #12
By Richard Rost
2 years ago
VBA Editor Text Size. Separate Subforms. More!
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are for me to answer your questions that may not need a whole video themselves. In today's video...
Topics Covered
- Adjust size of text in VBA editor
- Separate subforms for products and services
- Where's the U? (Temp Conversion Video)
- Integrating Access with WhatsApp
- Lock Specific Fields doesn't work for Combo Boxes
- Multiple Tables when you only need one
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq
Intro In this video, we'll talk about a variety of Microsoft Access questions submitted by viewers, including how to adjust the text size in the VBA editor, whether you should use separate subforms for products and services, organizing items with multiple statuses in a single table, and handling combo boxes with default values in forms. We'll also cover some feedback about temperature conversion videos and mention potential integrations with WhatsApp. If you've got questions about Microsoft Access, this is a great place to find quick answers. This is Quick Queries 12.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. It's that time again. I haven't done one of these in about eight months, according to my calendar. So, it's time for another Quick Queries, which is quick answers to queries about Microsoft Access, which may or may not be about queries. This is number 12. I've done a bunch of these before.
I get a lot of little questions here and there, either on YouTube or posted in my forums on my website, and they might not merit an entire video on their own, but put a bunch of them together, and you've got a pretty cool bunch of questions and answers to those questions. So here we go.
Here's one of my forums from Ludwig, one of my Platinum members. He says, "Can I adjust the text size in the VBA editor? I think it's already too small, and you can't zoom out." Yeah, I get this question from time to time too. Kevin, one of my moderators, answered this. You go to Tools, Options, and then click on the Editor Format tab. So if you're in your database and you go to one of your modules or any of your four modules, or any of those will work, and you're like me and your eyesight's not that great, you go to Tools and then Options, and you go to Editor Format, and right here, you can change the size as big as you want. You can change the colors and all that stuff, hit OK, boom, there you go, nice and big. I think I might leave it there for class; that's a lot better for my videos. Let's see, Tools, Options, yeah, I like 12 point. I'm going to leave it there for now. Thanks, guys. I would have never thought of that. I knew it was there. I know I've changed this setting before, way in the past, but I didn't think that for videos, it might make it easier for you guys to see it.
Okay, next. Here's one from James in my forums, one of my Gold members. He says, "Hey guys, I've created an invoice service order and an estimate form. The problem is I can't figure out if I need two different detail sub-forms for products and services. In other words, one for products and one for services. I have multiple products and services. What's the best way to accomplish this correctly?" So essentially, he's looking at something like my order form. And here, I've got an order, and then below that, I've got a single subform where we put products in here. And James is asking if he should do two separate subforms, one for products and one for services. Personally, I would suggest keeping it all in one subform and add a field in here if you need to separate the products from the services. So basically, add another ID, right, that's got whether it's a product or a service, and then you can put your quantity in there, your cost, all that stuff. And then, if you need to separate them, for example, if you have to show all of your products separate from your services, just use a grouping order in your report, right? This is what my moderator, Scott, told him to do, and that's exactly what I would recommend as well. You got your items; you got your type - is it a part, is it a service - and then later on, if you want to be able to break those up separately, see my grouping video to group them with different grouping levels in your report, and I'll put links to that video down below. See, I'm telling you, I've got the best moderators ever on my website. If you've got questions, post them in my forums. These guys are awesome. And of course, they learn from the best, so... No, I'm just kidding, I'm just kidding.
Alright, so next up, recently, I released a couple of videos on how to convert temperatures between Fahrenheit, Celsius, and Kelvin. And a lot of people asked me where the U is. And so, I had to share this that one of my good friends from high school, Dan, posted on my Facebook page. And I'll just leave it there so you can read it. Pause the video if you need to. Oh, I moved it. Sorry. I'm trying to get the rest of the thread in there. There we go. Love you, Dan.
Now here's a question. I get something related to WhatsApp at least once or twice a week; someone posts a question about how to do something with WhatsApp. I'll be honest; I don't use WhatsApp. I think I experimented with it when it first came out, like what, six, seven years ago, but it's not very popular here in the United States. I know it's very popular overseas. I know in Europe and Africa, you guys use it a lot. But I'm getting more and more people asking me about it. So here's my question to you. Here's my quick query to you guys. Do you guys use WhatsApp? If so, post a comment down below. Let me know. And if enough people are interested, maybe I'll look into it and see what kind of integrations we can do between Access and WhatsApp. That's not something that I have any experience with. But if there are ways to do stuff with it, we can figure it out, and we'll post some videos. But only if enough people are interested. I'm not going to waste my time unless lots of you are into it.
Next up, we've got a comment from YouTube from VicMano2, one of my YouTube channel members. He says that the technique that I use in 'lock specific field' doesn't work for combo boxes. And the reason being is because, normally with regular values, you get a null value on a new record. And then when the value is put in there, it will have something other than null. However, with combo boxes, generally, you're linking to a numeric field, and that default value is usually zero. In this video, I show you in the form current event to check if is null, credit limit. If the value is null, then locked is false, and make the background color white. Otherwise, if it's anything other than null, we're going to lock it and make it gray, right? So when a value is put into it, it goes gray and it's now locked. And that event will run every time you go from record to record. But if we look at most related tables right here, I got customers, orders, I got contacts, right? Every customer can have a contact associated with them. And usually, when you make a related field, ContactT, for example, right? That's the foreign key, and the default value is normally zero. So, if I had a combo box on the contact table or on the contact form, its default value is normally zero, which looks blank, right? Because there is no customer zero, but it's not null. So you just have to change the code to say if it's null or zero in the case of a combo box, then you can put data into it; otherwise, the field is locked. Okay? So, good question.
Okay, this is a variation of a question that I see all the time. This particular person says, "I have multiple tables for items, received items, issued items, returned items, damaged items, and I want to create one form and add buttons with received, issued, returned, and damaged. And after entering the details, I want to click one of the buttons and move the data to the corresponding table." What is the one thing that all of these things have in common? They're all items, right? They're all the same thing. So whenever you have multiple types of the same thing, you should have one thing table. You should have one items table. In that table, have a field that indicates what kind of item it is. Received, issued, returned, damaged. 1, 2, 3, 4. And you can link that to another table if you want with the descriptions. There's no reason to have four separate tables for the same thing.
Someone posted a similar question in my forums not too long ago on my website. It's the same thing. I've got basically quotations and then sold orders, and it's all the same thing. A quotation hasn't been approved yet; the customer hasn't actually purchased it yet. If it's the same thing, put it in the same table and just add a field to differentiate. Now, yes, there are some exceptions, like archiving data. For example, if you got ten years of orders in your order table and you don't need to go back that far, maybe you only need to keep the last three, you can archive that old information, and maybe you know, an order archive table. But when you do that, it makes it more difficult to do reporting, right? Because then you got to use union queries and all kinds of other tools to bring stuff together. But if we're not dealing with a table getting too big, just put all this stuff in the same table. Then you can use one form and have a combo box to pick which one it is, received, issued, returned, or damaged, or is it a quotation, invoice, check, or not, right? Go to the box, right? Just like employees, you've got new hires; you've got existing employees; you've got terminated employees. You don't need three separate tables for those. Put them all in the same employee table with the status. Where are you? Are you terminated? Click "terminated," but you don't move it to a different table. In fact, I get asked this one so many times; a couple of years ago, I made a video on it, and I still get asked about this all the time. It's one of the most popular questions. So, I'm going to keep repeating myself until everybody gets it. I had one customer; he had a table for airlines, rental car companies, hotels, and restaurants. What do they all have in common? They're all basically companies, right? So unless you've got lots and lots of fields that are unique to that particular type of organization, you don't need to have special airline stuff, right? Generally, it's all the same stuff. You want to add, you know, one or two extra fields here and there? That's fine. Like if there's only certain questions that you put in for airlines, you know, got a handful of fields, no big deal. But by and large, these are all the same type of thing, right? They're all companies. Put them in a company table and just have a combo box to differentiate it as a restaurant, a hotel, alright.
I thought we had time for today, folks. That's a Quick Queries. If you've got questions that you want answers to, post them down below. I'll be honest; I don't go through my YouTube comments as often as I'd like, so if you're a member of my website, post them on my website or even visit the visitor forum on my website. You'll find that the moderators on my website are awesome, but I don't check the comments on YouTube maybe once a week. I check the comments, and I'm still way behind answering those, so I do as I can. Usually, the way YouTube is set up is that they're normally sorted by the newest ones first, so if you get a comment that you made, like two years ago and I haven't gotten to it yet, as new ones come in, you still get pushed down the list. So post it again, and maybe I'll get to it soon. But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, please give me a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free. Click the bell icon and select All to receive notifications when new videos are posted. Want to learn more? Click the Show More link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here, right? The name, the videos up here, there's a little Show More down there, right? Down at the bottom. It's kind of hard to find. Once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
And YouTube no longer sends out email notifications when new videos are posted like they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list. And you can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly. Now, if you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the join button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my code vault, lots of VBA source code in there, template downloads, and much more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I got some puppies to feed. But don't worry, no matter what, these free TechHelp videos are going to keep coming as long as you keep watching them, I'll keep making more, and they'll always be free.
Now, if you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over four hours long, and you can find it on my website or on my YouTube channel. I'll put a link down below you can click on. And did I mention it's completely free? The whole thing, free, four hours, go watch it. And okay, okay, a lot of you have told me that you don't have time to sit through a four-hour course. So, I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster than about 30 minutes. And no, I didn't just put the video on fast forward. I'll put a link to this down below as well.
If you like Level 1, Level 2 is just a dollar. That's it, one dollar. That's another whole 90-minute course. Level 2 is also free for paid members of any level, including supporters. If you're a member, go watch Level 2. It's free. Want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the TechHelp page, and you'll have a better chance of getting it answered. And while you're on my website, be sure to stop by my Access Forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course, on YouTube. Yeah, I'm on Facebook too, but I don't like Facebook. Don't get me started. Now let's talk more about those member perks if you do decide to join as a paid member. There are different levels: Silver, Gold, Platinum, and Diamond. Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks. Gold members get all the previous perks plus access to download the sample databases that I build in my TechHelp videos, plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list for me to read them. And if I like your question, you've got a good chance of it being answered. You'll also get one free expert-level class each month after you've finished the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject. And I cover lots of different subjects, like Word, Excel, VBA, ASP, lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.
And finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors' page that will be shown on each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.Quiz Q1. How can you adjust the text size in the VBA editor in Microsoft Access?
A. Go to Tools, Options, and then the Editor Format tab to change the font size
B. Right-click on the editor window and select Zoom
C. Use the Zoom Slider at the bottom of the editor
D. Change the text size from the database property sheet
Q2. When handling products and services in an order form, what is the recommended approach for subforms?
A. Use one subform for products and one for services in the main form
B. Use a single subform with a field differentiating type (product/service)
C. Use separate forms for each product and service type
D. Only include products, excluding services in the subform
Q3. What is the suggested method for separating products and services in reports if they share the same table and subform?
A. Use different tables for each and run separate reports
B. Apply grouping levels in your report based on the type field
C. Manually copy data into different Excel sheets
D. Print products and services on different printers
Q4. What was mentioned about integrating WhatsApp with Access?
A. Integration is fully supported by default in Access
B. The instructor has extensive experience with WhatsApp integrations
C. The instructor is open to exploring integrations if enough viewers are interested
D. WhatsApp integration is not possible at all
Q5. What issue was discussed regarding locking combo box fields and their default values?
A. Combo boxes default to null values on new records
B. Combo boxes typically default to zero, which can visually appear blank but is not null
C. Combo boxes cannot be locked based on their value
D. All value checking should ignore zero values
Q6. What is the one thing that received, issued, returned, and damaged items all have in common according to the video?
A. They are entirely different entities
B. They all belong in separate tables for clarity
C. They are all the same type of item and should be in one table
D. They require different forms for each category
Q7. What design practice is recommended when you have multiple types of records like quotations and sold orders?
A. Create a new table for each record type
B. Use a single table with a field to indicate the status or type
C. Discard records after conversion from quotation to order
D. Never store both quotations and orders in the same database
Q8. Under what situation did the instructor say an archive table might be justified?
A. When the table becomes too big and old data is no longer needed for reporting
B. When archiving would make reporting easier
C. When every record must be permanently available
D. When there are less than 100 records in the table
Q9. What is the main benefit of having active, new, and terminated employees in the same table with a status field?
A. Easier to differentiate and manage employees using one form and queries
B. It wastes disk space
C. It is required by Access to use a single table
D. It is more confusing than using multiple tables
Q10. What membership perks are available at the Silver level or above?
A. Access to all extended cut TechHelp videos and one free beginner class monthly
B. Only access to free sample databases
C. Eligibility for a Diamond sponsor
D. Direct one-on-one training with the instructor
Q11. Which level of membership gives you access to all beginner-level full courses for every subject?
A. Silver
B. Gold
C. Platinum
D. Diamond
Q12. What should you do if you have a question and want the instructor to answer it in a video?
A. Post on YouTube comments only
B. Email directly to the instructor
C. Submit the question on the TechHelp page for higher priority
D. Wait for a live stream session
Q13. What did the instructor suggest to viewers who want quicker Access training instead of the full four-hour Level 1 course?
A. Watch the Access for Beginners video, which covers basics in about 30 minutes
B. Watch Level 2 instead
C. Only read the blog posts
D. Skip all video content
Q14. Regarding YouTube email notifications, what was mentioned in the video?
A. YouTube still automatically sends email notifications for new videos
B. The instructor sends email notifications himself if you join his mailing list
C. Email notifications are no longer possible for new videos
D. They are sent only to paid members
Q15. What is the key guideline emphasized throughout about database design when dealing with similar entities?
A. Use as many tables as possible for each possible type
B. Combine similar entities into one table with a distinguishing field for the type or status
C. Create different databases for each entity type
D. Relate every table to every other table using foreign keys
Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-A; 9-A; 10-A; 11-C; 12-C; 13-A; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I'm here to provide quick answers to common questions about Microsoft Access. These questions usually come from my YouTube audience or my website forums, and while they may not each need a full video, I like to group them together so everyone can benefit from the answers.
The first question is about the VBA editor. Ludwig wants to know if it's possible to increase the font size in the editor because the text is too small by default. Absolutely, this comes up fairly often. You can enlarge the text by going into the Tools menu, then selecting Options, and navigating to the Editor Format tab. Here, you can change the font size and adjust colors to your liking. This is great for visibility, especially when presenting or recording videos, and it's helpful even in everyday use if you have any trouble reading smaller text.
Next, James has been working on an invoice service order and an estimate form and wonders if he needs two different subforms for products and services. In my experience, it is best to keep all order items in one subform. You can simply add a field to distinguish whether the line is a product or a service. If you need to separate them in your reports, you can use grouping and sorting tools in your report design. There's no need to complicate things with multiple subforms unless your line items are vastly different.
Recently, I posted a couple of videos about converting temperatures between Fahrenheit, Celsius, and Kelvin. Some viewers pointed out the missing "U", and I shared a funny post from an old friend highlighting that. It is always fun to interact with you all and see your feedback and jokes.
I also receive many questions about integrating Access with WhatsApp. I am not a user myself, as WhatsApp isn't very popular in the United States, but I know it's widely used elsewhere. I want to hear from you: if you use WhatsApp and would be interested in Access integrations with it, let me know in the comments. If there is enough interest, I'll explore it further and maybe create some tutorials on the topic.
Another good question came from VicMano2. He mentioned that the 'lock specific field' technique does not work the same for combo boxes. The issue is that combo boxes often default to a value of zero rather than null, which makes it tricky when you are trying to detect when to lock the field. When using combo boxes that are linked to numeric fields, make sure to check for both null and zero values to determine if the field should be locked or editable.
Next, there's a recurring question about table design. Someone asked if they should use separate tables for received items, issued items, returned items, and damaged items, each with their own form and button to move data between tables. This is a common misunderstanding in Access database design. All these are simply different states or types of items, so you should keep them in a single items table and use a field to indicate the current status of each item, such as "received," "issued," "returned," or "damaged." This makes reporting and data management much simpler. The same principle applies to employees or companies with different types. Don't create multiple tables when a status field will do the job.
That wraps up this Quick Queries session. If you have questions, you are welcome to post them in the comments, but for the fastest response, ask them in my website forums, as I'm not always able to keep up with YouTube comments. I still try to read as many as I can, though, and I encourage you to ask and participate.
If you enjoyed this Q&A session, it helps me out a lot if you give the video a thumbs up and subscribe to the channel so you'll get notified when I post new content. If you want to receive email notifications when new videos come out, you can join my mailing list and choose how often to get updates.
For those looking to support my work, you can become a member of my channel. There are several membership options available, each offering different perks, such as access to extended cut videos, my code vault, database templates, priority question handling, and more. If you're not ready to become a paid member but still want to support the channel, you can always make a one-time donation via the tip jar link.
I also want to encourage everyone learning Access to try out my free Access Level 1 course. It's over four hours long and covers the basics of building forms, queries, and reports. For those who prefer a quicker introduction, I offer a condensed Access for Beginners video as well.
Higher membership levels get additional perks, like free expert or developer courses, and access to training materials for other subjects like Word, Excel, and VBA. Our forums are also a fantastic place to connect with others, ask questions, and receive help from experienced moderators.
Thank you for watching. For a complete video tutorial with step-by-step instructions on everything discussed here, you can visit my website at the link below. Live long and prosper, my friends.Topic List Adjusting text size in the VBA editor
Choosing between separate or combined subforms for products and services
Adding a type field to distinguish products and services in a subform
Grouping items by type in an Access report
Handling combo box default values in form field locking
Locking a field based on null or zero value in combo boxes
Consolidating multiple similar tables into a single table
Differentiating item types with a type field in one table
Using a status field for different item life cycle states
Recommendations against redundant tables for similar entities |
