How to Break Years Up Into Two-Digit Number Pairs for Speech Synthesis in Microsoft Access

 

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today we're going to talk about breaking up a year into two-digit number pairs so it sounds right if you have speech synthesis going on. The computer doesn't say 1,972; you want it to say 1972. That's what we're going to do in today's video.

This question comes from Rachel in Westminster, Colorado, one of my Platinum members. Rachel says, I have poor vision, I feel you. Thanks to your text-to-speech video, I have my Access database say a lot of things for me. So do I. One thing that irritates me, however, is when it comes to a year. For something like 1865, it will say 1,865 instead of how it's normally spoken, 1865. Any tips on how to make this sound better?

Yes, it's actually interesting, Rachel, because if you type in a year from 1900 up until I think 2100, it'll say it like that. It'll say 1972 or 2024. But if you type in something like 1865, it'll read it out like 1,865. So there's definitely a cutoff in there somewhere. I'm not exactly sure where, but today we're going to make it so that you can send any four-digit number and it'll correct it like that.

All right, before we get into it today, make sure you go watch my text-to-speech video. It's very easy to make Access speak. VBA's got its own speech library, and that's not the best, but it works. So go watch this. And of course, today we're going to be using some VBA to do what we need to do. So if you've never programmed in VBA before, go watch this video. It'll teach you everything you need to know to get started in about 20 minutes. Make sure you understand how variables work. We're going to be using some of the string functions like left, right, and mid, all those guys. Make sure you know how those work. Be sure you understand string concatenation, that's putting two strings together. And today we're going to create a couple of custom functions, so go watch this video too. These are all free videos. They're on my website. They're on my YouTube channel. Go watch those and come on back. There might be some more. I'm not sure. But if there are, I'll point them out. 

All right, let's get to it.

Okay, here I am in my TechHelp free template. This is a free database. You can download it on my website if you want to. The first thing I'm going to do is plug in the speech synthesis code, so let's go to the code vault. I'm going to go to my website and search for speak or speech or whatever. You'll see it right there. There are the text-to-speech videos there, and down here in the code vault, there's the text-to-speech. This one's free. Anybody can grab this one. I'll put a link to it down below, okay? And this is all you need right there. It's what? One, two, three, four lines of code. All right, so copy that to your clipboard. Only, this is the kind of cool stuff that you find in the code vault, though. So that's what? It's the benefit to be a code member.

All right, back in the database, I'm going to find my global module. There it is. Open her up and resize this. Okay, I'm just going to come right down here to the bottom and hit paste. And there's my speak subroutine. So all you got to do is tell Access to speak and then it'll say some stuff. So let's just test it. Let's come out here, design view. I am going to put whatever I want it to speak in this box here, all right? So speak this. We'll call it. And I'll make the name of this box, instead of current date, we'll make it speak this. We'll get rid of the control source. We'll get rid of the format. And then we'll change this button to speak. You can leave the name hello world button, that's fine. And what we're going to do in here is right-click, build event. And instead of status hello world, we're going to say speak, speak this. And it's going to send, speak this, whatever's in that box to speak and you should hear it. All right, let's test it. Save it, close it, put in here 1913 and speak.

1913. See, it says that one right. If you make this 1813, watch what you get.

1813. Okay, see, yeah. About 2024, let's see.

2024. Okay. How about 2124.

2124. All right, so we're going to make it so that any year, any four-digit year gets broken up like that into two pairs. So this will be 2024. And of course, then we got an issue with this. How do you handle that zero? 

2007. Yeah, see, some people want to hear 2007. So or 1907. What's 19 do?

1907. Yeah, so that one's right. What about 1807. I'm just trying to get a feel for all these different numbers that it does.

1807. Yeah, see, and it shouldn't say and according to the proper rules. And is only a decimal point. This should be 1807 and 15 cents, for example. You only say and where there is a decimal point. So Sammy, put that on the list for Microsoft. They need to fix that. And is only a decimal point. Okay, so let's put a code. 

In fact, what I'm going to do is just so we don't have to keep typing numbers in here, let's put a default value and let's do 1865.

Okay, save it, close it, open it. All right, we got a default value. So the first step is let's set up a function. We'll do it down here, public function, it's our own function. We're going to call it format year, format, format year. We'll send to it Y as a long and we're going to return a string that the speech synthesis can say. The first thing I want to do is check to make sure that Y is within normal parameters. If it's less than a thousand, we can't handle it. And if it's greater than 9999, we can't handle it based on the logic we're setting up. So we'll say if Y is less than 1000 or Y is greater than 9999, then we'll just return the value. And then, and the speech synthesizer, we'll just say it. If you send it the year 852, it'll just say 852, okay. So format year equals, we're going to convert that Y to a string because we've got to return a string. So it's CStr(Y) and then exit function. And we'll put some comments in here. Let's add a bit of commenting. Back for valid year, okay.

Yeah, see, I told you I probably missed one or two videos. Type conversion functions, convert between data types. You got a long, you want to make it a string, you got a date, you want to make it something. That's what all these functions do. Go watch this video.

All right, now if we get down here, then we have a valid year. So let's put that in a string. So I'm going to dim S as a string and I'm going to say S = CStr(Y). Kind of what we did a minute ago, okay. In fact, it's probably not a bad idea to put this first. So let's cut that out. Let's put it up here, all right, put our dim. I'm of the habit of putting all my dims up top. I've been trying to put them down with the declare, but yeah. All right. So we already have an S here, so we don't need to call that CStr twice. We can just say that, right, S = CStr(Y). 

Okay, so now here we can split S into two-digit pairs. So we're going to say S = the left of S, 2, and the space, and the right of S, 2. And yes, you can have a string work on itself like that. All right, because this starts off as 1, 9, 7, 2, and then this will be, it'll turn it into 1, 9, a space, and then 7, 2. If you don't trust me, you can say here, well, we're just going to return it. So return the value and we'll say format year equals S, okay. Save that. And now we're going to speak format year, speak this, got it. Okay, let's see what happens. And you know what, let's status it too, because we got our status box here. Let's status it too, so we can see it. So we'll put in here status S as well. All right, in fact, let's do this here. Let's say Y dash S, we can see what it came in as and what it's going out as. All right. Debug compile and let's do, go, 1865. Okay, there we go, we split it, right, came in as 1865 and now it changed it to 18 space 65. All right, so what if we got this, 1805, what happens?

1805. I don't want it to say zero, I want it to say O5, O5. All right, so back in here, let's put each of these parts into its own variable. All right, let's go left 2 as string and right 2 as string. Okay, and right here we'll say left 2 equals this, right, and we'll say right 2 equals that. So now we've got them in separate variables, easier to work with, right, we'll just replace that with this and that with this. 

But now it shouldn't. We should never have the left one here being a zero, because it would fail that rule, but this could be a zero. Okay, so now we're going to replace a zero with O like that. Okay, because this will make it actually say O, like O. So if the left of right 2, 1, equals zero, remember it's a string, so you can't just put a zero there, you got to put it inside of quotes, then we're going to say right 2 equals O and a space, and the right character from right 2, 1. So grab the five or the seven or whatever that second digit is, okay, and then we'll end up, that could be one line, but that looks good. Okay, save it, come back out now and hit the button.

Okay, yeah, 1805, that's what's being spoken about 2023, let's see what that does.

2023. Yeah, sounds good, 2003, 2003, yeah, sounds great. Now there are some other exceptions too you might want to throw in here, like this, 2000, 2000 doesn't sound right, or even like 1900, 1900, right, so we'll talk about those exceptions tomorrow in part two. So tune in tomorrow, same bat time, same bat channel, or if you're a member, you can watch it right now, because I'm going to keep recording until I'm done. But that is going to be your TechHelp video for today, I hope you learned something. Live long and prosper, my friends, I'll see you tomorrow for part two.

A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They are manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.

For a complete video tutorial on this topic, please visit https://599cd.com/NumberPairs

How to Automatically Load Multiple Images for Customers, Products (or Anything) in Microsoft Access

 

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Got a good one for you today. I'm going to show you how to automatically load multiple images for products or customers or whatever you want at one time in Microsoft Access. 

Today's question comes from Megan in Chandler, Arizona, one of my Platinum members. Megan says, I have to store multiple pictures for each of my products, and right now I'm just putting in the file names one at a time like you show in your images video. However, this is very time-consuming. Is there a way that I can select just a bunch of images and say these are all for product XYZ and have them all load up automatically? It would save me hours every day, and I will be in your debt, great Klingon warrior. Kapla. 

Now, the Star Trek reference alone will get you a video, and being a Platinum member, well, that just bumped your question up to the top of the list. So let's talk about it. 

Okay, in my images video, I show you how to display images in your forms and reports by just simply putting the file name in a text field and using the image control. We do not use attachments. We do not store images inside our databases. All right, they bloat your database, and they're bad for you. Go watch this video for all the information you need on that.

Now, in the extended cut, I teach you how to make a browse button. So you can click on that, pick a file, and it puts the file name in there for you automatically. Today, I'm going to show you a trick where you can set up an import folder, and you can drop all the pictures that you want into the import folder and then click a button, and they'll all get loaded into your database on whatever record you want. Let me show you a sample.

All right, so today, what we're going to do is we're going to take a customer form. We're just going to drop pictures in the contacts. Let's pretend this was a product record. You can have another table that's related to it where you can have multiple products, okay? And then those will be displayed in here. So what we're going to do is we're going to set up an import folder. We'll make a button so you can click on it, and it will open up that import folder. It's just a folder underneath your database folder, right? We're going to call it the import folder. 

Now, any pictures that you want to import into the database, you put in this folder. So I'll just go grab some. All right, I copied these pictures into the import folder, and now I'm going to go back to the database. I'm going to hit import photos, and look at that. They all came in automatically. There's this one, this one, this one, this one, this one, and they're all stored in the contacts under this customer. Whatever images were in that folder are just immediately tagged in here.

These aren't actually imported into the database, but we've stored the location in the contact table. So if you come down here, here they are. And there's the locations. We're going to copy them to an images folder under the database, rename them, and store them. So they're under here in images. There they are with the unique file names now, okay? And you can use them in your database, and they're attached to the customer right there.

And this could be, like I said, this could be a product, this could be students, this could be whatever you want   documents, doesn't matter. So that's what we're going to cover in this series.

Let's talk some prerequisites first. What do you need to know before we get started today? Well, it's going to be a developer-level video. So you're going to need some VBA. Obviously, go watch that images video that I mentioned earlier. Now, if you've never done any VBA programming before, don't worry, it's not scary. Go watch this video. It'll get you started, everything you need to know in about 20 minutes.

We're going to use a while loop to loop through the file, so make sure you understand while loops. We're going to use some of the string functions left, right, mid, in string, those guys, make sure you know these. We're going to use in string reverse. That's finding something from the end of a string. We'll use that to find the file extensions. We're going to use if then statements, make sure you know how to use these. And we're also going to use a select case statement. So brush up on this one. 

We're going to use a record set to add the file to the contact table. All right, so make sure you know how to use record sets. These are all free videos. They're on my website. They're on my YouTube channel. Go watch all of these if you're not familiar with any of this stuff, and then come on back. This is going to be a developer, developer. This is a higher-end developer bit of code we're doing today. So for those of you who keep telling me you want more advanced stuff, well, here's some advanced stuff. I usually cover this kind of stuff in my developer course. 

Now, we're also going to use some basic file input-output, reading through files in a folder, that kind of stuff. I don't have TechHelp videos for this kind of thing. I'm going to show you what you need to know today in today's video. But if you want to learn more about these things in Access Developer 30 and 31, I cover basic file input-output, reading, writing text files, file and folder navigation, copying files and lots more. So I'll put links to these down below as well if you want to learn more about these things.

All right, we're going to start with a copy of my TechHelp free template. This is a free database. You can download a copy from my website if you want to. I'm going to call this one load multiple images. And we're going to put this in its own folder. I'm just going to put mine on my desktop. So new folder, we'll call this images database or whatever you want to call it. And I'm going to put this guy in there. Okay, now inside of this folder, there we go, it opened up on my other screen. We're going to create two other folders. One is going to be our images folder. And that's where we're going to store images that are actually logged in the database. 

Okay, remember, we don't store files in the database itself. We put them in a folder, and we just store the reference, the file, and path to that image in the database itself. Then we're going to set up another folder called our import folder, new folder, import folder. And that's where we're going to put files that we have not yet inputted or imported into the database. And we'll just drag them all, drop them in there like you just saw me do a minute ago, hit a button, and they'll all get copied up to the images folder and then deleted, and then logged, and then everything will be hunky-dory. Okay, all right. 

And yes, for those of you that are asking, can't you just use a button to select multiple files with the file dialog? Yes, you can. It's a lot more advanced. I find this method a lot easier, actually. And I will cover that in the extended cut for the members after we're done with what we're doing here. But this works easy, and the programming for this is not that difficult.

All right, so let's open up our database. And the first thing we need to store is a location to keep track of the image file name. So I got customers and contacts already set up in here. What you want is if you want to have multiple pictures associated with something, you're going to want to have another related table to store all of that something. If you're doing a customer's profile picture, and it's just one, you can just store the file name in here, right in the customer table, which is what we did in the images video.

But, if you've got multiple profile pictures or multiple pictures that you want to associate with this customer, you're going to need a second related table. So if this is products, you can make a product images table, and you can store 15, 20, 1,000 pictures for that product in the related table. I've already got customers and contacts set up, so rather than waste all this time reinventing the wheel, we're just going to store the pictures related to this customer in the contacts table.

Okay, so let's go to the contact table design view. And right down here on the bottom, I'm going to put my image, don't use the word image   image is a reserved word, just like picture is a reserved word, you want to try and avoid those as possible. If you're not sure if something is a reserved word, just put my in front of it, like instead of date. Date's reserved, my date, or whatever. All right, save this. And I'm going to go, let's go into here and just put some sample data in one record. I'm going to put in my image for me. I'm going to put in here, I'm just going to put in Picard.jpeg. And I'm going to throw a file called Picard.jpeg in the images folder, just so we can see that it's working.

Okay. All right, here's my images folder. And I'm going to grab a copy of Picard that I just happened to have handy. We're going to drag and drop that into here. And there we go. So now you'll see that that's in there. When we build the form, you'll see that it's working. 

All right, next up, let's open up the contact form where we're going to store our pictures, or we're going to display the pictures, I should say. And we don't need the notes to be quite that big for this example. So I'm going to shrink up notes just like that, and we're going to put a nice big image right here in the footer. So we're going to go to form design, and find the image control, which is this guy, do not use this guy, this is different, this is an unbound object frame, don't use this guy, that's a bound object frame   those are different completely. You want this guy, image. All right, draw out a box down here, like about so. You're going to cancel this, we just want an empty image frame right there. 

Okay. If you want to give it a background color, I like to give it a background color so that people can tell something's there, like that. All right, that's just empty. Let's open up the properties for it. I don't like image eight, let's call it my image object. All right, so we don't confuse it with the actual image field value. Now, for the control source, I want to use my image, which is a field in the table underneath this, right, my image, but I need to tell the database to look in the database folder, and then go into the images folder under that to find my image. 

All right, how does that look? I'm going to zoom in, so you can see this better, shift F2. Okay, it's going to be equals current project.path, that's the current database folder, and backslash images, that's my images folder, right, backslash, close quotes, and then my image, the field name. So it's going to be whatever the path is to the database, right, C colon, backslash, users, backslash, Rick, backslash, desktop, backslash, whatever the database folder name is, slash images slash Picard. All right, that's going to be put all together, and it's going to be in the control source to this guy right there. All right, save it, close it, open it back up again, and there's Picard, right, because I got Picard.jpeg in the table. So that's easy. 

So far, nothing new. We've done all of this before in the images video. Now we're actually ready to start building our buttons and put some code in to pull in the multiple pictures and put them in different records up in here. And we'll start that in tomorrow's video. So tune in tomorrow, same bat time, same bat channel. 

Yeah, we got all of the prep work done. Sometimes the prep work can take a few minutes, right, but you got to get the database set up properly. And of course, if you remember, you can watch it right now, because I'm going to post it as soon as I'm done with recording it in just a few minutes. But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.

A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.

For a complete video tutorial on this topic, please visit https://599cd.com/LoadMultipleImages

How to Use the Mid Function to Get the Right Side of a String in Microsoft Access

 How to Use the Mid Function to Get the Right Side of a String in Microsoft Access

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we got one for the beginners, and it's surprisingly something I just learned myself. And that's how you can use the mid-string function, just like the right function, which totally blew my mind when I saw this a couple of days ago. Let me give you some background.

Okay, I've been working with string functions since I was a child, literally. I started programming in BASIC on my Cocoa, my Tandy Radio Shack Color Computer, back in the 80s. And I've been using left, right, mid, length, and in-string since I can remember. I've always used left to get the left X number of characters from a string; right to get the right X most number of characters from a string; mid to get stuff in the middle (you go in three characters and give me the next three characters in the middle of the string); length to go into the whole length of the string, and in-string to find the position of something inside that string. All right? And that's pretty much it. Go watch this video for more details on how all of these guys work.

Well, what I didn't realize was it's actually easier to get the right side of the string using the mid function than it is to use the right function. Now, let me show you.

All right, let's say you got a table or you import some data, whatever, where you've got a full name in one field and you want to separate them. Okay? Now, obviously, if you've got a string where you know the position of where you want to separate it, like it's always the fifth character, then yeah, it's easy to use the right string function to get the right four characters. But if it's different, if it varies like here, then it's actually easier to use mid. Let me show you what I mean.

So let's create a query, and this query will be based on that full name table. I'll bring in the full name. And if you look at it, now we've got to figure out where in that full name the space is. And yes, this is a very simple example. It only works with names that have one space in them. If you get a name like Jean-Luc Picard or Eddie Van Halen, it's not going to work. Just bear with me. I'm just trying to make a point. Okay?

Now, to find that space, we can use the in-string function. So, the space position is going to be in-string, the full name field, comma, and then a space like that. That'll say, give me the position of where that space character is. And if I run this now, there you go. In my name, it's in the eighth position. Down here, it's in the fourth position and so on.

Now, to pull out the left X characters of the first name, we just grab the left X minus one characters. So in here, to find the first name, we'd go first name is the left of full name, comma, space position minus one. And now if I run this, there you go, there's the first name, okay?

Now, traditionally, what I used to do to get the last name is you'd have to figure out the length of the entire string and then subtract the space position. So it used to look like this: The last name would be the right of full name, comma, the length of full name minus space position. That'll figure out the entire length of the string and then subtract that space position, and that will give you the right number of characters. And that works. That's what I've been doing for years.

But it's actually easier. All you've got to do is say, give me the right X characters from the space position and add one to it. Watch, it's really, it's weird. Watch. We'll do last name two over here. Watch. Oh, someone's beaming in. All right, we'll say last name two. It's going to be mid full name, comma, space position. That's the space. Plus one. 

Now, normally, I always thought since I was a child, I always thought you had to have that third parameter. How many over do you want to go? But if you don't specify that, it just goes to the end of the string. Hit OK and watch this. Mind blown. It basically says, start at that character, that space position character, and if you don't specify the next parameter, it just says, give me from that spot to the end of the line, the end of the string.

I saw this. I'm like, what is going on? This goes against everything I've ever known about mid, and some of you probably know this already. I did not. So, I immediately had to make a video and share this with all of you guys. I'm 51 years old, I've been programming in BASIC since I was probably eight years old, and I just learned this. So, don't feel bad because even I still learn the simplest things that just are like, what? That blew my mind.

So if you already knew that, I want to hear from you. Just comment down below and tell me. Say, Rick, what, have you been sleeping for these 30-some years?

OK, well, that's it. That's all. I've got lots more lessons like this on my website. Come check it out. You'll find links down below. That's going to be your TechHelp video for today. Hope you learned something. I know I did. Live long and prosper, my friends. I'll see you next time.

A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.

For a complete video tutorial on this topic, please visit https://599cd.com/MidRight

How to Send Email Using PowerShell in Microsoft Access (No Mail Program Required!)

 

In today's video, I'm going to show you how to send email from Access using Windows PowerShell. Now, what is PowerShell, you might be asking? If you've ever done any programming with DOS or Windows batch files, PowerShell is basically batch files on steroids. It's a command-line shell and scripting language. It's built on .NET, so it's new and it lets you automate lots of cool stuff. It comes with Windows, so it's already on your system, and most importantly, a lot of things that you can't do or you can't easily do directly in Access or VBA you can run in PowerShell. Then you just say, "Hey, PowerShell, do this for me," and PowerShell will give you a return code letting you know if it was successful or failed.

Now, we've got a new section on my website called PowerShell LearningZone, and my good friend and colleague Alex Hadley has set up a bunch of free TechHelp videos here to show you all about PowerShell and how to set it up. If you've got newer versions of Windows, you've already got PowerShell on your machine, but there's a lot of different stuff you can do with it, and so I'll put a link to Alex's PowerShell page down below. You can check that out later.

So, why learn how to do this with PowerShell? Well, it's another option for you to have in your Lego toolkit, right? For years and years, we've been sending email from Access using Outlook, but the new versions of Outlook have problems with that. So, I just recently put together a series of videos to show you how to use CDO--Collaboration Data Objects. This was last week's video series. If you haven't watched these, go watch these first because I'm going to use the database we built in these for today's video, okay?

Now, the problem with CDO is that it's been around for a while too. It's part of the Windows Script Host, and although Microsoft hasn't made any announcements as far as getting rid of Windows Script Host, you never know. They have already announced an end of VB script, and I used to use VB script all the time. So, this is just kind of planning ahead for the future just in case. PowerShell is new. It's based on .NET. It's not going to go away anytime soon, so again, it's just another tool to have in your kit.

Okay, so here I am in the database that we built in the send email with CDO series that I said you should go watch, and in here, we've got a customer form. We can send email. I added a bunch of extra fields in the extended cut for the members. We have a CC, a BCC, a reply-to field. We can add attachments by just picking a file. But we're just going to focus on the "to," the subject, and the body. Now, I'm not going to set up a whole separate module for this like we did in the other video. You can do that if you want to. I'm just going to put a button down here and just send directly from the form to keep things simple for today. I don't want this to be a four-part series. It's real simple.

Alright, so send email. I'm going to resize this button, and we're going to say this is going to be sent with CDO. That's the old button. Copy-paste. Put a new button down below. And we'll say send with PowerShell like that. And you can't use the alt S twice, so this can be alt P. How about that one? Okay. And we'll slide that down. Alright, give this button a name. Let's call it send with PowerShell button. Right-click build event. That'll put us in our code editor. And we're right down here on the bottom.

Now, like I've mentioned in a bunch of different videos, this is going to be simply a matter of copying and pasting the script. If you're a member, if you're a gold member, you can grab it out of my database. It'll be in the code vault. I'll put links down below. Everybody else, you can get to typing or you can use some screen OCR and copy it. But there it is. I just copy-pasted it in.

Alright, we've got some variables declared up top here: a shell object, which is an object we can do stuff with, right? The return code that we get back from PowerShell so we know if we succeeded or failed. The command string is a big long string that we're going to put together to send the command to PowerShell. Think of it like your batch file command. Here's the command string. We'll talk about this more in just a second. It's going to send all the data that we need to PowerShell, send the email, and then get a return.

Here's the shell object created there. Here's where it's run. Shell object.run. Your command string. Zero means hide the window. If you want to see the window, if you want to actually see the little PowerShell command prompt pop up, make that a one. There's other options too. I think like two is minimized, three is met, whatever. And then true is needed here. This is "wait till it's finished." If you set that to false, it's just going to issue the command and then continue running codes. You want to wait for that return code to come back.

Alright, and down here, it's either return code zero which means it's a success. Do the same thing we did in the last video. Otherwise, it's a fail. We don't get more information than that, though. Yeah, there are some ways to get more verbose error messages, but basically, it's a success or a fail. If you can't figure out why it's failing, just run this from the command line in PowerShell and it'll show you what the problem is.

Okay, let's take a look at the command. There's some stuff in here you don't have to worry about like execution policy and all that, but here it is right here. Command. You'll see try and catch in here. That is how it handles the errors. It's going to try running this command, and if it's successful, it exits with a zero. Otherwise, it exits with a one, which means it's a failure. Alright, and this little guy here reads the status of the send mail message command. There's a lot of cool stuff to learn with PowerShell. I'm still just a moderate user of it myself.

But this is where you put all the stuff in here. Here's all the parameters, right? To email. There's my from address. The subject is getting the subject in the form. The body is right there. We have one issue we have to deal with in just a few minutes. We'll talk about how to deal with that body issue in just a minute. The SMTP server that comes from our constants. Remember, we set up that send mail config module, right? And then here's where all your settings are: your SMTP server, the port we're going to talk about in just a second. There's your username, your password. Don't forget, if you're using Gmail, you have to set up the Gmail app password that I talked about in the last video. Alright, and some things like timeout we're not going to use here. But that's where you specify your SMTP server. SMTP: Simple Mail Transfer Protocol.

Alright, now port 587. I've hard-coded that in here instead of relying on it in the settings because with using PowerShell, it usually wants to use 587. Some servers might use 465 or even 25. That's completely up to you. I found that most of the time, whenever I use PowerShell, I've got to use port 587. You may need to change that. I'm using SSL all the time in here as well. Again, I just put that in here instead of using the parameters. If you want to change it to use the constants again, they're your Lego pieces. That's fine. Do whatever you like. Here's where you set up your credentials. Alright, there's your username and your password, and you can see this convert to secure string. It does some security stuff there for you.

Okay, and then when all that's said and done, it'll either exit with a zero or exit with a one and come down here and do your thing. Alright, okay let me try it first without my password in there, and that should generate a fail. Alright, so let me debug compile. Okay, we're good. Let's come out here and customer form. Send email, blah blah blah. Send with PowerShell. Failure. Alright, because it's the wrong password. Let me actually put my password in here now. No, you can't have it and now let's try that again. Send with PowerShell and away it goes. Okay, so it's working.

Now, I mentioned a minute ago there's one issue we have to deal with when it comes to that body text. Alright, if I put in here, "hi there," and then I say something like, "she said," quote, "hello," and then I try to send this. Oh, it actually went out? Let's see if it sent it correctly. Okay, it worked but only accidentally because I have the email set to HTML mail. Okay, so it converted that to a quote, quote like that. Alright, but let me convert this back to plain text. We did this; I converted this to rich text so I could use HTML in the extended cut of the last video series. Now, let's go back to plain text and do it again. Okay, hi there, she said, "no way," whatever. Okay, send with PowerShell and oh it worked again. That's interesting. I'm surprised it actually took it. Usually with something like that with a string, you get an error unless you escape those double quote characters. Okay, I wasn't sure exactly why it still worked though. Usually, I would expect an error and something like this. I didn't test it ahead of time, folks. I just knew we had to do this.

Normally, you have to escape those quotes. Alright, and it's different for every language. With PowerShell, you have to use a backtick. Where is it? Yeah, we have to use a backtick and then a double double quote. Okay, the backtick tells PowerShell that the next character is literal. Okay, the reason why--because I asked GPT--I'm like, why does this work? It says the reason it works in the sense that the command executes but the double quotes are removed is that PowerShell is interpreting the double quotes as part of the command syntax. So, the command still forms a valid PowerShell command. It runs without errors, but the quotes don't make it through as part of the email body. So, it's a weird quirk that it still works, it just removes them.

Okay, so yeah, that confused me for a moment there too. I was definitely expecting an error with that, like a malformed string or something, but it's easy to fix. We'll just come into here. Now, I don't want to change the actual body in the form, so we're going to have new body as a string, and then before we create that we're going to say new body equals replace(body). Now, wherever you find a quote, we're going to replace it with a backtick that's next to the one and under the tilde. Alright, that little guy--not this guy--that guy. Okay, and then put the quote in there, and we have to escape double quotes. Usually, like with JSON, you have to use a slash and then all kinds of different rules for different languages, but that's what you gotta do for PowerShell. Alright, so debug compile and now let's try it again one more time. Rick said, "wow this works," and then send with PowerShell and there it goes. Okay, and it didn't work and I made a classic error in my code. Let's see if you guys can figure it out. I'm leaving this in the video because I do this all the time. Alright, do you see what I did? Do you see it, anybody? Classic classic mistake.

Well, I set a new variable called new body and I'm still using the old body down here, aren't I? So, it's just basically ignoring that command. So, change it to new body, and if you caught that, give yourself three points. Save it, debug compile, let's come back out here. Let's try that again. Close it, open it. Hi there, hello. Ready, go. Okay, now this didn't work either, and I should have tested this ahead of time. I was pretty confident that that backtick wasn't the right character to use for the escaping of the double quotes. I thought it was a slash or a backslash, but I'm not a PowerShell expert, so I just went with what ChatGPT gave me. Let this be a lesson that you don't always listen to what ChatGPT gives you. It was wrong. It's not that character. It is indeed the backslash. So, we're going to convert double double quotes into backslash double double quotes. That'll put a backslash quote in the command string, and that's the right one to use. And yes, I tested this before proceeding.

Alright, one more try. Here we go. Test test, and then in quotes, "hi." Alright, send with PowerShell and let's go check our inbox, and it worked. Okay. Alright, that was a case where I knew ahead of time we were going to have to escape it, but I didn't test it before I just started recording a video. I know, I know, I know. But now it works, and now our code's good and there you go and that's what you need right there.

Alright, we're going to do an extended cut. I'm going to go through the same stuff I covered in the other extended cut for the other video, which is the cc, bcc, reply-to, HTML, and attachments. Don't forget to check out Alex's PowerShell stuff. He's got some cool videos on here. I'll put a link down below. Another scripting language you can do this with is Python. Now I've been learning Python myself for the last couple of months. It's really cool. It's very powerful. It's one of those languages where you can issue one command and it does a whole bunch of stuff. If anyone wants to see how to do this in Python, let me know. I've been thinking about putting together some Python lessons but I'm not sure if there's demand for it. So, we'll see. Post a comment down below if you're interested.

Also, don't forget to check out my Access Email Seminar if you want to learn more about sending emails and bulk emails and building a mail server and all kinds of stuff that you can do in Access with email. Alright, so that's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.

A special thank you and shout out to our diamond sponsor Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check them out at AccessExperts.com.

For a complete video tutorial on this topic, please visit https://599cd.com/SendEmailwithPowerShell

Send Email Using CDO in Microsoft Access Instead of DoCmd.SendObject and Outlook

 

Today, we're going to see how to send email from Microsoft Access without using Outlook. A lot of people have been emailing me lately, telling me that they upgraded to the new Outlook and they can no longer send email. In fact, I did another little video on this last week talking about an article from OfficeWatch that the new Outlook is not ready and has problems with sending email automated from Access.

But let's take a look at our question. It comes from Mallory in Adina, Minnesota, one of my Platinum members. Adina says, "Recently I've encountered issues with do-command send the object when trying to send emails from Access, especially with the newer versions of Outlook. It seems like this method is no longer available. Could you recommend any alternative methods for sending emails directly from Access?"

Yes, Adina, we're going to use something called CDO, which stands for Collaboration Data Objects. That's just a fancy library that comes with Windows that allows you to talk directly to a mail server without having to go through your email program like Outlook. This will work with most SMTP servers, including Gmail, although there's something that you have to do to get it to work with Gmail. We'll talk about that in a minute. But if you have your own corporate mail server or you pay for SMTP Access, you can send email using this code that we're going to talk about today.

Now, this is going to be a developer-level video. Unfortunately, you need some VBA in order to do this. You can't do it without programming. But don't worry, VBA isn't scary. Go watch this video. It's about 20 minutes long. It'll teach you everything you need to know to get started programming. Make sure you understand basic concepts like an if-then statement. If not, I got videos on that stuff too. And make sure you understand how to get a value from an open form. We're going to use that to grab the email address off the customer form and put it into our email-sending form. These are all free videos. They're on my website. They're on my YouTube channel. Go watch those and come on back. 

There's going to be a couple more as we're going through the video. I'll point them out where necessary. First off, I wanted to quickly address this issue that's coming up with the new version of Outlook that's out there. There's a new kind of web-based version of Outlook that is replacing the classic Outlook, which is a PC application that has come with Microsoft's office since I can remember since the 90s. But the new Outlook doesn't work like classic Outlook and the new Outlook doesn't allow a lot of the VBA programming stuff that classic Outlook always has.

So how do you tell which version you've got? This is on Microsoft's site. I'll give you a link down below so you can go read this on your own. But in a nutshell, if you don't see the File option up on the ribbon on the menu here, then you've got new Outlook. And just like new Coke, it's not very good. It's a real lightweight, simple application for you to read your mail. It's got some limitations. For example, you have to be connected to the internet for it to work, kind of like Gmail in the web browser. There's a lot more information in here and you can go in and read about it and stuff like that. But that's how you can tell what version of Outlook you have. And if you've got the new Outlook and you still want to use Outlook with Access, uninstall this and install or reinstall classic Outlook. You should be able to still use your do-command send object to send your reports and stuff.

But if not, that's what today's lesson is about. We're going to send email without having to even have a mail program on your computer. We're just going to use Access to do it. One more thing that I got to mention before we get started. If you plan on doing this with Gmail, you have to set up a Gmail app password. You can't just use your username and password that you log into Google with. You have to actually set up a specific password to send email through Gmail. So go watch this video. I'll give you step-by-step instructions.

All right. So here I am in my TechHelp free template. This is a free database you can grab off my website if you want to. And I got a whole video on how this thing was built. But basically, we got a customer form here, and we have contacts. Contacts are every time you talk to this customer, you put a note in here. What we're going to do in this video is we're going to make a button right here. This is send email. It's going to open up another form that's going to look like sending email here. I'll show you in my other copy of my database. I ran through this earlier. Sometimes I run through this ahead of time just to make sure. Here's a send email button. I shorten the customer form. Send email. It'll copy over the email. Put your right in here for the subject. This is the subject. And the body. How are you today? Whatever. Okay. And then you'll hit send. And it will send it. I got mine set up through Gmail there. It's successful. And it will log that in the contacts. So if I go to the contacts form now, you can see right here. There's one I sent earlier. Here's one I sent. Let's see. I sent this one to Will Riker from Jean-Luc Picard. There's that one. You can read that on your own time if you want to. And then this is the one that I just sent there. So you got your emails being logged in your contact table. That's what we're going to do in this video series. So we'll come back to this form in just a minute.

Let's start off by making the email form. Now I'm going to take a copy of the main menu because I want this to just be a one-off form. It's not going to have any data stored in it. We're just going to have some unbound fields and an unbound form. And the main menu is pretty close to that already. So I'm going to take main menu, copy, paste, and we'll call this my email F, my email form. Now open this guy up, design view. And I'm going to delete everything except for a text box, a button, and the status box. Yeah, I'm going to delete the logo. All right, fine. Okay, let's change the color of this just a little bit so we can tell it apart from what we're doing. Okay. All right, so this will become our two-box, right, two. And I will left-align that. Let's go a little bit darker with the background. Just a hair. Let's go to that. Yeah, that looks better. Let me open this up permanently by double-clicking on it. Okay, so this will be my two-line, the email. Let's open this guy up. We're going to change the name to email, and we'll get rid of the control source. We'll get rid of the format. Now if I want to copy this over from the customer form, I'm going to go to the data tab. And for default value, I'm going to say equals forms customer F, email. Okay, why am I putting that in the default value instead of the control source? Well, if you put it in the control source, it's locked. You can't change it. It's always going to be equal to that. If you put in the default value, it's going to start at that when you open this form, but you can change it. So if you're going to send an email to someone, but they're like, hey, send this to my personal address instead, you're going to always over-type that. Okay, or you can make this a combo box and pick from a list if the person's got multiple email addresses. There's so much you can do with this, folks. I'm just covering the basics. 

All right, so there's that field. Now we need a subject line, copy-paste. Let's put this up here. This will be the subject. And change the name over here to subject. And get rid of that data source. Put in the default value. We don't need that anymore. All right, this is going to be our status box, which we're going to like to see the status because we're going to want to know if the email went out successfully or not. We'll take care of that, too. We'll do a little debugging and some error handling. I'm going to move this button down here. This is going to be the send button. And in fact, I'm going to put a little ampersand in front of the S. That way I can hit Alt-S, and that pushes the send button. And we can make that text a little bigger if you want to, like that. Okay, anyways, one more field up top, copy-paste. This will be the body of our email. All right, body. And we'll make this nice and big. And I'm going to change one property here. Go to the other table. First, let's give it a name. Let's call it body. Did I name this one subject? Yes, I did. Okay, Alzheimer's kicking in. On the other tab, I'm going to change the enter key behavior. So instead of default, it's new line in a field. Treats it more like a long text field. So when I hit enter in here, I can make new paragraphs instead of it jumping to the next field. All right, save that. 

Let's go into the VBA behind this, which I put a button up here on my quick launch toolbar to do that. But you can just right-click on this button and go to build event. And that'll bring up the code editor. There it is. And I'm going to delete everything in here except for that status function. I want to use that for when we display the status of our email. Okay, okay, so we're done with that. Give it a quick debug compile. Make sure everything's good. Let's make a button to open this form from the other form. So let's close this, go to customer form. And like I did in my sample database, I'm just going to delete some stuff out of here just for the purpose of the video. We don't need all this stuff in here. I'm just going to slide these buttons over to the left, just like that. Make things a little bit easier to see on the screen. Okay, copy one of these buttons here, copy, paste. And this will be my send email button. All right, we're going to send this person an email. All right, let's give the button a good name instead of command 30, send email, BTN, send email button.

And then we're going to right-click, build event, and this is going to be do command, open form, email F. That's it. We don't have to send it any parameters because the email form gets the email address from the open customer form. Okay, now if you want to prevent the user from doing this where they, let me show you, where you open this and then you hit send email, right, let me move this over here. All right, and then they change customers back here. If you want to prevent that, just make this a modal form. Right, and then they can't click on anything behind it until they close this guy. Okay, let's change that caption. And one more thing I want to change when this opens. I want to make sure that the focus is in the subject field and not sitting down here on the last control that was added. That's an email button. Actually, that was there originally. So what I'm going to do is I'm going to go into design view, let's change that caption. All right, send email or whatever you want to put in there. I'm going to adjust the tab order, hit auto order, that's fine. But I'm going to put the email field last. So I'm going to click on this and drag it to the bottom. What's going to happen is it's going to start then in the subject field, which is first in the tab order. Right, that's where you want to start. It's just going to assume you're sending to this. And then tab goes to here, tab will then go to here. We're going to remove the status box from the tab order because we want to see it, but I don't want to stop there when I'm tabbing and then we'll go here. Not to you can tab back up to the email. So let's hit OK. Let's click on the status box and take that out of the tab order. So we'll go to tab stop, no. So the end result, what you get is click and now you're in subject. I can just type in my subject, tab, type in my body. Tab again and I'm on send email. And if I tab another time, I'm back up to two. Okay, see how that works? That's pretty cool.

All right, so we got our infrastructure already built. We got our button, we got our email form, the setup, you know, the visible stuff we need on the screen is all set. Now we're ready to put the code behind that button. And we're going to do that in tomorrow's video in part two. So tune in tomorrow, same bad time, same bad channel. Or if you're a member, you can watch it right now because I'm going to keep recording right now. And that's one of the benefits that members get is that they don't have to wait. They can watch my videos as soon as I post them. But that's going to do it for part one. We'll see you tomorrow for part two. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.

For a complete video tutorial on this topic, please visit https://599cd.com/SendEmailwithCDO

How to Use the Enter Key Instead of the Tab Key to Move to the Next Record in Microsoft Access

 

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we're going to talk about how to use the Enter key instead of the Tab key to move to the next record in Microsoft Access.

Today, I'm going to show you how to do it for beginners, and then in tomorrow's video, actually Monday's video since today is Friday, we're going to show you a developer method that's got some additional flexibility. Today's question comes from Ludwig in Dessel, Belgium, one of my Platinum members. Ludwig says, "How can I navigate through the records in a form using the Enter key instead of the Tab key? Is there a video on this possibly with some VBA code or an event procedure or something similar? My boss is acting strange again about how it goes faster with the Enter key instead of the Tab key." 

Yeah, I get it. If you hit Enter, it just behaves like the Tab key does normally. Just hit Tab or Enter does the same thing. It'd be nice if the Enter key moved you down, kind of like in Excel. And you could, if you wanted, also program it to work the same way with a single form, so you hit Enter and it moves to the next record. But let's see how we can do this.

First, a couple of prerequisites. First, of course, if you're a newbie to Access and you haven't watched my Access Beginner 1 class, go watch it. It's four hours long. It's absolutely free. It covers a lot of fundamentals that you really should know. For example, you should know what command buttons are and how to use the command button wizard to create them. In fact, this video specifically teaches you how to make command buttons to navigate back and forth through the records on a form, which is kind of what we're doing today.

This video shows you how to set a default button. A default button is the button that gets clicked when you press the Enter key on your keyboard. Now, you put these two videos together and you've got the answer for today's video.

Let's start with the continuous form. So I'm going to come into here, Design View, and I'm going to go up to Form Design, grab a command button, and drop it right here so the wizard starts up. I'm going to pick Record Navigation, go to Next Record, hit Next, put some text on there. You can put Next Record on there. It's fine. You can even put "Enter" to visually tell the user, hey, if you press the Enter key, it's going to go to the next record. It's going to push this button.

Next, give it a good name. Next Record Button or something like that, and then hit Finish. So now you've got a button that will go to the next record. Save it, close it, open it back up again. And now if I click the button, you can see it moves to the next record. Even if I'm over here, hit the button, it still moves to the next record. So you can still use Tab.

Now, how do I assign this button as the button that's pressed when I push the Enter key? Well, that's what that default property is for. Back here in Design Mode, open up the properties for this button by double-clicking on it. Come over to the other tab here and look for Default. We're going to change the default to Yes. Default means when I press Enter on the keyboard anywhere on this form, unless you're in a long text field, then it pushes that button. 

So let's save it, close it, open it, and now Enter, Enter, Enter, Enter, Enter. See? Tab, Tab, Tab, Tab. Enter, Enter, Enter, Enter. Nice. Easy enough. Tell the boss it's not so weird now. This will work on a single form or a continuous form. So we can copy that button. You can actually take this button and copy it. Then go over to a different form, like the regular customer form. Right-click Design View, and we can paste it right on here. 

Make this a little bit bigger, and then Paste, and then bring it down here. Now, even though the button macro carries with it, because inside this button, there's something called Embedded Macro, and that will copy over with it. But you still got to open up the properties and assign this guy to be the default button on this form. Save that, close it, close it, close it. Let's open up the customer form, and now we can use Tab to move through the fields, but Enter will still move us now to the next record. See, that's pushing that button, that next record button.

Now, as far as going to the previous record, if your boss wants to go backwards, there's a couple of things you could do at the beginner level. You could make another button to move to the previous record. I mean, if he's too lazy to use these buttons down here, I guess. Then you could assign that to a keystroke, like Alt P for previous, or you could even make it the Cancel button, and he can hit Escape to go backwards, but that's kind of weird too. I like the previous button myself, so let's do another command button. Drop it down here.

Record Navigation, go to Previous Record. Previous, I'll just put P-R-E-V in there, make it small, like that. Next, and the previous record button. What we're going to do to make this assigned to a keystroke, we're going to come right in here, and we're going to put an ampersand in front of that P. Here I'll zoom in so you can see it better. See that little ampersand right in front of the P? Press Enter, and now when you do that, you get a little underscore under the P. Now this just becomes a training issue now. Just tell the boss, well, if you want to go back a record, it's Alt P. That's just the easiest way to do it.

Save it, close it, open it, and now you can go Enter, Enter, Enter, Enter, or Alt P, Alt P, Alt P, Alt P. It's up to you. You can make an Escape button too. To make an Escape button, you just go to the Properties, and under Other, you set this Cancel button to True. Then when the user presses Escape, it pushes that button. I wouldn't recommend that for just moving backwards through records, though.

Now, in Part 2, when we get to the VBA version, I'm going to teach you how to do this with events, and you can actually assign the previous button something like Shift Enter. So it would be Enter to go forward, and then Shift Enter, or Control Enter, or whatever you want, to go back a record. We'll talk about that in Part 2.

Now, there is one caveat, and I kind of mentioned it a minute ago when you're in here. If you happen to be inside of a long text field, the Enter key gives you a new line in the field, see, just like that. So your default button won't get pressed unless you tab out of there and then press Enter. Oh, even that time, I happened to be on a button because this was next in the tab order. If you're sitting on another button, like the Orders button, then you press Enter. That will take precedence, too.

What you can do in this particular case is you could say, okay, for my long text fields, instead of the Enter key behavior being new line in a field, you can change this to default. What that will do is it will make it so that if you're in here and you press Enter, it's going to fire that button's event because it'll no longer move to a new line. If you do want to go to a new line now, you have to hit Control Enter to go to a new line. Or you can open up the Zoom box, Shift F2, and now you can type in here, and this is just like a regular little word processor.

There's a lot of things you can do. There's ifs and thens and that's and buts and everything has an exception. All kinds of things you can do. But if your weird boss's primary concern is just being able to move to the next record with Enter, well, there you go. There's an easy, nice beginner fix for you with absolutely no programming. 

If you do want to see how to do it with some programming, I'm going to be talking about that in Part 2. Let's see. This video goes public on Friday, August 9, 2024. On Monday, the 12th, we will talk about the VBA version, which is a little more work but a little more powerful. But I do like to give the beginners some videos too from time to time.

So that is going to be your tech help video for today. I hope you learned something. Live long and prosper, my friends. I'll see you Monday for Part 2.

For a complete video tutorial on this topic, please visit https://599cd.com/EnterInsteadOfTab

Preventing Accidental Clicks and Double-Clicks in Your Microsoft Access Forms

 Preventing Accidental Clicks and Double-Clicks in Your Microsoft Access Forms

You know it's happened to you at least once in your life where you accidentally double-clicked on something instead of just clicking on it. And you know, you double-click on a button that you should have just clicked on once and now it happens twice like printing a long report. 

So in today's video, we're going to talk about preventing accidental clicks and double-clicks in your Microsoft Access Forms. Today's question comes from Felix in Peoria, Arizona, one of my Platinum members. Felix asks, how can I prevent a user from accidentally double-clicking a button that should only be clicked once, such as a print report button? I want to ensure that the action stops after the first click. I've got a lot of users, myself included, who accidentally double-click on buttons and send down two copies of a 13-page report. Happens a lot.

Yeah, this has happened to me a couple of times, too. I used to have this end-of-month report that I'd run in my own database, and it involved action queries, like an Append query and some other stuff, importing. And once in a while, I would accidentally double-click on it, and it's like a two-minute process. And so I'd be like, ugh. All right, so how do we prevent that from happening?

First up, some prerequisites. This is a developer-level class, so you're going to need to know a little bit of VBA. So if you've never done any VBA programming before and you want to learn how, go watch this video first. It's about 20 minutes long and it will teach you everything you need to know to get started programming in VBA and Access.

Okay, so here I am in my TechHelp free template. This is a free database you can download a copy off my website if you'd like to. You'll find links down below. And in here I've got a little button that just says, Hello World, and it puts Hello World in the little status box. That's all that guy does.

But let's reprogram this button and we're going to pretend that this button launches some long event, like printing a big document or running a big append query or an import or something like that. Now, I'm just going to simulate that event by using a sleep timer. I'm going to make the system just sleep for one second and that will be enough for us to see that multiple clicks are happening. 

Now if you downloaded a recent copy of this in the global module here, you will find a copy of my sleep function. It's right here, sleep sec, and we declare it right there. That's where we get the sleep function from. I got a whole separate video that explains this thing and how it works. You'll find a link to this video down below.

All right, so let's reprogram this button so it does something and just so we can see how many times the button click actually registered, let's turn this box into a counter. So we can see every time that the event runs, we'll just increment the counter one. So let's just rename this here counter. The box itself, let's go to all. I'm going to rename the box to counter. Let's get rid of the control source and get rid of the format and I'll set the default value to 1. So when the form opens up, that will have the value 1 in it. 

Now the button itself, I'm just going to put on here print or whatever. We can leave it Hello World button, that's fine, that doesn't matter. Save that. Now let's go into the button, build event and instead of status Hello World, I'm going to say counter equals counter plus 1. We're just going to increment that counter variable. That's all that's going to do. Right? So come back out here, close it, open it back up again, click the button, it goes to two. Click it again, it goes to three. 

Now, you've all seen this before. If you haven't done it yourself, I'm sure you know someone who has. I used to teach Windows basis classes in the classroom and all the time I would tell people, no, just click on the icon and they would double click and you can't fault them. If you're new to computers and I used to teach a lot of newbies, it takes a while to get the concept of a double click and even if you're an advanced computer user like myself, I accidentally double click on something. I just did it this morning. 

So what we want to do is we want to intercept that second click and stop it from happening. The only way we can really do that is to make the button know if it's currently being used. We're going to set a variable and say, hey, hold on. I'm in use right now. I'm unavailable. So if a click happens, just ignore it. 

Now the first thing we're going to do is we're going to simulate a long process happening. So what I'm going to do is use a sleep in here. I'm going to say sleep 1,000. Those are milliseconds. So it's going to sleep one second between clicks. And that will give us the opportunity to see what's going on there. Save that, come back out here. 

Now if I click, you'll see there's a delay and then it goes to two. Click again, a delay, and it goes to three. Even if I double click, watch this, click, click, four, five, it's still getting that next click. One, two, three, I just clicked three times, six, seven, eight. So we have to intercept that somehow. 

All right, and the way to do that is back in the code, we're going to set a variable that is going to know that this particular button has just been clicked. It's processing something that's already started. Now, there's a couple different ways we could do this. We could use a form field. We could use a global variable. We could use any number of different ways to do this. I'm going to use a temp var because I love temp vars. And if you don't know what tempVars are, talk to Adam. 

Now, tempVars are basically just system memory variables that have a lot of benefits to them. I like to use them a lot. Go watch this video if you want to learn more about them. So right here, I'm going to say tempVars isProcessing equals true, okay? And then when the event is done running, I'm going to say it equals false, okay? 

Are you with me so far? Now, when we first come into this, I'm going to say, if tempVarsIsProcessing equals true, then exit sub. In other words, when you first come in here, if this is true, just leave. Don't do anything, okay?

Now we should initialize this temp vars somewhere. Normally temp vars for Boolean types will be initialized to false. Long integers default to zero. Strings default to an empty string, but I don't like relying on that, so I'm going to initialize this to false when the form loads. All right, so I'm going to find the form load event, and when the form loads, we're going to set it to false. 

In other words, it's not processing when the form first loads and we've made sure that the value is set to false. Now why can't we just use a regular variable in here? Well, it has to be at least a form level variable. We could declare a variable out here. That would work or you could use something called a static variable. There's all kinds of different options. I like tempvars. And yes, I've got videos for global variables and static variables and all that different stuff. You'll find them on my website if you want to learn more. 

All right, so let's save our work. Let's give it a debug compile. Always good to do once in a while. Debug compile, make sure there's no syntax errors. Come back out here, hit the button again. Okay, I got a nine. Let's double click it. Click click. Ten, eleven. Okay, alright, hang on. Click click. Twelve, okay, so what's happening? It's still registering twice. What happened here? It's not checking to see if it's equal to true and exiting sub. 

Well, the problem is that while the code is running, it can't stop to check anything else until the code is done executing. So what happens is it runs this, it runs this, it goes through this, it does this, sets it to false. And as soon as it exits back out again, your second click processes. And it starts the process all over again. So we need some way in here to interrupt that. We need some way to say, okay, when you're done sleeping, alright, but before you set this to false, I need you to go out and check to see if any other events want to run. Go out and check to see if, you know, some other form wants to do something or if the user happened to click.

These are all things that we can check for with doEvents. doEvents says, I'm going to hold on for a second, you do whatever else you got to do and then I'll finish doing what I was doing. What will happen is doEvents frees up the system for your second click to start processing. Once that second click processes, it's going to come in here and say, are you busy? Oh, yeah, you're busy because we haven't set up the false yet. See? So it comes back in here, says you're busy, exits out and doesn't process that click. Makes sense? You following me? doEvents is like going, all right, what do you want to do? Okay, and then it goes and does it. 

All right, so save that. Now, if I come out here and I go click, click, I only get one. Now, let's set this back to one so we can see it's easier. All right, so that's one. I'm going to go click, click. I just double-clicked. It goes to two and it stops. Why? Because as soon as it got to this spot here, it freed up the cycle so that the processor could go and run any other events that wanted to run. Alright, it grabbed that second click out of the buffer, re-ran this module, a second copy of the module basically ran, right, that's what doEvents kind of does, and the first thing it processed was this and it says, are you processing? Yeah, the other module, the other copy of the module was processing. So it exited out of the second click, then it comes back to the first click, now it can free up and say, I'm done processing. See how that works? It's tricky. It's all about learning how these processes run.

Another thing you could also do if you want the user to visually see that they can't click a second time is what you could do is like right here after the counter, you could say, make the button disabled. So say, hello world button dot enabled equals false. That will make it grayed out. Okay, and then I would throw another doEvents here so it updates the screen. Okay, so your counter will increment, the button will get disabled, the user will see that, the process will run, doEvents will run again and free everything up and clear that variable and then it will exit out. 

And if you want to re-enable the button after that, you can or you can just leave it disabled. If you want to make it so that you got like a big report and you don't want the user printing it a second time, you could force them to have to exit the form and then come back in so the button's enabled again. That's up to you or make a second button to unlock it, whatever. I think this works just fine.

So save this and back out here. I'm going to close and reopen it just to make sure everything is cleared. All right, ready? I'm going to double-click now. Double-click. See? It disabled the button, which you could visibly see, and it didn't run my second click. Let's do it again. I'm going to click three times. Click, click, click. See? It disabled the button, and it only processed one of those.

All right, I turned mouse click effects on in my software. Let's see. Click. All right, I'm going to double-click now. Click and a double-click now they only click once; I'm going to click three times and I only did one, see? I'm going to turn that off. I personally find those mouse click effects annoying. I don't know, what do you think about those? When I watch other tutorials and people have these, you know, mouse halos on and the click events and I find that distracting myself. I don't know. Do you like that? What do you think? Post in the comments down below and let me know what you think. All right. I turned it all back off. That's how I like to do it. Alright.

So there you go. That's how you disable the button, clear that event, and you only get one click out of that button no matter how many times the person clicks on it. Unless they happen to click before, you know, if they click while the process is just finishing, they might get that second click. You could add an additional sleep timer in here. So even after, let's say, this is your print event. You could add another second or two second pause down here before you set that temp var to false. And that will give them a buffer, maybe put like a five second pause in there, I don't care. That way, you know, in case they do click longer or multiple times, it will still eat up that time. Up to you. 

All right, I give you the Legos. You can play with them, build however you want. Do you like this stuff? Do you like learning with me? I got lots and lots of lessons on my website, folks. Tons and tons of them. Hundreds of hours of videos. And they're all arranged in a nice, orderly fashion. You know, level one, level two, level three, in the order that you should learn them. Okay, so check them out. You'll find a link down below. And yeah, it's cool stuff. And we have a lot of fun so that's gonna do it folks that is your TechHelp video for today I hope you learned something live long and prosper my friends I'll see you next time.

A special thank you and shout-out to our diamond sponsor Juan Soto with access experts software solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.

For a complete video tutorial on this topic, please visit https://599cd.com/AccidentalClicks

Big Button Form in Microsoft Access, Part 2

 Dynamic Forms with Big Buttons for Data Entry in Microsoft Access, Part 2

Today is part two of my big button form series where we're going to make a dynamic form that you can click to pick options just like these. If you haven't watched part one yet, what are you doing here? Go watch part one and then come on back. All right, so yesterday we got our tables all set. We got make, model, and year tables all set up.

Now once the user picks their selection, right, they pick a make, model, and a year, we've got to save that information somewhere. Now ideally, you would want to have maybe a subform over here or another form that pops up like the contacts so that they can have multiple vehicles. Ideally, but we're not going to waste our time setting up relationships and stuff for that. So for the purposes of class, we're just going to put one field in the customer table where we put the customer's vehicle. One vehicle, that's all they're allowed to have. I don't know, maybe they're registering for a demolition derby and they can only register one vehicle at a time. I don't care. But just to keep things simple for class, we're going to make one field in the customer table.

Now what should we store in the table to know they're unique, make, model, and year? Do we have to store all three of these fields in there? No, absolutely not. All we need is really the last one, the year table. Because if we know the year, we can very easily look up the model, and if we know the model, we can look up the make. So if you've saved your data this way, you really only need to store the vehicle year ID if that's your last field. And of course, if you have other options like color and stuff that are separate that aren't related, make those extra fields too. But again, for the purposes of class, all I have to do is go into the customer table and add is V year ID and that's it.

Now I can tell based on that what their make and model are. All right, so go ahead and save that. Now what we're going to do is on the customer form I'd like to put a combo box down here so we can see all three of those things together. Just like if you watched all my other videos, just like on the order form we put together last and first name into this LFQ. That just takes first name and last name and puts them together so you can see both of those fields in a combo box like that. Because normally you only see one field at a time.

So let's make a query that puts these things together. So I'm going to bring in the tables make, model, and year. You shouldn't have to worry about joins here. You should use inner joins because every record in each of these tables should have a matching record in the other table. You would never have a make and a model without any years. Otherwise, you can't. No, it's not going to work. So just bring in the stuff that we need for this combo box. I need the make name, don't need the ID. I need the model name, don't need the ID. I need the year to display it and the value that we're going to be storing in the customer table is going to be the year ID. That's the unique value that will give us all of this information. And if I open this up now, you'll see this is what it looks like.

Now I just got to put all three of these together with a little concatenation so I can see them all from my combo box. We'll go over here. We'll call this display name. Here, I'll zoom in so you can see it, shift F2. Display name and that will be colon. It will be the make name and a space and the model name and a space and the year or however you want to display it. And if you don't know about string concatenation, you're in the wrong class. This is a developer class. I cover string concatenation in my beginner classes. But just in case you missed it, yeah, of course, I got a video on it. Go watch this. You would not believe the number of times that someone emails me and says, hey, I don't need these beginner classes. I want to skip them. I'm going to go right to the VBA stuff. That's what I want to learn. And then I'm like, OK, I don't recommend it, but it's your money, do what you want. And then, you know, two days later, they're posting in the forums, what's this thing about concatenation? I'm like, well, I covered that in the classes that you skipped. So there's a reason why my course covers things in a particular order.

So anyways, a little concatenation, we get this, this looks great, that's what's going to be in our combo box, rearrange the order if you want to, I don't care. I know Adam does it, you're a make model. And let's save this. I'm going to save this one as Vehicle Q, Vehicle Q. Yeah, I'm spelling it out this time because VQ is just dumb. So Vehicle Q is fine. Now we can use this to make the combo box on our customer form to store their vehicle choice. Is it vehicle or vehicle? How do you guys pronounce it? Depends, if you're from the south, it's definitely a vehicle. Drop that combo box right there. Look up the values from a table or query. Go to queries, pick the Vehicle Q. Next, what do you want? Well, we need the year ID first. And then we need the display name. We don't need all this other stuff. All right, next, we're going to sort it by the display name. Next, we're going to arrange the columns like that. We're going to resize that one.

Normally, this is the key column. This guy, come here. I can't get my mouse on it. This is the key column, but since this combo box is made from a query, you don't get that little checkbox here that says hide the key column. Access will only give you that if it's a table. So we can manually make that zero and that will hide it. Make this as wide as you think it needs to be. You can take a look at the data that's in here. Some of those are pretty wide. Yeah, like the GMC Sierra. Let's bring it out to here. All right, next. What field is the bound field? In other words, what's the data we care about? The year ID, obviously. We're going to store that value in the year ID field that we just added to the customer table. All right, we're picking the value and we're storing it in the customer table. Next, what label do you want? Vehicle. And we're done. There it is right there. We'll slide it up into place like so. Make this a bit wider. That width that we set earlier is for how wide the box is when you open it when you drop it down. Little format painter and picks that. All right, save it, close it, open it, and now we can pick a vehicle. All right, there's our list. And this list is what we're going to make our button and the big form to avoid because if you got greasy technicians with, you know, two thumbs, it's very difficult to click on that little guy, open this up and scroll down to pick all these things. Even if you make these nice and big, it's still easier to do what I'm going to show you how to do with these big button forms.

All right. So now we've got a way to view the data in here. Now we can start actually building the form, the single dynamic form that we're going to use to select all of our options. Now, I'm going to make this as a really basic form. It's not going to have any records in it. It's going to be an unbound form just like our main menu. It's not going to have any record selectors or navigation buttons. So I'm going to just copy the main menu and use that as the template for the big button form. Let's find the main menu, copy, paste, control C, control V. We'll call this big button F or whatever you want to call it I don't care but if you want your code to match mine name your stuff the same you can rename it later if you want to right-click design view I'm going to delete everything in here except for one button and that label caption I want to keep these things while we're in here let's change the caption because the caption right now says tech help free template we'll just call this the Big Bottom. Big Bottom? Big Bottom Barrow. I'm thinking elementary school math. They teach a big bottom barrow. Big Button Form. We're going to replace that in code. So when it opens up, it's going to say select the make or whatever. All right, this guy, let's change the name of it to the title label. And again, we're going to replace this in code or the caption in code.

So I'll just put title in here. That's going to be changed. Remember, change captions of labels here, not over here so it doesn't resize this guy. Now for the button, let's change the name to button1. And you can also make that the caption. And again, the caption is going to be changed with code. Now here's the thing with the buttons. We're going to have to copy and paste and make as many buttons as we think we're going to need in the worst case scenario. So I'm going to do it with I think I'm going to fit 18 buttons on the screen. If you need more than that, you'll have to make more. The thing is that Access cannot create or destroy objects on a form like buttons or labels or text boxes. It can't create them or destroy them unless you switch to design view, which you can do if you want to do stuff for yourself. And I built some databases like that just for me, where I say make me 10 buttons and the VBA code creates them. But your end users aren't going to be able to do that. In fact, your end users shouldn't have design access to the forms at all because you should be giving them ACCDE files, in which case they can't get into design view. So you have to basically create as many buttons as you're going to think you're going to need up front. So if you got 30 items, you're going to need 30 buttons. And then what we can do is we can show and hide them by making the visible property true or false in our codes. If there's six options, we'll only see six buttons. And yes, it is possible to resize the buttons, to resize the form, to do all that stuff. We're not going to cover that right now. If enough of you want to see it, I'll show you how to do it in a future lesson. Post a comment down below if you do. So I'm gonna resize this guy so I can get roughly three of them across. All right, so I'm gonna go copy.

Actually, what I'm gonna do first is I'm gonna change the caption to just button because it's easier now if I copy all these just come on here and go two, three, four, five, and so on. So copy, paste, paste. I'm gonna line these up like this. So they're how I want to see them. That looks about right, close enough, maybe a little bit to the left like that. Okay looks good. Then I'm going to copy this and go paste, paste, paste, paste, paste. I got 18 of them, right, 3 by 6. Now all you got to do is come over here and change this to button 1. All right, now I'm going to copy button to my clipboard and I'm going to click on this guy and this is not going to be button 2 and 2. See, right here, button 3, 3. Next one, you can click over here on the name and it will select that for you. Button 4, 4. And yeah, this is one of those things that's a little tedious to do the first time but you only have to do it once or if you got to add more buttons later, you got to go, okay, come in here and add three more buttons. No big deal. We're going to have code in our VBA to check. We're going to set the maximum number. So if you do have 20 options, it's going to yell at you and say you don't have enough buttons. We'll get to that. I rehearsed this earlier when I was building the prototype database. It took me under two minutes to do all 18 buttons. So I'm going to pause this now. I'm going to do one more for you. Click on this, click here, paste, button 5, 5. See, it's not, it's not hard. I can do it in under two minutes. I'm not going to make you watch me have to do it. So I'm going to pause now and finish them all myself. Go ahead and do it yourself too. Go on, get going, do it right now. All right, I'm on 11 and I want to say, do you have to name them like this? Yes, name them exactly like this, button 11, button 12, exactly like I'm doing it because we're going to make a loop in VBA and we can programmatically access the stuff in each of these buttons, but it's got to be exactly that, button and then the number. And there we go. I got all 18 of them done, all set. And yeah, that took me under two minutes to do, so you can do it too. All right. I'm going to leave a little space down here because later on we're going to put a cancel button down here in case the user wants to cancel.

Okay. But this is all set. We're going to save this, close it, open her up, take a peek. All right, looks pretty good. Not too bad. One more thing I am going to do is I'm going to come in here and turn auto center on. Find the auto center property and set that to yes because we're going to be opening up this as a dialog form so that it pauses execution and when that happens, it treats it like a pop-up form. So you want to put auto center on so it opens up in the middle of your access application. Alright it actually opens up in the center of this here. Alright so that's what I want. Otherwise if you got a multi monitor setup like I do it could open up anywhere on any monitor and that's annoying. So make sure you got auto center on.

Alright so now we can put a button on here that our big fat fingered technicians can click on to open up that form. Okay so design view and I'm just going to grab one of these buttons over here, copy and paste, slide it down here, make a big fat fingered button that says, whatever, what do you want? Select vehicle. I will right click properties and name this, I shall henceforth name this button, what am I calling it? Select vehicle button BTN. Save it. And we'll put some code in it to open up that form. Right click, Build Event. Yeah, I know it was off the screen. You guys don't need to see all that. My code window's always giant because when I work on stuff myself, I expand it a lot. And then when I have to teach a video, I got to close it down. All right, do command, open form. What are we opening up? Big button F. I almost typed in big buffoon. Big button F. And then we could go comma, comma, comma, comma, comma all the way to window mode and pick AC dialog like that. Or if you don't want all the commas there, you just go like this. You say window mode. It's got to be the exact name of the parameter, colon equals AC dialog. And then it gets rid of all those extra commas. I just learned that one myself not too long ago, a couple of years ago.

Debug compile. Oh, what do we got? Variable not defined status box. That's right, I deleted the status box. Let's get rid of all the code because we've got in our big button F. We've got all this code that we no longer need and there's no reason to have it. This is all the stuff that was in the main menu and we can just get rid of all that. We're going to put a bunch of stuff in here in a bit. All right, save that and that's why we do a debug compile once in a while. Debug compile, it cleans up all that garbage.And also make sure you got Option Explicit in all of your modules: your form modules, your report modules, and your module modules. All right, we should be good to go now. Once again: main menu, customer form, select vehicle, and there it is. 

And we'll be able to click buttons and do stuff. Can we click buttons and do stuff now? No, we haven't programmed this yet. All we can do now is close this guy. Well, we're going to start programming that guy in part 3 tomorrow. 

So you know the drill: tune in tomorrow, same bat time, same bat channel. And if you're a member, you can watch it right now because I'm going to continue recording right now. So it should be posted soon. 

That's one of the benefits of being a member. You don't have to wait for this stuff to get published. But that is going to be your TechHelp video for today. I hope you learned something in part 2. 

We're getting to the good stuff, folks. We had a lot of setup to do before we get to the meat and potatoes. Right? We got some condiments we had to set up first. I don't know. 

Anyways, live long and prosper, my friends. I'll see you tomorrow for part 3. 

A special thank you and shout out to our diamond sponsor Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.

For a complete video tutorial on this topic, please visit https://599cd.com/BigButtonForm2