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

No video

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

Рет қаралды 30,423

Күн бұрын

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 hyperlink destinations.
Brent from Tracy, California (a Gold Member) asks: I have a list box where I am trying to change the font color. I have selected the list box in design mode and selected 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 fields to be the same color?
Silver Members and up get access to an Extended Cut of this video. Members will learn how to deal with web page addresses that may or may not have in front of them. I will then show you how to FIX a database that may have been made with Hyperlink fields. To do that, we will learn how to tear apart the complex Hyperlink field type into its individual components with the HyperlinkPart function. We will then use an update query to copy the actual Hyperlink to the appropriate text field.
MEMBERS VIDEO:
• Hyperlink Fields in Ac...
BECOME A MEMBER:
KZfaq: / @599cd
or My Site: 599cd.com/THMe...
LEARN MORE:
599cd.com/Hype...
LINKS:
Evil Access Stuff: 599cd.com/Evil
Intro to VBA: 599cd.com/Intr...
Access Developer 1: 599cd.com/ACD1
COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Cont...
Order Entry & Invoicing: 599cd.com/Invo...
More Access Templates: 599cd.com/Acce...
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/Tech...
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.o...
products.offic...
microsoft.com/...
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
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

Пікірлер: 58

@rabidfollower 2 жыл бұрын

My method to use the hyperlink feature without using hyperlink fields in tables is set a textbox's "Display as Hyperlink" property to "Always." In VBA, it's TextBox.DisplayAsHyperlink = 1. That way, regular text data is shown as a hyperlink with the finger cursor and everything. And I use the textbox's OnClick event to execute the hyperlinking. I actually make the textbox uneditable by enabling the Locked property to prevent accidental changes by the users. In fact, instead of the "Go" button (21:00), I have the users click an "Edit" button before they can make changes. But of course, everyone's need is different, and your method will suit most users.

@599CD 2 жыл бұрын

That sounds like a very elegant solution as well. :)

@charleswhite2941 3 жыл бұрын

Thank you, i have been fighting with this for months to get the hyperlink to files work properly.... i love the button idea.

@599CD 3 жыл бұрын

Awesome. Glad to help. Yeah, I hate the hyperlink field type... if you didn't get that already. :)

@MalcolmDavidson-hp5dz Жыл бұрын

This is an excellent tutorial and provided me with the information I needed. Well done, thank you.

@599CD Жыл бұрын

Welcome

@johanderu788 2 жыл бұрын

Very well explained thank you very much. Surely works for me. Picture Button is great.

@599CD 2 жыл бұрын

Glad it helped

@arthurbezuijen1434 2 жыл бұрын

Great! Very clear teaching.... :-) Not a tiny bit I did not understood!

@599CD 2 жыл бұрын

Glad you enjoyed it!

@jeromecahagnaan3567 Жыл бұрын

thank you, Sir, it's really helpful.

@599CD Жыл бұрын

Glad to hear that

@alveocampo8491 2 жыл бұрын

Hi. thank you for this. but im getting error cannot open the specified file. i can open the folder but not the pdf

@599CD 2 жыл бұрын

Something is probably spelled wrong.

@alveocampo8491 2 жыл бұрын

@@599CD i renamed the file name and still giving runtime error - 490

@stafbrosens6902 3 жыл бұрын

How to make a mail merge with mail adress (from access short text type) in excel? Or how to convert excel (short) text to hyperlink?

@599CD 3 жыл бұрын

I don't understand what you're asking. Why are you bothering with Excel to do a mail merge if your data is already in Access?

@stafbrosens6902 3 жыл бұрын

@@599CD See: Hyperlink Fields in Microsoft Access and Why You Should NOT Use Them

@stuartround 2 жыл бұрын

Also not quite sure what you're asking, but do you mean adding a mailto: prefix to the email address so that it becomes a mailing hyperlink?

@andrechapman Жыл бұрын

I am trying to use file location but because I am using a ShareList it will not transfer to the site contents error message says "There was an error copying data to a SharePoint list. URL data '............' in field '............' could not be sent to the server. The URL may be invalid or longer than 255 characters. "

@599CD Жыл бұрын

Bummer. Not familiar with that problem.

@m.albayati5925 3 жыл бұрын

Thank you so much

@599CD 3 жыл бұрын

You're most welcome

@costastsantires8098 10 ай бұрын

And what should we do if we need to open a Outlook Item (.msg) ? ?

@599CD 10 ай бұрын

That's a whole different ball of wax. 599cd.com/ask

@marielleorfila324 Жыл бұрын

Hello i have a question in email hyperlink, is there any chance when i click the hyper link button it will open to google browser in my gmail account and opened a composed message which the email encoded in my database is in the To: section?

@robertodc62 Жыл бұрын

richard need help this technic work in a local network ?

@janvanantwerp6574 Жыл бұрын

Yup!

@ivanuamusse 2 жыл бұрын

Good explanation.is possible to make this button to open to more the one location or to open a selection point as a map location of one place or region?

@599CD 2 жыл бұрын

599cd.com/MapLocation

@siujasper5050 Жыл бұрын

I actually encountered another problem using hyperlink. When I have to upload tons of past data with hyperlink into the database, simply copy and paste from excel to access simply does not work Am I doing it the wrong way or if there are any other better ways to do it? Thanks.

@599CD Жыл бұрын

I get a ton of questions every day, and I don't have time to answer them all here on KZfaq. Feel free to submit your question on my website at: 599cd.com/AskYT

@Wolfgang1224 2 жыл бұрын

This might be in the wrong place, but I'm trying to get Access to output a report to Word with clickable emails. It is for the body of an batch email that provides information to the people I send the emails to. Access outputs to the rtf fine when I open it with Wordpad. However, when I try to load it into word, I get "Error! Hyperlink is invalid". Word is far more complex than Wordpad so I just don't understand how clickable emails work in Wordpad, but not Word. I can't take the time to go through this email each time I generate it to manually add hyperlinks to the email addresses. Any ideas?

@599CD 2 жыл бұрын

Why not just export and email a PDF?

@Wolfgang1224 2 жыл бұрын

@@599CD Lazy users want to click in the email & not open an attachment. I actually tested different outputs & the way I fixed it is to export to HTML, past that into Word, hit enter & delete at the end of the email. That turns it into an active hyperlink & then removes the carriage return. A bit wonky, but it works without too much effort.

@stuartround 2 жыл бұрын

You shouldn't have to do enter and delete if you export a full hyperlink to word in html. email@domain.com. The part after href is the actual link, the part between the >and< is the text to display. You'd need to add this text in the query that generates the report, or you could do it in the report itself.

@tutsecret499 3 жыл бұрын

How can I put data on one specific field AGE. The data is from another field Automated Age that I created on the query with automated value. The specific field AGE belongs to the table. The only way I can put the data from the query is copy from the field from query and paste on the AGE field that is present on the table and on the query too. I want to avoid this copy and paste step. Is there a another way, when the query field automated age creates the age, I want the AGE field gets updated. The reason I don't want to automate the value on the AGE field is because of annual merging database that will cause error, saying that the AGE field does not match fields, it's my assumption, I did not test yet the merging to see if conflicts if I create the automation straight on the AGE field. Or another alternative, is to change the AGE field on the host merging database so that does not conflict with the original AGE field from the source database. For me to do that, my dept has to approve to change the AGE field property that was originally created by somebody else. Also I tried to make another query to update the table such as "update the table, AGE field as what contains on the Automated Age field on another query. But I did not get right yet. There might be a way to do it eliminating all these steps. I don't want user to copy and paste on AGE field, I want the AGE field get updated automatically. This Age field property is formatted as short text to input age manually such etc 60, 50, 20, etc

@599CD 3 жыл бұрын

Don't store age. Store DOB. Calculate age on the fly. 599cd.com/age

@jaswindersahota6329 2 жыл бұрын

in the form i have multi hyperlinks of PDF files. is any chance i can print all together.

@599CD 2 жыл бұрын

Sure. Use a recordset loop.

@jaswindersahota6329 2 жыл бұрын

in the access i like to print multi PDf files. please help. i am member

@nb1ist 2 жыл бұрын

how this button can work when each record has a different hyperlink address? this go button will use only for a single URL address? please reply :) 🙂🙂🙂

@599CD 2 жыл бұрын

It will go to whatever address you type in the field.

@saneneweyes6189 2 жыл бұрын

I was trying to make a button to do a search or "check record status" by having the first part of the URL combined with a specific field number and then put the rest of the URL. Sort of a "HTTP link 'table data' &what2do=&get+record" (or whatever the rest of the URL would be...) to run a query or check on an external site. that possible? I tried to do another & following the field name, but that didn't seem to work.

@saneneweyes6189 2 жыл бұрын

I sort of figured out a workaround, but it doesn't seem like Access likes to play nice with substrings ending with an equal sign.

@599CD 2 жыл бұрын

I don't follow. Why would your URL end with an equal sign? 599cd.com/user-check.asp?ID=123456&KEY=something You need key/value pairs.

@saneneweyes6189 2 жыл бұрын

@@599CD the first part of the url ends with an equal and the expression / field would follow the equal.

@stuartround 2 жыл бұрын

Do this in VBA, if the url is always the same that's easy, just construct the link in VBA as text, otherwise take the value from the url field, and then add the parameter to the url from the id field. Then follow the link to that. Even nicer, use a webbrowser control in Access so you can show the results on the same form!

@yassirbelbekri828 2 жыл бұрын

Hi Sir,i hope you are fine. I have a form where i have a text zone and a botton. I need a vba code to use in that botton which help me to only open the file location that i have its path in the text zone and only select the file and not open it (the folder has many pdf files, so i want the code to only select the file for me to see it and not open it) Let's say the text zone is named "txtfilepath" and the botton is named "openfilelocation". Could you help me to do that please?

@599CD 2 жыл бұрын

You can submit questions here: 599cd.com/TH

@keeranslade6392 2 жыл бұрын

Great video, great way to get around hyperlink fields. Works great for me but I do get an error when I use a link directly to a Sharepoint folder or file. Any ideas?

@599CD 2 жыл бұрын

Haven't tried that. Sorry. Not currently using Sharepoint.

@BackyardAstrophysics 2 жыл бұрын

@Keeran did you ever find a solution to this? I am having the same trouble trying to open a OneDrive file location. I receive a Run Time Error 8 pop up but other websites work fine.