Quick Queries #33
By Richard Rost
11 months ago
Books for Learning Access, Force Shutdown, Modal
In this Quick Queries tutorial, I will answer a range of viewer questions, covering topics like finding user groups in Toronto, recommended VBA books for Access, and challenges with using modal forms. You will also learn about Access features like auto-complete in continuous forms and using theme-based color indexes. I'll provide tips on managing the potential issues of having multiple modal forms open and discuss why I don't adhere to Microsoft's naming conventions, sharing my personal take on naming practices from my years of experience. Tune in for practical solutions and insights into common Access user problems.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, user groups Toronto, VBA book recommendations, Access 2016 Bible, Access programming by Example, Inside Out series, Sam's publishing Unleashed, Amazon affiliate link, primary key last record, autonumbers order, value list combo box, Microsoft Access themes, modal forms issues, Power BI teaching, TechHelp free template, database force shutdown, Hungarian notation, Northwind Traders naming conventions
Intro In this video, we'll talk about a variety of Microsoft Access questions submitted by viewers, including book recommendations for learning VBA, finding the primary key of the last record, handling auto-complete in forms, working with themed color indexes, dealing with multiple modal forms, and tips for managing force shutdowns in Access. We'll also discuss naming conventions, Hungarian notation, and a few related topics. If you're interested in practical Access advice and answers to common questions, this is Quick Queries 33.Transcript This is Quick Queries number 33. What's a Quick Queries video? Well, it's where I go over all the questions that people send to me, either on my website or by email or on YouTube. Lots of these questions don't deserve a video on their own, not that they don't deserve a video on their own, but that they're shorter answer questions. And so that's what a Quick Queries video's about. So let's take a look.
Next up, we've got Claudio asking if anyone knows of any user groups or events in Canada, specifically in the Toronto area, especially during July—quite specific, aren't we? They're living in Brazil, but I'll be in Canada during that time. No, I personally don't. I live in Florida, though I used to live in Buffalo, New York, so I used to be pretty close to Toronto, but I've been down here for the past 12 years now. I did a quick Google search. No more than you could do, and I didn't find anything off the top. So I can't help you there. Maybe someone else knows something. If you do, post in the comments down below.
Jackess wants to know if I'd recommend some books for learning VBA from Microsoft Access. Yeah, I got a couple on my shelf. Most of them are older, because I haven't actually bought any books in a while for Access. The last one that I bought, I think, was the Access 2016 Bible. In fact, I did a video book review of the Access 2019 Bible a little while back in 2019, I think, or 2020. So it's a couple of years old. Unfortunately, Access hasn't changed that much in the last five years. Some other books I'd recommend for the basics of VBA programming. The dummy's book's pretty good. Access programming by Example's pretty good. The Inside Out series is really good, although I don't think it's been updated in a while, but again, a lot of the VBA code hasn't changed. It's more about how it's written and the examples they give. These are some of the books that I learned from.
They used to make an Unleashed series. I think it was by Sam's publishing, and that was really good, too. I had all the Unleashed books. In fact, that's a look at most of them on my shelf. Yeah, just search on Amazon. They haven't updated that series since 2000, so that's a little bit older, too. But I'll put links to all these books down below for you, using my Amazon affiliate link, of course. I'll get a few pennies.
And don't forget, all of my developer courses, all my courses in general, but my developer ones, too, they all come with handbooks. So it's video training, but there's also a printable handbook that goes with it. So if you like reading this stuff, too, there's an optional PDF handbook right down here you can get. And there it is. It opens up in your browser, and you can read it. And it covers most of the stuff that's covered in the lessons. Where is it? Let's see, it's right at VBA. Lesson here shows you what we do. Screenshots. So yeah, I got books, too. One of these days, I'll get around to publishing some of these up on Amazon, but that's just a minute. Not enough hours in the day.
Alright, Sybil wants the value of the primary key of the last record in the table. And time is not important. So, you don't care about chronological entry. You care about the actual order they were entered into the table. Now, D first and D last generally return the first and last record in the current sort order. So if you change your sort order, then you might not get the actual first and last records. And if you're using autonumbers, autonumbers are not necessarily always in order either.
As far as the order they were entered, you could delete records, compact and repair. There's ways to insert records with autonumbers. If you delete auto number four, you can use an append query to sneak it back in there. So there are all kinds of tricks you can play. It generally gives you the first and last records the way the records are sorted right now. But again, that's why I don't like to rely on first and last. And it can change over time with compact and repair operations and stuff like that. I would much rather have some kind of either a time index, or if you care about chronological entry and you don't want to use time, you can have your own counter. That gets tricky with most setups. So it all depends on, I would use some other field rather than just the primary key.
Because me personally, I always use autonumbers as primary keys. And that's generally a pretty good way of telling when the record was added, but it's not perfect. So I would need more specifics on exactly what you're doing, exactly what kind of data your table is holding, and exactly why you need the last record and not the most recent record. Because I usually just use DMX, and if it's a table that I care about the entry, I put a timestamp on it. So I need more information to answer your question properly.
Next up, Armadougula says he wants to know if Access has an auto-complete for continuous form fields like in Excel, where it prompts text based on rows above in the same column. You know, it doesn't by default, but we could probably rig something like this up. I've got the gears smoking in my brain right now. You could use a value list combo box or even a regular combo box or a relational combo box. I bet you'll make it figure something out. Let me play with it and I'm going to add this to my list and see what I can come up with. But you're right, Access and Excel handle data completely differently. So I'd have to experiment with this and play with it, but I'll definitely add it to my list and stay tuned.
Michael says he just discovered the use of back-themed color index. And that is now his preferred method of setting colors according to the theme. And there's the code right there. Thanks for sharing, Michael. I almost never use themes. In fact, I talk about this in a lot of my videos. I just don't like users being able to manipulate their theme and change all my colors. And sometimes I build forms specifically with specific colors because I try to keep all the customers off of this color. And if I want it green, it's going to be green. But thanks for the code and I'll check it out. I have never seen this before myself. So they must have added this recently. But see, again, I don't play with themes much. There's certain things in Access that I never use, like the tab interfaces and stuff like that. So there's a lot of code that's new to me. But thank you for sharing.
Stuart says he has an issue where he has modal forms open sometimes two or three deep. That's your first problem. I'm going to stop you there. I never have more than one modal form open at a time. I try to avoid it if at all possible. I remember from years back, now we're going back. I had something similar to what you have happened where he just opened multiple modal forms and they just they start to go crazy. They don't like that. And then, yeah, you swap apps. You click back on Access. It's unclickable. You can't minimize; sometimes you got to kill Access. Is there a way to kill it? Not that I know of. No, but again, I try to avoid this situation. I try to limit it to one modal form at a time, and I'm very limited with what I do with modal forms. They've got their place. They're fantastic. They work great. But don't overuse them. Try not to go three deep if you can.
So the one problem I know that I've had with modal forms is that they'll open on a different monitor. Like the screen that I record on is my big monitor, on top of my laptop. My laptop is screen one. And sometimes if I'm recording a video for you guys and I open up a modal form, it opens up on my laptop screen. So that might be happening to you. I'd be opening somewhere where you just can't see it. Maybe in all of your modal forms, try baking in a button. You can click that's got a keyboard shortcut on it. Assign like an alt key, like an alt X or something. So you've got a button on each of your modal forms where if the user hits alt, exit will close it or bake in an escape key. Make a cancel button and hitting escape pushes that button. So if you've got modal forms open, hitting escape should close them down. You could try either one of those. Use a cancel button.
Missail says, can you teach Power BI, power by how we pronounce it? Like people who pronounce SQL, SQL. I don't like that. It's SQL. Anyways, I realized how I teach, having both Access and Power BI working together is something that managers like a lot. Yeah, okay. It's on my list, but I don't yet consider myself an expert with it. And I don't like to teach something unless I'm an expert with it. So Access, I'm an expert with, basic Excel, I'm an expert with. But I would need to do some more studying myself before I teach Power BI. So we'll get there. It's on my list. I got so much other stuff I want to do first, though, so I can't promise a date. But thank you very much for the compliments.
Georgia wants to know if the TechHelp free template is available to non-members. Yeah, it's of course. That's why it's called the TechHelp free template. It's a free database. You can grab it off my website if you want to. I recommend you start with this one, though. This is how I build the blank template. And then from there, I build it up until the customer form. We add the contact manager. We add the invoicing. Let's start here if you want to see how the whole thing is built. But if you want to just grab it off my website, then you can do that too. You'll find it on that page as well. But yeah, it's free. The members have their own version, which has some additional extra stuff in it. But yeah, go grab it. Get it. Learn it. Tear it apart. Figure out how it works.
All right, next up, this is one about my force shutdown video, where I show you how to force the database to shut down at a particular date and time. Or just a particular time. From mister EMH. EMH, you wouldn't happen to be a medical hologram, would you? Maybe. But anyways, he says he's got one issue. He says if one of the tasks the user is looking up a ticket, the database would pop up a little window as part of the query criteria. Okay, sounds like it's the enter parameter value window. To ask for the ticket number, and of course, that would sit there until the user input the ticket number and press enter. But if the user doesn't put in the ticket number, the window just sits there waiting for input. And when the auto shutdown happens, Access kind of hangs.
Yeah, that's a big problem. Any of those pop-ups, whether it's a message box, an input box, an enter parameter value window, those are all going to just sit there and force Access to freeze. So, the best option is to not use them if that's the case. You're going to have to replace them with your own. I've got videos on my website on how to make your own message box, how to make your own input box, that kind of stuff. And you won't have the same problem.
Or you can have another application watch your Access database. And when it's time to restart, if it's locked, force kill it. I've built a template for my database that does basically just that. I call it the Access Watchdog. And I've got a server machine running 24-7 that does things like check my website status and send out emails and all that kind of stuff. So, if it locks up, then I want, I need to reboot it. And if I'm not home or if I'm traveling, I don't want to have to log in and fix it, or get up at three in the morning. So, I built this and it will just force kill Access.
Now, generally, you want to try to avoid hitting end task on a Microsoft Access database, because you could corrupt it. But chances are, if Watchdog kicks in, if you have to end the task, Access has already frozen anyway. So, you're probably not going to interrupt any read-write because if you interrupt read-write to the disk, that's when you get corrupted databases. But I've been using this Watchdog template for, I think I built this maybe four or five years ago, and I've never had a corrupted database because of it.
Because whenever Watchdog kicks in, the database is already locked. And it's not doing anything. It's just sitting there. And it's usually sitting there at some kind of a VBA error, or it tries to connect on my website, sometimes that times out and it hangs. But this takes care of it. So, and you can do it with any old, if you know how to write a batch file, or some PowerShell scripting, or whatever, you can do it. This is just a little more elegant solution. And I think I used, yeah, I used VBScript for it. So, it's not hard to do. But you obviously can't do it from inside Access, because Access has frozen if that pop-up comes up. It stops all the execution. You have to have another program, a third-party program, or even a different—well, I wouldn't use a different Access database. You could run two Access databases side by side, but then I wouldn't do that. Because then if MSACCESS.exe itself locks up, it could freeze both of them. But it's kind of like when people say activate the medical, or emergency medical hologram, and then they just leave sickbay and forget to turn it off. That's annoying. It's just like when your Access database locks up. Okay, hope that helps.
Next up, TMD wants to know why I don't use Microsoft's naming conventions, like TBL customers, or QRY orders for queries, for example. And variables are LNG, STR, double, etc. So, the user knows what type the forms of variables are without looking for declares. Well, lots of reasons why. I'll give you the history. The notation that you're referring to is called Hungarian notation. That's a naming convention where prefixes indicate the object or data type, like TBL customers. And variables have like STR name.
Now, as far as the objects go, tables and queries and stuff, when I first started learning Access back in the Access 2.0 days, I was self-taught. I had a couple of books, and the book that I was learning from didn't have any naming conventions at all.And in fact, I learned from the Northwind Traders database too, and the original Northwind Traders database didn't have any naming conventions at all either. I'll talk about this in a minute. It just had tables named customers and a form named orders.
At that time, Access didn't clearly distinguish between tables and queries in dialog boxes, like with the form wizard. So, when you went to build a form, they were all just lumped together in one list. You'd see all the tables and queries to pick for your data source. You'd have customers and customers. You didn't know which one was which.
Now, it distinguishes between the tables and queries. But back then, it didn't when I was first learning. So, I had no idea about Hungarian notation at the time, and I just came up with the idea, well, I'm going to put T on the end of all my tables, and Q on the end of all my queries. So, I know which ones are which. And that's when I started doing that. I just did F and R for forms and reports to maintain consistency, and then I did M for macros, and MOD for modules. So, that was just something I came up with.
And remember, this is 1994, so I'm what? 22 at the time, and there's no internet. There's no Google. I couldn't learn this stuff online. So, I've been doing it that way for 30 years, and it's just stuck. But there's also some advantages to my system. All the customer-related objects could be sorted together. Customer T, customer Q, customer F. And it still distinguishes the objects clearly. It's just in a different position. Whether it's a suffix or a prefix doesn't really matter.
As far as the variable types go, string, long, that kind of stuff, I use those occasionally. Sometimes I'll put a string on the end of something, especially if I wanted to distinguish it between a text box and a string in memory. Like, I'll have first name text and first name string sometimes. So, I know which ones are which because you might have to handle them differently that way.
Using these prefixes can be helpful, but I find that good variable names and proper scoping usually make their purpose obvious. I don't have gigantic subs most of the time. So, if you're curious what it is, just scroll up a little bit, you can see it. I've been trying to declare variables close to where they're first used. That makes it easier, too.
I don't feel the need to rigidly stick to a convention as far as naming my variables go. Because most of them are very—what's the word I'm looking for? They're very self-explanatory. You know what first name is? You know what numb children means. That kind of stuff.
Ultimately, naming conventions are more about clarity and consistency. Microsoft's standard is fine. They didn't have the standard when I started learning, at least the Northwind Traders database didn't. But my system has worked fine for me and my students for decades. In fact, I find it funny because sometimes I'll go on Reddit or Quora or one of those other sites and I'll see someone post some code or help with a table or a query, and they're using my naming convention and I know I came up with that. So, I'm like, yes, they learned from me.
There's no single right way. It's just whatever works best for you and your team and your project. If you're working with a team of people, maybe some of whom have learned in college how to build databases the right way, then you might have to stick with this Hungarian notation. Yes, this is more popular in the world as far as real database, real "quote unquote" database people are concerned. But yeah, I just did a little googling and happened to find this: the original Northwind Traders that came with Access 2.0, and notice order space details. That's one of my things I never do. They're tables that's just categories, customers, employees. They're field names, product space name. I cringe. Never put spaces in field names or table names, stuff like that. So, they didn't have any consistency whatsoever when they put together the Northwind Traders database.
I learned on my own going with this in a couple of books and I just did it that way and it's stuck. It's a habit, and I like it. It works for me. It's consistent and I know what everything is.
I hope that answers your question, and thank you very much for it. All right, folks, that's going to do it. That's Quick Queries, Number 33. Hope you have a good weekend. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Recommending books for learning VBA
Access 2016 Bible and 2019 Bible review
Primary key of the last record in an Access table
Access auto-complete similar to Excel
Using theme color index in Access
Issues with multiple modal forms in Access
Force shutdown of an Access database
Handling Access pop-up message boxes
Naming conventions in Access and Hungarian notation
COMMERCIAL:
In today's video, we're discussing Quick Queries number 33, where we tackle viewer questions. Ever wonder about user groups or events in Canada? How about recommendations for learning VBA for Access? We've got input on setting colors with themed indexes, modal form issues, and even tips on handling database shutdowns. We'll chat about Access conventions, Hungarian notation, and more. Curious about Power BI integration? Access naming conventions? This video covers all that and answers your burning queries with humor and insight. 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 the main purpose of a Quick Queries video according to the transcript?
A. To provide in-depth coverage on a single topic
B. To address questions with longer, detailed answers
C. To answer a series of shorter questions that don't require their own video
D. To summarize the main content of previous videos
Q2. What recommendation is given for learning VBA for Microsoft Access?
A. Use only the latest editions of books
B. Start with the Northwind Traders database
C. Consult older resources like the Access 2016 Bible and other mentioned books
D. Focus exclusively on online tutorials, ignoring books
Q3. Why doesn't the speaker rely on DFirst and DLast for finding the first and last records?
A. They are always slower compared to other methods
B. They return the first and last records based on the current sort order, not entry order
C. They only work with tables containing autonumbers
D. They require additional tables to function correctly
Q4. What problem is associated with having multiple modal forms open?
A. It causes Access to display incorrect data
B. It makes all forms read-only
C. It can cause application instability and become unclickable
D. It automatically closes all forms when switching between applications
Q5. How does the speaker suggest addressing the issue of parameter input freezing Access during shutdown?
A. Avoid using Access altogether for such cases
B. Use built-in message boxes to replace parameter inputs
C. Replace built-in parameter inputs with custom solutions
D. Continuously monitor the parameter inputs to prevent freezing
Q6. What is the issue with Microsoft's naming conventions like TBL for tables and QRY for queries?
A. These naming conventions are not recognized by Access
B. The speaker prefers suffix-based conventions for clarity and organization
C. Microsoft's naming conventions are mandatory according to the speaker
D. Such naming conventions confuse users more than they help
Q7. What can be inferred about the speaker's approach to teaching and learning new software tools like Power BI?
A. The speaker often teaches without complete expertise
B. The speaker waits to gain full expertise before teaching a topic
C. The speaker relies on other experts to teach complex topics
D. The speaker considers himself an expert in all Microsoft Office products
Q8. How does the speaker feel about spaces in table and field names, based on the Northwind Traders example?
A. They are essential for clarity and understanding
B. They are fine as long as they are consistent
C. No opinion is expressed about spaces
D. Spaces should never be used in table and field names
Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-B; 7-B; 8-D
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, I will address some of the questions I've received from viewers like you. These queries often require shorter explanations, making them perfect for this format.
Claudio has inquired about user groups or events happening in Toronto, Canada, specifically in July. Unfortunately, I don't have any information on this, as I reside in Florida. However, having lived close to Toronto in Buffalo, New York, in the past, I attempted a quick online search but found nothing substantial. If anyone has leads, feel free to share in the comments.
Jackess has asked for book recommendations on learning VBA for Microsoft Access. While I haven't purchased many recent books, I suggest the Access 2016 Bible and Access programming guides by Example. The Inside Out series is decent, though a bit outdated. Some older resources, like the Unleashed series by Sam's publishing, are also worth exploring. I've reviewed some of these books, and I'll provide Amazon links for them below for your convenience.
Also, I want to remind you about the handbooks that accompany my courses. These handbooks complement the video material and are available in PDF format for those who prefer reading.
Next, Sybil wanted to know the value of the primary key for the last record in a table, not based on chronology but rather on entry order. Using DFirst and DLast with the current sort order might not yield the desired outcome due to possible discrepancies with autonumbers, deletions, or other modifications. To manage record entry effectively, consider using another field like a timestamp or a custom counter.
Armadougula is interested in whether Access has an autocomplete feature for continuous form fields similar to Excel. While Access doesn't offer this by default, it's an intriguing idea worth exploring. Using combo boxes could be one way to implement it, so I'll add this to my exploration list.
Michael shared his newfound discovery of using back-themed color index for setting themes. I don't typically use themes due to preference for consistent control over color schemes, but I'll look into this. It's notable that Access features I don't typically use, like themes, are sometimes updated without my full awareness.
Stuart brought up issues with having multiple modal forms open, causing stability issues in Access. I recommend limiting the usage of modal forms to one at a time to avoid such problems. Sometimes, forms might open on a different monitor, causing visibility issues. Implementing keyboard shortcuts or escape-key functionality can help manage modal forms more effectively.
Missail requested Power BI teaching sessions linked with Access, which many managers find valuable. While I plan to address this topic eventually, I'm not fully prepared to teach it yet. I want to ensure I have a thorough understanding before offering instruction in this area.
Georgia asked whether the TechHelp free template is available to non-members. Yes, it is accessible to everyone, serving as a starting point for building various components within Access.
The next question came from someone regarding a force shutdown video. They encountered a problem where pop-ups like "Enter Parameter Value" windows prevent auto-shutdowns from executing correctly. It's advisable to replace these pop-ups with custom input interfaces to avoid Access freezing during shutdown processes. Alternatively, you can use a third-party method, such as the Access Watchdog template I developed, to manage these situations.
TMD questioned my choice against using Microsoft's naming conventions, such as Hungarian notation. I started learning Access on my own, and the method I developed has worked well over the years. Naming conventions should prioritize clarity and consistency, and while Microsoft's approach is popular, my style has proven effective for me and my students.
I hope this session has been informative. For a comprehensive video tutorial with step-by-step instructions on these topics, visit my website through the link provided below. Live long and prosper, my friends.Topic List Recommending books for learning VBA
Access 2016 Bible and 2019 Bible review
Primary key of the last record in an Access table
Access auto-complete similar to Excel
Using theme color index in Access
Issues with multiple modal forms in Access
Force shutdown of an Access database
Handling Access pop-up message boxes
Naming conventions in Access and Hungarian notation |
