vba dynamic hyperlink

Try to open a pdf with a button in a form using vba - i want it to take the data from me!comp_ID and insert it into the file location and then add .pdf on the end and open it with this i get a type mismatch run time error '13'

Thanks for your help

Dim strfile As String

strfile = "Y:EMPLOYEE FOLDERSJMendes stuffCreditApp" + Me!COMP_ID + ".pdf"

Application.FollowHyperlink (strfile)

Post your answer or comment

comments powered by Disqus
Greetings all,

I'm new here as a registered user after often reading here anonymously. Thanks for all the help you've all been for a long time. So, to the question:

It seems simple: In an Access (2007) report, can I have a dynamically sized, dynamically hyperlinked piece of text? I should say, I want the text to read "No. 1234 - Application XYZ" and hyperlink to something like "h t t p : / / f o o b a r . c o m / S h o w A p p . a s p x ? I D = 3 5 7 9", rather than actually display such hyperlink as the content of the text field. The displayed text can run from one to perhaps four lines of text tall so needs to wrap and resize, and the hyperlink of course needs to be dynamically assigned each time. If I use a text box, I can do the former but not the latter. If I use a label control, I can do the latter but not the former. Have I missed out something simple here, or is this combination of resizing and dynamic linking truly not possible?

(Other than by resorting to things like discussed on e.g. h t t p : / / w w w . a c c e s s - p r o g r a m m e r s . c o . u k / f o r u m s / s h o w t h r e a d . p h p ? t = 1 5 8 5 4 8 that require a lot of extra code with API calls &c. &c. -- something I hesitate to distribute and be stuck supporting.)

Thanks much
-- Dave

p.s. sorry for non-working, bowdlerized links. I am

Thanks in advance for everyone's help.

I create two reports in access

report 1 with detailed information (company name, info, status, details etc)

report 2 is an outline form of report 1 with company name, status, and a link that I want to directly into the report 1

I publish both forms into word.

My problem is I do not know how to dynamically general a hyperlink that will link directly to the company

report 2 when created looks something like this:

Company Name Status Hyperlink
Gap Closed report1.rtf#Company Name
Limited Open report1.rtf#Company Name

I need it to look like this:
Company Name Status Hyperlink
Gap Closed report1.rtf#Gap
Limited Open report1.rtf#Limited

I do not want to manually change each link; however, I can not figure out how to format the hyperlink so that the company name is a field variable and not static text.


Hi All

I have text box and command button. When user hits the command button, it opens the dialog box, the user now select a word document file. Once the user selects the document file, I paste the same in the text box. Now I was the text box to be hyperlinked, so that when user clicks on the document file, it opens the file. I need to do it dynamically, means, after selecting the document file, the text box must be hyperlinked and the address will be the selected document file.


I am having trouble creating hyperlinks in an Access report. While I understand that the hyperlinks will not be active while viewed in Access, my end goal is to export the report as a pdf file with working hyperlinks.

I am working in an ADP and the reports recordsource is a SQL stored procedure.

The hyperlink is to a web copy of a particular invoice and the hyperlink address is created in the stored procedure.

Can someone tell me how to insert the hyperlink in the detail section of the report and have it display "View Invoice"?

Thanks in advance.

Hi Everyone,

A transient, virtual hobo, with only enough skill to be highly dangerous. You all know the type, as evidenced here:

The code I'm attaching, far below, does not work (shocker). It attempts to dynamically assign values to the properties? of my variable (of type ProjectRegions) via a loop that feeds it the property name and the property value. Thus, 1st time through:
projectRegion.projectName = "Dismantle hampster wheel that is my job". 2nd time through:
projectRegion.projectNumber = "42". 3rd time through:
projectRegion.projectDescription = "A futile endeavor". nth time through:
projectRegion.[regionName(n)] = regionValue(n) I have two questions:
1. Is it possible to accomplish this dynamically, kind of like 'variable variables' in PHP?
2. I am currently using a Select Case, but it consumes hundreds of lines. Should I stick with that?

#1 would be funner and shorter. Thanks for any assistance.

Public Type ProjectRegions 
 projectName As Integer
 projectNumber As String
 projectDescription As String
End Type
Dim projectRegion As ProjectRegions
Sub getProjects()
  'Assume currentExcelRegion and currentExcelRegionValue change each time through the loop
  For count = 1 To 1000
         projectRegion = saveProjectRegions(currentExcelRegion, currentExcelRegionValue)
  MsgBox "My project number is: " & projectRegion.projectNumber
  MsgBox "My project description is: " & projectRegion.projectDescription
End Sub
Public Function saveProjectRegions(regionName, regionValue) As ProjectRegions
 projectRegion.[regionName] = regionValue 'This doesn't work, not that I expected it to
 saveProjectRegions = projectRegion
End Function


Without getting into great detail, I have a query that worked when I constructed it in MS Access Queries (static data)but now that I am trying to get it to work in VBA (dynamic data) it claims that I have a Datatype Mis-Match (Error 13).

I have ensured all my fields are of the same datatype and match up perfectly. (I am going to check, again, after typing this post.)

I have tested small Insert statements on other tables within VBA and they worked fine within the same code.

I have inserted a MsgBox to make sure the SQL string has been constructed properly and looks identical to the one in Access.

What gives?

Any help is appreciated.

I am looking to basically update the hyperlink base every time the database is opened to be the folder that the database is currently in.

We are trying to make the database and supporting information portable, so that people can copy the entire folder anywhere on their computer and all of the links will still work. I can't put all of the info in the database because we are already pushing the size limit.

I would like to keep it from getting terribly complicated, as others will have to update this database with more info in the future and make the links themselves.

If there is a better or different way of getting the same result without going through the hyperlink base, that is fine.

Any help would be appreciated.


I have a form that lists records in a table. I would like to have a hyperlink beside each record that will open the data entry form and navigate to that specific record. Right now, the use can only open the form for all records and has to use the record navigation buttons to find the desired record.


I am creating two pivots from VBA dynamically on one sheet. On some occasions the last pivot is placed over the end of the first pivot. So I want to be able to determine dynamically what is the end of the first pivot, then start the next pivot 4 or 5 cells down so they never overlap.


Hi guys, I am trying to create a form or subform that should be created after a button is pressed and some complex filetering is occured and records are saved in a multiple arrays so I need to generate a form and controls according to those array values. Please help.

Actually I can not get data directly out of the query its just too complex bla bla bla. Finally I have three arrays each array has same number of values and I want to generate a form on the basis of the values of the array. Please Help me. I have tried creating form but Could someone please show me the right path.

many thanks

Hi Experts,

I have a Query, which is getting the parameters from a form. On ther other Hand, I would like to store the Query result in a recordset which I defined in VBA, dynamically. The code for this function is attached to a command button. When i press the command button, the query should be executed according to the paramaters given on the form, and result should be stored in the recordset.

Please somebody advise, what should I do?


Hi my name is michael i'm an IT guy my company wan't me to
make hyper links to pdf files and that can be updated automaticly with out changing the links to the pdf's when the pdf files change.Is there a program that does this?

Some time ago I took a class in Visual Basic Studio and had to construct a simple form which was linked to a database. I had a search box on the form that would filter my search results as I typed. In other words for every key stroke I made I could watch the filter working away filtering my results. This was really cool because it would give me instant feed back as I typed. If I accidentaly hit the wrong key I could would catch it immediately due to the visual feedback of seeing my filtered results go to hell. I would then back space once and then keep typing until my relsults matched what I wanted. VBA is a different story. I isn't built into VBA like it was in VB Studio. The way my form is currently set up, I have a search box that after I type in search words I have to hit enter to see the results. the search results are shown in a list box called search. I can then click on one of the items in the list and it will take me to that record and hide the list box. I want to make the search work like it did in Visual Basic Studio. As I type I want to see the list dynamically updating. Then when I see the results are sufficient, I then click on the record in the list. Way cooler and easier for the user. Here is the code I currently have. I have also attached a copy of the form and what it looks like when I enter my search. Any help would be greatly appreciated. Thanks.

Private Sub txtSearchField_AfterUpdate()
Me.Filter = "customer like '*" & Me.txtSearchField & "*'"
Me.Filter = Me.Filter & "OR desc like '*" & Me.txtSearchField & "*'"
Me.Filter = Me.Filter & "OR [partnum] like '*" & Me.txtSearchField & "*'"
Me.txtSearchField = ""
Me.blanksearch = ""
'Show the search results
Search.RowSource = "SELECT [TblOpSheetData].[ID], [TblOpSheetData].[DESC], [TblOpSheetData].[PartNum] As [PART #], [TblOpSheetData].[CUSTOMER] As [COMPANY] FROM [TblOpSheetData] " & _
"WHERE " & Me.Filter
If Search.ListCount = 2 Then 'ListCount includes header row
Search.Selected(1) = True
DoCmd.GoToRecord acDataForm, Me.name, acGoTo, Search.ListIndex + 1
ElseIf Not Search.Visible Then
Search.Visible = True
CloseSearch.Visible = True
End If
End Sub

Private Sub Search_AfterUpdate()
If Search.ListIndex > -1 Then
DoCmd.GoToRecord acDataForm, Me.name, acGoTo, Search.ListIndex + 1
Search.Visible = False
CloseSearch.Visible = False
End If
End Sub

i have put a hyperlink on my home page but i want it shut access once the hyperlink is pressed by editing the vba code, how do i do that?

I have a form with hyperlink field which contains the file name of an audio file in a folder of audio files.

I currently click on the hyperlink field to start the relevant audio file.

I would rather have a command button which starts the audio file.

I would be very grateful if someone could kindly tell me what the VBA code would be for this.

Greetings, "Dr. Frankenstein" here...

I have successfully meshed up management of 11 database tables with one set of classes / forms. Fruits from this thread:

"VBA variable to reference one of many possible class objects How do I pointer in VBA?"

So that works excellent. "Now if I could morph the Form to be able to work with other pick list table schemas!"

Is there a way to dynamically adjust the columns in a multiple records form? Since I bind the form to a DAO.QueryDef which gets dynamically built before the Form opens, it is no problem to support different schema at that level.

So how about physically adding columns dynamically to the multiple records form? And all of the associated events would need to be dynamically added as well... The Click event, the Double Click event, and so on...

Has anyone had success with this sort of thing in A2007? TIA!


As you may have guessed by the sunject heading, i am trying to output the results of a query to excel.
The query is dynamic ( build by vba depensing on form entry criteria ) and what i am finding is that if i use the following:

DoCmd.OUtputTo acOutputQuery, txt_exportsql, "MicrosoftExcel(*.xls)","", True, ""

then it seems to call the filename the initial part of the query. This therefore truncates the query and i don;t get the results.

What therefore would you recommend the best way is to export to Excel based on the fact that i have a text string which contains the query ( sql="SELECT contacts.name etc etc )

I need to be able to save the excel spreadsheet in variable places, so the filename cannot be hard-coded into the VBA.

Thanks for your help.

I have a table in a database where one field is a hyperlink to files on local network. I am using VBA to custom a SQL query based on some input from the user and return this field along with others from the table.

My problem is that in the query table returned the hyperlink field no longer works - it is no longer blue and underlined and you can't click on it to follow the link and open the file. However, if I make a basic query from the same table using the Access query builder, the hyperlinks work fine.

The SQL created by my VBA code is of the general form:

SELECT DocRef, DocTitle, DocAbstract, DocLink, DocAuthor, DocDate
FROM TechDocs
WHERE DocType IN ('ResRep', 'Misc') AND (DocAbstract LIKE '*Cure*');

where DocLink is the field that is the hyperlink, and "Cure" is the search term entered by the user.

This has been bugging me for a while but I just can't seem to work out why this is ... Any help is greatly appreciated!

I'm trying to build a dynamic sql query using VBA, thing is, i don't know how to assign a certain sql script to a Query in the database from within VBA.
Any help would be appriciated!

Ted de Vries

Here is a quick way to turn a cell's string into a hyperlink with Excel VBA: PHP Code:

               ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
                                                  Address:=THE ADDRESS, _
                                                  TextToDisplay:=LINK TEXT 
I had to do this once from an online PHP report that I copied into Excel. The hyperlinks from the browser copied as strings, so this function changed them accordingly: PHP Code:
Sub Rearrange()

Dim r As Range
Dim myvalue As String

   For Each r In Range("a1", Range("a1").End(xlDown))
      If InStr(r, "http://") > 0 Or InStr(r, "www.") > 0 Then
         myvalue = r.Value
               ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
                                                  Address:=myvalue, _
      End If
   Next r


End Sub 

Good day!

I have an Access database (CCR.mdb) in which one of the columns ([URL]) is a hyperlink to an Excel form specific to that record. I am wanting to write code that will access the hyperlinked .xsl file and pull the value of cell B24 into the database. I then want to append this value to [Copy of Master Table].

This will need to be done on a large number of files (3000+) and continue to pull and update the values on new records going forward, so importing each excel file as a table really isn't an option.

I would like to do this without opening the actual Excel file if possible.

So far I have identified the following elements that the code will need to accomplish, but I have no idea how to implement them:
Recognize [Copy of Master Table]![URL] as the dynamic file location
tbl(Copy of Master Table)!URLLook up the value of (Change Information)!B24 at the dynamic file locationAppend the value to [Copy of Master Table]![Implementation Date]Can anybody provide assistance? My knowledge of VBA is very limited.

Before I go down this path, I want to get some input from some experts.
In the queries discussion, I am seeking to create a parameter form which pulls up a base query and then creates a crosstab query for use in a report.
the idea is: param frm->qry 4 tables-->crosstab qry-->report
I believe the only way to create the ability for dynamic header info on a report is through the sequence:
param frm->qry 4 tables->event on open report ->vba code to format data -> display report


I'm having trouble dynamically creating the column headers before running the crosstab query. My only other idea is to code everything the report using VBA. And therfore no need for a crosstab, I simply run some loops to create the matrix.

looks like:

Courses: MGT401 MGT402 MGT403
Smith, Joe A B B
Sly, Robbie B B+



I wanted to pass some values from vba code to my report. The value is a result of some complex queries and calculations. So I dont want the report to connect directly to my query.

Can anyone tell me how to display the value of "LGST" in the below code in the report.

I dont want to dynamically create any controls. I'm looking for passing the value to a control on the report.

LSQL = "select count(*) from user"
Set Lrs = db.OpenRecordset(LSQL)
LGST = Lrs(0)

Thanks in advance,

With VBA I want to call a selfdefined subroutine "update()" in several reports. But whenever I try this, Access gives me an Error 2465. The Report is selected dynamically by the user and should adapt itself to other settings made by the user. Can anybody help me how to do this? Thanks.

Not finding an answer? Try a Google search.