MS Access HyperlinkAddress carries over the last used URL

Question

I have a form where the user enters a company's website (URL) into a text box. A command button is next to the text box so that the URL entered can be opened in a browser. It works perfectly when there is a URL in the text box. However, when the text box is blank, the MsgBox to "try again" appears (as it should), but instead of stopping the subroutine after the user clicks OK, it opens the most recently opened URL. How do I get the subroutine to simply stop when the MsgBox OK button is clicked? I am guessing the URL is being stored somewhere. Where? And how can I clear it?

Private Sub cmd_websitelink_Click()

'***********************************************
'** Opens browser for Website Link
'***********************************************

DoCmd.SetWarnings False

Dim sitestring As String

sitestring = Me.Website & vbNullString

If Len(sitestring) = 0 Then GoTo ErrorHandler

On Error GoTo ErrorHandler
    
    Dim ctl As CommandButton
    Set ctl = Me!cmd_websitelink
    With ctl
        .HyperlinkAddress = sitestring
    End With

Exit Sub

GoTo Finish

ErrorHandler:

MsgBox "Enter a valid URL in the website box and try again."

Finish:

End Sub

score 1 · Accepted Answer · answered Apr 05 '21 at 16:46

1

You set the .HyperlinkAddress property of the button in your procedure.

This property stays set until you close the form or your overwrite the property.

So you need to add this to the error case:

Me!cmd_websitelink.HyperlinkAddress = ""

Instead of all this, you could simply open the hyperlink in the Click event, see here:
How to open a URL from MS Access with parameters

answered Apr 05 '21 at 16:46

Andre

26,751
7
36
80

Thanks for the suggestion of adding the line to blank out the URL. Worked like a charm. – Tim Z Apr 05 '21 at 17:08

Vexen Crabtree · Answer 2 · 2021-04-05T16:50:29.570

I can't tell from your code where Me.Website gets set (is it from a DB field? Is all this code in a form linked to a record? Is Me.Website a fieldname linked to the textbox where you say user is putting text?)

I'm going to guess that the cause of the problem is that if the user changes the text in the textbox but it hasn't yet saved, and you don't autosave when the txtbox looses focus, then, the form is still using the original value from the textbox. To fix it, use .text instead of .value .

Instead of this:

sitestring = Me.Website & vbNullString

Do this:

sitestring = nz(Me.Website.text,"")

.text captures the current text in the textbox, regardless of whether it's been committed where .value only retrieves the committed value. And the code above also covers against the form or record having a null value, by using nz to replace null value with an empty string.

Also, as you're dealing with strings, might as well use string logic. Instead of this:

If Len(sitestring) = 0 Then GoTo ErrorHandler

Do this:

If sitestring = "" Then GoTo ErrorHandler

Also: You're running "Exit Sub" before you hit "Goto Finish"; remove one of those two methods of exitting the sub.

Also, if all you're doing is setting the hyperlink address, then nearly all your code can be simplified as this, using proper indentation to make it tidy:

Private Sub cmd_websitelink_Click()
    '***********************************************
    '** Opens browser for Website Link
    '***********************************************
    Me.txtWebsite.SetFocus              'Else VBA errors when accessing .text
    sitestring = nz(Me.Website.text,"") 'NZ to replace null values with ""
    if sitestring = "" Then
        MsgBox "Enter a valid URL in the website box and try again."
    else
        Me!cmd_websitelink.HyperlinkAddress = sitestring
    end if
end sub

(From comments) To test if the _Click() command button is using the old version of .HyperlinkAddress as its source, instead of this:

Me!cmd_websitelink.HyperlinkAddress = sitestring

Try this:

explorer.exe sitestring

_I can't tell from your code where Me.Website gets set (is it from a DB field?_ Yes, from a table called tbl_company_record1. _Is all this code in a form linked to a record?_ Yes. `SELECT tbl_company_record1.*, tbl_company_location1.[Street 1] FROM tbl_company_record1 LEFT JOIN tbl_company_location1 ON tbl_company_record1.[Company ID] = tbl_company_location1.[Company ID]`; — Tim Z, Apr 05 '21 at 16:36
_Is Me.Website a fieldname linked to the textbox where you say user is putting text?_ Yes. However, since your response, I've changed the name of the text box from Website to txtWebsite and it is linked to the table field named Website. I have tried the following: your suggested vba, added `Me.txtWebsite.SetFocus` (to avert the Runtime error 2185), and changed the text box name to `txtWebsite`. It still behaves the same way. — Tim Z, Apr 05 '21 at 16:37
Hmm. So, you're setting the .hyperlink for the button that is being pressed, and it is still opening the old one... I suspect that the _Click() event is accessing the old value for shell "explorer.exe URL". Instead of using Me!cmd_websitelink.HyperlinkAddress = sitestring , try shell "explorer.exe sitestring". I'll add that to answer, one sec. — Vexen Crabtree, Apr 05 '21 at 16:46
Thanks for you help! I had been wrestling with this for too long. By adding the line, Me!cmd_websitelink.HyperlinkAddress = "" as suggested below solved the issue of the URL carrying over. — Tim Z, Apr 05 '21 at 17:07

MS Access HyperlinkAddress carries over the last used URL

2 Answers2