Quick Queries #18
By Richard Rost
2 years ago
Paste Behavior, Box Margins, Vertical Alignment, More...
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are for me to answer your questions that may not need a whole video themselves. In today's video...
Topics Covered
- Access form paste location behavior
- Adjusting Access form paste behavior
- Access text box margins and padding
- Effect of Aptos font on forms
- How to change font in Access forms
- Vertical alignment in command buttons
- Adding line breaks in command buttons
- Creating transparent buttons
- Buttons on reports in Access
- Report view vs. print preview
- Object dependencies in Access
- Using undefined functions in Access
- Avoiding duplicated module and function names
- Multi-select options in Access
- Multi-valued fields in Access
- Advantages and disadvantages of multi-valued fields
- Using subforms for related data
- Naming conventions in Access
- Spaces in table and field names
- Singular vs. plural table names
- Deleting unused objects in Access
- Renaming objects as a safe deletion strategy
- Effects of indexing in Access
- Balancing indexed fields and performance
- Indexing strategies and best practices
- Using Access runtime version
- Managing Access runtime errors
- Comparing ACCDE and full Access versions
- Ensuring consistent Access versions in a network
- Handling Access database corruption
- Creating new databases to fix corruption
- Avoiding development on networked databases
- Dangers of shared file systems for Access databases
Links
Courses
Previous Quick Queries
Keywords
TechHelp, Access 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Access form pasting, cut and paste behavior, property sheets in Access, padding settings Access, controlling vertical alignment button, transparent buttons Access, print button on report, name conflicts Access modules, multiple values in combo box, multi-select list box Access, multi-valued fields Access, indexing database tables, creating relationships Access
Intro In this Quick Queries video, I answer a wide range of Microsoft Access questions submitted by viewers, covering topics like how form controls are pasted in the design view, troubleshooting text getting cut off with the new Aptos font, handling vertical alignment in command buttons, using buttons in report views, and fixing errors when naming modules the same as functions. We'll also talk about multi-select list boxes versus combo boxes, best practices for table and field naming, managing object dependencies, indexing tips, and dealing with database corruption. If you have Access questions, chances are they've come up in this episode.Transcript It's about that time again to reach into my grab bag of questions. It's time. I can't believe we've done 18 of these already. It's time for another quick queries. Quick queries are basically questions that I get asked either in email or in my forums or on YouTube or wherever that, well, they're good questions, but they don't necessarily merit a whole video on themselves.
So let's dig into the mailbag. For Robert from Dayton, Nevada, a Silver member says, I have an issue when I cut and paste something into a form, it always pops up in the left corner. How can I paste it where my cursor is? Well, it's a unique way that Access handles copy or cut and paste. If you got a form, let's say your customer form here. Now, if I go in here and I take the country field and I copy and paste without doing anything else, it will paste it right below where you're at. See, paste, paste, paste. Okay. But if you click off of that, if nothing is selected, it pastes into the upper left-hand corner. And that's just how Access works. So if you copy something from one form, right, and you go to a different form, let's say we open up this form and I paste in that object, it's just going to go in the left corner. That's just how it works. And don't forget, you got a label over here too. If you got a text box with an attached label to it. All right. Is there any way to change that? Nope. Not that I know of. So yeah.
Here's one from the forums. Kyle says that he's got a text box where the text is getting cut off like that. Now there are margin settings. And you can set your margins. You'll find them in the property sheets. There's margins and there's padding. You can change those things under the format tab. This one turned out to be the new Aptos font. And Kevin figured this one out. Kevin's good like that. He figures a lot of stuff out. And Thomas chimed in with some help too. But yeah, that Aptos font, I've noticed if you've got older forms, especially, you try applying the Aptos font or you create new forms and stuff, and it gets that as a new default setting. That's the new default for Office now. It will cut stuff off. I'm a big fan of Tahoma. That's the older default font. So if your margins and your padding don't fix it, check your font. Okay.
Next up, Amanda, a Gold member, asks if there's a way to change the vertical alignment of the text in a command button. And of course you can't, but Kevin comes in. See, I got a lot of great Kevins on my website. Kevin Yip comes in with, you can add a line break by pressing Control-Enter. And of course I didn't think of that. But if you hit Control-Enter and then type it in, it'll look like it's aligned to the bottom. Here's the middle and then do that. Hit Control-Enter after it, maybe put a space in there. Then you can make it look like it's vertically aligned on top. So that's pretty cool. And of course, Juan reminded everybody that you can also make transparent buttons. So you can basically create whatever kind of button looking-like thing you want. There's the video for that. I designed a whole form and you can just put what look like buttons on here and then put transparent buttons over the top of it. So you can make your buttons look neat. And of course, you can also put pictures on buttons themselves. There's lots of things you do. I cover this in the beginner classes.
Peter, a Silver member, is talking about putting a button on a report that you can use to then print it. You can do this, but only in report view, not in print preview. It will only display in report view. Let me show you real quick. If you've got a form with a report on it, like my invoice here. Okay, this is print preview, no matter what you do. All right, if you put a button anywhere on here, it will not show up in print preview. So if I go report design, a lot of times you can use this to actually print the report. All right, so here's a button. Doesn't matter what's in it. I just drop a button there. Now it's got properties down here that say display when. There's always, print only. Slide this down here so you can see it. There's always, come here, print only and screen only. All right, but again, it will not show it no matter what the setting is in print preview. Okay, so if I go to screen only, that means you don't want to see it when you print the thing. All right, if you open this up now in print preview, you will not see it. If you open it up in report view, you will see it. And you can actually click on it and do stuff. So you can open this up. The report view is kind of like an almost good enough, pretty much kind of what you're going to see when you do print it or make a PDF. So you can open this up first and then put a button right here if you want to. I never do this myself, but it's an option. It's always, you know, one more thing that you can do with Access. I like to just have a button here that says show print preview. And then once the print preview is open, right, just teach your people, okay, hit the print button or put it up here on the quick launch toolbar or make two buttons on your form, one for a preview and one to actually print it. You can, you know, set what printer it goes to and all that stuff. Okay, okay.
Here's one from the visitor forum. Visitor forum is where non-members can post. If you're not a student of mine, if you're just, you know, like a YouTube viewer or whatever, you can still come to my website and post in the visitor forum. Anyone can post here. Now his question isn't really, it doesn't matter what the function is that he's working with. He's got an undefined function, my network days. And he's using my network days function, where you can take two dates and figure out how many work days there are between them. Right, you can give it, you know, like a Monday and a Monday, two weeks later, it'll tell you there are 10 days between them. It'll exclude holidays, all that stuff. But anyways, the function itself is irrelevant, but he's getting this undefined function in expression. Okay, and it turned out, if you go through that, we go through some troubleshooting with him. It turns out that he was naming the function the same as the module. So let me give you an example of what I mean. Let's say you want to create a, well, let me create a new one here. Let's create a module is where you can put your code that can be used in any form or report. So let's make a public sub and I'll call it hi there. And all this guy does is message box, hi there. That's it. Okay, now I'm going to save this module as hi there. Okay, now, if I come to any other form or whatever, and I come into a button and I just say in here, hi there, save it. All right, if you come out here and try to run it, bam, expected variable or procedure, not module. What does that mean? And you'll also get that if you do a debug compile too, which you should always do a debug compile whenever you finish a bunch of code. And the problem is that hi there is the name of the module. And so that's what Access sees. Now the easy solution to this is just to rename this guy, right click, rename all this. I always end my modules in mod and that will get rid of the ambiguity. See, now it works. Okay, so if you're getting weird errors like that, I used to do this all the time when I was first starting out. I'd named, you know, I'd want to make a function or a sub and I'd put it in a module. It was named the same thing and I'm like, ah, and it took me doing it like four or five times before I realized, hey, you know, you can't do that. So that's what these quick queries are for. To teach you stuff like that. All right, let's head over to YouTube for a couple.
Quan Deal Rose asks, how do you select multiple values in a combo box? Well, the short answer is you can't. Combo box is you pick one item. That's it. There is no such thing as a multiple select combo box in Access. In Access, there is a multi-select list box. It is a lot more advanced and it does involve some programming. I cover it in my Access Developer 15 class. There's a multi-select list box there where you can click multiple items. And then in this example, we hit a button and it moves it between two different list boxes. And of course you can use a record set and save that information to a table if you'd like. Now Access does have something called a multi-valued field. And if you're a beginner and you want to be able to select multiple items, you can use this, but I don't recommend them. Because as you get into more advanced Access development, you'll find that these things are really more of a band-aid than anything else. They're designed, again, so beginners can do a multi-select like that. But accessing this information programmatically in the future, if you do become a little more advanced or using some VBA, some advanced queries, these are very difficult to work with. So watch this video for more information on multi-valued fields, but that's the other alternative. Or you could just use a good old simple subform. You can make a subform on your form and in here, in the continuous form records in here, you can put a combo box. So you can pick one for this record, pick one for that record, pick one for that record, and you get a nice little relationship going between those tables. I explain how to do that kind of in this video.
Kent wants to know why it's a no-no to have spaces in your table names and field names. I go over this in great detail in my Access Beginner 1 class, and then a couple of different times throughout my entire course. The biggest reason why is that you got to remember to put brackets around your fields all the time in SQL and VBA. And yes, there are sometimes VBA brackets anyways, but when you start writing VBA code and you have to put brackets around every field name, it becomes really annoying. So that's just my personal rule. I don't use spaces. I don't use anything other than letters and numbers and you can't start a field name with a number. So that's just my personal rule.
I also try to keep all my field and table names singular. It doesn't always work. Like notes is an exception because I've been using notes forever. But mostly because then when you get into coding, you don't have to stop and remember, okay, wait a minute. Was it customer unit or customer units? Just keep it all singular and it works out and there's no spaces and everybody's happy. So it's a no-no for me. If you're going to follow my courses, that's how I do it. You can do it however you want. Just stay consistent. These are just little tips and things that I've just come up with in my 30 years of doing. That's all.
Xavier says, when it comes to deleting objects, in my object dependencies video, I talk about how you can check to see if a query is being used anywhere or if any other object is dependent on your object because if you have a query that you're not using and no one's using it, is it okay to delete it so you don't have tons and tons of all these queries and forms and stuff in your database that no one's using? And I said, use the object dependencies to check and see if something else is using it. And Xavier's recommendation is rename the object with the current date. So you got customer queue, put the date after it. It doesn't matter if it follows my naming conventions because you're not going to use it. Now, if another object does use it, Access might rename things. Like if you have a query that a form is using, it'll rename it inside the form. So you still got to be careful about that. Right? But if it's in your VBA code, it won't rename it. But that's just one thing you could try. Just rename it and then if a year later, you know, nothing is broken, you can then try deleting it. But still, you got to make sure. I would still use the object dependencies too just to make sure that something isn't using it that did rename it inside itself. The only thing that usually doesn't rename automatically is VBA code. So that was the issue. So there you go.
Next up, just to mention about indexing. This user says that he ran through indexing his database and it went from 15 to 30 seconds to one second to open a query, which is great. Or the multi-table search form that he's using. I just wanted to make a note there. Make sure you don't overuse indexing. Because remember, when you index something, when you index a field, it will speed up searches and sorting for that field. But it will slow down updates. So if you got a table where you're constantly doing data entry and editing records and stuff like that, it might actually slow you down. But usually with most databases, the data entry is the limited part and the, you know, searching for records and doing sorting and reports. That's the stuff you do a lot more. So it all depends on your needs. Watch my indexing video for more information on that.
Shout out to Hartmut Schaefer. Thank you very much for the tip. Why do I always show the cheesy way to do something? I love cheese, especially provolone, Swiss, mozzarella, of course. I am half Italian, so mozzarella is my favorite. Whenever I try to make a TechHelp video, there's always two ways that I can think of to do something. Well, usually two ways I can think of to do something. My brain immediately goes to the VBA method, the developer method. Because that's how I was kind of, you know, that's how I was born and raised as a computer guy. I think in basic, because that's what I learned when I was eight years old. But I always try to come up with a simple, non-programming way to do things for the beginners who aren't necessarily programmers. Because you don't have to be a programmer to be an Access developer. So I try to show two ways, if possible. If there's an easy, simple way, I'll show that. If you think that's the cheesy way, well, then don't watch the video. Sometimes it's actually harder for me to come up with the easy way to do something than it is to just write some code. Because for me, writing code is easy.
Question from the same guy a couple days ago. Whenever I go through questions, I go through them backwards. Because whenever I look at the list of comments, that's how they are on YouTube. They're just, they're in reverse chronological order. So I've got some from like eight months ago that I still haven't gotten to yet. But you know, the most recent ones show up first. How do you make a select ship to address? Well, that depends. Do you want the best way or the cheesy way? Here's your answer. I cover it in Access Advanced Level 5. There it is right there. Okay, okay. And it's not the cheesy way. In all fairness, I do have this on my list for a future tech help video. I'm going to show you how to make shipping labels and you'll have a ship to address and a bill to address for the invoices. So that is coming up as a free video. So just stay patient.This user says, I got a quick question before I make my first table. She's dealing with hospices and patients in the hospices. Okay, and then you got medical rental equipment and then you rent to different patients through the hospice.
Okay, so the way I would set this up is you got a hospice table. Here's your hospice table. That's got a hospice ID, your primary key, and then of course the name, address, phone number, any other information about the hospice.
Okay, then you got patients. Your patient ID is your primary key. That's your auto number. And it's gonna have a hospice ID, a foreign key, which is a link back to this thing that tells you which hospice they're at.
Then the patient, you can also have any information about the patient, name, address, phone number, all that stuff. Now, if you're renting the equipment to the patient, you'll have your equipment table with an equipment ID, primary key. Name and description and all that stuff for just the equipment.
Then you'll have a rental key that is the act of renting that piece of equipment to a patient. So you'd have the rental ID as the primary key. Okay, basically consider it like the sale, okay? Now you'll have a foreign key, which is the patient ID. Optionally, you can store the hospice ID if you want to. That's up to you, you don't have to. Because you can find the patient hospice by, you know, two links. If you know the patient ID, you know what hospice they're at.
And if you have the date that it was rented and they change hospices, you'll at least be able to figure out, you know, where they were at that time if you keep track of if they change if a patient can change hospices. So that's why you might want to store the hospice as well. You don't have to. Okay, then the information about the rental, the date, the time, the cost, the due date, all that stuff.
Okay, I got videos on a lot of this stuff. Check out my relationships video, my combo boxes video. And this kind of stuff is covered in my expert classes with relationships. And I also have a relationship seminar as well. I'll put links to all this stuff down below.
But yes, it's good to lay this stuff out on paper like I show you in my Access Beginner One class. Paper, index cards, even an Excel sheet, whatever. Just, you know, get your tables and your relationships and stuff all mapped out first. It's easier to do it that way than it is to just start building tables and then figuring it out later. Okay, try to make a roadmap first. And it's good that you know what all the information that you need to store up front.
Alright, this is one, I see this a lot and I'm hiding the user's name because I don't wanna embarrass anybody. But this happens a lot. People say, I followed your instructions step-by-step, right, I did everything you said, but something's not working.
He's got this here with a me.id. So no, you didn't do exactly what I said because you will never see me use me.id. I will never use me to reference a field. You don't have to, alright. And I will only use me to reference properties of the form. Okay, or sometimes functions and stuff like that, never a field. And if I was gonna use it, I would use an exclamation point, not a period. In fact, you don't even really need me for a lot of the properties too. But I use it as a matter of just habit. So you didn't do exactly what I said.
Little things like that are very important. Alright, things like, you know, commas and stuff like that are also important. There should be another comma in there. Alright, that's the filter property. I believe in this video, I show the where condition, which is the next one over. Alright, so if you're gonna say, hey, I followed your, you know, step-by-step, make sure you follow it step-by-step. Every little thing counts, okay. And I can tell when you're not following the instructions because I know how I do things. And I know when you found, you know, code on chat GPT and it's not working for you.
Eric Cartman says, your VBA codes are simple, yet simple, so neat, yet powerful. I have a similar function, but my codes are much more complicated. Yeah, nine times out of 10, when I start writing code, it's not the perfect stuff you might see in the video for the first time. A lot of the times I just write it as I think it should be written.
And then I'll go back over it once or twice, sometimes more than that, to make it more efficient, to make it cleaner, to make it readable. You know, my initial code, like I said here, is just like Scotty's work on the enterprise. You know, the energizer's bypassed like a Christmas tree, so don't give me too many bumps. Yeah, I still now go back through my personal business database that I use, that I've been building for 20 years. I'll find sections of code that I wrote 10 years ago and I'll be like, no, we gotta do that better. So code is constantly being refined.
And the ability to write simple, neat, clean code, it just comes from practice. I've not always been great at it too. So just keep doing it, keep practicing, you'll get there.
Melvin said, you have a school library management system. No, I get a lot of requests for this. I don't know why. Is there not a good school library management system database available out there already? A lot of the times when I was a consultant, I would build databases that there was a need for that one didn't exist. I'd be surprised that there's not something out there already. But I do surprisingly get a lot of requests for this.
If you are looking for a school library management system database, post a comment down below. And if enough people speak up, maybe I'll build one. First off to look up exactly what a school library management system database does. I assume it's just for like library books and stuff and who's got it checked out. I do have a rental database. I got a TechHelp video. It's about three years old. It covers managing rental inventory. Basically checking out books, right, books. It's pretty much the same thing as renting like stuff. I think this is like for renting like, you know, blockbuster videos and stuff. But you could easily adapt this to what you're doing. Unless there's something I'm missing, let me know. I'll put a link to this guy down below. Check it out.
Shadow Dragon, one of my most frequent commenters, figured out why an aggregate query is failing. There was a long text field in there. Yeah, you can't have long text in an aggregate query. No long text, no images, OLEs, multivalued fields, or any kind of object type like that. Simple data types only. Okay, okay. I figure this might help someone else. And it's a shout-out to Shadow Dragon.
Tom Servo says that he's used an Access ACCDE before. And he knows that it can be used on a computer that does not have Microsoft Access. You don't need to have the full version of Access, but you do have to at least have the runtime installed. You can't just give someone an ACCDE file and have their computer know what to do with it. Okay, are there differences? Yeah, there's a lot of differences. They won't have the ability to make any modifications. They lose some menu options. But as far as I'm concerned, I think that's a bonus. You don't want users getting in there and poking around design. And you can specify whatever options you want them to have in your own development stuff that you put together.
So yeah, it doesn't make sense to have, you know, to buy 20 copies of Access for, you know, 18 people that don't need to be able to modify the database. So by all means, use the runtime. And I do recommend that everyone using the database has the same version of Access. As far as like, don't have some people with Access, you know, 2013, some with 2016, some with, you know, 2021, some of the 365 subscription. Try to get everyone using the same thing. And if your users are all using the runtime, make sure it's the same version of the runtime, okay?
I got three in a row from Kent. Kent says good video, but he has a chuckle because the video is titled Criticisms of Access, but I spend most of my time defending those criticisms or even trying to turn the criticisms into benefits. Well, yeah, duh, I'm an Access guy. And a lot of the criticisms of Access are unfair as I go through in the video. And the actual title of the video is Addressing the Common Criticisms. Yeah, I know the title slide doesn't say that, but the actual title of the video as it is on YouTube. But yeah, of course, I'm going to defend Access.
Access is not perfect. Don't get me wrong. It's got some drawbacks and some things that I want to see changed, but it's still, in my opinion, the best rapid development desktop database platform you're going to find. And I love it and I'm going to defend it. So that's what I do.
Another one from Kent. He says one of his biggest criticisms is that the database can get corrupted simply when you make design changes. Compact and Repair doesn't always fix it. His trick is to create a new database and import all the objects. Yeah, sometimes you have to do that. Compact and Repair won't fix everything. I've run into some problems myself, usually years ago. And in recent years, I haven't had any problems. You can also try a decompile. Sometimes that fixes things that Compact and Repair doesn't fix. But yeah, once in a while, it might be necessary to just create a brand new Access blank database file, import all your objects, and that fixes the problem.
A lot of the times I find that people that have corrupted databases often usually are people who are A, either developing on a computer that's not theirs. In other words, you've got the master copy up on the server and you're trying to do development work across the network. Don't do that. Always copy the file down to your local PC first and make whatever changes you want there. It's just better. Or B, you've got people that are still using the database while you're working on it. Especially people who don't have the database split and they're making changes to the database while other people are in it and it's a single database file. That will definitely lead to corruption.
Another big corrupting thing is people that try to run the database off of a shared file like a Google Drive or a Dropbox, one of those. Those are big no-nos too. So yeah, Access isn't perfect, but you got to learn its quirks.
Last one from Kent. Another complaint is when the user uses the runtime version, if there's any error, the code simply crashes. Yeah, that is true. That is annoying. If you are using the runtime version of Access, which most large network setups are, if you get an error of any kind, a VB error, it will crash the database. So you have to make sure you have error handling everywhere. If there's a spot, a de-lookup of a null value, anything like that. Syntax errors of course, make sure you compile your code. But if there�s any kind of an error that you haven't handled, it will crash the database. So as soon as someone reports something like that, make sure you figure out what they were doing and find that spot and put at least an on error resume next to there. But that's just good coding. Don't blame Access for that. You just got to make sure you handle your errors. You got to handle your business.
Here�s just a nice compliment from Clem that I'd like to share. Pause the video if you'd like to read the whole thing, but he compares me to Mr. Rogers, which I love Mr. Rogers. I grew up watching Mr. Rogers and Sesame Street. Mr. Rogers is cool. And thank you very much for the kind compliment. I appreciate that.
Alright, folks, that's where we're going to leave it today. Thanks for watching another quick queries video. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
COMMERCIAL
In today's video from Access Learning Zone, it's time for another Quick Queries session, where I tackle your burning questions that don't warrant a full tutorial but are still super important. From handling copy-paste quirks in Access forms to fixing cut-off text issues with the new Aptos font, I'll show you how to fine-tune those annoying little problems. You'll also learn tips and tricks for managing multi-select combo boxes, handling undefined function errors, renaming objects without breaking your database, and more. If you've got questions, I'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. When copying and pasting an object from one form to another in Microsoft Access, where does the object paste by default if nothing is selected?
A. The center of the form
B. The bottom righ6t corner
C. The upper left corner
D. The bottom center
Q2. What is a common problem when using the new Aptos font in Access forms?
A. Slow loading times
B. Text getting cut off
C. Incorrect text alignment
D. Inconsistent font sizes
Q3. How can you make text in a command button appear to be vertically aligned at the top?
A. Adjust the vertical alignment property of the button
B. Insert a new line using Control-Enter
C. Change the font size
D. Use padding settings in the property sheet
Q4. In what view can a button added to a report be functional in Access?
A. Print preview
B. Datasheet view
C. Report view
D. Design view
Q5. What often causes the 'undefined function in expression' error when writing functions in Access modules?
A. Incorrect function syntax
B. Duplicate function names and module names
C. Incorrect data types used in the function
D. Missing references in VBA
Q6. Can you select multiple values in a combo box in Access?
A. Yes, using special VBA code
B. Yes, combo boxes support multi-select by default
C. No, combo boxes do not support multi-select
D. Yes, but only in specific Access versions
Q7. Why is it generally advised against using spaces in table names and field names in Access?
A. Spaces cause compatibility issues with other database software
B. It makes field names case-sensitive
C. You must remember to put brackets around names in SQL and VBA
D. It slows down query processing
Q8. What is a common cause of corruption in Access databases?
A. Using different versions of Access on the same database
B. Frequently compacting and repairing the database
C. Developing on a networked copy of the database
D. Using too many query objects in the database
Q9. Why is indexing beneficial for a database?
A. It reduces the database file size
B. It speeds up searches and sorts on indexed fields
C. It makes data entry faster
D. It allows multiple users to Access the database simultaneously
Answers: 1-C; 2-B; 3-B; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I'm reaching into my grab bag of questions once again to address a variety of topics that have come up from students and viewers. It's hard to believe this is our 18th session already. Quick Queries are where I tackle questions that are a bit too specific or brief for a dedicated video, but still worth sharing and discussing.
One question raised concerns about Access forms and the behavior when copying and pasting controls. If you copy a control like a text box or label and immediately paste it, Access will position the new control just below the original. However, if nothing is actively selected on the form, pasted controls default to the upper left corner of the form. This is simply how Access operates, and as far as I know, there isn't a way to change it.
Next, we tackled the issue of text getting cut off in text box controls, which can happen if margins or padding are set incorrectly. These settings can be adjusted in the property sheet under the format tab. However, with the introduction of the Aptos font as the new default in Office, I've noticed this issue cropping up more frequently, especially with older forms or new forms using Aptos. Sometimes, simply switching back to a classic font like Tahoma can solve the problem if adjusting the margins and padding does not.
Another question dealt with aligning text vertically inside command buttons. While Access doesn't provide native support for vertical alignment in button controls, Kevin, one of our helpful community members, suggested adding a line break with Control-Enter, which can visually move text to the bottom, middle, or top, depending on your needs. You can further customize the appearance by making button backgrounds transparent, laying graphic elements underneath, or even using images as buttons, all of which are discussed in detail in my beginner classes.
There was also a question about adding buttons to reports for printing. You can place a print button on a report, but it will only function in report view, not in print preview. There's a property called "Display When" with options for always, print only, and screen only, but buttons never appear in print preview. So typically, I suggest adding separate buttons on a form to open print preview and to send the document to a printer, rather than trying to embed such controls in the report itself.
Another interesting issue involved an undefined function error when calling a custom VBA function in Access. In the example discussed, the function name and module name were the same, which confuses Access. The fix is simple: always give your modules distinct names, often ending them with 'mod' to ensure there's no ambiguity between modules and functions. This practice prevents those frustrating "expected variable or procedure, not module" errors.
I also addressed the frequent question about selecting multiple items from a combo box. In Access, combo boxes only allow single selection. If you need to select multiple items, you will have to use a multi-select list box, which is covered in depth in my Developer 15 class. While Access does have multi-valued fields that allow storing several values in one record, these are tricky to work with in code and I generally recommend steering clear of them for anything beyond simple uses. Alternatively, consider creating a subform with a one-to-many relationship, allowing individual selections for each record.
There was a question on why it's considered bad practice to use spaces in table and field names. My advice is to avoid spaces and special characters in database object names. This keeps coding simpler, as you won't need to bracket everything in VBA and SQL commands. I also try to keep names singular for clarity, avoiding confusion down the line.
For those concerned about deleting unused objects, such as queries, object dependencies can help you detect any linked objects before deleting. One tip is to rename the object with the current date and see if anything breaks over time. Just be mindful, as dependencies in VBA code or other areas may not always be handled automatically by Access.
Indexing came up as well, with an example of dramatic speed improvements after proper indexing. Indexing can make searches and sorts much faster, but over-indexing can impact performance on data entry and updates, so it's important to find the right balance for your specific needs.
I was also asked why I often start tutorials with what might seem to be a "cheesy" or overly simple solution before moving on to more advanced, programmer-style tactics. My philosophy is to present both approaches: the straightforward, non-programming way for beginners, and the code-driven method for those ready for more complexity.
We also looked at setting up tables and relationships for a scenario involving hospices, patients, and medical rental equipment. Planning out the relationships on paper or in Excel before building tables is key. Start with a distinct table for each entity, establish clear primary keys, and set up foreign keys to maintain links between them. Plan ahead for fields such as rental dates and cost, and consider whether you need to record hospice IDs at the rental level for historical accuracy.
A common pitfall I see is people claiming to follow tutorials exactly, but making small yet significant deviations, like using 'Me.ID' in VBA where it's neither needed nor correct. Small details in naming and syntax are critical in programming, and errors often trace back to such subtle differences.
On code quality, I emphasize that writing concise, effective code takes repetition and refinement. Initial drafts are rarely perfect, and I routinely revisit my own past work to improve logic and readability.
There's a recurring request for a school library management system database. While I don't have a dedicated template for that yet, I do offer a rental database that can be adapted for managing books and checkouts, which covers many of the same concepts.
There was a reminder about aggregate queries: avoid including long text fields, OLE objects, or multi-valued fields, as these data types are not supported in aggregate calculations.
Deploying ACCDE files came up too. Remember, users can run ACCDE files without a full Access license, but they do need the Access Runtime installed. ACCDE files restrict users from making design changes, which is often a benefit in a production environment. Just ensure everyone is running the same Access version to avoid compatibility issues.
Kent, one of our regulars, noted that although I address criticisms of Access, I tend to defend the platform as well. While Access has its limitations and occasional risk of database corruption, careful practices like making design changes on local copies and not running the master file directly from cloud storage can minimize these problems. Sometimes, when compact and repair fails, importing objects into a new database is necessary. Error handling is also essential, especially when deploying with the runtime version, since unhandled errors will crash the application.
Lastly, I shared a nice compliment comparing my teaching style to Mr. Rogers. I take that as high praise, as my goal is always to offer clear, approachable instruction.
That's all for today's Quick Queries. As always, you can find a complete video tutorial with step-by-step instructions for all these topics on my website at the link below. Live long and prosper, my friends.Topic List Cut and paste behavior in Access forms
Handling pasted controls defaulting to upper-left
Understanding text cutoff in textbox with Aptos font
Adjusting textbox margins and padding
Choosing appropriate fonts to fix display issues
Vertical alignment tricks in command buttons
Using line breaks in command button text
Creating transparent buttons for custom button designs
Button display settings in Access reports
Differences between Report View and Print Preview for controls
Using the 'Display When' property in reports
Adding print buttons to reports and forms
Avoiding undefined function errors by naming modules and subs
Troubleshooting expected variable or procedure errors
Naming conventions for modules and public procedures
Selecting multiple values in combo boxes vs. list boxes
Implementing multi-select list boxes in Access
Understanding and warnings about multi-valued fields
Using subforms to associate multiple items
Naming conventions for tables and fields (no spaces, singular)
Tips for renaming and deleting unused queries
Using object dependencies to check for references
Effects and best practices for indexing fields
Schema design for patient-hospice-rental equipment relationships
Designing related tables with primary and foreign keys
Mapping out database structures before building tables
Proper usage of Me keyword in VBA code
Paying attention to detail in following tutorial instructions
Writing and refining clean, efficient VBA code
Adapting rental or inventory systems for libraries
Aggregate query limitations with long text fields
Deploying ACCDE files and Access Runtime requirements
Differences between full version and runtime for users
Handling Access database corruption and repairing techniques
Importing objects into a new database to fix corruption
Best practices to avoid Access database corruption
Importance of error handling in runtime deployments
Adding robust error handling to prevent runtime crashes |
