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?

[mforema]

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 [ ]

You are awesome! It works great!

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

Private Sub Command255_Clic k()
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!!!

[Rabbit]

Glad to help. Good Luck.

[mforema]

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_Clic k()
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

[Rabbit]

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_Clic k()
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.

[mforema]

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.

[Rabbit]

Here's some sample code to get you started.

Dim rst As Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT Links FROM Table1;")
 
Do Until rst.EOF ' EOF is the end of the Recordset
   FollowHyperlink rst!Links
Loop
 

[mforema]

Here's some sample code to get you started.

Dim rst As Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT Links FROM Table1;")
 
Do Until rst.EOF ' EOF is the end of the Recordset
   FollowHyperlink rst!Links
Loop
 

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

Dim rst As Recordset
Dim x As Boolean
Dim y As Integer
 
Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans]")
 
x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'") ' Checks if [Does Record Exist?] field has a check mark.
If (x = 0) Then     ' If checkbox in unchecked, then show message below.
    y = MsgBox("Sorry, no scan found.", vbInformation, "Sorry")
Else                ' If checkbox is checked, then follow the link(s) to open the file.
    Do Until rst.EOF ' EOF is the end of the Recordset
        FollowHyperlink (DLookup("[Gamma Scan Link]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'"))
    Loop
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?

[Rabbit]

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

Then you can just use rst.RecordsetCo unt 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.

Dim rst As Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT Links FROM Table1 WHERE KeyField = " & Me.KeyField & ";")
 
Do Until rst.EOF ' EOF is the end of the Recordset
   FollowHyperlink rst!Links
   rst.MoveNext
Loop
 

[mforema]

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

Then you can just use rst.RecordsetCo unt 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.

Dim rst As Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT Links FROM Table1 WHERE KeyField = " & Me.KeyField & ";")
 
Do Until rst.EOF ' EOF is the end of the Recordset
   FollowHyperlink rst!Links
   rst.MoveNext
Loop
 

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.OpenR ecordset("SELEC T [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.

[Rabbit]

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.OpenR ecordset("SELEC T [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:

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

And sorry, yes, it's RecordCount.

If there are links then RecordCount would be larger than 0. Basically

If rst.RecordCount > 0 Then
   ...
Else
   ...
End If