Quick Queries #24
By Richard Rost
12 months ago
Close All VBA Windows, DropDown, My Profile Pic
In Quick Queries #23, we'll explore a range of quick queries, tackling issues such as managing VBA windows with MZTools, troubleshooting combo box dropdowns, securing your database from copying, and sorting database entries. Perfect for Access users seeking efficient solutions to common problems.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Microsoft Access Quick Queries, VBA Windows management, MZTools VBA, combo box dropdown, non-relational combo box issue, Access combo box troubleshooting, auto-number reset Access, Access alphabetical order, Access table vs query, Access status function, status box Access.
Intro
In this video, we'll talk about a variety of Microsoft Access Quick Queries, including managing VBA windows with MZTools, troubleshooting combo box dropdown issues, preventing databases from being copied to another computer, creating auto-numbering that resets each year, sorting names in alphabetical order, and the differences between tables and queries. We'll also discuss using the status command, voice dictation tools like Microsoft's speech-to-text, and share some background on my profile picture. This is Quick Queries 24.
Transcript
Welcome to another TechHelp Quick Queries video. This is number 24 brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. What's a quick query? Well, it's answers to questions that you guys sent me. You post them in my forums or on my YouTube channel, and maybe the question doesn't merit a full video on its own, but I lump them all together and put them in this and not call it quick queries. So I can talk about whatever I want to talk about, and you have to listen to it.
So, all right, let's see what we got today. This one is in the forums on my website in response to my close VBA Windows video, where I tell you that if you have a bunch of VBA Windows open, all these different Windows in your VBA editor, it's going to make things slow in your database starts. Thomas, one of my Gold members, says that there is a tool called MZTools. MZTools has a bunch of different options available for the VBA editor, including close all Windows but the active one. So that's pretty cool. I have not checked this out yet, but I'm mentioning it. A couple of other people have used it before, Kevin's used it. In fact, he gave a link, I'll put this link down below. It's mztools.com. I'll check it out when I get a chance, but Thomas is a whole bunch of different features that it has. No, this is not paid advertising for them. It looks like a pretty cool tool. Like I said, I haven't tried it out. Some of my guys have, so check it out. Looks like they have VBA stuff, Visual Basic stuff, VBA 6 stuff. I love VBA 6. So yeah, they've got some good stuff on here.
Next up in the forums, Michael says he's got a combo box on a form, and he's trying to use the dropdown command. He says when it's relational, it works fine, but if it's not relational, in other words, it's one that you type in the values yourself, it doesn't work. He says it looks like it opens and closes fast, but it doesn't stay open. All right, let's see if we can recreate this now. Let's go to the TechHelp free template here. I'm going to go to my order form where I know I have a combo box, and I'm just going to delete everything out of here just to keep this nice and simple.
All right, there is all that stuff. Now, what he's saying is when you tab to a combo box, or when you click on it, when it gets focus, you can use the on-got focus event, which is this guy. For a combo box, you can say the combo box name, in this case, customer combo.dropdown, and then we'll open it up so the user can more easily and quickly pick a value. And this is what happens. Tab, tab, tab, and it opens it up, and you can pick a value. Now, what Michael is saying is that it doesn't work if it's not a relational combo box. So let's try it, let's add one. I'm going to also copy this text box and put it here so it's after it in the tab order. So there's one more box between them just so we can tab, tab, and see it.
All right, so let's add another combo box, which is right here. Drop it right there. This time, we're going to type in the values that I want. What are the list of values? Well, let's get Kirk, we got Spock, we got Bones. All right, so we got three items in there next. We'll just remember it for later use. Next, what label do you want? Doesn't matter. All right, so here we got a combo box called combo 17. I will rename it to the Trek combo. Now in the Trek combo's on got focus event, I'm going to say trekcombo.dropdown. When that box gets focused, it should open up. Save it, close it, close it, close it. Let's open it back up again.
Ready? Tab, tab, tab, there it is. Tab, tab, and there it is, it opened up. So I'm guessing maybe there's some other code somewhere else in your form that's preventing that from opening, maybe a timer event could kill it. We need to play with it more, we need some more information in order to try to figure this one out for you. But at least you know you're not crazy. What I recommend in times like this is to run down my troubleshooter. Back up your database, restart Access, restart all your Access databases, compact and repair, compile the database, and so on. There's a whole list of things to try in order to see any weird problems, sometimes it's something you wouldn't even think of. Just sometimes restarting your computer will fix the problem.
In this particular case, try it with a different form, try it in a different database file, try importing this form into a different database file. There's all kinds of different stuff you can try, and I try to keep it so the easy stuff's up top and the more difficult stuff's down below. There's a huge list, go down it. If you have any problems, questions, still can't get it to work, let me know, post a follow-up, we'll see what we can do.
All right, head over to YouTube next, Mitchell. Thank you very much for your tip, your donation. I appreciate that very much. I'd like to give a shout-out to anybody who sends me a few bucks, buys me a coffee, or beer, whichever you want to do it. Thank you very much. Stocker Store wants to know if it's possible to prevent your database from being copied to another computer. Yeah, I got a whole seminar on it. It's called the registration seminar. I'll put a link to it down below. It uses a couple of different techniques where you can basically have it give you a code. You know how sometimes you see like your bank will text you, type in the six-digit code in the website, and it'll let you in? Well, I do the same thing here. There's lots of different options, check it out. I'm going to be updating this pretty soon to allow for an automated solution over the web too, so it's pretty cool.
Sometimes I get asked the same questions over and over again, and I'll just post a link to a video I've already done on this topic, but I like to bring it up in the quick queries too because some of you might not realize I've already covered that. Graeme wants to know if there's a way to create auto-numbering in Access that automatically resets every year or every month. Yeah, so I got a video on it. It's sequential. I use annual coding, where it resets every year, but it's very easy to change the logic so it resets every month. Remember, auto numbers are not for you. Auto numbers are for Access, used internally. You should not care what those auto numbers are. I get a whole separate video on that too. I'll put links to both of these topics down below.
This next question comes from Phoebe Jacobs. She says, how do you put names in alphabetical order if you already have your database completed and you need to insert a name you may have missed? This is a great question, and this is a perfect beginner question because I've seen this question a million times. People in my beginner classes always ask this. You can't think of a table in Access like you think of a spreadsheet in Excel. Because that data could be in there in any way that you want it to be. When you're in Excel and you want to insert a row in here, you right-click, you insert, and now you got a blank row. A lot of people that are coming from Access get confused when Access doesn't behave this way.
In Access, you have a customer table. If you want to insert someone here, you can go new record, but it brings you down to the bottom. You don't insert the same way that you do in Excel. And that's because this table is very fluid. This information, you can sort it however you want. So if you want to add somebody, like new guy. Once that record's in here, now you can sort this column anywhere you want to, right-click, sort, and it will sort those guys like that. But better yet, you can use a query and the query just lets you display this data in different ways. So you can display it with the last names and first names together, for example. You can make a query to sort it and display it however you want.
A table and query... the table just holds your data. All of the data in your database is in your table, one or more tables. In fact, that's the beauty of Access as you can have multiple related tables. Your orders can be related back to the customer with just that ID. That's a little more advanced concept. But just think of the tables as your containers and your queries are the ways you can view those tables differently.
I see you're already off to a good start. You're in my Access beginner level one class, lesson seven. Keep going, keep watching the rest of that. Watch level two. It's on YouTube free. By the time you finish with that, I guarantee you'll have a good understanding of the difference between tables and queries. Excellent question. Keep learning.
There's another question I see all the time. What is the status command that you're using in the demonstration? I use the status function that I wrote in a lot of my different videos. And I have a whole separate video on how I wrote it. If you watch the videos, I usually list prerequisites. And one of the prerequisites I almost always list is my blank TechHelp free template database video. That's where I build that video or build that database. I build the video to show you how to build the database. The blank template video shows you how I built my blank template database.
I use this database so I don't have to reinvent the wheel with every video that I make. I also have a separate video that just teaches you how to use the status box. All the status box is a little text box that I put on a form. You can just send it information so you can display it for the user or for yourself as a developer. You don't have to use message box, which stops execution and is annoying. That's why I like to use my status box. Just recently I made a follow-up video where I show you how to make the status box global for the entire database. So any form anywhere in the database can use that status box.
Go watch all these videos and explain all that in detail for you. Shout out to ShadowDragon for help assist. I love it when you guys help me answer questions. That's awesome. On my website, I've got a ton of awesome moderators that help answer questions there. But for some reason, the weird moderation tools won't work on YouTube as well as they do on my website. Of course, I built them. So on YouTube it's just me, so I appreciate when you guys help.
Resume CEO wants to know what I'm using for my voice dictation extension. I was using voice-in before, which only exists in your web browser. It's a pretty good extension. I think I did a video on it previously. Right now I'm just using Microsoft's built-in speech-to-text tool, which you hit Windows key H. Of course it can't run right now because I'm running my voice recording or my video recording software. So they don't work at the same time. But this is pretty good. It's pretty accurate. It works in any application, including inside of Access. It comes with Windows. It's got some quirks though that I don't like. Like as soon as you click something or hit anything on the keyboard, it stops recording.
I'm actually in the process right now of building my own voice recognition software using Google's API, the speech-to-text API. It is an application that I'm building in Python. I'm teaching myself Python right now. It will stay running constantly. So I can just keep talking and type some stuff and click some stuff and keep typing and keep talking. It will stay running in the background and constantly translating. I'm going to build into that the ability so that when I hit send and it sends it to whatever application I'm working on, it will run it through a chat GBT first and correct it for spelling and grammar. Still working on it. Trust me, when it's done, I'll post it. You guys will be able to see a copy of it. But right now I'm just using Microsoft's basic speech-to-text. It works fine. Good enough.
Nick says, love your series. I was wondering, I see your picture hasn't changed in five years. What are you using to stay looking so young? So my secret is to not change a profile picture. No, this picture of me is probably a little story behind it. This picture of me is probably about, I'm going to say 10 years old, maybe a little more, maybe 12 years old. I never wear a suit and tie. I live in Florida. So come on, we don't wear suits and ties down here. Even if I did go to a normal office job, which I don't, I work from home in my shorts. But even if I did have a normal job, I probably wouldn't wear a tie. But we were going to a wedding this night and I had a suit and tie on. I looked in the mirror and I'm like, damn, I look good. I was freshly shaved and I had just gotten a haircut. So I snapped the selfie and I've been using that as my professional picture ever since. So that's me probably circa 2013 or 14.
But I don't usually post live pictures of myself or updates or recent pictures because a few years ago, I was involved in an incident and I got sucker-punched and it left me blind in one eye. If you guys have been with me for a few years, you probably heard me talk about it before. When the camera is up close to my face, my eyes still track fine. If you're looking at me from 10 feet away, my eye still moves and it follows the other eye. But as the camera gets closer to my face, my eyes do not converge. If you take your finger and move it close to your nose, your eyes will cross. Mine doesn't. So it tracks with the other eye. As my right eye looks to the left, my left eye will continue looking to the left too. It looks weird when I'm up close. So I don't usually take, you'll never see me do live video with my face. Plus I hate shaving.
For those of you who'd like to see a current picture of me, this one's a couple of weeks old. This is my beautiful fiancée, Lauren, and me. We just took a cruise down into the Caribbean, including St. Lucia.And I got a nice hat, and that's an updated picture of me. Not shaving and with some sunglasses on. So there you go. And yeah, I put on a few pounds in the last 10 years, but I just bought a new home gym. It's in the garage I got to put together. So that's my goal for this coming week.
There's another little selfie of me on the cruise. Looks like my hair is thin just a little bit in the last 10 years. I always tell my barber not too much off the top because he can tell it's thinning. I'm pretty happy, though. My grandfather, my mother's father, had like no hair when he passed away at like 55. I'm 52. And my father's father, my other grandfather, had a full head of gray hair when he died in like his 60s or 70s. So I think I'm doing okay.
Plus, I look at my idol Captain Picard. He was 47 when he started Next Generation. 47. So I think I'm doing pretty good.
But that's going to do it, folks. That's your quick queries for today. Number 24 in the can. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Close all VBA Windows but active one
Combo box dropdown command issue
Troubleshooting non-relational combo box
Preventing database copying to another PC
Creating auto-numbering that resets annually
Alphabetical order for names in Access
Status command in Access tutorials
Voice dictation using Microsoft's tool
Building custom voice recognition software
Using speech-to-text in Python
Background on profile picture usage
COMMERCIAL:
In today's video, we tackle a variety of quick queries ranging from Access VBA tools to combo box quirks. You'll discover handy options from MZTools for managing VBA windows, understand combo box dropdown issues, and explore how to troubleshoot them. We also dive into preventing database copying, creating auto-numbering in Access, and understanding the differences between tables and queries. On the lighter side, I reveal a bit about my voice recognition journey and how I maintain a youthful profile picture. Don't miss it! 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. What is a quick query in the context of the video?
A. A full-length tutorial on a specific topic
B. A compilation of short answers to user-submitted questions
C. A live Q&A session with viewers
D. A detailed technical breakdown of Access features
Q2. What tool is mentioned in the video for managing VBA editor windows?
A. CodeLite
B. Visual Studio Tools
C. MZTools
D. EditorPlus
Q3. What issue is Michael experiencing with his combo box in Access?
A. The combo box does not populate data
B. The combo box disappears from the form
C. The combo box dropdown opens and closes quickly for non-relational data
D. The combo box does not save typed values
Q4. According to the video, what are auto numbers in Access primarily used for?
A. Creating visual identifiers for records
B. Counting the number of records in a database
C. Internal use by Access, not for display purposes
D. Sorting records in alphabetical order
Q5. How does Richard suggest resolving the issue with the combo box dropdown?
A. Reinstall Microsoft Access
B. Use a different programming language
C. Try troubleshooting steps like restarting Access or importing forms to a new file
D. Disable all events on the form
Q6. What does the registration seminar mentioned in the video focus on?
A. Enhancing database performance
B. Techniques to prevent database copying
C. Creating complex queries in Access
D. Designing forms and reports
Q7. What is the primary use of queries in Access according to the video?
A. Storing all the data in the database
B. Displaying and sorting data from tables in various ways
C. Designing the layout of tables
D. Backing up database information
Q8. What voice dictation tool is Richard currently using as mentioned in the video?
A. Voice In
B. Google's Speech-to-Text API
C. Microsoft's built-in speech-to-text tool
D. Dragon NaturallySpeaking
Q9. What is Richard's recommended solution for maintaining the appearance of alphabetical order when inserting new records in Access?
A. Use Excel instead of Access
B. Manually rearrange the table rows
C. Use queries to sort and display data in the desired order
D. Create a separate table for new records
Q10. What future tool is Richard planning to build as mentioned in the video?
A. An Access database management software
B. A new programming language for Access development
C. A custom voice recognition software using Google's API
D. A virtual assistant for database queries
Answers: 1-B; 2-C; 3-C; 4-C; 5-C; 6-B; 7-B; 8-C; 9-C; 10-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
Today's TechHelp tutorial from Access Learning Zone explores a variety of topics related to Microsoft Access. I'm your instructor, Richard Rost, and today I'm addressing some of the questions you've submitted via our forums and YouTube channel, collected together in this session called Quick Queries. These are inquiries that might not necessitate a full video response but are important enough to discuss and share helpful insights.
First, let's talk about a tool called MZTools, which was mentioned in the forums by Thomas, one of my Gold members. If you have numerous Windows open in your VBA editor, it can slow down your database's performance. MZTools offers options like closing all Windows except the active one, which could be quite beneficial. Although I haven't used it myself yet, other users like Kevin have and reported positive experiences, so it's worth checking out.
Next, Michael in the forums asked about a combo box issue on a form where the dropdown command works fine with relational data but not with self-typed values. He's noticed the box opens and closes quickly without staying open. If you're encountering a similar issue, ensure that you configure your combo box with the 'On Got Focus' event to help it stay open when activated. If it still doesn't work as expected, it might be due to other code in the form interfering with the command. Sometimes, a form reset or a database restart can resolve such anomalies.
Mitchell, thanks for your generous contribution. Let's address your question about preventing a database from being copied to another computer. I have a seminar focusing on registration techniques, one of which involves a unique code entry that users must provide to access the database. This method is similar to two-factor authentication used in online banking. You can find more details about this in my seminar linked below, with updates anticipated to include web-based automated solutions.
Graeme asked about creating auto-numbering in Access that resets yearly or monthly. Auto numbers in Access are intended for internal database management. However, if you need sequential numbering that resets periodically, I have a video demonstrating how to reset numbers annually, which can easily be adapted for monthly resets.
Phoebe Jacobs inquired about inserting names alphabetically in an existing database. It's important to understand that Access tables differ from Excel spreadsheets. In Access, data in tables is more fluid and is typically sorted through queries. Feel free to add records as needed, then use sorting options in queries to organize your data accordingly. As you progress in my beginner classes, you'll better grasp how tables and queries function differently.
For those of you curious about the status function in my videos, it's a tool I created to display messages without interrupting operations. It uses a small text box for status updates and has been outlined in a separate instructional video, showing you how to implement and use it effectively.
In the realm of voice dictation, though I've previously used browser-based extensions, I now use Microsoft's built-in speech-to-text feature available through Windows. It functions well, albeit with quirks. I am also experimenting with building my own voice recognition software using Google's API, combining continuous dictation with correction options.
Lastly, regarding my youthful-looking profile picture, it's a decade-old image from a wedding but accurately represents when I last dressed formally. Due to an incident that affected my eyesight, I prefer not to do close-up videos. However, I've shared more recent images from a recent Caribbean cruise.
To wrap up, thank you for tuning into this Quick Queries session. I hope you found these insights helpful. For a complete video tutorial with all the detailed steps discussed here, visit my website using the link provided below. Live long and prosper, my friends.
Topic List
Close all VBA Windows but active one
Combo box dropdown command issue
Troubleshooting non-relational combo box
Preventing database copying to another PC
Creating auto-numbering that resets annually
Alphabetical order for names in Access
Status command in Access tutorials
Voice dictation using Microsoft's tool
Building custom voice recognition software
Using speech-to-text in Python
Background on profile picture usage
|
