Hyperlink Fields in Microsoft Access and Why You Should NOT Use Them

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross in this video i'll show you how to use the hyperlink field data type in your microsoft access tables and i'll explain why you should not use them i'll show you a better alternative using plain short text fields and how you can still make buttons to easily navigate to those hyperlinked destinations today's question comes from brent in tracy california brent is one of my access veterans a gold member and a 10 year developer student so brent's been with me for a long time brent says i have a list box where i'm trying to change the font color i've selected the list box in design mode and change the color i want on the ribbon and all but one column changes to the color i pick the one that doesn't change color is a field that holds emails and is a hyperlink field how can i force all the fields to be the same color and this is what brent is dealing with right here you can see i've got a list box i can try to change the colors but no matter what i do those colors are going to stay based on whatever the default hyperlink colors are on your system you can't change them easily in access this is why i tell people in my classes hyperlinks are evil don't use them they're difficult to work with there's a much better solution available i'm going to show you in this video in fact hyperlink fields earned a place right there on my evil access stuff page want to see this whole page i'll put a link down below in the link section so what can we do with these hyperlink fields well first let me give you a little basic primer now back when hyperlink fields first came out i tried to embrace them i tried i actually taught them in a couple of my classes they're okay for beginner users they've got some pros but mostly cons once you get into a little bit more advanced database development you'll see that hyperlinks become a pain to work with let me show you why now this is a copy of my tech help free template you can download a copy of this off my website if you want again you'll find a link down below in the link section now you'll see if you go to my customer form i've got email right here but it's a standard text field that's how i like to store email addresses web pages and things like that standard short text fields all right don't use hyperlinks so in order for me to show you the problems with hyperlinks let's go create some hyperlink fields now let's go to the customer table right click design view here's my email short text all right let's leave that there let's go to the bottom let's make a secondary field let's call this one email address let's call it and we'll make that a hyperlink field okay then let's make website we'll also make that a hyperlink field you can also a lot of people don't know this you can also use hyperlink fields to point to files on your computer or on your network that's another use for them in fact let's say we're storing resume files all right let's put in here resume we'll also make that a hyperlink okay all right let's save the customer table let's go back over to the customer form and let's add those new fields design view all right make this a little bit bigger let's go to form design go to add existing fields right down here on the bottom click on email address shift click on resume and then click and drag those right about there let's drop them right here on the bottom of this form you can see they come in as hyperlink fields all right close this let's save it close it and open it back up again okay we got three nice blank forms on the fields on the bottom down here all right now the first problem that i have with hyperlink fields is they're difficult to edit once you've typed them in now if you've got a computer user someone using your database who doesn't know a lot about computers okay adding this hyperlink field in here is no big deal all right they're typing in the customer's info so they're typing in like amicron gmail.com okay good website is 599cd.com and then their resume file whatever so now if i click on this email address you see i get a little finger there that will actually load up your email program and send a pc email to this person which in this case is for me i use gmail so it loaded up my web browser if your default mail handler is outlook it'll load up outlook all right that's cool close that back to here if you want to go to this website you click on it right you get the little finger and boom there's my shiny face okay all right that's all well and good but now what if you want to edit this you typed it in wrong all right uh where do i go how do i click on this uh beginner users get confused with this all the time trust me i've taught this class a million times in my classroom okay and it just people you gotta teach them you gotta right click on it you gotta go to hyperlink edit hyperlink right now you can change stuff in here now here's another problem a hyperlink consists of many different things you got text to display you got the email address you could put optionally a subject in here so they might come in here and type in something different uh joe smith at amicron.com okay thinking that's what they need to do hit ok and it says joe smith at amicron.com but hold your mouse over and you'll see no it's got ammicron at gmail.com in there still because they edited the wrong thing hyperlink edit hyperlink see that's the text to display i've seen people do this a lot this is designed so you could put in here like email rick or richard or whatever and then hit okay and now it says email rich which this is another problem in and of itself all right i just want the email address in this field now if i tab over there let me see this on the bottom of the tab order now so let me tab to that field if you tab to it alright once you're sitting on it if you hit f2 on your keyboard that edits the text in place and you can see what's stuffed into that email address field all right a hyperlink consists of a couple of different parts there's the visible part there's where the link actually goes to then there's optionally an address on a page that it'll jump to it it's a mess it's a nightmare okay i don't like using hyperlink fields primarily for this reason all right let's put it let's put another one i want to show you something let's put in here for jim kirk uh how about uh jim at 599cd.com all right let's put one more in let's do uh deanna troy she's a beta zed at uh amicron.com i always use my own domain names in my classes now because i used to put in just whatever.com and i'd get get complaints from people at whatever.com hey you used our email address in one of your videos and we keep getting spammed so i stopped doing that i only use my own domain names now all right so here's another problem okay you got your customer table if you open up the customer table everything looks normal it looks good right okay let's export this to excel all right external data we're going to export to excel right there click the button where you want the file to go and drop it on my desktop so click on browse desktop hit save hit ok all right don't save the steps all right i'm going to go to my desktop and open up that excel file it opens up in excel and look at this garbage look at this over here that's what's in your email address field folks so if you want to export those email addresses and send them to someone else they're going to get these full formatted hyperlink fields no they don't want that you just want to send out a list of email addresses or web page addresses no no i don't want that let's take a look at brent's listbox issue all right right click design view let's drop a customer list box right here on the main menu all right find list boxes drag it out right there all right we'll use the wizard next customer teen if you've never made a list box before i've got videos for this right let's bring in the customer id the first name the last name and the yeah not that this is my email address come down and get the other email address all right the bad one next all right what do you want to sort by oh it's not a big deal last name then first name next all right this is what it's going to look like you can see the problem already okay let's shrink it up a little bit and then next and label finish and that's fine get rid of that label all right let's see what this looks like save it close it open it back up again and there you go you can see you got your standard hyperlink colors kicking in there all right this guy's purple because we followed it all right let's try to change it design view come in here format let's give it a background of like a light purple and i want the foreground text to be blue no not that blue because it looks like the hyperlink blue let's go with uh this green just just to see i know it looks nasty save it close it open it up again and look at that these guys are green this stuff is still hyperlink colors no i don't want that even if you go design view and go into the properties in here go to format all right here's your back color here's your four color even if you change this four color to something like manually black okay it doesn't matter access doesn't care you're still going to get those hyperlink colors combo boxes are even worse if for some reason you wanted to make a combo box let's say you've got um you know you want to pick a a an email address to send a particular email to and you want to pick by a combo box let's say hypothetically you've got uh you know a customer has a a second related table they could have multiple email addresses associated with them right so you want to pick one of them to send an email to so again make a combo box same thing same thing customer t right let's bring in customer id and then their email address all right next don't worry about sorting it next all right that's what it's going to look like in the box see next and then finish and i'm just going to show you here delete that slide this over i'm showing you guys what not to do right now by the way just so you have an appreciation for when i show you all right drop this down looks normal pick something and that's what goes in the box okay and when you click on it it's gonna launch actually i think it's going to launch anything from the combo box see nothing nothing happens it's it's the worst okay it's it's a nasty looking link and it doesn't even work so you want you can't use these in combo boxes another problem with hyperlinks someone modifies the display text even if they did a good job and put email rich in there okay yeah all right no big deal it works it's fine make a report out of it now let's say you want to make a report i'll use my blank card down here design view bind this to the customer table okay and we'll bind this to that email address where are you okay now look what happens when i generate the report right click print preview look at that i get email rich so if someone does go in and starts using that change the display of the hyperlinks it messes up all your data and yes you can come in here and you can format this to look like you want you can change the color here and you can change it with the normal text box too right you can go to format drop this down go to black turn off the underline okay you can do that that's not a problem and you could do that in a single form i could change this to look like normal text but you can't easily change that behavior there are even properties in here if you go into format there is where is it um here it is right up here all right is hyperlink set it to no and then display is hyperlink you got if hyperlink always and screen only you can't even turn that off okay it's going to either always be displayed if it's a hyperlink or screen only so if i put if hyperlink which is the default open it back up again and it still looks like that but you got the the finger still see it no the whole the whole implementation of hyperlinks is just a pain oh there's one more thing i have in my outline if you decide you want to use it the query all right create query design all right let's bring in the customer table again let's say hypothetically you've got the customer id the first name and you want to bring in their email address all right okay no problem you got the regular hyperlink there but if you want to use this in any kind of situation where you you know concatenate something else onto it let's say you've got multiple email addresses or let's say you want to put something else in this field okay if you want to come in here and say you know whatever is going to be you know email to and then their email address all right you want to format something to display in a report this way and just have it you know email to colon whatever all right you do that you get that stupid looking hyperlink thing again okay okay so i've complained enough about hyperlinks and i think you can see that aside from the most simplest uses hyperlink fields are pain especially as you get into more advanced database development now what's the alternative the alternative is to do what i did here and use straight standard text you just type in the email address type in the web page address type in the location of a document whatever you want okay so let's get rid of this stuff design view let's get rid of you goodbye i don't know why i bother with a resume let's go back to the customer table actually let's get rid of this stuff too design view goodbye you guys are gone you're useless all right and let's go back to the customer table right click design view let's come down here let's just delete these fields all right goodbye we already have email address in there let's put in here web make that short text and uh let's put this let's put just a file location let's say you got some kind of file on them okay save that close this let's go back to our main menu and our customer form all right we already have an email address here so design view let's let's bring the email address down here let's bring them all so they're all buddies in the bottom okay just like that and let's go to add existing fields and let's bring in the web and the file location drop them right there okay slide you up like that bring these a little bit over like this little uh format painter action click click ok and actually i'm going to bring these down just a little bit more right down here okay because we're going to put some buttons next to them all right save that close it open it back up again and let's put some stuff in here all right so we got 599cd.com and file location let's say you got a file it could be a pdf file it could be a word document it could be an excel spreadsheet some some file you want to store you know maybe the resume like i said before with their account can you click a button and browse to a file yes you certainly can that's a little more involved i'll show that in the extended cut for the members for the rest of us you just got to find a file somewhere on your network here i got a blank document.pdf it's a pdf file of a blank page i use it for training all right and here's the folder location so see user's richard google drive i'll copy that and then blankdocument.pdf so i'll put that right in here paste and then blank document.pdf okay so i've got three things in here tab let's go back that's at the bottom of the tab order remember all right i got an email address a web page address and a file location in there now this is a pro because it's easy to edit right if if your user decides oh i goofed this should be yahoo that's not yahoo but i'm gonna put it back to gmail see how easy this is to come in here and edit you have to learn how to right click and and work with a menu you can just type over this you can just type over that okay you don't have to worry about them changing the display name so it's different this will work in a query this will work in a report this will work on a combo box this will work in a list box and brent yes you'll be able to set whatever color you want the only downside is you can't click on it and easily launch that application you can't launch your email program or launch all right your web browser but you can do it with one line of code and i'm gonna show you that line of code but first if you've never done any vba programming go watch my intro to vba i just re-recorded it yesterday it's brand new it's free it's on my website it's on youtube i'll put a link down below in the link section go watch this please if you've never done any vba programming we're only going to do one line of code but you got to know where to put that line of code all right i'm going to show it to you in just a second and a very brief advertisement if you really want to learn how to program and learn how to be an access developer check out my access developer level 1 class there's the link right there again i'll put a link down below check it out all right advertisements over let's see how to launch these hyperlinks so what i like to do is i like to put little buttons next to these things to launch okay if you want to launch the email program you click the button you want to launch this web page you click the button you want to open this document guess what you click the button hyperlink fields and access behave like no other fields do all the rest of these fields you want to change something you click on it you want to change this you click on it you want to change fort you highlight it and type something in why should these hyperlink fields behave any differently you got to train your users for something totally weird all right if you want to edit this email address you just click here and edit the email address okay so to launch this stuff we'll put little buttons next to that that clearly indicate what to do remember you as a developer you want to build this system to be easy for your users okay so let's make a button let's do the web page first that's the easiest one right click design view command button drop it right down here now unfortunately what we need is not in the wizard so cancel i'm going to change the caption just say just go all right we're going to put pictures on this in a minute don't worry about that so slide that close a little bit like that let's open up the properties let's give it a good name let's call this web button all right let's right click go to build event that opens up my code builder and i see i got some code fragments in here let me just delete that all right so i'm down here on the bottom and my web button click and if you watched intro to vba you know exactly what i'm doing here's the command that you need to launch a web page application dot follow hyperlink then the address where's the address it's in the website field which for us is just called web that's it save it now there's one more little thing we have to do but i just want you to see what happens first because you're going to make this mistake i made this too when i first started all right so come back over here close this close this all right open it up and hit go and this comes up potential security concern has been identified the location may be unsafe all right basically access doesn't know where this is because we didn't tell it where to go specifically and for those of you who know web page addresses you have to put a little bit something in front of it okay you have to come in here and go http or https colon slash slash and then your address all right if you want to make sure you're safe you don't need to put the s on there my site's all on s because it's a secure site but most a lot of sites aren't so you just do this you'll be defaulted to a basic web page address now this assumes the user hasn't put http colon slash in all the web page addresses if you want to make sure that it covers both instances you have to put a little if then in here i'll cover this in the extended cut but for the rest of you just make sure you don't have this on the beginning of your web page addresses all right save it now come back over here and now click the button and it's loading on my other screen there it is it popped right up okay i just i got four monitors so it came up on a different monitor all right let's do the email one all right right click design view take this guy copy paste slide up here probably make these a little bit smaller right like that like that make this guy like that all right let's change this guy's name to email button all right right click build event come in here i'm going to copy this okay now instead of http colon slash slash email is mail to colon that's it and then the email field is what just email all right so come over here and change web to email and that will properly format an email address most of the time people won't put mail to in front of their email addresses they'll just type in the address save it come back over here shut it down open it up and click and there we go it launched my email program which for me is just my web browser all right last one is the file location so copy and paste again copy paste copy paste there we go this will be the file button okay right click build event again application dot follow hyperlink and this one is literally just file location you have to put anything in front of this one because that assumes you gave it a complete path right see colon backslash something backslash something or somewhere on your network right sir backslash backslash server whatever okay give it a valid file name or folder name you can use this to open a folder too if each one of your customers has their own folder where all their documents are right if i hit go right now and there it is it opened up there it was maximized as you can see it's blankdocument.pdf it's a blank document it's just a big white page but it's a pdf file it's valid okay but if i get rid of that file right and then i hit go it just opens up my google drive folder see you can give it a folder too want to make these look pretty just come in here design view click on the button for email go to where's the picture right there picture property there's a whole bunch of little pictures in here you can pick from for email i like to use envelopers envelope there you are envelope hit okay there's a little envelope picture for web there's a globe in there i think it's called world let's pick that one i think that's in the bottom down here world yup there it is see and then that last one file location there's a couple different ones you can use um if if you're dealing with all word documents there's ms word document where yeah there's a whole bunch of stuff in here now these might you may or may not have different pictures in here depending on the version of access you have installed like right here ms word document you know if it's excel spreadsheets um and and you don't have to specify the file type i'm just saying if this is always an excel spreadsheet feel free to use the excel button right i am going to use what do i usually do um let's see ms access form is pretty good there's also notepad icon that's kind of neat there's a pdf one in here too where's pdf right there all right that's not bad pick that one right see there you go little pictures now isn't this better right and less confusing for your user see they can come in here type type in an email address that's a standard text you know field a web page address and they want to launch it they click the button right there you go i think this is a much more user friendly solution than trying to train them to work with hyperlink fields which i think is a pain i hate i hate teaching hyperlink fields this way these behave just like normal fields okay if you want to learn more about how to deal with hyperlinks in my extended cut for members i will show you a how to intercept any hyperlinks that might have http or https in front of them if someone copies and pastes it into your form which i do a lot if i'm on a web page i want to put up my database i'll copy the whole address bar so i'll show you how to cut that off of there and deal with that and i'll show you how to fix a table that might be built wrong you might have built your database using hyperlinks all right you might not have known better i have been guilty of that in the past myself all right so is brent okay so i'll show you how to fix that with a function and an update query and we could take our old hyperlink fields and turn them into standard plain text fields that are much much easier to work with as you've seen that's all covered in the extended cut for members silver members and up get access to all of my extended cut videos i think we're approaching 200 of them now so there's lots of stuff for you to learn and of course gold members can download these databases how do you become a member click the join button below the video after you click the join button you'll see a list of all the different types of membership levels that are available silver members and up will get access to all of the extended cut tech help videos live video and chat sessions and more gold members get access to a download folder containing all the sample databases that i build in my tech help videos plus my code vault where i keep tons of different functions that i use platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses these are the full-length courses found on my website and not just for access i also teach word excel visual basic asp and lots more but don't worry these free tech help videos are going to keep coming as long as you keep watching them i'll keep making more if you like this video please give me a thumbs up and feel free to post any comments that you have i do read them all make sure you subscribe to my channel which is completely free and click the bell icon and select all to receive notifications when new videos are posted click on the show more link below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a new video click on the link to join my mailing list now if you have not yet tried my free access level one course check it out now it covers all the basics of building databases with access it's over three hours long you can find it on my website or on my youtube channel and if you like level one level two is just one dollar and it's also free for all members of my youtube channel at any level want to have your question answered in a video just like this one visit my tech help page and you can send me your question there click here to watch my free access beginner level one course more of my tech help videos or to subscribe to my channel thanks for watching this video from accesslearningzone.com

Channel: Computer Learning Zone

Views: 3,164

Rating: 4.9130435 out of 5

Keywords: microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, ms access hyperlink field, hyperlink color, display text, hyperlink to file, hyperlink button, hyperlink to open, hyperlinkpart, hyperlink alternative

Id: StFstslhzhs

Length: 27min 44sec (1664 seconds)

Published: Fri Aug 20 2021