Visual Basic Chr() usage and formatting of hyperlinks

[carterlangley]

Hi all,



OK, so here is my code.

="Home Phone " & [HomePhone] & Chr(13)+Chr(10) & "Mobile Phone " & [MobileNumber] & Chr(13)+Chr(10) & "Email Address " & [ClientEmailAddress]

So far so good. It brings it all in. Problem I'm having is with the display of the email address. It has that annoying 'mailto:'. What would be ideal would be the ability to display the hyperlink, whether email or web, as a hyperlink instead of just plain text. Is this possible using the above code? Must be a way to do this. I'm going to be adjusting the code to include the iif statements to cover myself for information that is not entered.

Hope you geniuses out there can help!

[isladogs]

If you want to continue using a hyperlink field, you could try using Mid([ClientEmailAddress],8) to lose the first part but I suspect it will no longer act as a hyperlink

[carterlangley]

Hi ridders52

Apparently there is a hyperlink.address property in access.

so, format is something like expression.Address
I'll try using your mid statement as the expression part and append the .Address to it and see if that works. I'll get back to you on the result once my old brain has figured out the syntax.

[isladogs]

You didn't say how and where you want to use this.

Personally, if this is on a form or report, I would but use 3 textboxes and labels for the captions
Hyperlink fields are problematic as they consist of two parts
I would instead use a text field for the email address and add Application.FollowHyperlink code to the textbox click event
You can easily format it to look like a hyperlink field.

[carterlangley]

Nope, that didn't work. Still getting that mailto: and it isn't behaving as a hyperlink (one click sends you to the ether). I think I am being a bit ambitious here for Access. For email addresses and hyperlinks I think the best and easiest is to drag and drop from the field list onto the report or form. There is probably a workaround using a custom function but that is beyond my capabilities at the moment. I don't want to get into re-inventing the wheel quite yet hahahahaha

[isladogs]

Have you tried doing what I suggested in my second reply as that will definitely work

[carterlangley]

Yes, I tried that but couldn't get it to work or display properly.
I seem to recall that hyperlinks do consist of two parts (even 3 maybe?), but it has been at least 20 years since I've programmed in VB and I've lost my touch. You're saying format the entry in the table as plain text and then use the click event to tell Access to treat it as a hyperlink. Makes sense that way, getting rid of the different parts. This is going onto a report, but I suppose the same rules will apply for forms as well in the most cases. Thanks for helping out, hopefully something in this old brain of mine clicks and I start remembering more of VB.

[isladogs]

Here's an example from one of my apps:



The third line down has a textbox which uses a function GetSupportEmail to retrieve an email address from a settings table
It is formatted to look like a hyperlink
NOTE: The actual email address could be used insteador a field containing the address

Clicking on that textbox runs this code:

Private Sub txtEmail_Click()    Application.FollowHyperlink "mailto:" & GetSupportEmail()
End Sub

This opens the default email application with the address in the 'To' field of a blank email

HTH