Quick Queries #19
By Richard Rost
2 years ago
Multi-Page Forms, Referential Integrity, Sorting, 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
Creating multi-page forms in Access
Using scrollable long forms
Implementing tab controls in forms
Creating and using subforms
Building navigation forms
Designing wizard-style forms
Embedding images in HTML emails
Using HTML image tags with public URLs
Sorting text fields numerically in Access
Converting text to numbers using VBA
Storing data like zip codes and phone numbers as text
Using VBA type conversion functions
Receiving emails without Outlook using Access
Running Access on a Mac
Running Access on a Microsoft Surface
Appending Outlook emails to an Access database
Importing and linking Outlook folders in Access
Moving controls up and down in continuous forms
Identifying ActiveControl and PreviousControl in Access
Determining NextControl in tab order using VBA
Fixing compact and repair issues in Access
Handling parent-child relationships and referential integrity
Using queries to identify missing parent records
Setting up outer joins in Access queries
Understanding and working with cascade deletes
Links
Recommended Courses
Previous Quick Queries
Keywords
TechHelp Access, Microsoft Access Quick Queries, creating multi-page forms in Access, Microsoft Access tab controls, Microsoft Access subforms, subform SourceObject property, Microsoft Access navigation forms, creating wizard forms in Access, sorting text fields in Access, CLng function in Access, Access on Mac solutions, using VBA in Access, outer join in Access
Intro In this video, we'll talk about a variety of Microsoft Access questions, including creating multi-page forms using tab controls and subforms, embedding images in HTML emails, sorting numbers stored as text, and using type conversion functions in queries. We'll also discuss options for running Access on Mac and Surface devices, syncing databases with cloud folders, handling referential integrity errors, working with tab order in forms using VBA, and tips for managing Outlook emails in Access. This is Quick Queries 19.Transcript Welcome to another TechHelp quick queries video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Welcome to number 19. Can't believe there are 19 of these already. Is there a quick answer? Quick answers? I can't talk today. Quick answers to queries. Although, you try saying that 10 times fast. Quick answers to queries about Microsoft Access may or may not contain questions about actual queries, although probably, I think we have one on there today.
Anyways, let's get to it. Today's first question comes from TXM More. He says, how do you approach a two-page form, that is, you have a form in which you are entering a ton of data and you want to break it up into two separate forms, aka page one and page two?
Well, there's a lot of things you could do. First, you could just make it a really big form. You can make it a really long form and just scroll down, and as your user is typing, just you know, go to the next field and just scroll down. You could set the form size whatever you want on the screen, but the user can scroll up and down as much as they want. Another thing you could do is use tab controls.
Now, when tab controls first came out, I didn't like them because they were kind of buggy, but in recent versions, they've gotten much better, and you can put this tab control on your form, right, and you can put like contact info, an address block, or more info. So you've got the customer information up here and then all their other information, you know, you can put their phone numbers and stuff in here. So a tab control works too if you want to switch between different tabs, I guess, inside of the customer form. That's another option.
You could make subforms that you can switch between, and in this video, I show you how to do that with a vehicle maintenance database. You've got the vehicle over here, the maintenance history, and the future maintenance coming up, and those are just two different forms inside of a subform control and you just change it with the subform's SourceObject property. That requires a tiny little bit of VBA, but this is the method I personally prefer. I like doing this because these can be completely different subforms in here.
Or, you could use any navigation form that you want. I personally like making a form and then putting buttons on it to go to other forms. You could even set up something like this and make like a wizard, right, where the first page has just the fields you want, and then on the bottom is a button that opens up the next form and you make individual forms for it. You know, the first one is enter your name and address, you click next and it goes to another form and it says okay, enter in your whatever, you know, banking information. I don't care. All these forms are still linked to the same table, to the same record, right, but they just display different information and you can have forward and backward buttons and all that stuff.
So it's all about how you want to do it. If you want to see some more examples, post a comment down below, and maybe if enough people are interested, I'll make a whole separate video just on this topic.
Alright? Okay.
Xavier says, are you going to teach us how to embed images in the HTML message? This comment was on my video on how to send email using CDO (Collaborative Data Objects) in Microsoft Access where you can send right from the database and not have to go through a program like Outlook. In the extended cut for the members, I show how to format HTML messages.
Well, if you're formatting your message as HTML, then all you have to do is put an image tag in it. This is just a basic HTML command, right, starts off with image src=" and then a link to your picture. I recommend putting your pictures on your website or at least a public web folder, right, a Gmail or a Dropbox folder that's public because you don't want to be sending images through email. It makes the emails big and slows down the whole system. So just drop your image on your website somewhere or in a public folder and stick an image tag in there. And if you want to hyperlink that you can too. So that's just knowing a little bit of HTML. I do have an HTML basics course on my website. It's real basic, but I'll put a link to it down below.
Ralph PHP says, I understand that using short text field is a good workaround if you're not doing math on a field, but doesn't Access sort numbers incorrectly if they are stored as text?
Yes, if you sort a short text field and you've got numbers in it, they're going to sort alphanumerically. Alright, so if I have a table and I've got, let's say, an ID, right, and I've got my value, let's call it, and that's a short text field, okay, my table, and if we put some numbers in here, alright, in my value, like 101, 2, 34, 14, whatever, okay, if we sort this field, alright, right-click, sort A-Z, you're going to get that. That's an alphanumeric sort, which looks at it letter by letter.
Okay, if you want to sort this numerically, just use a query and convert that over to a number. Alright, so you'll create a query and you'll bring in that table, right, my table. Okay, bring in whatever you want and then right here we're going to say my number is just convert to whatever you want, CLng, that's convert to long, my value. Okay, and if you run it now, you see there's numbers. Now here's the big difference, you can see these ones are lined up on the right side of that cell, let's call it the column, these are on the left. Left tells you that's a text value, right tells you it's a numeric value. Okay, and now if you sort it, whether you sort it in the query or not, you can sort it right here. Right, and now you'll see those sort by the proper number. That's all you've got to do, use a query.
Things like zip codes, phone numbers, social security numbers, I store those as text. They're easier to manipulate, they're easier to work with, they're easier to deal with than actually storing them as numbers. Plus, with some things like zip codes, you lose that leading zero, but it's real easy to convert it to a number if you want to sort them that way.
Okay, and that's just, again, the CLng function. There's a whole bunch of different type conversion functions. There's convert to byte, currency, date, double, long, whatever you want. Now, this video talks about all of them.
Lucian says, dude looks like Charlie Sheen. Thanks for the help. I certainly hope you mean the Major League young, handsome, attractive Charlie Sheen and not the older "winning" Charlie Sheen. This guy. Yeah, I could see it. Okay. Alright. This guy, I don't know, actually he's still not that bad looking. He's, what, pushing 60. So, not a bad looking guy for his age.
I've been getting a lot of emails like this one, a lot of comments like this one. Shaky says, is there any way to receive emails without Outlook? Yeah, it's possible, but it's not easy. I've been using Outlook myself to retrieve emails. I've got my Access server database, right, sits in the corner. It sends and receives emails. I send through CDO and Gmail, but I receive from Outlook. It's really easy to receive email through Outlook. I cover it in my email seminar. I'll put a link to that down below. But you've got to have Outlook for it, and I'm worried that they're going to change it so you can't do that in the future. So I am looking for a solution myself.
I know it's got to be possible to connect using Google's API or something like that to connect and pull in emails, but I just haven't done it yet. But I'll be on the lookout for a solution myself. If anybody knows of a good solution, let me know too, that doesn't require having to have an email program.
Next up, Elise has a few questions. She says, how do I get Access? I use an iMac. My condolences. I believe I can't use this for Access, correct? And I do have a Microsoft Surface. Can I use Access on that device?
First of all, I pick on Mac users a lot, but I do have respect for Apple products. They're just, with Apple, you're kind of in the walled garden. It's whatever Apple says you can use, and they're very hard to tinker with and play with. I like PC and Android because you can get in there and get your hands dirty and break stuff, right? With a Mac, it's like this is what you're using, these are the colors you hang outside. It's like, no. I haven't really used a Mac product since an Apple II in high school.
But let's address these questions. First of all, how do you get Access? Well, I got a whole video on how you can buy Access. There is no free version available, like some of the other apps like Word and Outlook and Excel. They've got free versions. There is no free version of Access. I currently use the Microsoft Apps for Business, which is eight bucks a month, which I think for a business application like Access is a steal. Okay, now this was as of October 2022. I don't think the prices have gone up since. I could be wrong, check Microsoft's website, and they rename stuff all the time. So it might even have a different name by now, but they do definitely still have a version. I recommend the 365 subscription. It's a small monthly fee instead of a big upfront charge, but whatever works for you. But this video explains a lot more.
Alright, how do you use it on your iMac? Well, you are right, Access by itself does not natively run on a Mac. This question comes up often in our forums, and Alex, my right-hand man, he's got a bunch of different things here. You know, you can use VirtualBox, there's all kinds of things you can do. I'm going to leave this up to Alex. Alex, why don't you put together a video on how to get Access to run on your Mac because you're a Mac user? And there's those different options that are available.
I just asked ChatGPT to see if it's got any new information, and basically it says, yeah, use a virtual machine, Parallels Desktop, VMware Fusion, VirtualBox. That'll basically create a virtual Windows machine inside of your Mac OS. There's Boot Camp which dual boots, you can remote access into a Windows PC if you've got a Windows PC running around. There's apparently a program now called Crossover. It says that not all features of Access may work perfectly with Crossover, so I don't know about that one. And of course, there's a cloud service. A cloud service is basically a PC in the cloud on the internet. You set up your own Windows desktop with whatever applications you want to install, including Access.
And this is great even if you are PC users, and you want to have a shared Access database up in the cloud that a bunch of people can use. I've been recommending Access Database Cloud for years, and they're fantastic. So that's definitely another option.
And as far as using it on your Microsoft Surface, well, yeah, you should be able to. Give it a try. And again, I posted this to ChatGPT just to get its opinion. The versions of the Surface that run Windows should definitely be able to use it, like the Surface Pro. Some of the other ones, as long as you're not running in Windows S mode, which only allows apps from the Microsoft Store, and Access is not available there. The Surface Duo and Neo are using Android, so you can't use those. Some of our other users, like Adam, one of our moderators, says he uses it just fine with the Surface. Jeffrey Crafts says yep. So yeah, as long as it's one of those better Microsoft Surfaces, you shouldn't have any problems.
Gary asks, I use Microsoft Outlook 365 for my work emails, is there any way to have those emails appended to a database so I can search and/or sort them, etc.?
Yeah, the easiest way to do that is to send those emails from Access using the techniques that I mentioned earlier with my sending email with CDO. You can also send the emails through Outlook if you want to, although I recommend getting away from that. Can you use Access to pull information in from Outlook? Yeah, you can. If you've already sent the emails from Outlook, you can import them into Access, or you can even attach to those folders in Outlook from Access to look through them. But again, this only works with classic Outlook, not the new Outlook. Okay?
And if you're not sure which version of Outlook you got, check this video out. I did a video on an Office Watch article talking about how the new Outlook is not ready. You lose a lot of functionality with the new Outlook about sending emails and stuff especially. So the new Outlook is basically just kind of like a web version of it. It's not the classic, beautiful Outlook that we've known for decades.
This was a comment on YouTube regarding my move it up and down with the keys. So you can use on a continuous form. You can up and down arrow to move like an Excel spreadsheet through your continuous form, and one user said that this is not needed. Go to Options, Client Settings, and change the up/down, how it works.
Yes, this is correct. You can change how the keys work on your system, but that's only in your client. Okay, if you're distributing a database to everyone else, you'd have to go around to everyone else's machines and make the same change. Okay, whereas if you do it with the move up/down keys trick that I show you, it's built into the database, and everyone gets the same functionality. So it's all about how you want it to work. Here's a link to that video if you want to check it out.
Alright, this one's from my guys beating me up because I forgot to cover something in the previous quick queries. When we were talking about copying and pasting controls, and Sammy says, you can somewhat control where Access will paste after you've copied the control. You want to click on another control, and then paste, and Access will paste under the control you clicked on between the copy and paste. And I'm like, yeah, I didn't cover all the tricks in the last video. Yeah, I know there's tons of tricks. There's so many tricks I don't always use all of myself. So if it's not something that I use on a regular basis, I forget about it. And one of the great things about my job is that I love learning things from you guys too. You guys teach me a lot of cool stuff that I never knew before, just a little like keyboard shortcuts and tricks like that. So let me show you what Sammy's talking about.
So if you're in a form, right, and you take this guy and you go copy, paste, okay, if you are on this guy and you go copy, paste immediately, it goes below it. If you don't have anything selected, it goes up there. Okay, what Sammy's saying is if you want to go under here, click on that guy first and it puts it under there. Okay, it actually lines up with the label, but right, if you click on, let me delete this guy.
Let's see, you want to put something on the bottom here.Let's see you click on this. Copy if you click on that and paste it goes right there. So there's so many little tricks, and I can't cover them all, but that's what Quick Queries are for so we can go over all this weird stuff together. So thanks, Sammy. Here's an interesting question in my form. I'm supposed to by Jerry. Uh, Jerry says they have ActiveControl and PreviousControl.
So ActiveControl tells you what control you're on, like a button you click on or what text box you're sitting in. PreviousControl tells you where you've been. Okay, but he said he couldn't find a NextControl or FutureControl. And sometimes when I think I heard about this stuff, my head hurts.
Um, yeah, and not being funny, you can figure out what the next control would be if you're tabbing right through the tab order. In fact, I asked Chat GPT to give me some code, and I have not tested this, but this looks about right. What you could do is you could say, "Hey, you know, if you click a button or whatever, look through the tab index," because every control on your form has a tab index, right? One, two, three, four, five. So that Access knows where to tab next.
Okay, and you can customize that order. I covered tab order in a lot of videos. But this will actually see what's the control with the next tab order. All right, and again, I haven't tested this, but it seems about right. So, yeah, okay, that's definitely possible. I can't think of a reason to do this when it would be useful, but is it possible? Yeah, possible.
Here's a great question by William. He says, "I have a database I made myself. I'm trying to compact and repair like I was told to do. When I try to compact and repair, I get an error message saying the compact and repair operation has been canceled. You might not have adequate permission to the folder the database is located in. It's on my own laptop. It's my personal database just used by me. My question is, what can I do to fix this problem?"
All right, I have a question. Can I put my database in a Google folder to do the compact and repair? All right, that's the problem right there, but there are some things. Obviously, run down the troubleshooter. Make sure it's in a trusted location. Okay, now the problem with putting it in a Google Drive folder, okay, is that Google Drive is constantly trying to synchronize whatever is in that folder with its copy in the cloud and your other machines. Okay, so if you're doing a compact and repair while Google's trying to back that file up, it's not going to say exactly that you don't have adequate permission because the file is open. Someone's using it.
All right, so you got a couple of options. One thing you could do is go to your Google Drive while it's running. All right, right-click on it, go to settings, and then pause syncing. Okay, that will pause Google Drive from trying to make backup, so whatever is in your drive folder, and at that point, you should be able to do your compact and repair. Just remember to make sure you turn it back on. Or the other option is to not store your database in your Google Drive folder. What I'd personally do is I keep my databases in my C drive, uh, in a different uh, folder that's not backed up in my G drive, for example, and then I have my nightly Access backup copy my databases into my Google Drive folder for backup.
Okay, so don't run your actual Access database out of a Google Drive or a Dropbox or a OneDrive or any of those shared folder-type setups. All right, this question comes up so much. I got a whole separate video on it. Do not run Access out of a shared folder like that. Even if you're the only person using it, you can have problems like this. All right, I used to use, um... I think it was Google Drive. This is going back probably four or five years, but I used to have an office, and I used to drive, you know, from my house to my office, and it was about a 30-minute drive from Cape Coral to Fort Myers. And in the morning, you know, if I was on my computer, I would, you know, have my database open, and it was in my Google Drive, and I'd close it.
And then by the time I got to the office in Fort Myers, it had had enough time to sync up. It was just a small database file, maybe, you know, a couple hundred megs, and no problems if you're the only person using it. But if you got two or three people doing that, or the database doesn't sync properly, you're going to have problems, or if it tries to do something like a compact and repair. So just you're better off just not using these things with Access. It's not designed for that, and of course, I like to mention this as much as I can, make sure you got good backups of your data. Okay, okay, back up back up back up!
This question's from June. He says, "I'm having a problem creating a relationship between OrderT and OrderDetailT with referential integrity and cascade deletes. It says that data in the table violates referential integrity rules." This usually happens when you're missing a parent. Okay, you're missing an order, but you've got records in the OrderDetail table already that are looking for, you know, that are linked to a parent that you deleted. Let me show you.
All right, here's my TechHelp database. I got orders, and I got order details. Okay, these all should be matched up. I don't have any global relationships in this database. I don't use them that often, to be honest with you. I prefer to handle stuff like that in my code. But let's say, all right, we've got orders here, and we've got order details. Let's say I delete order one. All right, we got detail items in here, right? For order one, those are the three line items for order one. If I delete that order, all right, lets me do it because I've got no referential integrity. One of the things referential integrity does is it says, you know, everybody on the one side of a one-to-many relationship, you got to have that one record, or you can't have any many records. It prevents orphans and widows. Well, it prevents it prevents orphans. It doesn't prevent widows. That's a whole separate thing.
A widow would be basically an order that doesn't have any line items which you can check for that too, but it's not quite as easy. I got a whole separate video on that one. But now if I try to go into relationships and create a relationship between OrderT and OrderDetailT, right, just to link them by order ID, enforce referential integrity, okay, hit create and say ah can't do it. All right, it violates referential integrity rules because you've got records in the system already that are no bueno.
Okay, so you're going to have to go back in there and figure out who's missing what before you can set up that referential integrity. And how do you do that? Well, let's make a query. Create, query design. I'll bring in OrderT and OrderDetailT. Now the relationship here in the query is formed because Access sees that you've got that and that they're named the same and they're the same data type. There's, they're both numbers. So this is called an ad hoc relationship. It's not an actual system relationship, but Access is like, yeah, these things are probably related. So I'll relate them. Now bring them both down here.
And if you run the query now, you'll see they all match up. What's the problem? Well, remember a basic inner join like this says all the records from here have to match all the records from here. So I need to be able to see where this might be missing. So I need to make this an outer join. So double click on that guy. Now we're going to say I want to see all records from OrderDetailT. I want this one show me all the detail items. All right, and if this is missing, okay. See, and you get that little arrow pointing that way. It says show me all of these guys and the related one over here. Now if you run this, oh look at that. You can see there's someone's missing right here.
Okay, so now I can go over here and I can say give me criteria is null, and now that'll show you just which orders are missing. So you've got four detail items in here that are missing a parent item. Now you can go and fix that. You can figure out what happened in the order. You can add something new. You can delete these line items. However, you want to do it, and then once you do that, you'll be able to create your referential integrity, and once you've created the referential integrity, you can't have this problem anymore in the future. Okay, okay.
If you want to learn about referential integrity, go watch this video. I've also got a video on cascade deletes, which I don't like them. I mean they've got their place sometimes, but be very careful because cascade delete says if you've got a relationship set up between like orders and details, that's fine. You delete an order. You know, you want all the line items gone even though you shouldn't delete stuff. You should archive stuff or mark it, you know, invalid or whatever.
But if you've got like a customer related to your orders related to the contacts, you accidentally delete that customer, all of this stuff is gone. That's going to mess up your accounting. So be very careful with cascade deletes. I also spend a lot of time with relationships and stuff like that in my Access Expert Series Level 1. We go over relational database concepts. We go over referential integrity and Expert Level 2 normalization, global relationships, all kinds of cool stuff. And if you want to learn about outer joins, go watch this missing records video. Usually you see this where, you know, you've got a relationship set up between customers and contacts or customers and orders, and you do a query, and you're like, "I'm not seeing all my customers. Why aren't they showing up in this list?" Well, that's because you've got an inner join, so they have to have an order or a contact for you to see them in that list.
And that's where you usually set up an outer join, left join, right join, outer join, inner join, self joins, all kinds of joins. And of course, I cover all the different kinds of relationships you could possibly want to know in my relationship seminar. Yeah, this is an older image. They're not as cool as my new ones, but this is one of my first seminars I did, but it's classic, and it's got lots of great information in it. All right, so I think that's going to about do it for today. There's your Quick Queries Number 19. I hope you learned something. Live long and prosper, my friends. I'll see you next time for Quick Queries Number 20 or whenever we decide to come out with it.
All right, take care. A special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts. Software solutions manufacturing experts specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at AccessExperts.com. Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office Specialist, and he not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guide. Check them out at ShamaConsultancy.com.
TOPICS:
Creating multi-page forms in Access
Using scrollable long forms
Implementing tab controls in forms
Creating and using subforms
Building navigation forms
Designing wizard-style forms
Embedding images in HTML emails
Using HTML image tags with public URLs
Sorting text fields numerically in Access
Converting text to numbers using VBA
Storing data like zip codes and phone numbers as text
Using VBA type conversion functions
Receiving emails without Outlook using Access
Running Access on a Mac
Running Access on a Microsoft Surface
Appending Outlook emails to an Access database
Importing and linking Outlook folders in Access
Moving controls up and down in continuous forms
Identifying ActiveControl and PreviousControl in Access
Determining NextControl in tab order using VBA
Fixing compact and repair issues in Access
Handling parent-child relationships and referential integrity
Using queries to identify missing parent records
Setting up outer joins in Access queries
Understanding and working with cascade deletes
COMMERCIAL:
In today's video, I answer many questions about enhancing your Microsoft Access skills. We start with breaking down data input forms into two pages, offering three methods including tab controls and subforms. Next, we explore embedding images in HTML emails and sorting numbers stored as short text fields. I also address running Access on different devices, like Macs and Microsoft Surfaces, and syncing databases through Google Drive. Plus, I share tips for maintaining referential integrity and using VBA to navigate form controls. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.Quiz Q1. What are some methods to handle large data entry forms in Microsoft Access?
A. Use a very long form that users can scroll through
B. Implement tab controls to separate different sections
C. Utilize subforms and switch between them
D. All of the above
Q2. What is a recommended way to include images in HTML emails sent through Microsoft Access?
A. Directly embed the image as an attachment
B. Use the image tag and link to an image hosted on a public web folder
C. Use plain text to describe the image
D. Copy and paste the image directly into the email body
Q3. What happens when you sort a short text field that contains numerical values in Microsoft Access?
A. It sorts them numerically
B. It sorts them alphanumerically
C. It sorts them randomly
D. It does not sort at all
Q4. How can you sort a short text field containing numbers as actual numbers in a query?
A. Change the field type to Numeric in the table
B. Use the CLng function to convert the text to a number in a query
C. Manually sort each row
D. Create a macro to handle the sorting
Q5. Why is it not advisable to store social security numbers as a numeric field in Microsoft Access?
A. Numeric fields consume more storage space
B. Sorting numeric fields can lead to loss of leading zeros
C. Numeric fields do not allow special characters
D. It is difficult to validate numeric fields
Q6. What is one way to view a web-based user interface for Microsoft Access on an Apple iMac?
A. Access for Mac OS application
B. Virtual machine using VMware Fusion or Parallels
C. Direct installation through the App Store
D. Using an Android emulator
Q7. What service allows you to set up a cloud-based Windows desktop that can run Microsoft Access?
A. VirtualBox
B. Crossover
C. Access Database Cloud
D. Boot Camp
Q8. What should you check if you are getting permission errors when trying to Compact and Repair a database stored in a Google Drive folder?
A. If Google Drive syncing is turned on
B. If the database is open in another instance
C. If the database is corrupted
D. If the version of Access is outdated
Q9. What is the main use of the 'Enforce Referential Integrity' option in Microsoft Access relationships?
A. To ensure consistency between related tables by preventing orphaned records
B. To allow deletion of parent records without restrictions
C. To speed up database operations
D. To back up data automatically
Q10. How can you identify orphaned records in a query when setting up referential integrity in Access?
A. Use an inner join to find missing parent records
B. Create an outer join and check for null values in the parent field
C. Use a self join to find unrelated records
D. Manually inspect each table for discrepancies
Q11. What precaution should be taken when implementing 'Cascade Delete' in Microsoft Access?
A. Make sure it is only used when absolutely necessary as it deletes all related records
B. Always enable it to maintain database consistency
C. Use it on all parent-child relationships without exception
D. Disable it to prevent data loss
Q12. How does the 'tab index' property in Access forms help in navigation?
A. It defines the order in which controls are navigated through using the Tab key
B. It sets the default text for each control
C. It locks the control from being accessed
D. It changes the appearance of the control
Answers: 1-D; 2-B; 3-B; 4-B; 5-B; 6-B; 7-C; 8-A; 9-A; 10-B; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I'm here to answer a variety of your questions about Microsoft Access and related topics. This is the nineteenth installment of the Quick Queries series, where I provide concise answers to your most pressing Access questions.
The first question today focuses on creating multi-page forms in Access. If you have a large form that seems unwieldy, there are several techniques to make data entry easier for your users. One straightforward method is to create a long, scrollable form where users simply scroll down as they enter information. Another popular option is to use tab controls. While they were unreliable in earlier Access versions, tab controls now offer a stable way to organize related data on separate tabs within the same form, such as dividing a customer record into contact details and additional information.
Alternatively, using subforms can give you flexibility. By switching which subform is displayed within a main form, you can present completely different sets of fields and information, all related back to the same record. This approach might need a bit of VBA coding, especially to switch subforms using the SourceObject property. Personally, I find this method to be robust and versatile.
Navigation forms and buttons are another way to mimic a multi-step entry process. You can set up a series of simple forms, each representing a page in a data entry wizard, and use navigation buttons for users to move back and forth between them. All the forms should still be tied back to the same record source.
If you're interested in further demonstrations or have specific use cases, let me know in the comments. If there's enough interest, I can devote an entire lesson to creating multipage forms in Access.
Next, I cover how to embed images into HTML emails generated from Access. If you're sending HTML-formatted emails directly from your database using CDO or similar tools, you can include images using a standard HTML image tag. For the best results, store your images on a public web server or use a publicly accessible cloud folder, and then reference their URLs. This keeps email sizes manageable and ensures images load reliably.
Questions also arose about storing numbers as text and the impact on sorting. When numbers are saved in short text fields rather than as numbers, Access sorts them alphanumerically, leading to misleading orderings like 101 coming before 14. To sort properly, use a query to convert those text values to a numeric data type, then sort by that calculated field. Functions like CLng let you cast text to numbers for accurate sorting. For non-numeric fields like zip codes or phone numbers, storing them as text is appropriate due to leading zeros and formatting quirks.
Receiving emails into Access without using Outlook is possible, but so far, most straightforward solutions require Outlook. I use Outlook to pull emails into Access, though there are APIs and possible cloud solutions if you want to avoid Outlook altogether. If anyone has a reliable method for doing this without Outlook, I welcome your suggestions.
Running Access on Apple devices is a common topic. Access does not natively work on Macs. However, you can use virtualization tools like Parallels Desktop, VMware Fusion, or VirtualBox to create a virtual Windows environment on a Mac, then run Access inside that setup. There are also services that let you run Windows desktops in the cloud. Keep in mind, some solutions like CrossOver may not support every Access feature perfectly. If you own a Microsoft Surface device, as long as it runs a full version of Windows (not the stripped-down S mode or an Android-based device), it should handle Access with no trouble.
For anyone looking to append Outlook emails into an Access database, you can use various techniques to link or import Outlook folders, provided you're using classic Outlook rather than the new web-based version. The newer Outlook loses much of this integration functionality, so be sure you're using the traditional desktop app. There are dedicated videos and resources if you want more detail on integrating Access with Outlook.
When it comes to navigating within forms, such as moving controls up and down in a continuous form, Access does offer client-level settings to adjust arrow key behavior. But if you want everyone using your database to have the same experience, it's better to implement your own navigation logic in your forms so that the feature is consistent across all users, regardless of their local settings.
A related question involved copying and pasting controls in form design. You can influence where a pasted control appears by selecting another control before pasting. Access will position the new control relative to the one you highlighted. This is one of many small shortcuts and tricks in form design, and I'm always happy to learn new ones from community feedback.
Someone asked about accessing the NextControl in VBA. While ActiveControl and PreviousControl are easily available, there is no built-in NextControl property. However, every control on a form has a tab index, and you can determine which control comes next by examining the tab order. You can programmatically identify the control with the next highest TabIndex, though practical uses for this might be limited.
Compact and repair errors also came up. If Access says you do not have permission to a file, and your database is stored in a cloud-synced folder like Google Drive, chances are the sync service is locking the file while it attempts to back it up. This can interfere with compact and repair operations. To prevent this, pause file syncing temporarily or, better yet, store your active database files outside your Google Drive or OneDrive folders. Reserve syncing and backup folders for copies of your database, not the live versions.
Maintaining proper referential integrity is another challenge. If you try to enforce referential integrity between tables like Orders and OrderDetails, but get an error, it usually means your detail table has "orphaned" records that no longer have corresponding parent records. You can identify these using outer join queries, which will show you all detail records that have no matching parent. Simply correct or remove these orphans, and you should be able to create the relationship while enforcing integrity.
A word of caution on cascade deletes: enabling this setting will automatically delete all related child records when a parent is deleted. While this can simplify data cleaning in some cases, be very careful with it, especially where hierarchical data is involved. Accidentally deleting a top-level record could inadvertently wipe out a large segment of related data. Often, it's better practice to "archive" records or mark them inactive rather than delete them outright.
I hope today's video helped clarify these topics and answered your pressing questions about Microsoft Access. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating multi-page forms in Access
Using scrollable long forms
Implementing tab controls in forms
Creating and using subforms
Building navigation forms
Designing wizard-style forms
Embedding images in HTML emails
Using HTML image tags with public URLs
Sorting text fields numerically in Access
Converting text fields to numbers in queries
Storing zip codes and phone numbers as text
Using VBA type conversion functions
Running Access on a Mac with virtual machines
Running Access on a Microsoft Surface device
Appending Outlook emails to an Access database
Importing and linking Outlook folders in Access
Moving controls up and down in continuous forms
Controlling paste location for form controls
Identifying ActiveControl and PreviousControl in VBA
Finding the next control in tab order with VBA
Fixing compact and repair issues in Google Drive
Pausing sync to allow compact and repair
Best practices for database backups
Handling referential integrity errors
Using queries to find missing parent records
Setting up outer joins to identify missing data
Understanding and using cascade deletes in relationships |
