0

`

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

Improve this question
1
  • The dlookup doesn't have a where clause Commented May 13, 2022 at 4:30

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the details although I am still getting an error saying "Run-Time error '8': Cannot download the info you added". Code Change: "OpenLink = DLookup("Link", "dbo_tbl3_RankedLM", "LoadID=" & Me!LoadID)" I have three tables connected in the access recordset with PK set as LoadID for tbl1 and ID (Autonumber) for tbl2. While they are connected to each other based on LoadID to LoadID.
0

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://learn.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.

Improve this answer

4 Comments

I added exactly as you said, but it does open the link nor does it show any error. LoadID is my Primary key here. Private Sub Link_Click() Dim address As String Dim Link As Variant Link = DLookup("Link", "dbo_tbl3_RankedLM", "LoadID = " & Me.LoadID) address = Application.HyperlinkPart(Link, acAddress) FollowHyperlink address End Sub
Try these things, print out link after the dlookup and address after application.HyperlinkPart with debug.print. If those look ok then rename the form textbox LoadID to something else like txtLoadID and set LoadID = Me.txtLoadID in the dlookup. Most likely the address isn't valid as the code is not firing an error.
address must include the protocol. So www.google.com may not work but http:// www.google.com should work (remove space after //)
Follow HyperLink should be directed to LINK and not Address.TY Private Sub Link_Click() 'Dim OpenLink As String 'OpenLink = DLookup("Link", "dbo_tbl3_RankedLM", "LoadID =" & Me.LoadID) Dim address As String Dim Link As Variant Dim LoadID As String Link = DLookup("Link", "dbo_tbl3_RankedLM", "LoadID = " & Me.LoadID) Debug.Print Link address = Application.HyperlinkPart(Link, acAddress) Debug.Print address FollowHyperlink Link End Sub
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.