Command button that locates a hyperlink in a table and then opens it

[mforema]

Hey Everybody, I have an URGENT REQUEST (I need a reply within 24 hours, or I lose my job) I need help writing vba code for a command button. I want this button to locate a hyperlink in a table (the hyperlinks have their own field) based on the record's name (the first field in the table). If the there is a hyperlink for the record, then I want it to be opened. If there is no hyperlink for the record, then I want a message box that states so.

My code so far is as follows:

Private Sub Command255_Clic k()
Dim x As Integer

If ([hyperlink exists]) Then
' Open hyperlink
Else
x = MsgBox("Sorry, link does not exist.", vbInformation, "No link found")
End If

End Sub

Obviously, my problems are that I cannot find a way to specify whether a link exists in the table, and I cannot find a way to locate the link and open it for the user.

I've created another field in the table that has a check box to indicate whether a link exists for each record or not. I don't know if that was needed or not.

Can anyone help me?

[Rabbit]

Hey Everybody, I have an URGENT REQUEST (I need a reply within 24 hours, or I lose my job) I need help writing vba code for a command button. I want this button to locate a hyperlink in a table (the hyperlinks have their own field) based on the record's name (the first field in the table). If the there is a hyperlink for the record, then I want it to be opened. If there is no hyperlink for the record, then I want a message box that states so.

My code so far is as follows:

Private Sub Command255_Clic k()
Dim x As Integer

If ([hyperlink exists]) Then
' Open hyperlink
Else
x = MsgBox("Sorry, link does not exist.", vbInformation, "No link found")
End If

End Sub

Obviously, my problems are that I cannot find a way to specify whether a link exists in the table, and I cannot find a way to locate the link and open it for the user.

I've created another field in the table that has a check box to indicate whether a link exists for each record or not. I don't know if that was needed or not.

Can anyone help me?

There's no context.

What is the recordsource of this form, is it the table with the hyperlinks?
What is the format of the hyperlinks?
When you click the button are you on the record you want to look up?
If not, how do you know which hyperlink you want to look up?
What tables are involved? What is the metadata of these tables?
If there's more than one table what is the primary key? The foreign key?
Is it a one to many relationship?

[mforema]

There's no context.

What is the recordsource of this form, is it the table with the hyperlinks?
What is the format of the hyperlinks?
When you click the button are you on the record you want to look up?
If not, how do you know which hyperlink you want to look up?
What tables are involved? What is the metadata of these tables?
If there's more than one table what is the primary key? The foreign key?
Is it a one to many relationship?

I'm sorry. I'm very new to Access and vba lingo. I have acquired an equipment database that is big and (apparently to me) complex.

There are 15 tables that the form is getting records from. The table with the hyperlinks is a new table that I created; I already related it to the "master list" table that the other tables are related to. They are all one to many relationships. The foreign key in the hyperlink table is the "Column Name" (the distillation columns at our plant). The "Column Name" is the primary key in the "master list" table. Again, it is a one to many relationship.

An example of one hyperlink is as follows:

\\jaxfsp01\grou ps\ProcEng\Dist illation Equipment Database\Gamma Ray Scans\O Column Preliminary Report.pdf

These hyperlinks are located in a directory on our company's G:\ drive, but they are not just .pdf files; there are also microsoft documents, such as .doc, .ppt, etc. The links work perfectly. I already checked them.

When I click the button, I am on the record that I want to look up.

I don't know what metadata means, but the tables have numbers, text, and hyperlinks in them.

I hope I clarified everything. Please let me know if you have anymore questions.

And thank you very much for helping me!

[Rabbit]

Try this:

FollowHyperlink DLookup("HyperlinkField", "HyperlinkTable", "ForeignKey = '" & Me.PrimaryKey & "'")
 

You'll need to change HyperlinkField, HyperlinkTable, ForeignKey, PrimaryKey to reflect your names. They PK/FK is text right? If not, leave off the stuff with the single quotes.

[mforema]

Try this:

FollowHyperlink DLookup("HyperlinkField", "HyperlinkTable", "ForeignKey = '" & Me.PrimaryKey & "'")
 

You'll need to change HyperlinkField, HyperlinkTable, ForeignKey, PrimaryKey to reflect your names. They PK/FK is text right? If not, leave off the stuff with the single quotes.

Thanks! But I keep getting an error message that says, "Method or Data Member Not Found," and then it highlights the Me.ID in the code below:

Private Sub Command255_Clic k()
FollowHyperlink DLookup("Gamma Scan Link", "Gamma Scans", "Column Name = '" & Me.ID & "'")
End Sub

(Of course, the FollowHyperlink code is on one line)

"Column Name" is the foreign key in the HyperlinkTable( aka, Gamma Scans), and ID is the primary key of the HyperlinkTable. What did I do wrong?

[Rabbit]

Thanks! But I keep getting an error message that says, "Method or Data Member Not Found," and then it highlights the Me.ID in the code below:

Private Sub Command255_Clic k()
FollowHyperlink DLookup("Gamma Scan Link", "Gamma Scans", "Column Name = '" & Me.ID & "'")
End Sub

(Of course, the FollowHyperlink code is on one line)

"Column Name" is the foreign key in the HyperlinkTable( aka, Gamma Scans), and ID is the primary key of the HyperlinkTable. What did I do wrong?

Usually that means you mispelled something. It's kind of hard to mispell ID though. What's the form's recordsource?

[mforema]

Usually that means you mispelled something. It's kind of hard to mispell ID though. What's the form's recordsource?

The Form's recordsource is "General Column Detail Query." So, I have an idea...maybe I haven't added the HyperlinkTable to the Query...how do I do that without screwing something else up?

[Rabbit]

The Form's recordsource is "General Column Detail Query." So, I have an idea...maybe I haven't added the HyperlinkTable to the Query...how do I do that without screwing something else up?

You don't need to add the hyperlink table to the query, but you do need to add the ID to the query. What's the SQL of the query?

[mforema]

You don't need to add the hyperlink table to the query, but you do need to add the ID to the query. What's the SQL of the query?

SQL of Query:

SELECT MasterColumnLis t.[Column Name], MasterColumnLis t.Plant, [A - General Details].*, [B - Column Internals - Packing Details].*, [C - Column Internals - Distributor Details].*, [D - Stillpot Details].*, *
FROM MasterPlantList INNER JOIN ((((MasterColum nList INNER JOIN [A - General Details] ON MasterColumnLis t.[Column Name] = [A - General Details].Column) INNER JOIN [B - Column Internals - Packing Details] ON MasterColumnLis t.[Column Name] = [B - Column Internals - Packing Details].Column) INNER JOIN [C - Column Internals - Distributor Details] ON MasterColumnLis t.[Column Name] = [C - Column Internals - Distributor Details].Column) INNER JOIN [D - Stillpot Details] ON MasterColumnLis t.[Column Name] = [D - Stillpot Details].Column) ON MasterPlantList .Plant = MasterColumnLis t.Plant;

I know that's a lot, but I'm not sure what piece of it you need.

[Rabbit]

Try:

Private Sub Command255_Click()
FollowHyperlink DLookup("[Gamma Scan Link]", "[Gamma Scans]", "[Column Name] = '" & Me.Column_Name & "'")
End Sub
 

If you have spaces in your names you have to surround it using [ ]