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.
For a complete video tutorial on this topic, please visit https://599cd.com/QQ19
Top comments (0)