Access: VBA code not pulling the right hyperlink from Database

Question

`

Private Sub Link_Click()

   Dim OpenLink As String
   Dim db As DAO.Database
   
   Set db = CurrentDb
   
    OpenLink = DLookup("Link", "dbo_tbl3_RankedLM")

FollowHyperlink OpenLink

End Sub

`My Access database has a SQL linked table from where I am pulling a txt datat type which has a hyperlink stored. I have put that link column in a Access Form as a button. So each row has it's own link button.

I am using Dlookup to get the hyperlink field and db but the link is not from the corresponding row, instead it's a random link from that table. I need help on how i can set a criteria to make sure it pulls the correct link.

enter image description here

Answer 1

DLookup has a third argument that that allows you to specify a criteria, otherwise it just returns a "random" record. Try something like:

OpenLink=DLookup("Link","dbo_tbl3_Ranked_LM","LinkID=" & Me!LinkID)

This assumes that there is a unique value (normally an Autonumber Primary Key) in the table that allows you to get the link value (otherwise you just get the first record that matches). You should also do a check for a Null value being returned. Note that there is no need to declare and set a reference to a Database object as this isn't required.

Answer 2

I don't know what you mean by SQL linked table and this is my first time working with hyperlinks in access but try the following:

As per the comments and Applecore's answer add a where clause to the dlookup to get only one link.

Next, FollowHyperLink expects a string but you have a link so get the address out of the link using the HyperlinkPart method. see: https://docs.microsoft.com/en-us/office/vba/api/access.application.hyperlinkpart

note: Dlookup returns a Variant so if you can put the link in the forms recordsource so you can call HyperLinkPart directly with something like

Private Sub Command8_Click()
Dim address As String
address = Application.HyperlinkPart(Me.Link, acAddress)
'Debug.Print address
FollowHyperlink address
End Sub

But if you must use Dlookup then use the Variant DataType to pass the link:

Private Sub Command8_Click()
Dim address As String
Dim link As Variant
link = DLookup("URL", "Links", "LinkID = " & Me.LinkID)

address = Application.HyperlinkPart(link, acAddress)
FollowHyperlink address
End Sub

note: URL is the Name of the column of Hyperlinks in my Links table.

Answer 3

You need to add criteria to your Dlookup:

OpenLink = DLookup("Link", "dbo_tbl3_RankedLM", "someField = 3")

In this example DLookup will take the link of the row where column called 'someFieldd' = 3. I recommend to use a unique ID column as the Dlookup criteria to make sure it retrieves the link of specific row.