Automatically Creating Hyperlinks

[NeroMaj]

Okay, this should be a simple one but I am not finding the right sources through searching.

I have a form that is used to input records. One of the fields of the records is Permit #, which is a text field. Another field is Query Link field which is a hyperlink address that goes to a web based database.

What i want to happen is the hyperlink be automatically created in the AfterUpdate method of the Permit # field.

Right now, this is my general approach.

Get permit number from form.
Create hyperlink address from permit number (e.g. http://blah.blah.blah.blah.asp?Q=" & permitNumber)

set form field value to hyperlink (e.g. Me.QueryLink.Value = hyperlink)

This will change the "value" to the hyperlink but will not set the hyperlink address to the hyperlink so nothing happens when you click on it.

Any suggestions on how to set the HyperlinkAddress of the field?

 

[vbaInet]

On the Click event of the textbox use the Application.FollowHyperlink function instead. So:

If Not IsNull(Me![Permit #]) Then
     Application.FollowHyperlink "http://..."
End If

 

[boblarson]

I agree with vbaInet as I store hyperlinks as text (I don't mess with the Hyperlink data type as it is a pain to work with). Then in my control I just color it blue and underline it (yes, the mouse pointer doesn't change to a hand but that doesn't seem to mess up my users) and use the code that vbaInet posted in my click event of the control. I usually check, in code, to make sure there is something there before doing the FollowHyperlink.

 

[NeroMaj]

I agree that using this On Click method for the text box would be appropriate, however, the goal here is to create the hyperlinks so that they show up in queries later and can be followed.

This database is only used by the few people I work with and each of us has a customized query that we can view (We don't worry about the security issues with this set up since its only like 6 engineers that use it), however, the engineer that is responsible for adding new records to the database uses a form to do so.

When he adds the record on the form I want the link to the web database to be automatically generated as a hyperlink so that it can be followed from the query. Because of the necessity for a clickable hyperlink in the query, the on click work around is not applicable in this situation.

Update:

This is what I have gathered so far. I have successfully created and Update SQL statement that will add the hyperlink to the proper field in the table. However, I am unsure as to where this statement needs to go when adding a new record. I tried the after updat method for the Permit # text box, and I tried adding a command button to do it.

It seems as though Access does not recognize the newly entered record until it is no longer the active record, am I correct in assuming this? Where would you suggest adding the UPDATE SQL statement?

 

[vbaInet]

I agree that using this On Click method for the text box would be appropriate, however, the goal here is to create the hyperlinks so that they show up in queries later and can be followed.

When he adds the record on the form I want the link to the web database to be automatically generated as a hyperlink so that it can be followed from the query. Because of the necessity for a clickable hyperlink in the query, the on click work around is not applicable in this situation.

It is applicable. Use a form and set it's default property to Datasheet View which looks like a query. You will then be able to use the On Click event.

Update:

This is what I have gathered so far. I have successfully created and Update SQL statement that will add the hyperlink to the proper field in the table. However, I am unsure as to where this statement needs to go when adding a new record. I tried the after updat method for the Permit # text box, and I tried adding a command button to do it.

It seems as though Access does not recognize the newly entered record until it is no longer the active record, am I correct in assuming this? Where would you suggest adding the UPDATE SQL statement?

If this http://blah.blah.blah.blah.asp?Q is static I don't see any reason why you would want to save it in a field? It will just take up extra space. Use what has been suggested and concatenate it to [Permit #] whenever it is clicked.