Opening a file from a userform commandbutton

[Walter349]

I have a database that contains three columns, FName(File Name), FPath(File Location) and Loc1(The full path to the file)Loc1 is concatenated from FPath and FName in that order.

I have a user form that allows the user to search for a file, this is then displayed on the user form, FName and Loc1.

On the userform is a command button

What I want to do, is for the user to be able to open the document shown in Loc1 from the command button. The application.followhyperlink seemed the way to go. but I cannot find how to link that with the Loc1 field content.

I have searched the forum on access vba, but have not been able to locate anything that addresses what I am looking for.

Any guidance would be appreciated

'If at first you don't succeed, then your hammer is below specifications'

 

[Walter349]

OK, Update

I have made the Loc1 field a Hyperlink, but it will not open the relevant document when clicked on.

So have put the following on the command button, but it will only open one document at the top of the list, but no others and there are about 230,000 documents to choose from. To make it easier, I have limted everything to WORD documents only for the moment. But only the top of the heap responds to the command button click

Private Sub Command5_Click()
Application.FollowHyperlink Forms!OpenFile.[Loc1]
End Sub

'If at first you don't succeed, then your hammer is below specifications'

 

[TheAceMan1]

How are ya Walter349 . . .

Referencing your initial post:
[ol][li]Be sure your using a [blue]UNC[/blue] path for [blue]Loc1[/blue].[/li]
[li]Wether a server or drive letter ... be sure to use forward slashes ([blue]\[/blue]).[/li]
[li]Be sure to include a final forward slash in [blue]Loc1[/blue] (before the filename) or you'll have to concatenate it yourself.[/li][/ol]
Examples:
Loc1 = "C:"
FName = filename.ext
Application.FollowHyperlink Me.Loc1 & "[blue]\[/blue]" & FName, , True

Loc1 = "C:[blue]\[/blue]"
FName = filename.ext
Application.FollowHyperlink Me.Loc1 & FName, , True

If this doesn't work ... post a few examples of [blue]FName[/blue] & [blue]Loc1[/blue] ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Walter349]

Thanks for the reply,
The Loc1 is concatenated from two data columns in a query , FName and FPath. The FPath does have the forward slash at the end in all instances in the database and the resulting Loc1 Hyperlink in the form field looks like so;

Loc1: S:\03 ATM\Training\Training_Handouts\2_Welcome_Package\0- Induction Pack - Welcome.doc

It is this that the commandbutton actions on in the 'on click' event.

I have discovered why the documents are not opening after the first one. But not how to fix it. It seems that on selecting a document from the FName field in the user form, the associated concatenated hyperlink is not being updated correctly. It updates the file name at the end of the path displayed in the Loc1 field for the original document that was opened when the userform is opened. As so;

FName: 01_WSUSStepbyStep.doc

Loc1: S:\01 PABX\02 - Documentation\Cholet Training Documentation\Training\Disk 4\IS\1= Active Directory\3.= Logical Design\Archive\01_WSUSStepbyStep.doc

The correct path for this document as per the query should be;

S:\00 IS\_WSUS\DOC WSUS\01_WSUSStepbyStep.doc

this is causing an error 490 fault and quite resonably in the face of it. As it is looking in the wrong folder location.

The assumption was that as I selected the FName field content in the userform, the associated Loc1 Field would update with the correct concatenated path and field name for the data row. This does not seem to be happening. The userform Loc1 field data control source is set to the Loc1 field in the query, that contains the concatenated full path.


Any thoughts on this?


'If at first you don't succeed, then your hammer is below specifications'

 

[TheAceMan1]

[blue] ... It seems that on selecting a document from the FName field in the user form, [purple]the associated concatenated hyperlink is not being updated correctly[/purple] ...[/blue]

Are you talking a combobox or listbox here? If so, post the [blue]rowsource[/blue] and [blue]any code[/blue] for the box ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Walter349]

The FName field which is used to select the file name from the data is a combobox.

The Loc1 field which is to display the associated full path and file name, is a textbox, with Is Hyperlink set to yes.
The rowsource type is set to table/query
The Row source is set to

SELECT [Word Documents].FName FROM [Word Documents];

There is no vba code for the Loc1 textbox

'If at first you don't succeed, then your hammer is below specifications'

 

[Walter349]

Heres a curious thing and may throw somelight on the problem.

When selecting the file name from the userform combobox, only the filname at the endof the Hyperlinked path is updated instead of the whole correct path. Causing the document not to be found and opened.

But, when I use the record counter at the bottom of the userform, to move from record to record, it updates everything correctly and opens the relevant file as intended.

problem is, this is not the way I want it to work

'If at first you don't succeed, then your hammer is below specifications'

 

[TheAceMan1]

Walter349 . . .

Surely there's some code for the combobox ... in one of the events? Post this ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Walter349]

Problem fixed.

Changed the combobox to unbound and the following code

Private Sub Combo7_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[FName] = '" & Me![Combo7] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This now works and the command button opens the word document as required. Now I have to do the same for Excel files, JPGs, TIF images and PDF files.

It seems I was looking at this from the wrong point of view.

'If at first you don't succeed, then your hammer is below specifications'