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:

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
 

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:

Dim x As Boolean
Dim y As Integer
Dim rst As Recordset
 
x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'")
If (x = 0) Then ' if record does not have a checked box, then show message below.
    y = MsgBox("No scans for column " & [Column Name] & ".", vbInformation, "Sorry")
Else ' if record does have a checked box, then
    ' Create a recordset that only includes the gamma scan links for the current column.
    Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "'")
    Do Until rst.EOF ' EOF is the end of the Recordset
        FollowHyperlink rst![Gamma Scan Link] ' follow the link(s).
        rst.MoveNext
    Loop
End If

So, thanks again for your time and patience! You've been a great help! Do you see anything wrong with it?

[Rabbit]

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:

Dim x As Boolean
Dim y As Integer
Dim rst As Recordset
 
x = DLookup("[Does Record Exist?]", "Gamma Scans", "[Column Name] = '" & Me.[Column Name] & "'")
If (x = 0) Then ' if record does not have a checked box, then show message below.
    y = MsgBox("No scans for column " & [Column Name] & ".", vbInformation, "Sorry")
Else ' if record does have a checked box, then
    ' Create a recordset that only includes the gamma scan links for the current column.
    Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "'")
    Do Until rst.EOF ' EOF is the end of the Recordset
        FollowHyperlink rst![Gamma Scan Link] ' follow the link(s).
        rst.MoveNext
    Loop
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.

Dim rst As Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "' And [Gamma Scan Link] Is Not Null")
 
If (rst.RecordCount = 0) Then
    MsgBox "No scans for column " & [Column Name] & ".", vbInformation, "Sorry"
Else
    Do Until rst.EOF
        FollowHyperlink rst![Gamma Scan Link]
        rst.MoveNext
    Loop
End If

[mforema]

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.

Dim rst As Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT [Gamma Scan Link] FROM [Gamma Scans] WHERE [Column Name] = '" & Me.[Column Name] & "' And [Gamma Scan Link] Is Not Null")
 
If (rst.RecordCount = 0) Then
    MsgBox "No scans for column " & [Column Name] & ".", vbInformation, "Sorry"
Else
    Do Until rst.EOF
        FollowHyperlink rst![Gamma Scan Link]
        rst.MoveNext
    Loop
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!