|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Map Overlay By Richard Rost 
6 years ago
Overlay Access Data on a Map or Image in a Form or Report If you are looking to overlay data from your Access database onto a map of any kind, this video is for you. It works for real estate agents who want to show properties, for cemeteries who want to list plots, for restaurant owners who want to show a map of their tables, and an endless list of others. Tom T. from the US (a Gold Member) asks: I operate a cemetery. I need a way to visually represent which plots are vacant on a map. For the ones that aren't vacant, I'd like to see who owns them. Can Access do this? MembersI'll show you how to open up the lot owner's customer record using a double-click event. You'll learn about the Tag property and the DLOOKUP function.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksFull Map Overlay Template: https://599cd.com/MapOverlay
IntroIn this video, I will show you how to overlay location data on a map using Microsoft Access. We'll talk about creating a map form, placing custom text boxes over a background image to represent lots or locations, and using VBA to display which plots are vacant or owned, and by whom. You will learn how to link your map overlays to your underlying data, making it easy to visually track things like cemetery plots, real estate lots, or even restaurant tables directly inside an Access database.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor.In today's video, I'm going to show you how to overlay location data on a map using Microsoft Access. Today's question comes from Tom T, one of my gold members. Tom says, I operate a cemetery. I need a way to visually represent which plots are vacant on a map. For the ones that aren't vacant, I'd like to see who owns them. Can Access do this? Well, yes, Tom and Annusha, there are a couple of different ways you can do this. Access really isn't designed for mapping, but with a couple of tricks I can show you, there are ways to do it. This technique will work for any kind of map overlays. It could be a cemetery with the plots. It could be a neighborhood for a real estate agent. You could put all the different lots that are available or houses that are available on a map. You could do a city map and show points of interest. You could do a restaurant and show a map of your tables, and you can display which tables are currently in use and what customers are at them. There are all kinds of different ways you can use this technique. Now I do have a previous video and I'll put a link in the description below where I show you how to store a location in your database and you can open up Google Maps to a specific location on the globe. If you want to store individual bits of data and then open up Google Maps to that one particular point of data, you can use this technique. But today I'm going to show you how to create a map in your database in a form or a report and overlay different blocks over it. You can say this is lot one, lot two, lot three, and so on. Then in your database, store who's in those lots, or for houses, who owns them, or for tables, what customers are there. You can use this technique for a lot of different things. OK, here's my sample customer template. I use this for a lot of my lessons. You can download a free copy of this off my website. Again, link will be below. We've got basic customers in here, orders, and of course contacts. Now, instead of orders and contacts, we're going to sell lots. Let's say we're a real estate agent or in Tom's case, he's selling lots in a cemetery. Doesn't matter, either one. So I'm going to create a new table. We're going to have a lot ID. That'll be my autonumber. I want to assign a lot number. This will be a number that I'm going to assign, one through whatever, how many lots I've got, one through 10, one through 20 and so on. Then we're going to have a customer ID. Who owns that lot? That'll be a number of type long integer, my foreign key that links back to the customer. I'm going to save this as my lotT. No primary key defined. That's OK. There's my autonumber. I close that. Let's put some data in here real quick into my lot table. Now my customer IDs look like this. One is Richard Rost. That's me. Two is James Kirk. So that's my customer ID for my links. Three is Sue Jones. Let's put some different lots in here. Let's say I've got 10 lots. I'm just going to number them one, two, three, four, five, six, seven, eight, nine, ten. Some have not been sold yet. So they're empty. They're vacant. We won't put them in here. Just so you don't get that confused with the lot ID, I'm going to say these get deleted and then re-added again. That can happen. So this ID over here is meaningless. That's the autonumber, pay no attention to it. You don't need to know what that is. Your tables should have it for making relationships. The lot number is a human assigned number that's on the map, one through 10. Now I can assign what customers own these lots. Let's say I own 1, 4, and 5. Jim Kirk owns 2. Sue owns 6. And let's say Jim Kirk comes by later and also buys 2. So there's the customers that own each lot. Now I can link back to the customer ID and pull out things like this person's first name, last name, credit card number, all that stuff. If you don't know how to make basic table relationships, I've got lots of videos on it. I'll put some links below. Now let's make a map showing the neighborhood that we're selling lots in. Or this could be your cemetery map, or this could be your restaurant with the tables, however you want to do it. I am going to create a new form with Form Design. I am going to make it about yay big, the size of my map. Of course, if you need to make it different dimensions, go ahead. I like to assign a splash of color. I'm going to open up this menu permanently here. Let's shrink this up just a little bit so I can see everything better. That's good. I like to give this a splash of color, so let's give it a little bit of a background. Now the first thing we need to do is throw a picture in here. The picture is going to be a map of our whatever. This is something you have to go get or draw or do whatever you want. You can use Paint. You can use copy and paste. Whatever you want to do. I am going to use Google Maps and go out to my old neighborhood here. That's where I used to live, and zoom in. Let's say we're selling lots in this neighborhood. Right about here is good. Now I'm going to use my screen capture tool, and I'm going to snip out some of this right about here. Let's say this is the area that I want to sell my lots in, right there. I just snipped it using my screen clip tool. I'm going to go back over to Access and paste that in. There it is. I grabbed a slightly smaller screen clipping for the purposes of recording the video so it didn't take up the whole screen, but it's the same concept. It's a smaller clip of the neighborhood there. So I got my picture and my form background. I'm going to save so far. Let's save this as my mapF. Now what I'm going to do is I'm going to draw text boxes over each of the lots that I have for sale. If you're selling all of them, draw over all of them. So I go up to Design. I'm going to grab a text box, not a label, a text box. Put one there. Get rid of the label that comes with it. Resize this text box over that lot as best as you can, however you want to make it. Just for the sake of things, give it a little tiny bit of color. Let's go with green, a light green. We're going to color it in the code in a minute. Now this guy, I'm going to open up the properties. I'm going to name this one Lot1. Very important what the name is. OK, that's Lot1. Let's say I got that one. I'm going to copy and paste it. I'm going to put this one next door. That'll be Lot2. Copy and paste. Let's say I don't own these two. This one here is already a home. There's already somebody in it. That's a double lot. It looks like it's a nice size. Yeah, I remember that house. There we go. That'll be Lot3. OK, copy and paste. Let's say I own this one here. Lot4, Lot5. Let's say this one I own right here. Lot6. We're just going to go with 6 for now. I know I put a bunch of them in earlier, but let's go back to the lots here. Let's say 2, I got rid of it. And 5, I got rid of it. So I own these two. Sue and Jim Kirk own the other ones. So I own 1, 3, 4, and 6. The other ones are missing. That's fine. You can have as many of them as you want here, just name them all accordingly, name each block accordingly. You might have to save it, and then close it, and then reopen it again. Let's open up that map. OK, looks good. Design view. OK, good. Now, we know who owns what lot by this right here. So what I'm going to do is when this form opens up, I'm going to loop from 1 to however many lots there are, and I'm going to check and see what the status of that lot is. We're going to go to the properties of the form, Events. We're going to find the On Open event, right down here, On Open, hit the dot dot dot button. If it asks you, hit Code Builder, but I've got that turned off, so we don't need to worry about that. This is the sub that runs when the form opens. First, we're going to need some variables here. So, Dim, I'm going to Dim a customerID as a Long. I'm going to look up each customer that owns each lot. I'm going to get, let's say, get their first name, Dim firstName as a String. You can look up whatever other information you want. I'm going to need a counter variable, so Dim x as an Integer. Now, I'm going to say: for x = 1 to 6. I have six lots now, but this loop will work for as many as you want to put in here. You can put it up to 10, even though there's only six lots on there. That's OK, because we're going to write this code. It's not going to matter. Don't go too big, because then your code will take forever to run, but if you anticipate you're going to probably add three or four in the near future, you can jack this up to 20 in a second, not changing much. I'm going to stick to 10 for now. It's a good number. Now, the first thing I'm going to do is look up the customer for Lot1. I'm going to go to the Lot table, look up and see if lot number 1 has a customer associated with it. How do we do that? Well, I'm going to say: customerID = DLookUp("customerID", "LotT", "lotNum = " & x) If you haven't used DLookUp before, again, I got lots of videos. I'll put it on the list. I have a little list I'm keeping for you. I'll put all the links to these kinds of videos down below. If you haven't used DLookUp before, go watch that video first, and then come back. So what will happen here is x is going to be set to 1. Then the DLookUp is going to say go find the customer ID in that Lot table where lotNum equals 1, and in this particular case, it's going to say, OK, lotNum is 1, bring back customerID 1. Then we know which customer owns that lot. Now, the problem is if it doesn't exist, it'll throw an error message. For that, we're going to use the NZ function. NZ is null to zero. If it doesn't exist, it's going to bring back a null, which will create an error message. So I'm going to say ,0. Again, I've got videos on how NZ works too. If this doesn't exist, it'll just return a zero then, which is much easier to check. So I'm going to say, if customerID = 0 then this particular lot is vacant. Let's color it green. Now, how do you access Lot1? It looks like this. Normally, if this was firstName, you'd say firstName.BackColor, and you can access the properties that way, or set the value if it's a field in your form. But we have a field that is named Lot1. So I can't say, Lot1, LotX, or whatever. Here's how you have to write it. It's Controls("Lot" & x). That's how you access a field on your form when you don't know the name of it ahead of time. We have to build it with our code. So Controls("Lot" & x).BackColor = vbGreen I'm going to also make it say vacant. Controls("Lot" & x).Value = "Lot " & x & ": Vacant" Otherwise, let's do some other stuff. Let's copy this and put it down here. Otherwise, this returned a number other than 0, that means there's a value. So let's make it say "sold". We'll put the person's name in there in just a minute. Let's make the background color yellow. And then we can get rid of these extra lines. Save it and let's see if it works. Let's close this, close this, close this, and let's open up that map. Oh, I can't find Lot7. That's fine. Debug, Lot7 doesn't exist. I told you earlier I'd show you a way to get around that. We can just very easily up here, throw up here: On Error Resume Next. That should get around the problem. Now open it up, and there we go. You can see 2 and 5 are vacant. 1, 3, 4, and 6 are sold. Yeah, this is cheesy. The best way to do it is to say, well, is to actually make your loop go to the right number. What I like to do in these cases is I'll say something like Const maxLots = 6. I've got 6 lots. Now I can just come down here and say maxLots. This is easier to see. It's all in caps, but it's a constant, it can't be changed. This way, if you add more lots in the future, all you have to do is remember to come in here and just change this one spot. You don't have to look all throughout your code. How do we put the person in here that owns it now? Well, that's just another DLookUp. Come back in here, that's what we made this firstName for. So down here, there is a customer. I can say: firstName = NZ(DLookUp("firstName", "CustomerT", "customerID = " & customerID), "") Then down here, put firstName. You could check to see if it's empty and do whatever you want, but I'm not going to bother right now. Let's open it up again. Boom, there we go. Lot 1 is Richard, lot 3 is James, lot 4 is Richard, lot 6 is Sue. You can make all these smaller text so it all fits in here nicer. That's just a matter of design. Go in here, select them all, format, maybe go down to 9 point. Everybody fits on there better. Save it, close it. Open it up, there you go. Want to add someone in the future? Let's say lot 2 is sold. Lot 2 goes to customer, what do our customers look like? Let's say it goes to Walter, customer 7. Close it. Now when I look at my map, boom, Walter's in lot 2. You can put whatever details you want in your customer ID, and all that stuff. As far as seeing what customers own lots from the main menu, you can go to main menu, customers, make yourself a subform. Like the orders are, and it shows you the lots in here, instead of the orders or contacts. I've got videos to show you how to do these things too, all on my website and on my YouTube channel. The point of this was to show you how to overlay stuff on this map. You just put your text boxes in here, then you run through a loop in your code and fill in the values of what needs to go in each of those text boxes. If you want to learn more about mapping data, in the extended cut for members only, I show how to add a double click event to each of these boxes, where you can double click on it and open up the record for the person who owns that lot. You can use the same technique to access any of the information about that lot that you want. I've also built a much more complex version of this that I have available as a template on my website. I will put a link to it down below in the description. This database allows you to store an unlimited number of maps where the mapping information is stored in tables instead of in static controls in the form. For example, here's the world map. If I want to go to Gotham, I can double click there, go to Gotham City. Back it up. I can go to Rickopolis. There is a park map, and you can see the baseball fields. Or you can go to the zoo. Each one of these loads a separate image in the background and puts custom boxes on the field. There's the golf course. Go back. Go back again. I can do a restaurant with seating charts for the tables, or go to the waterfront district and get a map of that. If I'm a realtor, here's Glenwood. Here's the houses available that I have for sale. There's House 1, House 2, House 3—model home. See, it's unlimited. You can go as many levels as you want. Again, I have this available as a template on my website. I'll put a link in the description below. It's called the Mapping Overlay Template. These boxes are created dynamically in the code. You can see here's the map detail. It stores the X and Y coordinates for the top and bottom right corners, top left, bottom right. A description there, you can use rich text to store that. The jump to location so it knows what map to load up if you double click on it. All this is covered in the template. Silver Members and up will get access to this extended cut video that covers how to do this with the database that we just built. How do you become a member? Click on the join button below the video. Silver Members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the join button, you'll see a list of all the different perks that are available: Silver, Gold, Platinum, and Diamond. Don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making them, and they'll always be free. If you enjoyed this video, please give me a like and share. Click on the subscribe button to subscribe to my channel and be notified of any new releases. Check for additional resources down below the video. Click the Show More button and you'll see a list of other links to other videos, downloads, resources, lessons, and lots more. If you have not yet tried my free Access Level 1 course, it's three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just $1, and that's free for my members. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can post your question there. Also, be sure to stop by my Access Forum on my website and also look for me on Facebook, Twitter, and of course, YouTube. Once again, my name is Richard Rost with AccessLearningZone.com. Thanks for learning with me, and I'll see you next time. QuizQ1. What was Tom T's original request in this video?A. How to connect Access to Google Maps for individual locations B. How to display customer reviews on a map C. How to visually represent cemetery plot availability and ownership in Access D. How to import GIS data into Access Q2. What general concept does Richard demonstrate in this video? A. Printing labels for address envelopes B. Overlaying data blocks onto a map image in Access forms or reports C. Creating pivot tables for geographic data in Excel D. Integrating Access with SQL Server spatial features Q3. Which of the following is NOT mentioned as a potential use of the mapping overlay technique? A. Real estate neighborhood lots B. City maps with points of interest C. Restaurant table seating status D. Generating interactive maps for online websites Q4. What main objects does Richard create in the Access database to solve Tom's problem? A. Only an image field in the customer table B. A new table for lots, a form with a map image, and text boxes over the map C. A subform within the customer records D. Additional queries with no forms or tables modified Q5. What is the purpose of the LotT table in the solution? A. To track inventory for each customer B. To store information about each lot, its number, and the customer who owns it C. To keep a log of all customer orders D. To list available products for sale Q6. What specific Access form feature is used to overlay information on the map? A. Command buttons B. Text boxes named according to lots (e.g., Lot1, Lot2, etc.) C. Embedded web browser controls D. Hyperlinks within labels Q7. How does the code determine which customer owns a particular lot? A. By filtering the form's record source B. By looking up the corresponding customerID in the LotT table for each lot number using DLookUp C. By checking the caption of a label assigned to each lot D. By comparing the form's current record to the lot number Q8. If a lot does not have a customer assigned, what is displayed and how is it represented visually? A. The word "Sold" in red B. The owner's full name in yellow C. "Vacant" in green D. The lot number only with no color Q9. Why does Richard suggest using the NZ function with DLookUp in the VBA code? A. To prevent lookup of non-numeric values B. To convert a Null return to zero for easier checking C. To speed up the code execution D. To ensure DLookUp always returns a string Q10. What is the preferred way to access the properties of a control when the name is being built dynamically in code? A. By referencing the control directly by its name B. By using the Controls("Lot" & x) format C. By using form field indexes D. By hard coding every control's property Q11. What is suggested as a best practice when determining the loop range for lots in the code? A. Always looping up to 100 lots just in case B. Using a constant value for the maximum number of lots and updating in one place C. Entering the value manually in every code section D. Having the user input the number at runtime Q12. In the sample solution, what is changed in each lot's text box besides the background color? A. The border color is randomized B. The text value is updated to display the lot number and owner or status C. A tooltip is added with owner details D. The text box becomes invisible if sold Q13. What additional advanced feature is demonstrated or available in the Mapping Overlay Template on AccessLearningZone.com? A. Automatic integration with GPS devices B. Dynamically stored boxes in a table with X and Y coordinates, allowing unlimited maps and overlays C. Scheduled automatic color changes based on time of day D. Drag and drop editing of map images directly in the form Q14. How can users expand this technique to access more information about each lot owner? A. By right-clicking the text box B. By adding a double click event to the box to open the owner's record C. By importing an external Excel sheet D. By using calculated fields only Q15. What benefit is mentioned about using text box controls instead of static data for overlays? A. They allow you to easily update displayed status or owner information dynamically via code B. Text boxes are required for database normalization C. Labels cannot be colored D. Static data is easier to update Answers: 1-C; 2-B; 3-D; 4-B; 5-B; 6-B; 7-B; 8-C; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on how to overlay location data on a map using Microsoft Access. The idea came from a question posed by someone who runs a cemetery and wants to visually display which burial plots are vacant and, for the ones that are not, show who owns them. Although Microsoft Access is not primarily a mapping tool, there are ways to accomplish this with a few clever techniques.This approach works not only for cemeteries but for many other scenarios where you need map overlays. For example, a real estate agent might want to show which houses or lots are available in a neighborhood, or a restaurant could use the same system to display which tables are occupied and who is sitting at them. The same concept can be applied to city maps, parks, or similar layouts. Previously, I recorded a video that demonstrates how to store specific location data in your database and open Google Maps to view those locations. That technique is perfect when you want to store latitude and longitude data and jump to that spot on Google Maps. There is a link to that video on my website if you are interested in that method. For today's lesson, I will show you how to build a map directly inside your Access database using forms or reports. You will be able to overlay blocks onto a static image. Each block can be linked to records in your tables. For our example, these blocks represent lots, but they could just as well represent houses, tables, or other items. I started with a simple customer template, which is available on my website for free if you want to follow along. This database comes with basic customer details, order information, and contact management, but for this purpose, I replaced the orders and contacts with "lots." Suppose you're managing property lots or cemetery plots. First, I created a table for lots. Each record includes an autonumber LotID for internal reference, a user-assigned lot number matching the map, and a CustomerID linked as a foreign key to the customers table. The table can handle as many lots as you need. I then entered some dummy data: customers with IDs assigned to each lot (for example, Lot 1 belongs to Richard, Lot 2 to Jim, and Lot 6 to Sue). The core idea is that the LotID is only used for relationships, while the Lot number is what you see and use on your map. The next step is to show the lots on a map inside an Access form. I created a new form with Form Design and sized it according to the map image I wanted to use. I added some color for clarity, but the important step here is inserting an image of your map. In my case, I took a screenshot of a neighborhood map using a screen clipping tool and then pasted that image onto the form. With the background set, I placed a text box over each lot location on the image to represent each lot. It's important to use text boxes, not labels, so you can change the text and background colors via VBA. Each box gets a meaningful name to match the lot number (for example, Lot1, Lot2, and so on). You can add as many as you need and position them visually over the corresponding spots on the map image. After laying out the text boxes, the VBA code comes into play. In the form's On Open event, the code cycles through each lot and determines its status. It uses the DLookup function to see if a customer is assigned to a given lot. If the result is null, the NS function (which converts null to zero) helps the code decide if the lot is vacant. The code then sets the background color of the text box to green for vacant lots and displays "Vacant," or yellow for occupied lots and displays the owner's name, which is also retrieved using DLookup. To handle missing controls (for example, if your loop looks for Lot7 and only Lot1 through Lot6 exist on the form), a simple error handling line is used so that the code does not break when it tries to reference a non-existent control. It's often better to set a constant to specify the maximum number of lots, so you only have to update one spot in your code as your map grows. If you later add a new customer or sell another plot, simply update your data and the form will reflect the changes the next time it opens. Adjusting text formatting for readability is simply a design choice within the form itself. You can also add subforms to your main customer form, which let you see all the lots owned by a customer, much like tracking their orders. There are additional videos on my website if you need help building subforms or making basic table relationships. The main takeaway is that you can overlay dynamic data-driven controls on top of any map or schematic inside Access, and then use VBA to update them with current status, owner information, or other details. In the Extended Cut for members, I demonstrate how to enhance this map further by allowing users to double-click any box to open the record for the person who owns that lot. Using similar methods, you can display any extra information about these mapped locations. I also have a more advanced database template available that allows you to create an unlimited number of maps, using tables to store all the mapping data and information about each box's location and properties. For example, you can have world maps, neighborhood maps for real estate, restaurant seating plans, or park locations. The mapping template stores coordinates, descriptions, links to other maps, and supports rich text formatting. If you want to see how this works or use it for your business, there's a link to that template on my website. Silver Members and above can watch the extended cut video where I show how to apply these dynamic mapping functions within the database. If you would like to become a member to access these extended tutorials, head over to my website where you'll find a full list of membership benefits including access to live Q and A sessions, exclusive videos, and more. Remember, all TechHelp videos will remain free and I regularly add more content as long as you keep sending in your questions. If you found this information helpful, be sure to like and share the material, subscribe to my channel, and check out additional resources and free lessons, including my free three-hour Access Level 1 course. Level 2 is also available for just one dollar and is complimentary for members. If you have your own question and want it answered in a future TechHelp video, visit the TechHelp page on my website to submit your query. Also, stop by my Access Forum and follow me on social media for more tips and community discussions. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListOverlaying location data on a map in Microsoft AccessCreating a table for lots with customer ownership Assigning lot numbers and linking to customer records Manually entering sample data for lots and customers Adding a map image as the form background Drawing and naming text boxes to represent map lots Formatting text boxes with background colors Accessing controls dynamically in VBA using Controls() Looping through lots to display ownership status Using DLookup to find lot ownership from the table Handling empty (vacant) lots with the NZ() function Setting text box values and colors based on occupancy Using a constant to manage the total number of lots Displaying owner names in lot text boxes using DLookup Adjusting text box formatting for fit and clarity Updating the map when lots change ownership |
||||
|
| |||
| Keywords: TechHelp Access Image Overlay, Microsoft Access Mapping, Real Estate Property Maps, Map of Cemetery Plots, Layout of Tables in a Restaurant, City Map Points of Interest, Gaming Maps Overlay Data on a Map or Image PermaLink Overlay Data on a Map or Image in Microsoft Access |
