populating address of hyperlink with VBA

[ClassicVinyl]

Entries are made into the fields of an existing record through a bound form.



For the hyperlink field, the files being linked to (all pdf) are located on my C drive and the mdb is located in the same folder as the pdf

The pdf names are the same as the file name (7 numeric text characters with a "-" between 3 and 4) plus the suffix ".pdf". So, 990-0001.pdf, 990-0002.pdf etc

I can create the address as a string with a procedure by clicking on a command button.

Using VBA to place the string in the text box with the control source being the hyperlink field only shows what the link looks like

Can I place the file name in the address of a hyperlink using VBA? Please provide examples

[orange]

What is the purpose of the hyperlink field?
You can use a string/text field/variable with Application.FollowHyperlink

Here is a sample from one of my test subs

Sub Open_jpeg()
      Dim str As String
10    str = "D:\PatPictures\2011-Mar\"
20    Application.FollowHyperlink str & "DSCF5330.jpg"
End Sub

[ClassicVinyl]

What is the purpose of the hyperlink field?
You can use a string/text field/variable with Application.FollowHyperlink

Here is a sample from one of my test subs

Sub Open_jpeg()
      Dim str As String
10    str = "D:\PatPictures\2011-Mar\"
20    Application.FollowHyperlink str & "DSCF5330.jpg"
End Sub

The hyperlink field is to provide access to view family letters by clicking on the hyperlink.

The rest of the extensive fields provides the means to search for the desired letters. The content of the search and number of letters in the returned list will vary greatly

With pdf and mdb in the same folder, I can right click on the text box, hyperlink, edit, enter just the file name in address or select it. Click on the hyperlink and the pdf displays in Acrobat Reader

Is there no way to place the pdf file name in the address of a hyperlink using VBA?

[orange]

You can use a click event and the FollowHyperlink approach to achieve what you need (if I'm understanding correctly).
Many do not use hyperlink datatype.

In the example code I showed, the file path and name could come from a combobox on a form, and you could click a button to open the jpg or a pdf or whatever was the target of the "hyperlink".

[ClassicVinyl]

FollowHyperlink approach (as shown) won't do what I need. It will create a link to only one record. Each search of my mdb will return a different list of records and quantity with each having its own pdf and needing to be accessed. To me, the simplest way seems to be a clickable hyperlink field for each record.

Can FollowHyperLink be modified for my need?

[orange]

Please give an example/sample of your data to clarify

Each search of my mdb will return a different list of records and quantity with each having its own pdf and needing to be accessed.

Show us a typical search and what your result(s) would be.
It would be helpful if you could show us your table design(s).

[June7]

A true hyperlink has 3 parts. Hyperlink can be constructed with code. Review http://allenbrowne.com/casu-09.html

I don't use hyperlink field. See no reason orange's code would not serve.

[ClassicVinyl]

All data is stored in one table being entered through a bound form using combo boxes or command buttons. Kept it simple for it will be exported to Excel for the family to use (via DropBox). I am using Access to create the basic structure for I don't understand VBA in Excel.

The data sample is a search result having sorted the table on letter date then filtered for the first 6 months of 1916 and the writer John P Williams. 8 records were returned with 8 letters to be viewed.

I did review the "Similar Threads" below. Saw one post were Record Set was used to place the address in the hyperlink field using "value" and "with" and loop while not eof() but prefer to place the link in the address portion when I enter data for each letter.

[ClassicVinyl]

Please give an example/sample of your data to clarify


Show us a typical search and what your result(s) would be.
It would be helpful if you could show us your table design(s).

Still getting the hang of using the posting functions.

[isladogs]

Your screenshot appears to be for a table or query
As already suggested, use Application.FollowHyperlink on a continuous form or datasheet instead.
It will do exactly what you want for a text field.

Use CurrentProject.Path & "" & ..... Then your file name

[ClassicVinyl]

Found a solution to placing the link in the address portion of a hyperlink datatype field. As I entered above, the key was finding ".value" in "Similar Posts" on this forum. Will be a lot of repetition but is saves me from more than 1,200 manual entries. Would check the "Solved" control but don't know where it is

Private Sub LetterAddress_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strAddress As String


Set db = CurrentDb
Set rs = db.OpenRecordset("Letters")


rs.MoveFirst
Do While Not rs.EOF
   strAddress = rs!LetterId + ".pdf"
   rs.Edit
   rs("LtrLink").Value = "#" + strAddress + "#"
   rs.Update
   rs.MoveNext
Loop
  
End Sub

[June7]

Thread Tools dropdown above first post.
Done.