Dynamic Forms with Big Buttons for Data Entry in Microsoft Access (Combo, List Box Replacement)

 Big Button Form in Microsoft Access

Got a good one for you starting today, folks. We're going to be building a big button form. What's that? Well, we're going to be creating a dynamic form with lots of big buttons on it so you can select options in your Microsoft Access database. 

Now I'm not just talking about a bunch of different forms with a bunch of different buttons on it. I'm talking about one form that you can control dynamically using VBA code. So for example, let's say you want to pick a vehicle. Make, model, and year. Normally you could do this with cascading combo boxes or something like that, but if you're working in a garage, as we'll discuss in a minute, this might be easier. 

You click a button, you pick the make. Ford, you pick the model, Mustang, and picture someone with a touchscreen right just tapping on it. You pick 2022, and you can have more options if you want, and then it puts the value right here in that combo box for you. Right, that replaces cascading combo boxes. So this is going to be our end result, this thing right here. 

But we got a lot of work to do to get to this point. So let's take a look at the question first. Today's question comes from Adam in Fort Dodge, Iowa. Adam is one of our platinum members, is one of the moderators on my website, and he's our resident badge geek. We call him something different than badge geek, but badge geek is more appropriate for a video. 

Anyhow, since he's so proud of his badges, I figured I'd put them all here in the video. On the website, we got different badges for all kinds of things like whether you're a Trekkie, what your rank is, what country you're from, all kinds of different stuff. 

Alright, so Adam's question is, have you covered creating button controls on the fly anywhere? Can Access even do that? I need to select the year, make, and model of a vehicle. I agree, cascading combo boxes or list boxes would be so much simpler, but for the people that would be using this with tablets and touchscreens, they are not as friendly as buttons I've found, especially covered in grease. LOL. 

Yes, I do understand exactly what you're talking about. I've had to build some interfaces before that were touchscreen friendly and you need lots of big buttons because picking from a combo box or a list box isn't quite as good. You can make the list boxes really big, but if people have to scroll up and down it gets cumbersome. 

Now, a couple of years ago I put together this Tech Help video and database called Vehicle Maintenance and we did something similar here where we pick a make and model and these cascaded. So for example, if you pick Audi, you'd see only the Audi models here. You wouldn't see Mustang, for example, under Audi. So go watch this video if you want to get a little heads up on what we're doing today.

And of course, cascading combo boxes are where you pick a value in the first box and that will limit the options in the second box. So if you pick Florida here, you'll see only cities from Florida, for example. I've got another video that explains these in a lot more detail. This is what we're going to be replicating with buttons.

You should also watch my custom message box video as well, because in this video, I teach you how to open a form (in this case, a custom message box form) and then have this form return a value to whoever called it, using the AC dialog command. So this guy opens up, execution stops, the user clicks a button, and then execution can continue after this guy returns its value. This is covered in this video, go watch this.

There are a lot of ways to set and return those values. You can use global variables, you can use form arguments with the open args, there's lots of different ways you can do it. I chose to use temp vars. I think it's the best method and of course Adam loves temp vars. In fact, I almost called this video Adam's big button form featuring temp vars but I figured this would be fine.

Now there's going to be a lot of VBA in this series so this is pretty much for more advanced developers. If you are not a VBA developer and you want to learn how to program in VBA, go start here. Go watch this video. It's about 20 minutes long. It'll teach you everything you need to know to get started and I've got lots more beginner VBA videos on my website as well. 

But today is going to be for the developer developers. So those of you who have been asking me for more advanced developer stuff, well strap in. Here we go. 

Alright, so let's get started. I'm going to start with my TechHelp free template. This is a free database. You can download it off my website if you want to. It's got a lot of things in it that I like that already have my options and stuff set. I've got some sample code that I use in here and I've covered this in a lot of my other videos. 

But let's start out by making our tables that we're going to need for today's video. We need a make table, a model table, and a year table. Yes, we need a year table. So bear with me as we go through the setup here. We're going to start off with VMakeID. That's our auto number. Auto number should be in every table pretty much. VMakeName, and that'll be short text. Why V? Because I don't feel like spelling out vehicle every time. That's one of those words I always type wrong. I know how to spell it, but I just, my fingers don't always type it in right. Okay?

For VehicleMake, we could probably index this guy right, no duplicates. You wouldn't want two Fords in this table, right? We're gonna save this guy as VMakeT, my vehicle make table, right? Primary key is the ID. Let's put some sample data in it. We got, what do we got here? Ford, we got Jeep, and I personally, I'm an Audi driver, so I'm going to put Audi in there. Alright. I like the great German engineering. 

Alright, now I'm going to leave this table open and just slide it over here because I'm going to want to be able to see what these IDs are to put them in my model table. Alright, now normally, you'd make data entry forms for all this. You wouldn't have to know what the IDs are. You guys are developers, you know all about that. I'm not going to waste time doing that in class, but you can do that if you want to. You know, make a form with a couple of sub-forms on it, fill all this stuff out, but we're just going to put the tables together for now.

So, create table design, this will be our model table. So vehicle model ID, that's my auto number. I like to keep all my IDs up top. We need to relate this back to the make. So VMakeID, that's a number, that's our foreign key. Right, that'll link back to the make table. And then VModelName, that's where we'll put the model name in there. Alright, save this. VModelT, model T, see what I did there? It's a database about cars and they got a model T in there. Just kidding, that's stupid. Alright, hit OK, primary key, close this guy. 

Alright, let's put some data in this one. Now this is where you're going to want to see the makes over here. So you come over here. You're going to put, okay, so we'll put, we'll do one, we'll do, what do we got, F-150, that's Ford, we'll do another Ford, we'll do the Mustang, we'll do the Explorer, we'll do the Escape and the Bronco. You need data for this one, Bronco. You need data for this database to actually see that it's working, what it's doing. If you want to cheat, if you want to get a list of these somewhere, you can. 

I actually went out to chat GPT and I just told it to give me a list of these that I could just type in. Watch this, I'm gonna show you a little trick. So I went to chat GPT, right, and I said, please give me a list of models of vehicles made by Chevy. Please display them in a format that I can easily copy and paste into an MS Access table with line breaks. Because if you don't tell it to give you the line breaks, you won't get the line breaks. Hit OK, and it should just put a list together for you. There you go. 

I don't need that many. And in fact, let's update that a little bit. Let's say, give me this, copy that. Give me five models and put them in a code window. If you put something in a code window, it gives it to you like this in one of these code blocks. You just go copy code, makes it easier. Now that I've copied that, watch what happens. Come back over here, now click on just this cell, just like that, I know this is access, these are not cells, but just make sure you got just that highlighted in it. Paste. Boom. There you go. See that? 

Now we just got to come over here and fill all the twos in. This is just a simple one-time data entry thing. That's pretty cool. And now I'll say do the same thing for GMC. Give me seven of whatever, there you go, GMC. And you could do the IDs first if you got like one, two, three, four, five, six, seven. You can come in here and select, watch this, move right over the border and select just those cells, we'll call them, and then it pastes. 

Well, they are cells. Well, I'll say give me five for Jeep. Beautiful. Copy code, paste it in there. Jeep is four. It's these little tricks like this that sometimes make your job as a programmer, a developer, a lot easier knowing little tricks even just sometimes a shortcut key. And this is something you're probably just going to do once and never have to do it again. 

And give me ten for Audi. I'm always polite to chat GPT because you never know when they become our overlords so I want them to be on good standing with these guys. Copy code and I will come here and we'll paste. And Audi, what's, what's with the model names, Audi? Come on. Awesome German engineering, not too creative with the naming. I got a Q5 and an S3, which I didn't even see on the list there. Alright, we'll put the Audi in there. Alright, so we got that table all set. Okay. 

Now, when it comes to this table, you don't necessarily want to index no duplicates this. I know technically the car manufacturers try to not step on each other's toes, so you're not going to see like a GMC Mustang, for example, but you never know, it could happen. You know, maybe both car manufacturers comes out with a Raspberry model or something, and two of them do it in the same year and oops.

But if you want to prevent duplicates for a specific manufacturer, you can set up a composite index that's a lot more detailed. I do have videos on it. It's actually easier to check this in code if you have a form where you put this stuff in. Just do a quick D lookup and say if this is already in here, at least give the user a warning. That's a whole separate video though. 

Alright, so next up is going to be the year table. For that, we don't really need to see this right now. Save changes, yes. Save changes, yes. Let's create table design. We'll have a VYearID auto number. Yes, we're going to put an auto number in this table too. This is going to relate back to the model, VModelID. And this will also be a number, that's our foreign key. And then just VYear, because we're going to make this a number as well, that won't be text.

Now what you got to do is, let's actually save as VYearT. You have to put a year in here for each model for each make. I know it's a ton of data entry, but is this necessary? Yeah, it really is. If you want to make sure you get all of the available years for a specific model of car, then you've got to have a table for it. If you don't care, if you're not that particular about it, you know, if you've just got maybe a service shop and you just want to type it in, that's fine. 

I don't care but if you if it matters to you you need a table for it. Not every model was made every year, for example the Chevy Camaro they made it from 93 to 2002 then they stopped making it until 2010 so there is no such thing as a 2008 Chevy Camaro so that would be something you put in this table.

Now, restoring the model in the year table, which if we know this we can go back to the model table. Do we need to store the make in this table? No because as long as we know the model, we can go back to the model table to find what make that belongs to. That's why these are cascading tables. 

Okay, so we know the make, okay? We know the model and now we got to put in the years for each of those. Let me make this a little bit smaller here. I guess we do need to see these right now. Now this can be extremely cumbersome. You know, the makes were tough to get in here right? The years are even worse. 

What you have to do is say okay the model all right the F-150 is model one. Alright now you got to put all the years in that you want for that vehicle. Let's just do 2020 right? 2021, 2022, 2023, 2024. Okay and you got to come over here, fill all these in. And yes, you can do this with code if you want to or if you want to just block, copy, and paste this in here. 

Excel is really good at this. You can use Excel because it can auto fill and find the patterns. So if you want just let's say 2020 to 2024, say that 10 times fast, 2020 to 2024. And you want that for all of your models that are currently in here. What are we up to? Model number, let's see. So we need 32 of them. For this, let's go over to Excel. 

I'm just gonna open Excel up. And I'm gonna come over here and let's say, for each of these, so I need, let's do 2, 2022. Let's just say we're gonna do 2021, 2022, 2023, and 2024. Okay? 2023 and 2024. 

Okay now here, I'm going to say equals this plus one, that's three. I'm going to auto fill this down like that. Okay, and for here, I'm just going to say this equals that press enter and I'm going to auto fill that down. Right, see how that works? 

Now just auto-fill this down as far as you think it needs to go. So I'm going to bring it down to here maybe. There's 4, 5, 6, keep going. What do we need, 32 of these guys? So drag it down. Let's see this form 32, about 120 rows worth. Let's see. I'm going to write it down here somewhere. There we go. Okay, good. So there's 32. We can get rid of these ones. Delete. 

Alright, now just copy all of these cells. Ready? Copy. Switch back over to Access. And now I'm going to select these two cells together and then hit paste. Boom. And there's all my data that I need. See how that works? See, selecting these cells in table view that's handy and now I've got all of the years in for all of those cars right model thirty which is the Q5 right and I've got all those in here and you can come in here and tweak whatever you want to tweak you want to have more years less years years don't exist that's up to you but this is just getting the initial data setup and yes I know Adam was saying in the forums that they basically their shop gets a big, huge Excel spreadsheet with all of this in it so he doesn't have to fill it in. 

But we need data to play with. We need data for our data setup. Alright, so our tables are all set. Good to go. And again, you could put together a fancy VBA solution to do that. Sure, okay, but if you're only going to do it once, just do it in Excel real fast like I just did.It took, what, two minutes? I am thinking of making an add-on for this later on. Maybe I'll do a video on it where the next year comes around and the 2025s are released. Now you've got to take all of last year's data and just copy it and make a whole new set of models from all the existing ones and just add that year. Maybe we'll do that in a future video. It's on my list of possible stuff for the future.

But I'm always of the mindset that if it's a task I'm only going to do once, I don't bother automating it. If it's a task that I do every day, I don't care how easy it is. If it's a three-minute task that I do every day and I can simplify that with some VBA code, if it takes me two hours to write that code just one time but it saves me three minutes a day, well after what, 40 days, now I'm in net positive time gain.

Right, so I just click my button. But it's not just that. That's three minutes off of every day. So I'm all about automating stuff if it's warranted. That's a good make, model, year. Could we do more stuff? You could do whatever you want. You could do styles like is it a sedan, is it a coupe, is it a hatchback, is it an SUV, is it a van? Rule number one, in my van, it's rush, all rush, all the time. You could do drive type, you could do fuel type, you could do all that stuff. You just make more cascading tables and just add to what I'm showing you right in this video.

You could do things like color, although I wouldn't necessarily make that a table. You could just enter that or make a list of colors to pick from, unless you care about stock factory colors, because people can always custom paint their cars. So you might get a purple Ford F-150, I don't know, whatever.

Now, like I mentioned earlier, when it comes to proper data entry, if you don't want to deal with the tables or if you want your end users to be able to modify the make, model, year tables, you don't want them working in your tables. End users do not get access to tables. That's a rule of mine. They have to work in forms. So you could use nested subforms. Yes, you can have multiple nested subforms: make, model, year. This video explains how.

You could also use synchronized subforms. This is a little more advanced, but essentially you'd have a parent form that doesn't have any data in it. Then you'd have, you know, make over here, model in the next subform over, and then maybe have a third form over here with the years. You can add stuff like that, and these are all synchronized. So check this form out if you want to learn about that. But we're not going to focus on those kinds of forms in this series unless we do later. If you guys want to see that, let me know. If you guys get hooked on this database and want to see how those forms are integrated into this one, let me know and I'll make more videos. Squeaky wheel gets the grease; post a comment down below.

But for the rest of this video series, we're going to focus on this form, this pretty guy. We're going to take those makes and load them into this form. This is one dynamic form that we're going to control with some VBA code. Right, you pick your make, you pick your model; this comes out of the model table based on the value that the user picked in the previous step. Same form, we just change the parameters. Mustang. And then you pick your year. And it puts that here. 

And we're going to get to this part in tomorrow's video. So tune in tomorrow, same bad time, same bad channel. If you remember, of course, you can watch them as soon as they're finished. I'm going to try to record part two right now. It's getting late though. I might actually record part two tomorrow, but we'll see. But we've got lots to go. This is probably going to be at least a five or six, maybe seven, eight, nine, ten. I don't know how many parts. This is going to be a long series. I've got tons and tons of notes. Lots of VBA coding coming up. 

So 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. Oh, and wait, someone's beaming in. Hold on. 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/BigButtonForm

Weather API in Microsoft Access, Part 2

 How to Use a Web-Based Weather API To Pull Current Weather Data into Microsoft Access, Part 2

This is part two of my weather API series where we're getting the weather from the internet with an API, and it's pretty cool stuff. If you haven't watched part one yet, go watch that so you know what we're talking about, and then come on back.

Alright, so yesterday we learned all about the API call we have to do, the URL that we have to use, this guy, right, to get what we want. There are other forms of this too. I put some other ones online for you. Here are two other versions. You can either say Q equals and then your location like that, or instead of Q you can say zip and use a zip code. I don't know what they have available for other countries, but here in the US you can use a zip code. You can probably do this in the UK and Canada too. You'll have to look if you're somewhere else, sorry.

And then you can also use latitude and longitude, which was pretty funny because the first time I did this, I just put in 81.9 for my longitude, and I forgot the negative. So it had me somewhere way out on the other side of the world. But these are the three different methods you can use. And there are tons of different options too. You can change the units from Imperial, which we talked about last time. There are different language settings. So read their website for a lot more information. I'm just going into the basics here.

But I'm gonna stick with the zip code version, I think, in this one because that's just the easiest; punch in a zip code. Okay. We're gonna do some modifications on this guy. So let's design this. The first thing is let's change this today's date to current, oh, I'm sorry, zip code. We're gonna put the zip code in first. So this guy, we no longer need a control source. Leave that unbound. Let's change the name of this to just zip. It's no longer a short date. And I am going to put a default value in there of 33909. That's my zip code in Cape Coral, Florida.

Now for this class, we don't need these buttons. You guys can go away. Let's change this. While I'm at it, real quick, why not? Weather API, whoops, weather API. And I'm gonna slide you over here. I'm gonna put this button down below it. All right, get current weather. And we're gonna status the response that comes back from the API. So we'll keep this little status box here. And if you don't know what the status box is, I use this in a lot of my videos. I got a whole separate video on the status box. Here it is, I'll put a link down below. It's basically just a message box alternative. Instead of message boxing stuff and popping something up, you can just put it in this little gray box.

All right, now get current weather. We are going to go to build event, see what we got in here. All right, so this guy basically calls a function called getUTCTime and then just displays the status there. So we're going to change this getUTCTime. And I believe that is in this getTime module. So if we open that up, here is this stuff. All right, let's change some stuff around here. So we're going to say instead of getUTCTime, we're going to change this to getWeather as a string. We're going to return a string back, all the information that comes back to us. We need the HTTP object, the response text, the URL. We don't need this, this lns. Okay?

Now, my URL is where you're going to put your URL that we've been building. I'm going to copy it from where I have it, my clipboard. Where are you? Where's the clipboard? Oh, wrong one. There you are. I found you. There you are. Okay, let's copy this. Copy, move you out of the way, and paste right here URL. Boom, there it is. It's pretty long. Let's split it up. I'm going to split it up right here after that question mark. And we'll put it on the next line like that. Now the zip code, we're going to specify on the form. Now this is a module, so we have to use the full name of that form, okay, which is what we're going to close off our quotes and forms main menu F zip is the name of the field. I just smacked that right inside the string there, okay?

The next thing we need is the API key. Now I'm going to put the API key in a separate module because if I put it up here, I'm going to accidentally flash it at least three times during the video, and I don't want you getting my API key. So I'm going to come over here and I'm going to create a new module. And I'm going to say global constant API key equals, and I'm going to put my API key right there. And then I can use this constant called API key, and I don't have to worry about you seeing this. In fact, I'm going to save this as Rick's API key. And you can come in here if you want to put your API key in here or just move this into there. Whatever you want to do. I don't care. Alright. But I'm gonna paste my API key in there and then you're not going to see this anymore. Okay. So the API key is in there. Now I can take this and just delete that and put the API key out here. See? And it just goes out and gets that constant. Nice and easy.

Okay, so we got there, we got this is the same, this is the same, this is the same, this is the same, this is the same, and right down here we get s equals response text. All I'm gonna do is I'm gonna get rid of all of this stuff here. That's where we do the calculations for the date and stuff. And I'm just gonna say get weather equals response text. So this guy is literally just going to go out to this address, get the data back, and return it as part of this function call. Pretty easy.

All right, let's go back to the main menu. All right, main menu, right-click, build event, here's the button for that. Now it's no longer getUTCTime and it's no longer a date. This is going to be s as a string and s is going to be get weather. And what are we going to do with it? Well, we're just going to status it. Status S. All right. Save it. Give me a debug compile, make sure everything is good, everything compiles. Okay, great. And let's come over here, close it, open it. That moves. I got code in here that moves. This is one of my older ones. I used to have in this Rixmod keep out, I used to have code in here that would move the startup window. I'm just going to exit sub this. You can keep this if you want to. This is so that when I log on, it would put the window in the middle of my screen for recording purposes, but I don't use it anymore.

Okay, let's try that again. Get weather and boom, there it is. It went out and grabbed this stuff from the web, from the website we specified, openweathermap.org. All right. Now, while we're building this, I'm going to take all of this code that's in here. It's on a shift F2. We're going to... Oh, that's really big. Let's move this like this. I want to copy all of this and drop this on my clipboard as well because there's a lot of data in here. We need to go through and parse it.

So just copy that to your clipboard here, come back to my handy-dandy clipboard. We're gonna put this on the bottom. I use a clipboard constantly when I'm programming, constantly, just to put stuff back and forth. All right, so save this. Now, what we're gonna do next is parse the bits of data out of here that we need. For example, the temperature is right here. Let me just squeeze this a little bit so you can see it better, there we go, okay. Temperature value equals right there. You can probably even grab it from here. Okay. That's what you want. That's the start of the temperature value, and it's going to end with the next quote. So we're going to use my find between function to pull that out of this string.

Once again, as a reminder, here is the find between video where I explain how this code was built. If you are a gold member, you can come to the find between page, there's a link down below and you can go right to the code vault and grab the find between code. If you're a silver member or you're not a member at all, here it is, get typing. I'm just going to hit copy and now it's on my clipboard. See, benefits of being a gold member or platinum if you want.

All right, back over here, let's go to the global module and I'm just going to paste that in the bottom here under my sleep function. Boom, there it is. So now I've got findBetween. And again, if you want to learn how this thing was built, go watch the findBetween video. Okay? Okay.

Now, back to here. Let's make another variable real quick, t as a string. And we're going to say t equals findBetween. Where's the string? Well, that's in the status box. That's the box on the main menu that's got the data in it, right, status box. All right, what's the start of the string? Now, put a set of quotes like that to start with, trust me. Okay, now go back to your clipboard. That's the text you're looking for for the beginning of it. Notice it's got a double quote inside there. Now what you have to do in Access, this goes back to beginner or early expert level stuff. For every one of these inside of a string, you have to put a double double quote. All right? So I'm going to take this, copy it, come back over here inside those quotes, and paste that, but I got to make one more quote because this becomes a double double quote inside the quotes. That says there's a literal quote there. This is a topic that's confusing for a lot of people including advanced developers. I personally sometimes have to stop and think a couple of times about how many quotes do I need. So go watch this video if you need more help. I talk a lot more about the double double quotes problem. Okay, go watch this.

All right, so back to this guy. All right, copy. Now we need, what are you looking for for the end of the string? Well, that's just the closing quotes. So it's going to be quote, quote, quote, quote. That's a literal set of quotes inside of a string. Now we're just going to message box T, and we'll see what that value is. Save it. Do a debug compile. Everything checks out. Let's come back over. You get out of the way. Come back over here, hit the button, bink, and there's my temperature, 79.05. And that's about right, it's about 79 degrees outside, not too bad tonight. If you look in here you should be able to see that, 79, there it is, 79.05. I don't think their feels right is right because it's a little more humid, so this might be something with their data. I've been working with this for the past couple of hours and that hasn't changed. The current temperature is always the same as the feels right. What's the humidity? 92, yeah, it's up there. It feels like it's about to rain, folks.

All right, now what you can do is, you can take this, instead of putting it in T and message boxing it, why not throw it in some more fields in here. Design view. Copy this guy, copy, paste. I'm going to put that over here. We're going to do current temp. We're going to do, let's change this here to current temp. Oh, come on. Current temp. Can you guys read this text okay? Is this big enough for you to see? I know YouTube sometimes compresses the video and makes it a little harder to see this. I know my website has clearer video than YouTube does. So, let me know. Post in the comments down below. If you have problems reading this text, let me know. We'll do current temp, we'll do feels like, even though I don't think it's right. Feels like, I know I zoom in on text if it's something that's really important to see. Why is my box so big? There it is, feels like. Hit okay. That's interesting to know by the way. If you zoom in like this and then let's say you change the dimensions of this box. If you hit OK, it saves the dimensions. If you hit Cancel, it doesn't. If you open it up again, it's back to its old dimensions. I discovered that a little while ago. Feels like, I got the location. And then we'll do the current date and time. Date and time.

It's funny because date is a reserved word in Access. Time is a reserved word in Access, but date time is not. So you can use date time safely. But don't always use like date or just time. Always have it like, you know, order date, appointment date, that kind of stuff. Never use just date. Just like, you don't want to use name. Those are all reserved words. All right. Date time, location, feels like, and current temp. Come over here and we'll do the same thing. Now, we can get rid of the T. We don't need T anymore. I just put that in there so we can message box. All right. We're going to say, right here, it's Current temp equals that. I'm just going to copy and paste this. Copy, paste, paste. We only need three of them because date time is just a date time equals now, like that. All right, we have feels like and we got location. 

All right, now let's take a look at our text. Feels like is, where are you? That's up here. Let me see. Yeah, I like to squish this so you can see it all together. All right, there it is. Feels like value equals right there. All right, that's what we need. Basically, it's just replacing temperature with feels like in the other one. All right, just like that. Location, what does location look like? Location is here. Okay, so we need... Now, I've noticed, I did some playing with this before, and I've noticed that city ID is always zero unless you specify a city ID. You can give a city ID. If you guys find out that this is different, let me know. And also, when you do this, no matter how you get it, whether it's the zip code or not, it'll give you the longitude and latitude. That's pretty cool. Okay? 

So, for this one, we're looking for city ID equals zero and the name equals. And notice all the quotes in there, so we got to fix all of those. All right, so for location, it's going to be that, but then we got to go quote, quote, quote like that. And I think that's all we have to do. Yeah. Okay. All right. Save it. Debug compile. Click the button. Close it. Close it. Open it. And oh, wait a minute, I got my default value and all that. Well, let's go to that. Select all these guys, data. Default now. You forget to take that out of there. All right. Now save it. Close it. Open it. And click the button. And there we go. Obviously you gotta make this bigger. And yeah, see I don't think that I don't think there feels like feels right is working. Oh well, we can take that with them. There we go, beautiful. 

That's it, you can pull out whatever other information you want out of here. There's more stuff, there's wind speed and pressure and all that stuff. And they've got different other APIs. You can get forecasts, you can get little pictures, look at the little sunshine. They've got free and paid accounts, so go check them out. It's a good service and I endorse them fully and it works so give them your patronage if you can but I think that about covers it. If you like this stuff, if you like learning with me, and you want to learn more about VBA, come to my website. I got lots and lots of lessons. If you like my quirky teaching style and you enjoy my fantastic sense of humor, then come and learn some more from me.I got lots of lessons here. Also, consider signing up and joining if you want to become a member. Obviously, as a Silver member, you get all of my extended cut videos. You get a free beginner class every month, which you may or may not need.

Oops, someone just beamed in. Gold members can get an expert-level class every month, and Platinum members can get a free developer-level class every month after you've finished all the beginner and expert ones. There are all kinds of cool stuff available on my website. Come and check it out.

But that is gonna be 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/WeatherAPI2

Weather API in Microsoft Access

 How to Use a Web-Based Weather API To Pull Current Weather Data into Microsoft Access

In today's video, we're going to talk about how to use a web-based weather API to pull current weather data into your Microsoft Access database. This is going to be both an expert and a developer video. I'm going to show you a developer method using an API call that is a lot more powerful, and you can get data results like this very easily.

What is an API? It's an application programming interface. That's simply a fancy way of saying you can ask somebody else to do something for you. Like a WinAPI, you can ask Windows to do something or give you some information. A web-based API is a web server that you can say, hey, give me some information, like what's the current temperature. So that's really all an API does, and there's tons of them, there's millions of them.

All right, today's question comes from Jeffrey in Linwood, Washington, one of my Silver members and an Access Expert student. Jeffrey says, I'm taking your Access Expert 24 class and in there you give a link to an RSS weather feed from Weather Underground. That feed no longer exists. Are there any other sites that you can recommend that still work?

Yes, Jeffrey, unfortunately, that class is about seven years old, and as always when dealing with websites, they come and go, they change things. So unfortunately, Weather Underground's API doesn't work anymore, but a quick little Google search shows AccuWeather has an API, and you just send it your zip code and it will tell you the weather conditions. You can do pretty much exactly what I show in that video.

Let me walk you through it real quick. Take any database, this is my TechHelp free template, you can grab a copy of this off my website if you want to. Copy that URL that I gave you to your clipboard. This guy. Yeah, it's a PowerPoint slide. Let me copy that. Copy, control C. Now in Access, we're going to go to External Data, New Data, From File, and then pick XML file. I know it's a URL, it's not a file, but just pick XML file. Now you're going to paste that URL in here. Change your zip code obviously to whatever you want. If you're out of the US, I'm not exactly sure what you do, there's instructions on their website. Hit OK. Now it's going to go out, it's going to find this table with some other stuff in it. You want to make sure you import the structure and the data. Hit OK. All right. Save the import steps if you want. I've got whole separate videos on saving import steps and doing imports in general. 

All right. Now you're going to get some tables imported over here. There's channel, there's image, what you're looking for is item right here. And there's your data. All right. There are some links in here to give you direct links to that stuff. There's a description right here. All right so you can pull this stuff out of this table just like you would any normal data. So if you want to learn more about this I cover it in a lot more detail in my Access Expert level 24 class. It's all the same from this point it's just the URL is different from the one that I use in this class. I also cover other things like setting up an SQL server database online. There's all kinds of cool stuff covered in this. I'll put a link to it down below.

Now this is the lesson for those of you who aren't programmers and don't want to learn VBA, but there's a more powerful method if you use a little bit of VBA. Now if you've never done any VBA programming before and you want to learn, this video will get you started. It's about 20 minutes long. It teaches you everything you need to know. After that, go watch my Web API video. This teaches you how the code works that can have Access, go out to the internet, connect to an API server, and bring back some information.

In this particular video, I show you how to get the current date and time from an internet time server. And it'll just bring back the current date and time, and that will prevent your users from, like, changing the system clock on their computer to act like they logged in to work an hour earlier. Okay? Okay. So go watch this. And also go watch my Find Between video. I build something called the Find Between function. Basically, when these internet servers give you data, they're going to give you a whole bunch of stuff. All kinds of stuff formatted really weird, and we can use my Find Between function to very easily pull out a bit of information. Like right here, we can very easily pull out the timestamp or the date using the Find Between. So go watch these. These are all free videos. They're on my website. They're on my YouTube channel. Go watch those and then come on back.

Okay, now on my website I am going to use the database that we built in the Web API video because it's got all the code that we need to connect to the web API service. We just have to change the URL. Instead of getting the current date and time, we're going to grab the weather from the Open Weather Map website. So, I'm going to come down here on the member section. I happen to be a gold member on my own website, so I can download this. All right, so here's that database. It's pretty simple. You hit Get Current Time and it gets the current time. It goes out to the web server. Now this one is pretty straightforward. Here's a get time mod that we built in the other video. OK, get UTC time. And it just literally goes to this web page and pulls in the information. Here's all the code that you need. OK, now the weather API from OpenWeatherMap is a little more involved, and you have to get something called an API key. Basically, you have to register. It's free, don't panic. But you have to sign up with your name and email address to request information. And they do that to prevent abuse. I think they give you like a thousand free calls a day, which is more than generous. There are, what, 1440 minutes in a day? So you could make a call every two minutes and be fine. Let's go sign up for that first. Let me show you how to do that. Their website is at openweathermap.org. Here's their signup page and I'll put links to all this stuff down below. Put in a username, email, password, repeat the password, all this stuff. You got to be 16 years or older, agree to their terms, hit the little thing here and then go create account. Once you verify your email address and all that good stuff you're going to log in, sign in successfully, then come over here and go to My API Keys.

When you create an account, you'll be given a default API key. You can also create your own. I created one called Weather. You just type in a name over here like I typed in Weather, hit Create or Generate, and it will give you a new key. It's not a super long code here. And yes, I blacked mine out. You cannot have mine. Make your own. They're free. Once you get an API key, copy it to your clipboard. We'll use it in just a few minutes. Now I will say that it took about 10 to 15 minutes between the time that I signed up and got my API key until it worked on their server. So it's not immediate, but it doesn't take long. So just wait a few minutes and it'll start working. All right. So paste your API key into Notepad and here's an example of the URL that you have to use to get data. I'm going to paste that in there as well.

It looks like this. I'm going to make it a little bit bigger. Here's what the URL looks like that you have to go to. And you can just put this in your browser. I'll put a copy of this down below so you can just copy and paste it. Here's the URL right out to about there. The question mark says here's where the parameters start. So I've got Q equals Cape Coral, Florida, USA, that's your location. It's pretty good about a wide variety of location formats. Then I specified at units equals imperial because the default for temperature is Kelvin. How very science-y of them. You can also switch it to Celsius. I'm in America, so we're still using the Fahrenheit, so that's imperial. And then app ID equals, this is where your application, your API key goes you put that right there don't use mine. Okay now you could take this whole thing and copy this and paste it right into your web browser and you'll get something that looks like this. Now this is a JSON response. JSON is a particular data format that's increasingly becoming the standard on the web. JSON can be a little difficult to parse information out of. I personally prefer XML if it's available, and with this particular API it is available. So I'm going to come back in here, and I'm going to add at mode equals XML, if you want. That's up to you. So again, now I'm going to copy this whole thing, drop it in my web browser. And now you get this guy. This is an XML formatted version of the same stuff.

This is just easier to pull data out of because now if you look at this you can see here's the temperature value equals so really what we're looking for is this. I want to find everything between everything between temperature value equals quote and then the next quote and we can use my find between function to very easily do this. We're going to get this information into Access and then just use find between we can also pull out the feels like values right there the humidity the pressure the wind speed all this stuff right what's the location name just to make sure you got the right spot right all right so now we know where to get the data from we've got our account set up we got our API key we can get the data in the web browser.

In tomorrow's video now, we're going to put it all together and use the API code that we built in the web API video to get that information, put it inside of our database, parse all the information out, and your end result will look something like this. We'll do that in tomorrow's class. So tune in tomorrow. Same bad time, same bad channel. You know the drill. If you remember, you can watch it right now because I'm gonna keep recording right now. 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 2.

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/WeatherAPI