Quick Queries #23
By Richard Rost
12 months ago
AutoHeight v CanGrow, Overlap ListBox, More!
In this Microsoft Access Quick Queries #23, we'll cover a range of quick queries, including how to correct misconceptions about transparency in images, the benefits of using Access over other software solutions, mapping shared drives, and understanding the quirks of report properties. We'll also address practical questions around keyboard layouts in VBA, overlapping controls, and more.
Topics
- Transparent images in Access forms and reports
- Mapping shared drives as trusted locations
- Auto height vs. can grow/shrink in Access reports
- Overlapping controls in Access forms
- Switching keyboard layout using VBA in Access
- Finding extended cuts of videos on YouTube
- Access Database Cloud and user access
- AI-generated voice options on YouTube videos
- Freezing columns in Access forms and tables
- Option compare settings in VBA modules
- Purpose of the instring function with comparison
- Using Access vs. SQL Server
- Copying query fields to a table in Access
- Using status command to display info on forms
- Enter parameter value error in Access
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, Performance over VPN, Record Locking, Transparent Images in Reports, Custom Database Solutions, Shared Drive Mapping, Internet Domain Name Mapping, Auto Height Property, Can Grow Can Shrink Property, Overlaying Text Control, Activate Keyboard Layout VBA, Freezing Form Header, Access Database Cloud
Transcript
Quick queries videos where I answer your queries about Microsoft Access that maybe they don't need a whole video for themselves. I get a lot of little questions, and that's what we do in the quick queries videos. So here we go.
First, a correction in quick queries 22. I said that transparent images like transparent GIFs will be transparent in forms but not in reports. And of course, Kevin comes to the rescue and says all you have to do is change the back style property to transparent. Now you tell me. So yes, even I learned something. I love it when I learn something from you guys.
A lot of times you have to remember some of my knowledge of Access is decades old because I might have learned it back in Access 97 or 2003, and that's just the way it was then, and I didn't know they changed it or fixed it. So I love learning things from you guys. Once again, thanks Kevin.
This question came up in a Microsoft Access group that I belong to on Facebook. Remco Peter says, "The question for all of you who sell build Access programs: Why would a company use a program made in Access instead of a ready-to-use solution from one of the big brands?"
Well, for me, the biggest benefit of Access versus package software is control. You can tailor the database to meet the business's needs rather than change the business processes to meet the software's requirements. You can custom build the database to store exactly the information you need, and with a little automation, you can make it do exactly what your business does instead of changing the way you do things to fit the software.
Back when I was a consultant, I spent a lot of time building databases for mostly small to midsize companies, but their accounting or their procedural software needs were more than you could do with an off-the-shelf product like QuickBooks, and there might be some vertical software for their market, but it might cost $40,000 or whatever.
In that case, an Access solution is perfect for them, and there are still lots of businesses looking for these kinds of solutions. But the problem is that a lot of people, especially business owners, don't know that Access exists. They don't know what Access is. They're looking for a solution. They know they need custom software. So that's where it's the consultant's job to put them in a product like Access if it meets their needs.
If they want something that's all web-based, maybe Access isn't the best solution for their website. But if they need an in-house database to handle customers, orders, inventory, whatever, then you can build them an Access solution. As far as whether there is a market for it, yes, there is a market, but like I said, a lot of companies don't know that it's a good available solution for what they need.
I spent probably 20 years of my consulting career building Access database solutions for different companies. There's still a market for it. I get tons of people on my website still looking for consultants. I don't do consulting anymore, but there's plenty of work to go around.
This next tip comes from Michael, one of my YouTube members. I tried to figure out which Michael you are, Michael, but I've got like 30 Michaels that are members, so I couldn't tell. And I couldn't tell from your email address because you're not signed up on my website. So sign up on my website. Anyways, he says, "If you map a shared drive, like a drive letter, like T or Z or whatever, with an IP address, then it cannot be used as a trusted location in Microsoft Access, but it works if you use the domain name."
That is true. I've never tried doing this to an internet domain name. I mean, I've done it in-house using domain names, but I'd be very careful with running a database over the internet like that. Access tends not to like it. It might corrupt your database. Definitely don't want to share a split database that way. Maybe a small one where you're the only user, but if you're running this over the internet and there's any kind of latency whatsoever, Access is going to have a fit. This is where you want to use something like SQL Server, so just be careful.
This is a funny one, and this is one that user NR Gens, I hope I'm pronouncing that right, posted on Reddit. He said, "Ever wonder why report header, footer, and detail sections in a report have both an auto height property as well as can grow and can shrink properties? No one seems to know why these sections have an auto height property when they already have can grow can shrink."
I wondered this too. I remember using auto height years ago, but I almost 99% of the time use can grow can shrink. I tried to find one thing that auto height could do that you couldn't accomplish with can grow can shrink. I believe it's a throwback to older versions of Access before they had can grow can shrink, and they just left it in there for compatibility.
I tried resizing text boxes, objects with code, like boxes and lines, but I just couldn't do it. I sat down with chat GPT and tried to get it to tell me, and this is the reason why I'm putting this in this video. You want to make sure that you verify everything chat GPT tells you. If it writes you code, test the code thoroughly because I got chat GPT to admit it was wrong in the end.
So here's the whole conversation. What is the purpose of the auto size property? And here's its reply. I said, "No, this doesn't work." Finally, by the end, it said, "You're absolutely right. Your test proves that the auto height property does not affect the dynamic resizing of sections or controls during report runtime. Whether it's set to 'Yes' or 'No,' the section adjusts based only on the growth or shrinking of the individual controls due to the can grow can shrink properties." This is after it was basically arguing with me up here, saying that they were different.
I'll put a link to this in the links section. If anyone else has any other ideas, I'm all ears. I googled this too. I tried to find something online. I don't just go to chat GPT, but I couldn't find anything. I know a lot of documentation from older versions of Access isn't online. I've got some old books on my shelves, like some Access 2000, and I think I even have an Access 97 book floating around here somewhere. I looked in there too. Couldn't find anything. As far as I could tell, this is not true because you can set it to false and it still grows. So I don't know.
Next up, I got a question from Eva. I hope I pronounced that correctly. She wanted to know if it's possible to overlap a text box over a list box. No, unfortunately, one of the properties of a list box is that it's always on top of other controls. This goes back several generations in Access. Even if you do "Send to Back," it'll always stay on top. You could take the list box and put the list box in a subform and put the subform control under other stuff, which then you can overlay text boxes on top of that.
But then you have to ask yourself, why do you want to do that? Is it possible? Yes, but I wouldn't. If you can give me some examples of why you'd want to do this, then maybe we could figure it out. But I don't know.
This next question comes from Willem in my forums on my website. He's a gold member. Basically, he's learning a second language. He's learning Polish. He's got English and Polish together on the same form. Every time he switches between fields, he needs to manually change the keyboard settings to match the language of the field, US English or Polish. He wants to know if there is a VBA solution for it. The solution is thanks to Alex, a number one. Here's the code that he found to be able to switch the active language based on whatever field you're on. This here is a Windows function. You have to declare it in your VBA code. Then you can use this function called "Activate Keyboard Layout." Very cool solution.
That's one of the things that I have very little experience with, is other languages. When people ask me questions about regional settings and languages and other versions, I'm English only. I know lots of languages, but they're all programming languages, and I know a little bit of Klingon. Kupla.
Next question, let's head over to YouTube. Dean Brody says, he's a silver member. He'd like to do the extended cut for the dynamic default value, but doesn't know how to get to it. I replied, "Look for the link to the extended cut in the description below the video," or you can find it on my website. This is a comment on that video, by the way. He said, "I'm sorry, but the only link I see is to your mug." At first, I thought he meant my face. You'll see in just a second. I tried looking it up on your website, but haven't figured out how to do it.
I get this question a lot. Let me show you something. Here we are on YouTube. If you scroll down, that's the mug he was talking about. That's my mug. They said you could put merchandise in here, so I made a mug. If anyone wants a mug, there's a mug there for you. And here's the comment.
You know how to add a comment that's fine. Now, this is not your fault. YouTube does a really good job of hiding the description. It's this block of text right here, below the video. I would really wish YouTube would do something to make this stand out more. I'm in dark mode, by the way. If you're in light mode, it's still hard to find. There's a little "more" over here, or you can just click on this field. When it opens up, there's lots of additional text in here. There's the extended cut right there. Click on that, it takes you right to it. That's a YouTube link. Yeah, I even say in the outro on all my videos, YouTube does a really good job of hiding it.
I know, no one ever watches that stuff at the end of the videos. If you know the name of the video, and you go to my website, just use the search box over here. Paste it in there. That'll take you to the video where it is right here. TechHelp dynamic default. Scroll down, and there's the extended cut right there on the member section. I try to make stuff as easy to find as possible.
Steve is asking about Access Database Cloud and whether or not you can have remote users and local users or if all the users must run on the backend data. I believe that all the users have to be on the Access Database Cloud because the database is running on their server. But this would be a question for them. I would contact their support and ask them. Maybe there's a way you can connect to it.
Roger, Kevin is awesome. He's fantastic. He answers tons of questions in the forums. He's definitely an amazing resource.
My guys are the best when it comes to answering questions. I can't answer all the questions myself. I just take the best one sometimes and put it in a video. But my moderators are fantastic. If you've got questions, post them here. But I'll try to answer them if I can. The best place to post them is on my website in the forums.
This next one comes from Giovanni, "I'm writing to you from Italy. I would love to go to Italy. It's definitely on my bucket list. My mom's family is all from Italy."
"I usually listen to all your videos in English. But the last couple of weeks, the video started with an AI-generated voice that's very annoying. Could you please post your video avoiding this behavior? Apparently, there's no way to prevent this from our side."
This is a new feature that they just started doing. I actually think it's kind of cool. But I have gotten some emails from some people that say that they find it annoying. They prefer to listen to the original English because the computer-generated voice in different languages just doesn't sound like me. It's going to be cool when they actually can get my inflections of my sound on my voice. Let me show you how to turn it off.
This will only be in my newer videos. All the older ones, they're not going back and adding this to older videos. This is one of my newer ones. This is yesterday's video. If you come down here and click on the gear, you'll see right here, it says audio track. This is where you can change it. You can have it in the original English or you can change it to Italian. This is probably what you're hearing.
If you don't speak English, it's better than nothing, I guess. Just come over here, go to audio track, and pick whatever language you'd like to listen to me in or just pick the original. Then I should go back.
Next up, John asks, he'd like to know how to freeze in Access form header text boxes and buttons when moving right in an Access form. Do you mean like in Excel where you can freeze columns? Like in Excel, you can highlight a column, go to view, and then freeze panes. Freeze the first column or whatever.And now if you scroll to the right, that column is stuck there. That's what freezing something means. It's like freezing a column, freezing the row headers, that kind of stuff. In Access, you really can't do that with a form if you scroll to the right. There's no way to freeze this stuff in here. Now if you want to go into a table, you can freeze a column in your table, the same way that you can in Excel. But I try not to let my end users work directly in tables. I like to keep everybody in forms. I mean, you could use a datasheet view form, which looks like a table. You could freeze stuff in here too. This gives you the benefit of still being able to use the events for the different fields in here. I almost never use these myself. I pretty much always stick with regular form view. I guess if you wanted to get fancy, you could do two subforms next to each other. And always have the ID here, but then you'd have to match it when you scroll up and down. I got separate videos on how to do that. I just synchronized some forms.
Next up, we got a couple of questions here on the option compare video. And I'm going to try to get them in the right order here. This person says thanks for the explanation. I'm wondering if it's possible to set option compare binary for just one sub in a module. Yes, I do this all the time. If you have multiple modules, you can have several modules down here. And it also applies to form and report modules as well. But if you open this up, you can change this one to have option compare database. You could have another one that's got option compare binary. And you could put all the stuff that's case-sensitive in the binary module.
And like JC Win says here, the instring function does have a third argument that allows you to specify the type of comparison. This video talks about the instring function, which is to find the location of a string inside of a bigger string, like finding a person's name inside a long text field, for example. I also talk about something similar in my change case video. So yes, Jeffrey, that answers your question. Two, six, nine asked the same thing too.
I love it when we get random questions on videos that have nothing to do with the video. But that's fine. I do that myself sometimes. And this is a whole separate ball of yarn here. Do people still use Microsoft Access? Yes. Yes, people still use Microsoft Access. I get tons of views every day from people who want to learn more about Access. Why not just use SQL? I mean, SQL Server. That's apples and oranges. Access is a desktop database designed for building forms and reports and stuff like that. SQL Server is just a server. It doesn't allow you to build an interface.
Isn't VBA old? Yeah. It's old. So is C, C++, all those languages. Those are old too. The IDE is terrible. Oh, wow. That's just your opinion. And you know what they say about opinions. Everyone's got one and they all stink. They're just like some piece of anatomy that I could mention. Just learn Python and SQL. Well, I disagree. I love Python. I do love Python, but it's not a replacement for Access. Sorry. Bye.
My question is, if you think that the IDE is terrible, why are you watching my videos? What are you doing here? This person says, I'm unable to understand your accent. I beg to differ. I do not have an accent. I sound like the people on TV sound. I don't have an accent. My fiancée had an accent. She's from the Philadelphia area. And I just got her to stop saying "water." So that's a plus. But hey, sorry, I can't change my voice. Maybe try one of those AI language options. Maybe they'll sound better for you.
I get people all the time saying slow down. I can't understand you. This is just how I talk. Sorry. I can't slow down. I record videos in the morning usually after a couple of cups of coffee and there's just no slowing this down. Sorry. If I did, I would talk like Ben Stein. Bueller, Bueller. I can't do that either. I'm just me, sorry.
Shadow Dragon is always looking forward to my quick queries. Good, because I like doing them. These are fun. I'm going to try and do more of them moving forward. I get so many comments and emails that I don't have time to make full videos out of. So this is my chance to address some of them. I see variations of this question once in a while: How can I copy a field from my query into a table? I don't understand exactly what you're trying to do, but keep in mind that a query is just another way of viewing data that's in a table. So that data doesn't exist in the query. There's nothing in a query. A query is just saying, hey, you got this table over here or these multiple tables. I want to see the data in that table in a specific way. You can filter it. Show me just the customer from New York. You can sort it different ways. And then there's action queries. You can add records, delete records. But you don't take a field from a query and put it into a table unless you're talking about an append query. That's where you can take data from one table and then use a query and put it into another table. That's an append query. And you can learn more about that right here.
George Eow asked, what's the status command that you're using in the demonstration? I use the status command to display information in a text box on a form instead of a message box, which stops execution. Shadow Dragon correctly answered the question for you there. I have a whole separate video on it. You can find out more about it here. Sometimes I apologize. Sometimes I forget that you guys haven't watched all of my previous videos. So I probably should be a little more upfront about that when I use the status box. But that's what it is. And I think I covered it in my blank template video too. It's really very simple if you know VBA. This is a box here called status box. And in this button, if you look at the code, you bring it up so you can see it here. It says status, hello world. What's status? Well, let's right-click on it. I'm going to go to definition. And it's right up here. It's a private sub status. It takes in S as a string. And it just adds that to the status box plus whatever was in it previously. So it has the effect of just adding stuff to the status box. Click, click, click, click, just add stuff. And I use this for, you know, if you're doing a loop or you're going through record sets or whatever, and you can display information for the user so they know something's going on. Because if you pop up a message box, well, it stops execution right there. So this is one easy way to display information on an ongoing basis to your user.
And finally tonight, Bernard says, he does whatever he's doing, and he presses a button and he gets an enter parameter value box. Why is that? 99% of the time it's because you spelled something wrong or you got a field in there that's not in the underlying query, let's say, and Access has no idea what you're looking for. It's quite common. I get questions about it all the time and I got a whole video on it. So go watch this. And that's going to do it for Quick Queries number 23, folks. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
- Transparent images in Access forms and reports
- Mapping shared drives as trusted locations in Access
- Auto height vs. can grow/shrink in Access reports
- Overlapping controls in Access forms
- Switching keyboard layout using VBA in Access
- Finding extended cuts of videos on YouTube
- Access Database Cloud and user access
- AI-generated voice options on YouTube videos
- Freezing columns in Access forms and tables
- Option compare settings in VBA modules
- Purpose of the instring function with comparison
- Using Access vs. SQL Server
- Copying query fields to a table in Access
- Using status command to display info on forms
- Enter parameter value error in Access
COMMERCIAL:
In today's video, we're addressing your burning questions about Microsoft Access in Quick Queries 23! We'll correct a past mistake about transparent images, thanks to a helpful viewer tip. From creating custom Access programs versus ready-made software to understanding mapping shared drives with Access, we've got it covered. You'll also learn about controlling keyboard layouts dynamically with VBA and why some report properties are just carryovers from older Access versions. Discover how to handle common user interface questions and even how to sort through YouTube's tricky extended cut links. Whether it's remote database solutions or eccentric quirks like freezing text boxes, we've got answers. 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. According to the video, why might a company choose Microsoft Access over a ready-to-use solution from big brands?
A. Access is cheaper than all big brand solutions.
B. Access allows for building a custom database tailored to specific business needs.
C. Access is the most popular database management system.
D. Access is mainly used for web-based databases.
Q2. What is a common concern when using Microsoft Access databases over the internet?
A. Better security compared to local databases
B. Increased likelihood of database corruption due to latency
C. Faster access speed than local networks
D. Automatic updates are more frequent
Q3. What was the issue discussed regarding placing a text box over a list box in Access?
A. It is not possible under any circumstances
B. The text box will always be beneath the list box
C. The list box is always on top, even with 'Send to Back' command
D. Using VBA can resolve the overlay issue
Q4. What did Kevin contribute to regarding transparent images in Access forms and reports?
A. Suggested a third-party software solution
B. Explained a workaround to use colored backgrounds
C. Showed how changing the back style property can make images transparent in reports
D. Recommended not using transparent images at all
Q5. What does the 'Activate Keyboard Layout' function in VBA do according to the video?
A. Switches the operating system between different versions
B. Changes the keyboard settings based on the current Access field
C. Integrates with Access to provide voice commands
D. Automatically translates text into multiple languages
Q6. What does the narrator recommend about ChatGPT when it comes to coding?
A. Always trust the code it generates
B. Verify and thoroughly test any code suggested by ChatGPT
C. Ignore any technical explanations from ChatGPT
D. Use ChatGPT only for high-level strategy
Q7. What limitation does Access have when trying to "freeze" sections in a form, similar to Excel's freeze panes feature?
A. Access forms cannot have multiple sections
B. Freezing sections is only possible in Access tables, not forms
C. Freezing requires an upgrade to the Enterprise version
D. Freezing is only applicable to form headers, not columns
Q8. What response did the narrator provide to a user querying about the potential of still using Microsoft Access today?
A. Assured that Access is no longer in use
B. Explained that Access and SQL are used for identical purposes
C. Confirmed that Access is still widely used and provides unique benefits
D. Suggested replacing Access with Python entirely
Q9. How does the narrator propose to find the dynamic default value extended cut on YouTube?
A. Search on Google for related articles
B. Visit the narrator's website and use the search box
C. Access the official YouTube playlist
D. Watch any Access tutorial for embedded links
Q10. How does the narrator suggest handling errors when users get an "Enter Parameter Value" prompt in Access?
A. It usually indicates a system error and needs reinstallation
B. It is a sign of incompatible form designs
C. It often means there is a spelling error or missing field in the query
D. It is generally caused by internet connectivity issues
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 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 Microsoft Access Learning Zone addresses a variety of questions and corrections regarding Microsoft Access, compiled from quick query videos. I often receive numerous smaller questions about Access, which I address in these quick query sessions.
Firstly, I need to correct a statement from quick queries 22. I had mentioned that transparent images, such as transparent GIFs, remain transparent in forms but not in reports. A viewer named Kevin pointed out that by altering the back style property to transparent, you can maintain transparency in reports as well. This was enlightening for me and shows how much we can learn from community feedback, especially since my knowledge dates back to earlier versions of Access like 97 or 2003, so updates can occasionally elude me.
A pertinent question from a Microsoft Access Facebook group asked why companies would choose a custom Access program over ready-made solutions from major brands. The key advantage of Access is the level of control it offers. You can design a database specifically suited to business needs rather than forcing the business to adapt to generic software. This customization can save substantial costs compared to expensive industry-specific software.
As a former consultant who designed databases for small to midsize firms, I found Access to be the ideal solution for businesses with unique needs that off-the-shelf products couldn't address. Despite being relatively unknown to many business owners, Access still offers valuable opportunities for custom solutions.
Michael, a YouTube member, shared a useful tip about mapping a shared drive in Access. According to him, mapping a drive using an IP address doesn't qualify it as a trusted location, but using a domain name does. While I've experimented with domain names in-house, I'd caution against using Access databases over the Internet due to potential problems like database corruption and latency issues. In such cases, considering SQL Server might be more appropriate.
In an amusing twist, user NR Gens highlighted a longstanding curiosity regarding why report sections have both an auto height property and can grow/can shrink properties. It seems the auto height property is a remnant of older Access versions, and current functionality relies more on can grow/can shrink.
A question from Eva addressed whether a text box can be overlapped on a list box in Access, which isn't possible due to the list box being always on top. Possible workarounds include using a subform to position the list box beneath other controls, although questioning its necessity might be worthwhile.
In another intriguing query, Willem, a gold member on my website, sought a VBA solution to switch active language settings in an Access form, assuming different fields require different keyboard settings. With the help of Alex, a code utilizes Windows functions to manage keyboard layouts based on fields, which is particularly useful for multilingual data entry.
Dean Brody, a silver member, asked about accessing extended tutorial cuts. For additional content like the extended cut of the dynamic default value tutorial, I advise checking the video description or my website. YouTube's setup sometimes makes this less obvious, but the descriptions contain these valuable resources.
Steve queried about Access Database Cloud capabilities, specifically user accessibility. Generally, users need to access the database on the cloud server, but contacting their support team for confirmation is advisable.
There's a lively community answering questions, as exhibited by helpful contributors like Giovanni from Italy. While some prefer traditional English voices over the AI-generated ones in different languages, adjustments can be made in newer videos via the audio track settings.
John wants to know if Access has a feature like Excel's freeze panes for columns in forms. While Access doesn't support this directly, alternative approaches involve using datasheet views or synchronized subforms.
Regarding a follow-up on the option compare video, it's feasible to use different comparison settings across multiple modules. JC Win notes that the instring function offers a comparison argument, a useful feature for string positioning tasks.
Finally, addressing frequent inquiries, queries merely represent data views rather than containing fields themselves. Append queries can transfer data into tables, and other functions like the status command provide user notifications during processes. If an "enter parameter value" prompt occurs, it typically indicates a spelling error or missing query fields.
For more comprehensive instructions, you can explore a detailed video tutorial on my website at the link below. Live long and prosper, my friends.
Topic List
- Transparent images in Access forms and reports
- Mapping shared drives as trusted locations in Access
- Auto height vs. can grow/shrink in Access reports
- Overlapping controls in Access forms
- Switching keyboard layout using VBA in Access
- Finding extended cuts of videos on YouTube
- Access Database Cloud and user access
- AI-generated voice options on YouTube videos
- Freezing columns in Access forms and tables
- Option compare settings in VBA modules
- Purpose of the instring function with comparison
- Using Access vs. SQL Server
- Copying query fields to a table in Access
- Using status command to display info on forms
- Enter parameter value error in Access
|
