By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,696 Members | 1,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,696 IT Pros & Developers. It's quick & easy.

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

P: 72
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_Click()
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?
May 31 '07 #1
Share this Question
Share on Google+
22 Replies


Rabbit
Expert Mod 10K+
P: 12,427
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_Click()
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?
May 31 '07 #2

P: 72
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\groups\ProcEng\Distillation 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!
May 31 '07 #3

Rabbit
Expert Mod 10K+
P: 12,427
Try this:
Expand|Select|Wrap|Line Numbers
  1. FollowHyperlink DLookup("HyperlinkField", "HyperlinkTable", "ForeignKey = '" & Me.PrimaryKey & "'")
  2.  
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.
May 31 '07 #4

P: 72
Try this:
Expand|Select|Wrap|Line Numbers
  1. FollowHyperlink DLookup("HyperlinkField", "HyperlinkTable", "ForeignKey = '" & Me.PrimaryKey & "'")
  2.  
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_Click()
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?
May 31 '07 #5

Rabbit
Expert Mod 10K+
P: 12,427
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_Click()
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?
May 31 '07 #6

P: 72
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?
May 31 '07 #7

Rabbit
Expert Mod 10K+
P: 12,427
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?
May 31 '07 #8

P: 72
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 MasterColumnList.[Column Name], MasterColumnList.Plant, [A - General Details].*, [B - Column Internals - Packing Details].*, [C - Column Internals - Distributor Details].*, [D - Stillpot Details].*, *
FROM MasterPlantList INNER JOIN ((((MasterColumnList INNER JOIN [A - General Details] ON MasterColumnList.[Column Name] = [A - General Details].Column) INNER JOIN [B - Column Internals - Packing Details] ON MasterColumnList.[Column Name] = [B - Column Internals - Packing Details].Column) INNER JOIN [C - Column Internals - Distributor Details] ON MasterColumnList.[Column Name] = [C - Column Internals - Distributor Details].Column) INNER JOIN [D - Stillpot Details] ON MasterColumnList.[Column Name] = [D - Stillpot Details].Column) ON MasterPlantList.Plant = MasterColumnList.Plant;

I know that's a lot, but I'm not sure what piece of it you need.
May 31 '07 #9

Rabbit
Expert Mod 10K+
P: 12,427
Try:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command255_Click()
  2. FollowHyperlink DLookup("[Gamma Scan Link]", "[Gamma Scans]", "[Column Name] = '" & Me.Column_Name & "'")
  3. End Sub
  4.  
If you have spaces in your names you have to surround it using [ ]
May 31 '07 #10

P: 72
Try:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command255_Click()
  2. FollowHyperlink DLookup("[Gamma Scan Link]", "[Gamma Scans]", "[Column Name] = '" & Me.Column_Name & "'")
  3. End Sub
  4.  
If you have spaces in your names you have to surround it using [ ]
You are awesome! It works great!

Here is the final code with the If...Else statement, if you're interested:

Private Sub Command255_Click()
Dim x As Boolean
Dim y As Integer

x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'") ' Scan either exists or it doesn't.
If (x = 0) Then ' if scan does not exist...
y = MsgBox("Sorry, no scan found.", vbInformation, "Sorry")
Else ' scan does exist; open linked file
FollowHyperlink (DLookup("[Gamma Scan Link]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'"))
End If

End Sub

THANK YOU SO MUCH FOR YOUR HELP AND PATIENCE!!!
May 31 '07 #11

Rabbit
Expert Mod 10K+
P: 12,427
Glad to help. Good Luck.
Jun 1 '07 #12

P: 72
Glad to help. Good Luck.
The HyperlinkTable sometimes has multiple links for the same "Column Name." The code that you gave me only opens the first link pertaining to the "Column Name." How do I ask for all of the pertinant links to be opened? A copy of my code is found below:

Private Sub Command255_Click()
Dim x As Boolean
Dim y As Integer

x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'") ' Check if a link exists in the Gamma Scans table.
If (x = 0) Then ' If link does not exist, then show message below.
y = MsgBox("Sorry, no scan found.", vbInformation, "Sorry")
Else ' If link does exist, then follow the link to open the file.
FollowHyperlink (DLookup("[Gamma Scan Link]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'"))
End If

End Sub
Jun 1 '07 #13

Rabbit
Expert Mod 10K+
P: 12,427
The HyperlinkTable sometimes has multiple links for the same "Column Name." The code that you gave me only opens the first link pertaining to the "Column Name." How do I ask for all of the pertinant links to be opened? A copy of my code is found below:

Private Sub Command255_Click()
Dim x As Boolean
Dim y As Integer

x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'") ' Check if a link exists in the Gamma Scans table.
If (x = 0) Then ' If link does not exist, then show message below.
y = MsgBox("Sorry, no scan found.", vbInformation, "Sorry")
Else ' If link does exist, then follow the link to open the file.
FollowHyperlink (DLookup("[Gamma Scan Link]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'"))
End If

End Sub
I assume that each hyperlink would have it's own record. Rather than a DLookup, you'll need to create a recordset to return all the records and then use a loop to go through each record and open the link.
Jun 1 '07 #14

P: 72
I assume that each hyperlink would have it's own record. Rather than a DLookup, you'll need to create a recordset to return all the records and then use a loop to go through each record and open the link.
How would that look if it were applied to my code? I don't know how to create a recordset or write the loop.
Jun 1 '07 #15

Rabbit
Expert Mod 10K+
P: 12,427
Here's some sample code to get you started.
Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2.  
  3. Set rst = CurrentDb.OpenRecordset("SELECT Links FROM Table1;")
  4.  
  5. Do Until rst.EOF ' EOF is the end of the Recordset
  6.    FollowHyperlink rst!Links
  7. Loop
  8.  
Jun 1 '07 #16

P: 72
Here's some sample code to get you started.
Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2.  
  3. Set rst = CurrentDb.OpenRecordset("SELECT Links FROM Table1;")
  4.  
  5. Do Until rst.EOF ' EOF is the end of the Recordset
  6.    FollowHyperlink rst!Links
  7. Loop
  8.  

I tried applying it to my code, but I created an infinite loop:

Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2. Dim x As Boolean
  3. Dim y As Integer
  4.  
  5. Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans]")
  6.  
  7. x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'") ' Checks if [Does Record Exist?] field has a check mark.
  8. If (x = 0) Then     ' If checkbox in unchecked, then show message below.
  9.     y = MsgBox("Sorry, no scan found.", vbInformation, "Sorry")
  10. Else                ' If checkbox is checked, then follow the link(s) to open the file.
  11.     Do Until rst.EOF ' EOF is the end of the Recordset
  12.         FollowHyperlink (DLookup("[Gamma Scan Link]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'"))
  13.     Loop
  14. End If
The Do Loop continues to open the hyperlink. How do I cause the Do Loop to only select and open the hyperlinks once?
Jun 4 '07 #17

Rabbit
Expert Mod 10K+
P: 12,427
Have the recordset only select the links where the foreign key matches the primary key.

Then you can just use rst.RecordsetCount to determine if there are any links. Using a recordset means you no longer need to use DLookup.

And my apologies, my code was flawed in the first place.

Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2.  
  3. Set rst = CurrentDb.OpenRecordset("SELECT Links FROM Table1 WHERE KeyField = " & Me.KeyField & ";")
  4.  
  5. Do Until rst.EOF ' EOF is the end of the Recordset
  6.    FollowHyperlink rst!Links
  7.    rst.MoveNext
  8. Loop
  9.  
Jun 4 '07 #18

P: 72
Have the recordset only select the links where the foreign key matches the primary key.

Then you can just use rst.RecordsetCount to determine if there are any links. Using a recordset means you no longer need to use DLookup.

And my apologies, my code was flawed in the first place.

Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2.  
  3. Set rst = CurrentDb.OpenRecordset("SELECT Links FROM Table1 WHERE KeyField = " & Me.KeyField & ";")
  4.  
  5. Do Until rst.EOF ' EOF is the end of the Recordset
  6.    FollowHyperlink rst!Links
  7.    rst.MoveNext
  8. Loop
  9.  
I tried your code, but a run-time error occured. It said, "Too few parameters. Expected 1." When I clicked the Debug button, this line of code was highlighted:

Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = " & Me.[Column Name] & ";")

Did I do something wrong, or leave something out?

Also, is it RecordsetCount or RecordCount , because I can't find any help info on the former? How would I use it in my if...else statement? Or, in other words, how do I use it to tell whether there are anyother links? I'm sorry for being so confused, but I have very little experience in VBA, let alone programming.
Jun 4 '07 #19

Rabbit
Expert Mod 10K+
P: 12,427
I tried your code, but a run-time error occured. It said, "Too few parameters. Expected 1." When I clicked the Debug button, this line of code was highlighted:

Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = " & Me.[Column Name] & ";")

Did I do something wrong, or leave something out?

Also, is it RecordsetCount or RecordCount , because I can't find any help info on the former? How would I use it in my if...else statement? Or, in other words, how do I use it to tell whether there are anyother links? I'm sorry for being so confused, but I have very little experience in VBA, let alone programming.
Try:
Expand|Select|Wrap|Line Numbers
  1. Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "'")
  2.  
And sorry, yes, it's RecordCount.

If there are links then RecordCount would be larger than 0. Basically
Expand|Select|Wrap|Line Numbers
  1. If rst.RecordCount > 0 Then
  2.    ...
  3. Else
  4.    ...
  5. End If
  6.  
Jun 4 '07 #20

P: 72
Try:
Expand|Select|Wrap|Line Numbers
  1. Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "'")
  2.  
And sorry, yes, it's RecordCount.

If there are links then RecordCount would be larger than 0. Basically
Expand|Select|Wrap|Line Numbers
  1. If rst.RecordCount > 0 Then
  2.    ...
  3. Else
  4.    ...
  5. End If
  6.  
Thanks! The change worked. I was able to get my code to work, but without the RecordCount. Initially, I tried using the if...else format the you gave me, but I ran into a problem with NULL values. Some of the records do not have links available; therefore, I left those spaces blank. The blank spaces resulted in a NULL value. So, when I tried to run the code, the RecordCount was greater than 0 for ALL of the "Columns." I'm not sure how I could get around that problem and still use the RecordCount. So, I just used my old code with some modifications, and it worked!

My code is found below:

Expand|Select|Wrap|Line Numbers
  1. Dim x As Boolean
  2. Dim y As Integer
  3. Dim rst As Recordset
  4.  
  5. x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'")
  6. If (x = 0) Then ' if record does not have a checked box, then show message below.
  7.     y = MsgBox("No scans for column " & [Column Name] & ".", vbInformation, "Sorry")
  8. Else ' if record does have a checked box, then
  9.     ' Create a recordset that only includes the gamma scan links for the current column.
  10.     Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "'")
  11.     Do Until rst.EOF ' EOF is the end of the Recordset
  12.         FollowHyperlink rst![Gamma Scan Link] ' follow the link(s).
  13.         rst.MoveNext
  14.     Loop
  15. End If
So, thanks again for your time and patience! You've been a great help! Do you see anything wrong with it?
Jun 4 '07 #21

Rabbit
Expert Mod 10K+
P: 12,427
Thanks! The change worked. I was able to get my code to work, but without the RecordCount. Initially, I tried using the if...else format the you gave me, but I ran into a problem with NULL values. Some of the records do not have links available; therefore, I left those spaces blank. The blank spaces resulted in a NULL value. So, when I tried to run the code, the RecordCount was greater than 0 for ALL of the "Columns." I'm not sure how I could get around that problem and still use the RecordCount. So, I just used my old code with some modifications, and it worked!

My code is found below:

Expand|Select|Wrap|Line Numbers
  1. Dim x As Boolean
  2. Dim y As Integer
  3. Dim rst As Recordset
  4.  
  5. x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'")
  6. If (x = 0) Then ' if record does not have a checked box, then show message below.
  7.     y = MsgBox("No scans for column " & [Column Name] & ".", vbInformation, "Sorry")
  8. Else ' if record does have a checked box, then
  9.     ' Create a recordset that only includes the gamma scan links for the current column.
  10.     Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "'")
  11.     Do Until rst.EOF ' EOF is the end of the Recordset
  12.         FollowHyperlink rst![Gamma Scan Link] ' follow the link(s).
  13.         rst.MoveNext
  14.     Loop
  15. End If
So, thanks again for your time and patience! You've been a great help! Do you see anything wrong with it?
Not a problem, good luck.

There's nothing wrong with the coding other than it can be cleaned up a bit. I took out the comments but leave those in.

Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2.  
  3. Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "' And [Gamma Scan Link] Is Not Null")
  4.  
  5. If (rst.RecordCount = 0) Then
  6.     MsgBox "No scans for column " & [Column Name] & ".", vbInformation, "Sorry"
  7. Else
  8.     Do Until rst.EOF
  9.         FollowHyperlink rst![Gamma Scan Link]
  10.         rst.MoveNext
  11.     Loop
  12. End If
Jun 4 '07 #22

P: 72
Not a problem, good luck.

There's nothing wrong with the coding other than it can be cleaned up a bit. I took out the comments but leave those in.

Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2.  
  3. Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "' And [Gamma Scan Link] Is Not Null")
  4.  
  5. If (rst.RecordCount = 0) Then
  6.     MsgBox "No scans for column " & [Column Name] & ".", vbInformation, "Sorry"
  7. Else
  8.     Do Until rst.EOF
  9.         FollowHyperlink rst![Gamma Scan Link]
  10.         rst.MoveNext
  11.     Loop
  12. End If
Ah, yes! That is a much nicer code! It allows me to delete the [Does Record Exist?] field, which had the checkboxes.

Thanks!
Jun 4 '07 #23

Post your reply

Sign in to post your reply or Sign up for a free account.