Decision Tree 6
By Richard Rost
3 years ago
Decision Tree DB Part 6: Drag-and-Drop Images
In this series of Microsoft Access tutorials, we are going to build a decision tree database that can be used for troubleshooting, logical decision-making, questionnaires, tech support, game development, and lots more.
In today's video, we are going to add images to our decision tree. I'm going to teach you how to drag and drop an image into your editor form and store the path and filename of that image in your table. We can then use that to display the image in forms and reports.
Members
Members are going to learn how to add images from any location, either relative to the database folder or anywhere on their hard drive or other hard drives, or even a network location without having to have the images folder in or under their database folder.
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!
Prerequisites
Links
Recommended Courses
Code Vault
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Drag and drop, drag-and-drop, add images, add pictures, drag and drop pictures, drag and drop images, chatgpt for images, DALLE-3 images, hyperlinkfield.hyperlink.address, currentproject.path
Intro
In this video, you will learn how to add images to your Microsoft Access decision tree database by dragging and dropping picture files directly from a Windows folder into your database using a hyperlink field. I will explain why it is best to store only the path and file name of each image, show you how to modify your tables and forms accordingly, and demonstrate how to display these images in your forms. I will also offer some tips on organizing your files and getting free, royalty-free images using AI tools. This is part 6.
Transcript
Today is part six of the Decision Tree Database, and today we're going to have a lot of fun. I'm going to teach you how to add images, pictures to your decision tree. I've covered images before, but today I'm going to show you how you can drag and drop them right from a Windows folder into your database.
This is part six of my Decision Tree series. Obviously, if you have not watched parts one through five, please go watch those first and then come back. Also, since we are dealing with images today, I want you to go watch my images video. Here's the link right there. You'll find it down below. It's on my YouTube channel and on my website. It's free.
In this video, I explain why we do not store images inside our database. Instead, we store their path and file name, and then we can use that to display the image in our forms and reports. But we don't want to save the image itself or any kind of files. Don't use attachments. Don't use OLE objects. They're all bad. They'll blow up your database. They'll make it too big. They'll make it really slow. Remember, a big database is a slow database. So we just store the path and file name. You can see a bit of it right there. This video goes into more detail on how to do that.
Now, in this video, I show you how to store the path and file name. In the extended cut for the members, I show you how to make this browse button. You can click the browse button, which will open up the file dialog, and you can pick the image, and then that will save the file information there instead of having to type it in or copy and paste it yourself.
Tons of people have asked me, is there any way to drag and drop images like that into your database? Yes, that's what I'm going to show you how to do today. Again, we're not going to store the image in the database. But you can use a hyperlink field and drag and drop the image hyperlink - the file name - and save that in your database.
Now, those of you who have been following me for a while know that I do not like hyperlink fields. I don't use them myself. They are a pain in the behind. I don't use them for email addresses or websites or any of that stuff. The only exception is this: If you want to drag and drop a file and store the path and file name of that file in your table, you can use a hyperlink field for that. That's the only reason I ever use them.
For today, here's my database file. Now, make yourself an images folder. This images folder, as you can see here, has a couple images. I've got these handsome guys in here. You can put whatever images you want.
At the end of class, I'm going to show you an interesting way that you can get your own custom images. We'll talk about that a little bit later. For now, just grab any old images you have lying around and put them in the images folder. This has to be in the same folder as your database or underneath it. You can have it in a path underneath that. Again, I'll explain later at the end of class, but for now, have images right underneath your database folder.
Let's go into our database, and we're going to modify our question table. Now, if you try to modify the question table right now, go to design view, and you can't do that. Why? Because there is an object open that's pulling data off that question table, this list box right there. So we're going to close the main menu first, and then we're going to edit this guy.
Down here, we're going to make a field to store our image hyperlink. That's going to be of type hyperlink, which I normally don't like to use, but here we go. Now, why didn't I just call that "image"? Image is a reserved word. So don't use "image." Don't use "picture." I know I've used that in classes before, and it can cause problems if you use reserved words, so try to avoid those. I've got a whole separate video on reserved words, and Alex - one of my admins - has compiled a great list. I'll put a link to that down below.
This is where we're going to store our image hyperlink. You could make a second field if you wanted to pull out just the address, which is just the file name. I'll show you how to do that in a few minutes. For now, I'm just going to use the one file name. So save that and then close it.
Let's go back to our main menu. Let's open up the editor for one of these right here, design view. We're going to make this a little bit bigger like so, and I'm going to add that hyperlink field down here. So, add existing fields. There it is. Drop it down here.
Now, this box is literally just for you to drag and drop the image on it, so we can make this really small like that. Over here, I'll change this to say "drag and drop image here." Slide this over like so and do a little resizing. Your user won't see this. This is literally just for you. Your user will see the final product. Save that, close it, and this is what it's going to look like.
What you're going to do is you can take one of your images, like Jimmy Kirk here, click, drag, and drop. There you go, and it saves images\\kirk.jpeg. That's a relative path to where that file is relative to your database. That's why it's important that this be in or under your database folder.
Go to another record, like this one here. Pick this guy, click, drag, drop, and there's the same thing right there. Go back up to the first one.
Now, let's put an image object down below here that gets the data of that address right there. Before we can do that, we have to learn how hyperlinks store data. So let's quickly make a button. We're not going to keep it. We're just going to make a button right here just for illustration purposes. Cancel that wizard. Right-click, build event.
In here, I just want to show you some stuff. I want to say message box, and I want to see the hyperlink, or it's going to be the image hyperlink.value, so you can see what's in there. Come back out here. Let's close that. Take a peek at it. Click the button, and that's what it looks like. That's what's actually stored in there.
There are two parts; there could be more, but these are the two you've got. You have the actual path to the file name, and then what's displayed. You can change that, and this is one of the reasons I don't like using these. If you rely on this for email addresses or web page links, it's kind of crazy. You have to be able to know how to pull stuff out of here. If you just want the address portion of this to give it to the image control, you have to know how to pull that out of there.
What we want actually is we don't want the total value. We want the image hyperlink.hyperlink.address. That will give us just the address portion of that hyperlink, which is what we need for the file name. Save that, come back out here, and now click the button. There you go. There's just the address portion.
Now, we can get where the database is by using currentproject.path. I cover that in my Developer 33 class. In the more advanced, if we put that in front of here, if we say currentproject.path and a little backslash and that, now we've got the full path and file name for that image. Again, that's why it's important that it is either in or under the current database folder.
So save that, come back out here, and now click the button and look at that. There's the full path and file name to that Kirk image. Go to the other one. There's the full path and file name to me.
Now, knowing this, I can use that to put in the control source for an image. So come back in here. We're going to delete that button. Leave the code there for just a minute, though. We're going to use it in a second. Don't use this to insert the image because it runs through the wizard and wants you to pick a file. We're going to drop this down and find the image control right here. Put a box down here underneath.
Cancel this, and now you're just left with an empty frame. There's your frame. Double click, and that will bring up its properties. Let me slide this back over here. We're going to give it a name; first call it the image object. The control source is going to be - come back over to your VB window - this is your control source right here. Copy that. Come back to your database, and it's got to start with an equal sign. Zoom in so you can see better, shift+F2, and then just put in here "=", and then that whole thing, currentproject.path, etc. Hit OK. It may put some brackets around stuff. That's okay, it won't change anything.
Save it, close it, close it, open it. Oh, look at that. There's Jimmy Kirk. Go to the next one. There's me. We took the hyperlink information, added the currentproject.path to it, and we made that the control source for our image control. Let's do another one.
Let's come over here and use Picard in something here. Let's go back to the top. Let's take Picard and drop him down here, and boom, there's Picard. Make it so, number one. It's that easy to drag and drop images. You just have to know how to work with it. You have to know this stuff here. Now, we don't need this anymore; we can get rid of that because it's in our control source.
What you could do, if you'd rather have just that file name, is you can have the hyperlink field. You can add a text field, a short text field for that image's file name only, and then in the after update event for this guy, you could just figure out what the path and file name is and save that in a text field, then use that text field as the control source for the image. However, this works fine just like this, but you have to keep the hyperlink around, that's all.
Displaying this in your user form is pretty straightforward too. Save that. Open up the user form here. Right-click, design view. Come down here and you'll put your picture down here, or if you want, you can put your picture over to the side. Whatever you want—it's your stuff, do whatever you want with it.
Now, we can go over to the editor and just grab this guy, copy, and then paste it over here and do something like this. Save that. Close it. Open it up, and... oh, wait a minute. I'm not seeing that. What happened there?
This is unfortunately one of those times where you do have to put the field on here. So, add existing fields, add the hyperlink field. We don't need to see it, so we're just going to delete that. We're going to take this, make it nice and small, and stick it up here just for now, just for a second. But watch what happens now. Open it up. There it is. But we don't need to see this. So I'm just going to maybe slide it here and then we'll make it invisible. Properties, format, visible, no. Sometimes you have to have that field on there; sometimes you don't. There you go. See? Beautiful.
Now I mentioned at the top of class, I'd teach you an interesting way to get some free images for your database. Let's say you're doing the dungeon crawl and you want to have a cool dungeon-y picture here. There are a lot of AI websites and different services out there that you can use to create AI-driven images that are royalty-free. You tell it what you want, and here's an image.
I've been playing around with it and it's really cool. There's one called Leonardo and it's really cool. I did one like, "give me a generic PC logo," kind of a computer. I did one that's an old man using a computer looking frustrated. I actually used this image, I tweaked it a little bit and changed the colors and stuff. I used that in my Intro to PCs video that I just made recently. So you can get some pretty cool stuff.
There is no cloud. It's just someone else's computer, that kind of stuff. "Professional businessman teaching a small group of people in a computer classroom." I used that in my class too. This is just something that the computer generated, and you can find this one at leonardo.ai.
Another one of my favorites is Chat GPT. I love GPT, and under GPT-4, this is a paid feature. You have to be a paid member. It's 20 bucks a month. If you have any need for business or whatever to do with custom graphics, or just for Chat GPT itself, it's fantastic. I think it's well worth it. Here's the DALL-E 3, which is a type of image generation.
I've done some stuff before. For example, here I said, "give me an image of a unicorn riding a bicycle on the rain," and there you go. It gives you a couple different options.
So I told GPT, "give me a classic D&D cave opening for an adventure story in the style of Larry Elmore." Larry Elmore is one of my favorite fantasy artists. He's amazing. I grew up admiring his work. He did all the D&D covers for the basic books and stuff. GPT said it can't do that due to policies; it's obviously copyright. I'm glad to see that they're not ripping off artists' work. But it then gave some different options. It provided four detailed descriptions to generate images, and it's amazing what this stuff can generate.
Here are the images it gave me. Look at that. Those are AI generated images. I'm mind-blown by the stuff this can do. Here are the images based on detailed descriptions provided. Pick one of these, pick whatever you like, save it, download it. I think, since this is a classic dungeon crawl, I'm going to go with this one. This one looks awesome. So I'm going to use my little screen capture tool. I use Hyper Snap. Grab that there. I'm going to make a dungeon crawl folder under here. I'll just call it dungeon, and I'll put my images in here for that. There's my Hyper Snap image. I'll call this just "start" or whatever.
Now we'll go back to our editor, and I'll grab that start image and drop it there. Close that. I didn't even have to restart that, and it just popped up right there. Look at that. Amazing. You can make it so you can click on this and see a bigger version. Just make a form with a bigger image in it—a modal form if they want to just see this. There you go. And I'm in the dungeon.
There, I resized it a little bit, made it a little bigger. Next, I asked it for the character class. I said, "give me some sample pictures for fighters." I'm going to go with this guy. That looks awesome. So I'll go here, grab my fighter, and drop it. Oh no, this is the viewer. We have to go back out here, to the editor, go to the fighter. Now I can drop it. There we go. Close that, come back in here, go to the fighter.
At this point, you'd want to see what the fighter sees here, but I'm just using this to illustrate the point. I'm not trying to make an actual game here. Here, you'd probably want to generate - you know what, I'm going to try generating that instead of the fighter. Let me literally take this and put that in GPT and see what it gives me. An interesting thing to do would be on this page, when you click on one of these, it can update the image accordingly. That would be pretty cool. You guys want to see that? Post something down below. Then you can click on it to see a sample, and it would update the picture over here. I don't know, I'm just having fun now.
So I gave it my text. I said, let's continue making images for my adventure. Here's the next page of text. That's what it gave me. That's not too bad. Sometimes it gives you cartoonish ones. Let's reload the page here. Hang on. There we go. That's not bad at all. It gives you two oil painting ones and two illustration ones. This one's kind of cartoony and childish. But it all depends on what you're going for. If you're writing a book or an adventure for kids, that's perfect. I like these, though. These are cool. So yeah, you can just capture one of these.
It's got dice on the ground too. My description said they were playing some kind of a dice game, so with these dungeon dice in here, that's awesome. I want to copy this. Let's go to someone else. I think they're here. Oh, I keep forgetting, you have to go back to the editor. It also would be nice to build in here - if you're in here, have some kind of a secret button somewhere or a keystroke you can use to launch the editor on this page, so you don't have to keep jumping back to the editor again. I'll put that on the maybe list.
Let's go to cleric and we'll grab the goblet. Close it. Dungeon crawl, cleric, there's that. That's pretty cool stuff. So I'm done playing around. That's a lot of cool stuff for today. That's basically, in a nutshell, how to drag and drop images and put them inside of your form like that.
Now, I mentioned earlier that the images have to be in or under the current database folder. If you try to grab something that's not in or under the current folder, maybe even on a different drive—let's go to this guy here, I'll grab something off another drive here, click and drag and drop it. Now, it's on my G drive - G:\\mydrive\\fighter.jpg - and it's not working. Why? Because it's not under the current folder, and so the technique we're using to display, to get the address, doesn't work.
So, for the members in the extended cut, I will show you how to take this full path, chop off just the file name, and then we'll get the proper path for it and use that for display. That way, we can display images that are anywhere on your network, on your hard drive, or on different drives. That will be covered in the extended cut for the members. And no, I have not made the title slide yet. Hang on.
There we go. Silver members and up get access to all my extended cut videos. If you want to learn how to do that, check it out. If you want to learn more about working with images in your database, check out my Imaging Seminar. We do lots and lots of different stuff. In fact, one of the cool things in this seminar is I show you how to click a button, browse to a file, pick a file on your hard drive or on your network, wherever. Then it will take that image and copy it to a shared images folder.
So, you can set it up on your server, or wherever. Users anywhere on their network can pick local files on their local hard drive. It will then get copied up to the shared server folder. The file will get renamed. You can rename it based on anything you want - a date, timestamp, or the customer ID, whatever. Then that will show up on everybody's databases because it will now be in the shared server folder. That's one of the cool things I do in this Imaging Seminar.
That currentproject.path thing, I cover in Access Developer 33. Lots of file system objects stuff in there. Of course, check out all of my developer lessons. If you like learning with me and you want to learn more VBA and how to build databases and all that cool stuff, check them out. There's the link.
Yes, I just realized this is part six. Sorry, my bad. Oops.
There you go, folks. That is your TechHelp video for today. I hope you learned something. I hope you had some fun. I enjoy this. This is a great video. I had a lot of fun today. Live long and prosper, my friends. I will see you next time.
I'm not sure when we're going to do another Decision Tree video, because like I said in part five, these haven't been really getting a lot of views. So like them, share them, post comments down below, tell me what you want to see. If we ever get around to making part seven, then we'll make part seven. If not, I might continue this in a seminar. So those of you that are interested, let me know.
Thanks. See you next time.
Quiz
Q1. Why does the presenter recommend NOT storing images directly inside the database?
A. Because image files are not compatible with databases
B. Because storing images in the database makes it large and slow
C. Because databases can only handle text data
D. Because images become corrupted inside Access databases
Q2. What does the presenter suggest storing in the table instead of the image file?
A. The physical image data as a binary object
B. The path and file name of the image
C. An embedded thumbnail of the image
D. A direct link to an online image
Q3. According to the video, which field type is recommended for enabling drag and drop functionality of image paths?
A. Text
B. Memo
C. Hyperlink
D. OLE Object
Q4. What is the presenter's overall opinion of using hyperlink fields?
A. He uses them for all types of links
B. He thinks they are useful for both email and images
C. He generally dislikes them except for drag-and-drop file paths
D. He only uses them for storing website URLs
Q5. Why should you avoid naming a field "image" or "picture" in your database?
A. They are too long for Access field names
B. They are considered reserved words and can cause problems
C. They cannot be indexed properly
D. They cannot be seen in queries
Q6. What must be true about the images folder relative to the database folder?
A. It must be located on a shared network drive
B. It must be on the C drive
C. It must be in the same folder as the database or a subfolder beneath it
D. It can be located anywhere on your computer
Q7. When you drag and drop an image into the hyperlink field, what is stored?
A. The image itself
B. The thumbnail and full image
C. The relative path to the image from the database location
D. The absolute path to the image
Q8. To display the image in a form, what does the presenter set as the control source for the image object?
A. The raw hyperlink field value
B. The hyperlink.address property combined with currentproject.path
C. The hyperlink's display text only
D. The hyperlink type property
Q9. What is the function of currentproject.path in this tutorial?
A. It retrieves the current user's file path
B. It gives the absolute server path of Access itself
C. It gives the folder path where the current database is stored
D. It returns the location of the images folder
Q10. If your image file is not within or under the current database folder, what happens when you use this technique?
A. The image displays correctly regardless of location
B. The image fails to display because the path does not resolve
C. The image is automatically copied to the right location
D. The database converts the path to a web address
Q11. Why does the presenter add the hyperlink field onto the user form and then make it invisible?
A. Because the field must be on the form for the image control to work
B. Because it looks better for the end user
C. To prevent users from editing the field
D. To allow the form to be saved automatically
Q12. What alternative does the presenter offer to using a hyperlink field for storing the file name of the image?
A. Use a Memo field instead
B. Store the file name in a text field with code to process after update
C. Store the image in a separate linked database
D. Use a lookup field to store image metadata
Q13. Why should you refer to the presenter's reserved words list when naming fields?
A. To avoid using words that are illegal in SQL
B. To ensure field names are easy to remember
C. To make sure fields appear in reports
D. To improve the speed of your queries
Q14. What method does the presenter recommend for obtaining custom images for your database projects?
A. Drawing them yourself in Paint
B. Downloading copyright images from Google
C. Using AI image generation services like Leonardo and GPT's DALL-E
D. Buying them from stock photo websites
Q15. What does the presenter say about using services like Chat GPT's DALL-E for image generation?
A. They are free for everyone to use
B. They require a paid subscription for certain features
C. They are not useful for database projects
D. They cannot generate creative content
Q16. What is a benefit of copying image files to a shared server folder as described in the Imaging Seminar?
A. Each user can have their own copy of each image
B. Only administrators can access images
C. All users access images from one location, ensuring consistency
D. Images are automatically backed up to the cloud
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-C; 8-B; 9-C; 10-B; 11-A; 12-B; 13-A; 14-C; 15-B; 16-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone is all about enhancing your Decision Tree database with images. This is the sixth part in my Decision Tree series, so if you have not watched the previous five lessons, I recommend reviewing those first. Also, because today's lesson focuses on working with images, you should check out my free tutorial on images in Access, available on both my YouTube channel and website. In that lesson, I explain why we should not store images directly in an Access database. Instead, it is much more efficient to store only the path and file name of an image. By doing this, your database stays small and runs smoothly, without the bloat and slowness that comes with using attachments or OLE objects. Please avoid those methods, as they can quickly cause performance problems.
In my earlier images video, I show you how to record the path and file name of an image. For Access Learning Zone members, the extended cut of that lesson also covers making a browse button, which opens a file dialog so you can select an image and save its path, rather than entering it manually.
One of the most frequent questions I receive is whether there is a way to drag and drop images directly from a Windows folder into an Access database. The answer is yes, and that is what I am going to demonstrate today. Again, we are not storing the image itself, but you can use a hyperlink field to drag and drop the image's file name so the path is saved to your database.
I have made it clear in past lessons that I generally do not like hyperlink fields; I do not use them for emails, web links, or similar data, because they can be difficult to manage. However, there is one exception: when you need to drag and drop a file from Windows and save the file path, hyperlink fields do work well for this.
To get started, create an 'images' folder within or directly below your database directory. Place any images you want to use in this folder. The location matters, because relative paths ensure your solution is portable and does not depend on absolute directory structures.
Next, open your database and modify your question table in design view. If you are unable to change the table structure, it is likely because another object is using the table. Be sure to close any forms or queries that are open, then add a new field to store the image hyperlink. Make this a Hyperlink type. Avoid calling the field 'image' or 'picture,' as these are reserved words in Access and could lead to problems. I recommend checking my separate video about reserved words for more information.
Once you have your hyperlink field, you can save and close the table. Then, open your form in design view and add this new field. Keep the control small and mark it as used for drag-and-drop purposes only. You might want to add a label like "drag and drop image here." This control is only needed during data entry, and your users will not see it in the main workflow.
With this setup, you can now drag an image from your Windows folder and drop it into the hyperlink field. This will record the relative path, such as images\\kirk.jpeg, which keeps your solution flexible and portable.
The next step is to display the image in your form. To do this, you need to understand how Access stores hyperlinks. A hyperlink field may include both the displayed text and the link address. Access provides properties that let you pull out just the address portion, which is the actual file path. Use the .Hyperlink.Address property of your control to do this.
To create the complete path, combine CurrentProject.Path with the stored address. This constructs a full path to your image file and allows Access to display the image correctly. You will need to use this expression in the image control's Control Source property. Whenever you switch records, the displayed image updates accordingly.
If you choose, you could create a second text field to store just the file name, and use an AfterUpdate event to extract and save the right information from the hyperlink control. This makes it a bit cleaner, but either method will work.
When displaying images to users, you might notice that the image does not appear unless the hyperlink field is also present on the form—even if it is invisible. Sometimes Access relies on controls being loaded into memory, so keep the field present but set its Visible property to No.
Now, I want to cover a way you can get free or royalty-free images for your database projects. If you are looking for creative artwork, especially for something themed like a dungeon crawl game, there are many AI-based sites that can help. Leonardo.ai, for example, lets you generate custom images from prompts. I have used AI-generated images for different projects, including some recent lessons. You can also try DALL-E 3, which is accessed through ChatGPT-4. While there are copyright restrictions (as with images based on specific artists' styles), you can still create impressive artwork for personal or business use.
When you find or create an image you want, use a screen capture tool to save it to your images folder. Then drag it onto your Access form as described above, and it will be stored and displayed properly. You can even build features such as enlarging an image on click, or syncing displayed images with choices made in your decision tree.
Be sure to always keep your images in or under the current database folder. If you try to drag and drop an image from outside that location—for example, from a different drive—your current path handling will not work, because the code relies on constructing a path relative to your Access file.
For those of you who are members, today's Extended Cut will cover handling image paths from anywhere on your network or hard drive, even if they are not in or under your database folder. I will show you how to extract the right information and display those images as well.
If you want to learn more about handling images in Access, check out my Imaging Seminar. In that course, I walk you through how to let users select an image on their local machine or network, copy it into a central shared folder, and rename it according to your database standards. That way, everyone in your organization can access the same shared set of images.
File path handling with CurrentProject.Path and other tools is covered in detail in my Access Developer lessons, including Access Developer 33.
That wraps up today's tutorial. If you have enjoyed this series, let me know by liking, sharing, and commenting. If there is enough interest, I will continue with a part seven, but otherwise, I might move this content into a dedicated seminar. Your feedback will help determine the future direction.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Setting up an images folder for your database
Adding a hyperlink field to store image file paths
Avoiding reserved words for field names
Modifying table and form design to support images
Dragging and dropping image file paths into a hyperlink field
Storing relative paths for images
Displaying images in a form using an image control
Extracting the address from a hyperlink field
Combining CurrentProject.Path with image file path
Setting the control source of the image control
Hiding fields from user display on forms
Testing image display by navigating between records
Acquiring custom images using AI generators
Saving and organizing AI-generated images
Troubleshooting image path issues with files outside database folder
|
