Building out hyperlink with VBA

[rhewitt]

Hello All,



I'm trying to build out a hyperlink by concatenating a base hyperlink with a UID stored in a field. I assign the following code to a small symbol so it doesn't take up a ton of space on my report.

Private Sub link_Click()
'On Error GoTo err_link ** Commented this out because it was going to error message every time

txtURL = "http://ecos.fws.gov/speciesProfile/profile/speciesProfile.action?spcode=" & Me.ECOScode

DoCmd.Hourglass True
FollowHyperlink txtURL
DoCmd.Hourglass False

'Exit Sub


'err_link:
'    DoCmd.Hourglass False
'    MsgBox ("Sorry, there isn't currently a species profile for " & CommonName & "."), vbOKOnly, "Warning!"

End Sub

I'm getting error message 2465: MS Access can't find the field 'ECOScode' referred to in your expression, but it's there!

[June7]

Where are you running this code from? Button on report? Is a textbox on report bound to ECOScode field?

Want to provide db for analysis? Follow instructions at bottom of my post.

Review http://allenbrowne.com/func-GoHyperlink.html

[rpeare]

If you're clicking something on a form make sure the item on the form is actually NAMED (not the CONTROL SOURCE, the NAME) ECOSCODE.

I tested your code with a empty string and it worked fine though I would think you'd want something more like:

Private Sub link_Click() 

On Error GoTo err_link '** Commented this out because it was going to error message every time 
if isnull(ECOSCODE) or trim(ECOSCODE) = "" then
    Msgbox "No Species Profile Available"
else     txtURL = "http://ecos.fws.gov/speciesProfile/profile/speciesProfile.action?spcode=" & ECOScode      
    DoCmd.Hourglass True     
    FollowHyperlink txtURL     
    DoCmd.Hourglass False 
endif 

Exit Sub  

err_link: 
debug.print err.number & ": " & err.description 
End Sub

[rhewitt]

@June7 -- Allen Browne is a lifesaver, I've wound up on his blog more than once. That's quite a function he wrote, it's amazing that he's single-handedly more proficient than an entire team of Microsoft programmers.

I received a database from a private company that includes a small icon that is used for the link. The icon appears to be a special character: ="" (copy, paste). Within the access Control Source property it looks like a bullet, or a multiplication operator (the dot not an x).

Here's the image:

I like this method because it's easy to tuck a long link into a small icon, and I have multiple links for each row that I want to include.

I did everything the same way as they did in the database they sent, which is why I'm confused that it's not working.

[June7]

Never seen anything like this. If you want to provide dbs for analysis, follow instructions at bottom of my post.

[rhewitt]

Would it be possible to do with a button instead? I'd just like the layout to be standard instead of having a random looking 4 digit alphanumberic code as a hyperlink.

[June7]

Yes, code behind button could run your code or adapt Allen Browne's.

What is the '4 digit alphanumberic code'?

[rhewitt]

Basically the link is comprised of two parts. The 'base' url, and a unique identifier used in the database on the backend of the website. Since the base doesn't change it seems silly to store it hundreds of times. I'd rather generate the full URL with a button 'click' event that concatenates the base URL with the unique ID (which is just a combination of four letters or numbers).

[June7]

So is issue resolved?

[rhewitt]

No, the vba code I'm using still won't let me access the field that contains the code.

I'm using a button and concatenating the URL & Me.ECOScode, still getting the same error as reported earlier. It doesn't seem like there's a way to tie the button to the value of the ECOScode field. I must be missing something.

[June7]

My questions in post2 still unanswered. Also, looks like we will have to review db to be any further help.

[rhewitt]

Here's the DB again. Haven't prepared the values for the NatureServe link (again it should be a base URL + UID), but if you help me get the ECOS link working then I should be able to figure out the second.

Thanks,
Roy

[rpeare]

Can't view 2010, can you convert to a prior version.

[rhewitt]

.mdb Version:

[rpeare]

You didn't convert it you just put .mdb on the end (and it's still .mdb.accdb). To convert to a prior version click the windows icon in the upper left, hover over the SAVE AS option, there should be 3 - 4 options there, save it in anything prior to 2010 then zip up that converted file and upload it.