CODEWITHSUNDEEP
vbams-accessms-access-forms
Who Dis
Who Dis

Reputation: 76

How can I set the value of a text box in a record set to a hyperlink address?

I am trying to loop through a record set and set the value returned for txtC1 to a hyperlink address for each record.

Here is the code I have come up with so far: 

sqlqry = "Select A1, B1, C1 From Table"

Me.Form1.Form.txtC1.SetFocus
i = 0
rs.Open sqlqry, cn, adOpenDynamic, adLockReadOnly
With rs
Do
    Do Until rs.EOF
    i = i + 1
        If rs.EOF Then
            Exit Do
        End If
    Me.Form1.Form.txtC1.HyperlinkAddress = Me.Form1.Form.txtC1.Text
    rs.MoveNext
    Loop
Loop Until rs.EOF
End With

I'm getting the error:

Object doesn't support this property or method.

On the line

Me.Form1.Form.txtC1.HyperlinkAddress = Me.Form1.Form.txtC1.Text

Perhaps a different approach would be better?

EDIT:

After messing with it some more, I've been able to set the hyperlink for all records by using a label and hidden text box. However, instead of changing the hyperlink target to the value of the hidden text box for each iteration of the loop, it is setting each link to the value of the first text box.

I am trying to implement a for each loop to change the link for each record by using the ADO Field object, but I'm not sure how to tell it what field to look at. Here is the new code

i = 0
rs.Open sqlqry, cn, adOpenDynamic, adLockReadOnly
With rs
Do
    Do Until rs.EOF
    i = i + 1
    hLink = Me.Form1.Form.txtC1.Value
        If rs.EOF Then
            Exit Do
        End If
   Me.Form1.Form.lbl.HyperlinkAddress = hLink
    rs.MoveNext
    Loop
Loop Until rs.EOF
End With

Upvotes: 0

Views: 725

Answers (1)

June7
June7

Reputation: 21370

An Access hyperlink is composed of 3 parts separated by # character (http://allenbrowne.com/casu-09.html). If a text field has a string that meets one of the second part structures, such as a filepath or URL string, a textbox can be a clickable hyperlink with IsHyperlink property set to Yes and expression in ControlSource:

="Click to Open File#" & Me!fieldname & "#"

Or use VBA to execute FollowHyperlink method.

Upvotes: 1

Related Questions