Quick Queries #20
By Richard Rost
14 months ago
Avoid Lookup Fields, Dlookup & DB Maintenance
Quick Queries videos are for me to answer your questions that may not need a whole video themselves. In today's video...
Topics
- Lookup Wizard Issues
- Avoiding Lookup Fields in Tables
- Problems with Dlookup in Queries
- Using Joins Instead of Dlookup
- Database Corruption Causes
- Access Database Backup Practices
- Releasing Temp Vars
- Changing Access Theme to Office 2013-2022
- Importing Multiple CSV/Text Files
- Moving Controls with Keyboard Shortcuts
- Fixing Date Field Format Issues
- Explanation of Command Button Naming
- Restoring Form Property Box and Rulers
- Converting Forms to Reports with Code
- Verifying Required Fields in Forms
- Table Level Validation for Multiple Fields
- Setting Rich Text for Long Text Fields
- A-Z Jump Buttons Functionality
- Using Filter Property for Jump Buttons
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, lookup wizard, lookup fields tables, Access normalization, database development lookup, dlookup query slowdown, join tables Access, tempvars Access usage, tempvars.remove syntax, Access themes 2013-2022, reverting Access theme, Access importing CSV files, Access importing multiple files, form design view tips
Transcript
It's about that time again, folks, time for another Quick Queries video where I take all the questions you guys send me that don't necessarily need a full video on their own, and I put them all together. We do that, and it's called Quick Queries. This is episode 20. I don't believe I've ever done 20 of these already. Well, doing 20 of them now. Anyways, let's get to it.
First up, I get a lot of people asking me about this one. Why don't I like the lookup wizard? Why don't I like using lookup fields in tables? I explain why in this video about the lookup wizard. It's not proper normalization. It's not proper database development to have lookup lists inside of a table. It's just not how it's supposed to be. You have a regular table like your customer table, and if you want to look up some stuff for a list like titles or suffixes, prefixes, juniors, etc., that should be in a separate table. That's just how databases are designed to work. You don't want lookup lists inside of tables. Later on, when you get into programming and development, you want to be able to programmatically work with that data. It's near impossible if it's stored inside of a lookup list inside of a table.
As far as your question goes, as far as hearing a degree of opposition against lookup functions using a dedicated external table, some people think it will corrupt the database. If what you're talking about is a lookup function like putting a dlookup inside of, say, a query, I generally recommend against that because, for each record in that query, you have to perform a dlookup on another table. Is it going to corrupt the database? No, probably not. But in fact, most definitely not. But it's going to be slow. It's going to slow things down. Better off with a join. If you've got customers and, again, let's go with suffixes, senior, junior, third, you want to look that up in a table based on an ID, don't use a dlookup or any other kind of d function, dcount, dsum, dmax. Don't use those in queries. Use a join if you can and join the other table in. But it's not for corruption purposes. It's just for speed, mostly.
Human issues in Access are usually caused by things like improper shutdowns. That's the number one thing. Your computer locks up, and Access doesn't shut down right. Super large file size is two gigabytes as a limit. But if your database is over a gig, you should really start thinking about splitting it. Network issues can cause corruptions. Some of the recent Access versions that have bugs in them can cause corruptions. That's why it's important to back up, back up, back up, back up. Every night, back up. Every time you make any important changes, back up. Always run backups. But no, you don't have to worry about lookup functions. Lookup functions are best if you're doing them for one or a small group of records. You want to dlookup something like on a form. Putting lookup functions in queries can slow things down depending on the number of records you have. That's why joins are better.
I've actually got a TechHelp video on my list called Use Joins, Not Dlookup. It's been on there for a while. So look for a video on this topic coming out pretty soon to a store near you. No, it'll be out pretty soon. So keep an eye out for it.
Next up, Gary, one of my Gold members, says, Is it a best practice to release temp vars at the end of a function when you're done with them? Yeah, yeah, yeah. You can. Temp vars have a lifecycle of the entire database. So once you declare a temp var, it's going to be in use the whole time. Unless you're concerned about memory, I mean, unless you're using tons and tons of temp vars, I wouldn't worry too much about it. But in a perfect database, you could destroy the temp vars when you're done with them. It's tempvars.remove and then your temp variable name or remove all.
But again, computers nowadays have so much memory that this should not be a problem unless you're using tens of thousands of temp vars, which I wouldn't do. We use temp vars, maybe a dozen of them, over the course of the database's lifetime. So, of course, Adam loves temp vars. So is this something you can do? Sure. Do you have to? I wouldn't worry about it, honestly, unless you've got a reason to destroy it, unless you've got a reason why you don't want it around anymore.
Next up, this one isn't so much a query. It's a tip from one of our moderators, Kevin. He says, If you're not keen on the new default theme and colors for Access, you can revert back to the 2013-2022 theme with Calibri. Go to File, Options, Client Settings, scroll down to the bottom, and there's a checkbox right down there on the bottom. Use Office 2013-2022 theme. There you go.
I personally, and I mentioned this in there, prefer just using copies of older databases when I make new ones. I don't remember the last time I created a new database file. I always keep using the TechHelp free template and then working off of that. That's got the template that I like. Of course, Kevin, you've got some other ideas in here too, so check it out. There's the link. I'll put it down below.
Next up, Eve wants to know how to have multiple CSV files or any kind of files, text files, whatever, get imported every time one is added to the folder. So if you want it to happen automatically, then your database is going to have to be running like on a loop. It sits there and it's running and it's just waiting for files to be put in this folder, or you can do it with a button. You click the button, and it imports whatever it finds in the folder.
At the time that I wrote this, I said I've got a video coming out soon that shows something similar, how to import any pictures that happen to be dropped into a folder. And I do have that video available. It's this guy. It's a four-part series, how to load multiple images. You've got images for a customer. You click the import button, and it just pulls in whatever images it finds in your images folder and imports them into the database. Well, it saves their file names and locations and stuff in the database and then moves them to a separate folder. You could do the same thing very easily with text files. Just instead of importing the names of the files, you would be importing the data with whatever import code you want to use. So is it possible? Absolutely.
Up next again is another tip coming from Matt Hall, one of our Platinum members. It's talking about moving things around on a form in design view without the mouse pointer getting in the way. Arrow keys move the object, control arrow keys move the object in small increments, shift arrow keys shrink and stretch, and control shift arrow shrink and stretch in small increments. That's fantastic. It's much more user-friendly than using the mouse. I hate trying to get the controls on the little tiny grid dots. But my problem is I always forget to use these. These are fantastic. I always forget them. I just sit there and start it with the mouse. I sometimes use my screen magnifier and just zoom in. So yeah, that's a great tip. Thanks, Matt. And I love Matt's profile picture. He's got my website in the background. That's awesome.
All right, this is a good one, and I wanted to point this out because this comes up once in a while, and I give credit to Sammy, one of our moderators, for helping Mohammed with this one. He says he's got two date fields in his database. Both are formatted like this, but only the first date shows in the correct format while the second still shows up like that. First of all, I have to use this as an opportunity for advertising the ISO date format. It is one of my missions in life to get everyone around the world to switch to this date format. It's the only one that makes sense. It's the best one for computers. So do it. World peace, and then ISO date format, and getting rid of daylight saving time. I hate daylight saving time. But anyways, this is one of my things I want to see everyone do. And of course, switching to metric, but that'll never happen in my lifetime.
Anyways, so Sammy walks Mohammed through this very carefully. Here's what you can see. It looks like in the form. Then he says, show me the tables. So here's the table date start in the table. He's got his format right there. Here's date end in the format. There it is right there. It looks good. See, this is why I tell people to post it in the forums with some screenshots. Because then we can see what's going on. Just in the description with just text, we can't tell. Now he wants to check out the forms. Here's the forms. There's his data tabs, but don't get fooled because it says text format here, but that's not the format. That's text format. It's plain text or rich text. You don't want that. He says, okay, this is the data table. I want to see the format tab. So we see the format tab. Here it is, the format tab. Format. Oh, look at that. It's right there. This one's missing it.
So even though you've got the format in the table, the form might not have it. And the form will always override the table. What probably happened here was he built the table, didn't have the formats in it, then built the form, then went back and added the formats to the table at that point. When you build a form, it will inherit the formats at that point, at the moment that you build the form. But if you go back and change the format in the table, that doesn't propagate down to the forms and reports that are based on it. So you've got to make sure you check the format tab and the format property for each of the controls you want to change. And then he got it. So shout out and thanks to Sammy for taking his time to help out Mohammed with this one. And that's fantastic. Thank you. And this comes up a lot. This is one of those things that I talk about in my classes too. So you got to be careful with your formats.
Here's one I see a lot. This one comes from Larry, one of my Gold members. He's got command buttons in his database, and he wants to change those names so they match the names in my videos. He's got command 10 and command 13, whereas in the video, it's command 9 and command 12. And he has no idea how this happened. Well, see, what happens is whenever you add a control to a form, it gets the next number. So it could be command 10, command 15, whatever the next numbered control is on your form. These numbers are completely arbitrary. You don't need to worry about what they are. Generally, I never refer to a command button in code unless I'm changing one of its properties, like changing the caption or the color of the button. Then I'll say, like, command 10.caption equals hello there or whatever.
But what I try to do and Alex, my good friend and colleague, got me on this a few years ago because I never used to bother naming my buttons. I didn't, but now I try to because it does make sense when you're going through the code. Try to name them something meaningful, like send email button or open customer button. But you have to be careful because if you rename the button, it usually will break it from its code. This is one of my pet peeves in Access, and I wish the Access team would fix this. If you're in your database, and you create a new button that's command 10 or whatever, as soon as you create it before you put any code in it, give it a good name like I call my hello world button here. And if you right-click and go to build event, you'll see that there's some code in that hello world button.
But if you come over here and you change this to a new button name or whatever, you've now broken the link between that button and its code. So you have to right-click on it, go to build event, and look, I'm down here in new button name. See all that? So what you got to do is then find the code that was in your old button, cut it out, paste it in here. Obviously, you want to indent that because indenting is important. And then you can delete this old shell, the old subroutine. So be careful when you do that. You don't want to break your code, and renaming your objects does. Sometimes if you rename stuff, it does propagate down through the database. If you rename a field in a table, most of the time, not 100% of the time, but most of the time, any queries, forms, etc. that have that field linked to that table field, usually will rename it, but it doesn't always.
So be careful. Try to get your names right the first time because going through and fixing these things afterward is a pain, but the Visual Basic Editor will definitely not rename your objects for you. And again, as far as assigning names goes, if you copy and paste the button, notice now it's command 16. It's the 16th control on this form. And if you go to put some code in here, this is what I used to do. I used to come in here and be like, you know, message box, this is some code. You're not going to ever really need to refer to command 16 click anywhere else. So this is fine. Sometimes what I would do is put in here like, you know, this is the new button that does whatever. Just so when I'm reading through the code, I can tell what this button is.
But Alex is right. You should give your buttons good names upfront. As soon as you create this button, give it a good name. My other new button, obviously, you'll call it whatever it is, open customer form or whatever. But now once you've done that, again, notice I've broken the link to my code. So you got to go find that code. Here it is. Cut this out, delete this, come back down here and then paste it in. And now you're fixed. So just be aware of that. But as far as command 10, command 13 versus command 9, it shouldn't matter. It doesn't matter.This is an older seminar to my email seminar. So this was back, what, 10, 15 years ago before I was in the habit of naming my buttons properly. But you don't have to worry about it.
Here's another one that comes up frequently. I use this little box here a lot when I bring up form properties. And sometimes people say, hey, I can't find it. I've lost that little box and I've lost my rulers. What happened? Well, Scott found it. Go to the Arrange tab, Size & Space, and then the little ruler icons are right down there. All right. So that's where it is. See, I lost it. It's gone. No rulers, no box. All right. Go under the Arrange tab, Size & Space, and there's Ruler. Click it, and there it turns back on. You got your rulers and you got your little box that you can double-click to bring up the properties for the form.
A lot of people lose their grid too. They're like, why don't I have a grid? Well, that's right there. I have a whole separate video on why you might see grid lines versus grid dots. It has to do with where you are. This is an old one. This goes back before I was even doing TechHelp videos. It's in the Tips and Tricks section. All right. This video here will explain how to turn grid lines into grid dots. I'll put a link to this one down below.
Here's another tip. This one is from Adam, one of our moderators. He's got code here to convert a form to a report. I haven't tried it yet myself. But Adam is usually very reliable with the code that he produces. So if you have a form and you want to copy everything over and create a report, this looks like it does pretty much that. Check it out. Ludwig, let me know if you try it and if it works. Yeah. Okay. Cool.
Next up is a question from Donald, one of my gold members. He says he's discovering from employees that they might be short-cutting some data entry on some important forms. Is there a way to verify that required fields are entered before letting an employee commit the form to the database?
Alex brought up the required video, which definitely shows you how to set up a field so it is required, so the users have to put something in there. But, of course, and I mention this a lot, I think even in that video, no data is better than bad data.
If you have something like a phone number and you require it, and the user has to put it in, but the customer has already left and they forgot to get it, they're going to put in something like 239-1234567 or whatever because they have to in order to save the record. But now you can't tell, you can't generate an easy list of here are the people that we didn't get their phone numbers. Let's contact them. Let's, I don't know, send them mail to get their phone numbers. You got a bunch of bad data. You got garbage in your database. So don't make something required unless it's actually really, seriously required.
If it's something that involves multiple fields, you can also look into table level validation. This allows you to look at the values in multiple fields to determine if the data is valid or not, not just a single field.
Kenneth asks, he's just curious, then why can't you set all of your note fields, your long text fields, to rich text all the time? In my van, it's all rush all the time. You can if you want to. I generally don't, unless I really need that formatting, if I need to be able to bold something or something like that.
Usually, if you're going to be exporting, sometimes whatever you're exporting to doesn't like the HTML tags. So this is just a matter of personal preference. If you need it, great. If you want it, just be aware that it's harder to strip out those HTML tags later if you don't need them and you put them in there anyway.
Next up, James has a comment on my A-Z Jump Buttons video. In case you forgot about this one, it's where I put a bunch of letters across the top. I just did A, M, and R, but you can go all the way through Z if you want to. You click on it, and it jumps down to that location in the list.
James has an idea where he uses the filter property and the like, and he just filters the list so that you only see those records. That's a great idea. That's definitely another option for you, especially if you have tons and tons of records. Instead of moving down to that location, you might be better off filtering the list to show a smaller list. You can use this, or you can actually change the record source for the where condition. There are lots of things you can do. I love playing with all these Legos. There are millions of different solutions, but I figured I'd share this with everybody. Thank you James for the tip.
Well, that's going to about do it for today, folks. If you have questions you want to see answered, post them in the forums on my website. Even if you're not a student, I've got a visitor forum. You can go to the visitor forum and post there.
I do try to read all the comments that are posted on my YouTube channel, but there are a lot of them. So I can't promise everybody a personal reply. But if it's a good question, I might include it in a quick queries video or make a TechHelp video for it. Do members get priority? Yeah, they do. But if I see a good question, I'm all about answering good questions. Doesn't matter who you are.
All right, so there's your TechHelp quick queries video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Lookup Wizard Issues
Avoiding Lookup Fields in Tables
Problems with Dlookup in Queries
Using Joins Instead of Dlookup
Database Corruption Causes
Access Database Backup Practices
Releasing Temp Vars
Changing Access Theme to Office 2013-2022
Importing Multiple CSV/Text Files
Moving Controls with Keyboard Shortcuts
Fixing Date Field Format Issues
Explanation of Command Button Naming
Restoring Form Property Box and Rulers
Converting Forms to Reports with Code
Verifying Required Fields in Forms
Table Level Validation for Multiple Fields
Setting Rich Text for Long Text Fields
A-Z Jump Buttons Functionality
Using Filter Property for Jump Buttons
COMMERCIAL:
In today's video, I'm tackling your most common Microsoft Access questions. First, I explain why I avoid the lookup wizard and recommend using separate tables for better database management. Then, I discuss why using joins is faster than Dlookups in queries. I also cover managing temp vars, restoring older Access themes, and how to automatically import files. Plus, you'll get some top tips like moving controls with keyboard shortcuts, fixing date formats in forms, and renaming command buttons properly to avoid broken links. 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. Why does the instructor advise against using lookup fields in database tables?
A. They are too difficult to use.
B. They are known to cause data corruption.
C. They go against proper database normalization principles.
D. They make the database file size exceed the limit.
Q2. What is the recommended alternative to using dlookup in queries?
A. Use dsum and dmax functions instead.
B. Merge all tables into one.
C. Create lookup fields directly in the tables.
D. Use a join to connect the tables.
Q3. Which scenario was given as a common cause of Access database corruption?
A. Using too many lookup functions.
B. Renaming buttons frequently.
C. Fake data entry.
D. Improper shutdowns and network issues.
Q4. What advice is given concerning temp vars in a database?
A. They should always be removed at the end of a function.
B. They can be left in memory unless memory usage is a concern.
C. They must be removed to prevent database corruption.
D. They should not be used more than once in a database.
Q5. What can you do if you don't like the default Access theme and colors?
A. Switch to the dark mode.
B. Revert back to the 2013-2022 theme via Options.
C. Change each color manually via advanced settings.
D. Download a third-party theme editor.
Q6. What needs to be done to have a database automatically import files added to a folder?
A. Restart Access every time a file is added.
B. Set the database to run on a loop to check the folder continuously.
C. Use a form with a refresh button.
D. Manually drag and drop files into the database.
Q7. What key combinations are recommended for moving, shrinking, and stretching objects on a form without the mouse pointer getting in the way?
A. Arrow keys for moving and spacebar for shrinking/stretching.
B. Arrow keys for moving, control+arrow for precise movement, and shift+arrow for shrinking/stretching.
C. Shift+space for moving, control+space for shrinking.
D. Use only the mouse for all actions.
Q8. What common issue was highlighted when comparing two date fields in different formats on a form?
A. Differences in regional date settings.
B. Form-level settings can override table-level settings.
C. Usage of different date functions.
D. The database version being older.
Q9. Why does the instructor recommend naming buttons with meaningful names?
A. It automatically increases the database performance.
B. It makes it easier to identify button functions in code.
C. Access requires meaningful names to generate events.
D. It improves the aesthetics of the form.
Q10. When creating a new button and then changing its name, what must be done to preserve its functionality?
A. Delete the button and create a new one.
B. Copy the button's event code, rename the button, then reassign the event code.
C. Click "Refresh" in the properties window.
D. Change the button's color to indicate it's been renamed.
Q11. What is recommended when it comes to making fields required in a form to avoid bad data entries?
A. Always make all fields required.
B. Use table-level validation for complex checks.
C. Avoid making fields required to prevent bad data entries.
D. Make all text fields rich text.
Q12. How can you restore the ruler and grid lines if they are missing in Access form design?
A. Restart Access to reset the settings.
B. Use Arrange tab, Size & Space options to turn them back on.
C. Reinstall Access to fix the settings.
D. Reset the user profile in Access.
Answers: 1-C; 2-D; 3-D; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-C; 12-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
Today's TechHelp tutorial from Access Learning Zone focuses on addressing various questions that don't require an entire video on their own. This is episode 20 of our Quick Queries series, where I compile your frequently asked questions into one comprehensive session. Let's get started.
A common inquiry I receive is about my aversion to the lookup wizard and lookup fields in tables. The main reason is that using lookup fields in a table isn't considered proper normalization or good database design. For instance, if you have a customer table and want to store titles, suffixes, or prefixes, such lists should reside in separate tables, not as lookup fields within the customer table. This approach ensures better database design and facilitates easier manipulation and programming of data later.
Some people worry that using lookup functions in queries, such as Dlookup, could corrupt the database. While it's unlikely to cause corruption, it will significantly slow down performance. Each Dlookup call in a query must process a lookup for each record, which can be inefficient. Instead, I recommend using joins to link tables, which is much faster and more efficient.
Database corruption in Access is typically due to issues like improper shutdowns, large file sizes approaching the two-gigabyte limit, and network problems. Some bugs in recent Access versions can also cause corruption, which underscores the importance of regular backups. Using lookup functions is fine for small-scale lookups, like those on forms, but avoid putting them in queries to maintain speed.
Regarding the use of temporary variables (temp vars), it is best practice to clear them at the end of their use within a function. While temp vars persist for the entire database session and modern computers have ample memory, tidying up by removing or clearing temp vars can prevent unnecessary memory usage, especially in extensive databases.
Another handy tip from our forum moderator, Kevin, is about reverting to the older Access theme. If you prefer the look and feel of the 2013-2022 theme, you can change to it by navigating to File -> Options -> Client Settings, and selecting the checkbox to use that theme.
Eve asked about automatically importing multiple CSV or text files when they are added to a folder. You can set this up by having your database run a loop to check the folder or by adding a button that triggers the import process. This can be similar to how you would import images into a database.
For those who struggle with moving objects in Access forms without using a mouse, Matt Hall shared a useful tip. You can use arrow keys for moving objects, control-arrow keys for finer movement, shift-arrow keys to resize, and control-shift-arrow keys for precise resizing. These shortcuts offer more precision than dragging with a mouse.
Another common issue involves date formatting in forms. Even if you have set the date format in the table correctly, it might not reflect on the form because the form's settings override the table's settings. Ensure you check the format settings in both the table and the form.
Larry brought up a question about command button names not matching those in my videos. Every new control added to a form receives a sequential number, which is arbitrary. To avoid confusion, give meaningful names to controls as soon as they are created, and be cautious when renaming them to avoid breaking code.
Many users sometimes lose the little box or rulers in their form design view. To restore them, go to the Arrange tab, select Size & Space, and click the Ruler icon.
Adam contributed code to convert a form into a report, which can be useful if you need such functionality.
Finally, Donald asked about verifying required fields before committing forms to the database. While setting fields as required ensures they must be filled, it's often better to allow blank fields rather than risk invalid data entry. For complex validation involving multiple fields, consider table-level validation rules.
Kenneth wondered why not all note fields use rich text. While rich text can be useful for formatting, it can complicate data export processes. Use it only when necessary.
James suggested using filter properties to make A-Z jump buttons more efficient by filtering rather than jumping to specific records. This method could be particularly beneficial when dealing with large datasets.
That's it for today's Quick Queries. For more detailed tutorials and step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.
Topic List
Lookup Wizard Issues
Avoiding Lookup Fields in Tables
Problems with Dlookup in Queries
Using Joins Instead of Dlookup
Database Corruption Causes
Access Database Backup Practices
Releasing Temp Vars
Changing Access Theme to Office 2013-2022
Importing Multiple CSV/Text Files
Moving Controls with Keyboard Shortcuts
Fixing Date Field Format Issues
Explanation of Command Button Naming
Restoring Form Property Box and Rulers
Converting Forms to Reports with Code
Verifying Required Fields in Forms
Table Level Validation for Multiple Fields
Setting Rich Text for Long Text Fields
A-Z Jump Buttons Functionality
Using Filter Property for Jump Buttons
|
