Quick Queries #31
By Richard Rost
13 months ago
MS Access Not Working, String Parameter Errors, More!
In this Quick Queries tutorial, I will answer various questions from viewers, offering advice on troubleshooting Microsoft Access issues, and emphasizing the importance of providing detailed error information. You will learn about handling common Access errors, naming convention tips, and best practices for database frontends in shared environments. I'll also touch on topics like customizing checkboxes on forms and managing multiple instances of forms. Whether you're navigating error messages or deciding on database configurations, this video aims to enhance your Access experience with practical advice and real-world examples.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, Troubleshooter, problem-solving tips, SQL error message, record set issue, SQL string error, database forums, object naming conventions, Hungarian notation, frontend best practices, continuous form checkbox, multiple form instances, DNA cloning discussion, YouTube video narration, staying updated with Access.
Intro In this video, we'll talk about a wide range of Microsoft Access topics in Quick Queries 31, including tips for troubleshooting databases, how to provide the right details when seeking help, handling SQL statement errors, and best practices for naming conventions. We'll also discuss user-specific frontend files, button visibility options in forms, challenges with custom checkboxes on continuous forms, and opening multiple instances of a form. Along the way, I'll answer viewer questions and share why I leave mistakes in my tutorials to help you learn.Transcript Today's Quick Queries number 31. What is a Quick Queries video? Well, I get tons and tons of questions and emails and comments, and I can't make them all into videos, but I can put them all into a Quick Queries video. So this is kind of a combination of all the rest of the stuff that I get throughout the week. Let's take a look. Let's head to the mailbox first.
First up, I get tons of emails just like this one. Now I'm hiding the person's name not just to not embarrass them, but it's not just them. I probably get 10 or 15 of these a week, and I get people saying, hey, my database isn't working. Can you help me fix it? I don't have the time to do individual back-and-forth one-on-one emails with everybody. So if you need help, there are lots of different places you can go to get help. You can post comments. I strongly recommend my forums. I've got awesome forums on my website and a great group of moderators that help me answer questions. So post your question there.
And more importantly, I need information. I need details. What's going on? Just don't tell me that it's not working. I need information. Now you're getting an error message. What is happening? What's not happening? What have you done? The more information you give, the better it is for me to be able to help you. And also check out my troubleshooter on my website. If you're having weird problems, especially, and you can't figure it out and you've Googled it and you don't see anybody as an answer for it, try my troubleshooter. It's got all kinds of common sense things you can try to fix your problem.
Don't just tell me your database isn't working. I need details. Here's another great question that comes up for one of my silver members, Tom. Tom said he's trying to open a record set with an SQL statement and you get an error message saying that Access cannot find the table or SQL. See, folks, he showed me what he did, and he told me what error message he was getting. Very, very helpful.
Now, take a second. Can you see what the problem is? Pause the video if you have to. Did you get it? Take a look right here. Do you see the problem now? He's setting a variable equal to this. That's the name of his table, select star from table. But then he's sending that as a string to the open record set. So basically, open record set is getting SQL strength, not the value in SQL string, the actual word SQL strength. Congratulations to Donald. One of my platinum members got it. He answered it correctly. And yep, Donald is correct. He gets the prize. Whatever the prize is, I don't know. We'll figure something out. But be careful. Quotes matter, spelling matters, brackets matter, all those little tiny details.
This is something, Tom, that happens all the time. It's a common mistake. Don't feel bad. Making this mistake three or four thousand times will help you to remember not to do it again in the future. I know I've done this a ton of times myself.
Next up, Shadow Drag asked about Access Day and if they're going to be streaming the presentation. For those of you who don't know, Access Day is in Redmond, Washington, on March 28th of this year, 2025, hosted by Armin Stein with a bunch of MVPs presenting, not me. I'm not presenting. I'm just going as an attendee, but you could find out more information here. I'll put a link down below. Unfortunately, no, they are not live streaming the presentation. It'll be an in-person attendee conference only. Hope to see you there.
Michael says regarding my naming conventions, and this was something that came up in last week's Quick Queries 30. I use an F to designate form. So customer form is customer F. R is for reports. If you're going to need to know which type of object you're opening, I use my naming convention. So I can just look at the right one character. If it's an F or an R.
What I said in this video is if you're not using my naming convention, all you have to do is make another field in the table to indicate if it's a form or a report. It's not really a lot of extra work. It's just one little extra character that you have to put in the table to indicate if it's a form or a report. Could you look it up somehow? You probably could. There's VBA code where you can determine what all the objects are. But I think it's not a ton of extra work to just simply put an F or an R or a checkbox: is form or is it a report? Yes or no? It's a little extra work, but I'm just using my naming convention.
I know a lot of people also use different types of naming conventions. Whatever your naming convention is, just use that. A lot of people use what's called Hungarian notation. If I were M customers for forms, our PT for reports, if that's your naming convention, then just look at the first three letters there. It's unique to your database. I'm just showing you the legos you can put them together however you want.
AZ Poolcare Pros says, please, please keep leaving your goofs in the video. See, I just goofed there, and I just left it in. I made mistakes and remembered from watching your goofs what I did wrong and how to fix it.
That's how I feel. When I first started doing this, if I made a mistake, I would be like, oh, I have to stop. I would take that out and redo it. Somewhere along the line, maybe 10 years ago, I discovered that if I make these mistakes, chances are you guys are going to probably make the same mistakes at some point.
That's why I started leaving them in at first. Eventually, I started intentionally making mistakes because now I've done this so many times and for so long that I know exactly what mistakes are coming up. I'll make the mistake intentionally. Sometimes I'll even preface it with, okay, watch this. This is what you don't want to do. It's hard because a lot of people agree with me. They say, yeah, leave the mistakes in. Some people complain because it wastes time. I guess I need to make two tracks for every course. I have to make a fast track for people who don't want all the bull, and then I need the one for people who actually want to learn this stuff and take the time.
I'm of the latter. I'm not in a hurry. I want to learn it. Whenever I look for stuff to learn myself, I want to take my time and immerse myself in it. Not everyone's the same way. Thanks for the comment. I'm going to keep leaving the goofs. I mean the mistakes. I mean the problems in the videos.
I got a lot of great comments on my Changing Clocks is Dumb video, which I'll put a link down below if you guys want to see them all. But yes, I am pro 24-hour clock, although I don't have them all in my house because my wife would probably yell at me. But I like 24-hour time. Like I said in this video, I'm forgetting my time zones completely. I am pro Celsius, pro metric system. But I will only call the letter Z Zed if I'm referring to the Rush song YYZ or Toronto airport. Otherwise, it's Z. It does sound like DG. I guess Zed is not that bad.
Shane says I've got my frontend on my LAN drive. My users are using virtual machines. I can't provide each user with a frontend file. Any suggestions? 15 users with no more than six on at once. If you've got people simultaneously using your database, they should have their own frontend file. Each user should have their own copy of the frontend file on their local drive. If you're using virtual machines and they're logging in, they should each have their own virtual machine session. That virtual machine should have its own drive with its own copy of the frontend. If not, you're going to run into problems eventually because Access doesn't like it when two people are working on the same frontend file simultaneously. It's a common problem I see all the time.
People take a database, whether it's split or not, and put the database up on a shared server drive. Then they have five people working from the same frontend, same database file, and it invariably corrupts. Back up what you're doing routinely, nightly, and do your best to try to give each user their own copy of the frontend. That's the best way to do it. If not, good luck to you, sir.
The Ryan wants to know if hiding the button is better than denying access when someone's trying to log on. That's up to you. You can either give them a message and say, hey, no wrong, or you can hide the button completely or do whatever you want. Whether you're databases, they're your legos, you put them together however you want. I'm just showing you lots of different ways you can do things. Either one works. That's fine. Hide the button completely until they enter the right information. If you have a main menu and then an accounting menu, and you don't want your regular users to see the accounting menu button at all, that's fine. You can give your accounting people a whole completely different frontend if you want.
Next, PowerJ3V says this was great. He's talking about my Large Checkbox video until I went to a continuous form. The continuous form that checkbox toggles fine, but the caption toggles all the records instead of one record. Can you explain how to get it to work on just one record?
The technique that I use in this video, and for those of you who don't know what video I'm talking about, it's this guy, where I show you how to take a little tiny default checkbox and make a bigger one. With this bigger one, we're using basically a graphic, an image, our own custom button. The caption we can change, but it only works on single forms. The reason why is that if you've got a continuous form, you've got fields in here, first name, last name, state, and so on. This is basically the same control with just different records in it. If you have VBA code that says first name, back color equals red, it changes all of them. You can't do things like change a caption or change the property of an image or any of that kind of stuff or change the visible property with VBA like that.
There are some tricks you can play, like in this video. I show you how to hide a field in a continuous form. You don't make the field not visible because visible is a property of the text box itself. As soon as you make one of them not visible, you make them all not visible. The technique that I show in this video utilizes conditional formatting. I guess you could do something with conditional formatting to make it look like a bigger checkbox. But you have to play with it. If enough of you want to see me try, then post a comment down below. Maybe I'll give it a shot. Squeaky wheel gets the grease. Unfortunately, the technique from this video is only going to work with a single form. Sorry.
Oriel Ramirez says he's Googled how to open several instances of a form that my videos haven't covered yet. He wants to make sure he does it right to avoid ending up with a Thomas Riker clone like from Second Chances. We don't want any fake Will Rikers running around the universe. Then that happened. He came back and was in Deep Space Nine and caused some problems working with the Maquis. But anyway, I have covered it. I just have not covered it in a TechHelp video. I covered it in Access Developer 47, where we can make multiple copies of the same form opening up. If you want to have like three customers open at once, you can. This is fairly complicated. I spend a good hour on this. It's beyond what I would cover in a TechHelp video. TechHelp videos are shorter, like 10-15 minutes. If you want to check out Developer 47, it's on my website. There's the link right there. No more clones.
I am thinking of saving my dog's DNA for the inevitable. I would like another one of him eventually, but it's kind of pricey. We'll see.
I just thought this one was cute, so I had to share it. It's 1:45 a.m., and I spent all day trying to work this out, learning Access. Finally, I climb into bed and decide to watch a little YouTube, and there you are. I'm tempted to get up and try it out. But being well into my 60s, I need my beauty sleep. I get you. I just turned 52 myself, and I need my beauty sleep too. If I don't get a solid eight, I'm a crab in the morning. Don't mess with me. My brain doesn't turn on if I haven't slept well, and I need my brain so I can do what I do.
I watch a couple of YouTube channels myself when I'm ready to go to bed. I love watching space documentaries. But they can't be ones that have orchestral music and loud noises. It's gotta be calm and relaxing. It's interesting but not interesting enough that when they release a new episode, I get stuck watching it because I might learn something new. I like to watch ones that I've already seen, and it puts me right to sleep. I love these guys. Cosmo makes awesome videos. Lots of good content. But it's not loud. I love Neil deGrasse Tyson and that, but when he gets together with Chuck Nice and they start goofing and laughing, it wakes me right up. I can't watch Neil deGrasse Tyson's StarTalk to put me to sleep. But Cosmo is perfect for going to sleep. The content's great, but after you've seen the video once or twice, it's nice and chill, and it's relaxing.
You have to get YouTube Premium because then you don't have to watch commercials. Well, I shouldn't tell you that because then I don't get paid for advertising. Actually, I think I still get a little bit. I get fractions of a penny for YouTube Premium users. Check out Cosmo. I'll put a link down below. If you like space stuff, they have really good space content. I'm a science nerd, so I love this stuff. No, I can't watch Star Trek going to bed either because then even if it's an episode I've seen, I want to watch the whole thing and I pay attention to it. Star Trek before bed is no good.All right, folks, that's going to do it for another quick queries. My dinner is on the way and we just ordered dinner, so I have to say goodbye now and that's going to do it for today. Enjoy your Friday. Enjoy your weekend. I hope you learned something. Live long and prosper, my friends. I'll see you on Monday with a new video. Take care.
TOPICS:
Troubleshooting Access databases
Common SQL statement errors
Access Day conference details
Naming conventions in Access
Importance of user-specific frontend files
Handling button visibility in Access forms
Using custom checkboxes on single forms
Multiple instances of a form in Access
Benefits of leaving mistakes in tutorial videos
COMMERCIAL:
In today's video, we'll discuss Quick Queries number 31, where we address some common questions from viewers. We'll talk about providing detailed information when asking for tech help, managing database frontends for multiple users, and effective naming conventions for forms and reports. We'll also explore if hiding buttons is better than denying access and answer queries about continuous forms and multiple form instances. I'll also share my thoughts on leaving mistakes in videos and my bedtime YouTube habits to keep it interesting. 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 purpose of a Quick Queries video?
A. To summarize the week's news headlines
B. To address frequently asked questions and viewer comments
C. To provide in-depth tutorials on a single topic
D. To showcase trending social media videos
Q2. What does the instructor recommend for someone experiencing issues with their database?
A. Send detailed questions via email for personalized help
B. Try using the troubleshooter on the instructor's website
C. Visit a tech support center
D. Watch more Quick Queries videos for answers
Q3. In Tom's SQL issue, what was the main problem identified by the instructor?
A. Tom was using the wrong table name
B. Tom forgot to save his SQL query
C. Tom was sending the string 'SQL string' instead of a variable value
D. Tom's database was corrupted
Q4. Why will Access Day not be available for live-streaming?
A. Technical difficulties with streaming services
B. It is designed to be an in-person only event
C. They plan to release recorded sessions instead
D. The event location does not have internet access
Q5. What naming convention does the instructor use to identify forms and reports?
A. Prefix 'F' for forms and 'R' for reports
B. Suffix '_form' or '_report' to object names
C. Use numbers to differentiate object types
D. Use color codes to distinguish between type
Q6. Why does the instructor leave mistakes in some video tutorials?
A. To save editing time
B. To show viewers common errors and how to fix them
C. To extend the video length
D. To decrease production costs
Q7. What is the recommended solution for managing Access database frontends according to the instructor?
A. Keep a single frontend file on a shared network drive
B. Each user should have their own copy of the frontend on a local drive or virtual machine
C. Use cloud-based database solutions instead
D. Regularly update a single master frontend for all users
Q8. What type of content does the instructor prefer to watch before bed to help fall asleep?
A. Action-packed movies
B. News broadcasts
C. Quiet and calming space documentaries
D. Comedy sketches
Q9. What video series does the instructor moderately tease regarding a fictional cloning incident?
A. The Big Bang Theory
B. The X-Files
C. Star Trek: Deep Space Nine
D. Lost
Q10. How does the instructor suggest handling access permissions or menu visibility in a database application?
A. By creating separate frontend versions for different user roles
B. By sending users notification emails with their permissions
C. By displaying an error message when access is denied
D. By using hardware-level security measures
Answers: 1-B; 2-B; 3-C; 4-B; 5-A; 6-B; 7-B; 8-C; 9-C; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In Quick Queries #31, we'll address various questions and comments I've received over the week. Quick Queries videos allow me to share answers to numerous inquiries all at once since creating individual responses for each question isn't always feasible. Let's dive into the topics.
Many emails come my way asking for help with databases that aren't working properly. I want to emphasize that it's not just one person facing this issue; it's a common problem. Regrettably, I can't engage in one-on-one email assistance for everyone. Instead, I suggest posting your queries on my website's forums where we have a great team of moderators ready to help. When you ask for help, providing detailed information is crucial. Tell me if you're getting an error message, what happens or doesn't happen, and what you've already tried. Additionally, you might want to try the troubleshooter tool on my website if you're experiencing unusual problems that standard solutions haven't resolved.
I received a question from Tom, a silver member, who faced an error with an SQL statement while trying to open a record set. Tom did an excellent job by providing both the error message and the SQL statement he used, which made troubleshooting much easier. The problem was related to how the SQL string was being sent— the name was being passed as a reference rather than the content it should have had. Congratulations to Donald, a platinum member, who identified the mistake correctly. Remember, details like quotes, spelling, and brackets matter significantly.
Next, Shadow Drag asked about Access Day presentations. For those who don't know, Access Day is in Redmond, Washington, and will host several MVPs on March 28, 2025. Unfortunately, there won't be any live streaming of the event; it's strictly for in-person attendees.
Michael had questions about my naming conventions from last week's Quick Queries. I use specific letters to denote forms and reports (F for forms, R for reports), which makes it easier to identify these objects. Even if you're not using my system, just having an extra field in the table to distinguish between forms and reports can be beneficial. While there are alternative ways like VBA code to identify these objects, using a naming convention can save time and effort.
AZ Poolcare Pros expressed appreciation for leaving mistakes in my videos, which helps others learn. I agree with this approach because I believe that showing real-life errors can help learners understand what to avoid and how to fix issues. While some may view it as time-consuming, many find it valuable for learning.
I got some interesting responses on my "Changing Clocks is Dumb" video, where I expressed my preference for certain systems like the 24-hour clock. Although personal preferences vary, these topics often spark engaging discussions.
Shane asked about front-end files for users on virtual machines. If users access your database, they should each have their own front-end file on their local drive. This prevents conflicts and potential corruption when multiple users access the same file simultaneously. Always maintain regular backups to safeguard your work.
The Ryan questioned the best way to manage access in a database, whether by hiding buttons or denying access directly. The approach depends on your preferences. You can choose either method or create different front-end versions for distinct user groups. It's all about customizing your database to fit your needs.
PowerJ3V encountered an issue with a large checkbox toggle on a continuous form. The method I presented works well on single forms. For continuous forms, alternative methods like conditional formatting might be necessary because certain properties apply to all records simultaneously.
Oriel Ramirez inquired about opening several instances of a form, a topic covered in Access Developer 47 but not in a TechHelp video due to its complexity. Creating multiple form instances requires a deeper understanding of the subject.
I'll end by sharing a personal anecdote from a viewer who enjoys watching my videos before bed for a calming end to the day. I share their sentiment; it's essential to unwind with relaxing content to facilitate restful sleep.
That's it for today's Quick Queries session. My dinner has arrived, so I'll wrap things up here. I hope everyone has a fantastic weekend and that this video has been informative. If you seek more detailed guidance on the topics discussed here, please visit my website for full tutorials. Live long and prosper, my friends.Topic List Troubleshooting Access databases
Common SQL statement errors
Access Day conference details
Naming conventions in Access
Importance of user-specific frontend files
Handling button visibility in Access forms
Using custom checkboxes on single forms
Multiple instances of a form in Access
Benefits of leaving mistakes in tutorial videos |
