Browse to file location issues (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
Hi,

I'm attempting to modify some code so that I can drag a file to a save location but then view the record in it's new location as a hyperlink.

I have a form with a bound field for both "HyperlinkIN" & "HyperlinkOUT"

I think the problem is that the string returned does not include the username? Then crashes at the "Filecopy" line. If I recall it worked OK when I first tried it on a networked PC.

Any clues?. Have I given sufficient to work on?

As always, appreciate any help.


Private Sub HyperlinkIN_AfterUpdate()

Dim InPath As String
Dim FileName As String
Dim OutFolder As String
Dim OutPath As String
Dim RecordNo As String
Dim FileExt As String
Dim objFSO As Object
Dim strHyperlinkPath As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
InPath = objFSO.GetAbsolutePathName(Me.HyperlinkIN.Hyperlink.Address)
OutFolder = objFSO.GetAbsolutePathName(Me.HyperlinkOUT.Hyperlink.Address)

'OutFolder = Me.HyperlinkOUT & ""
Debug.Print Me.HyperlinkOUT

RecordNo = Me!ID

If Len(InPath) > 0 Then
FileName = Right(InPath, Len(InPath) - InStrRev(InPath, "")) 'get the file name
FileExt = Right(FileName, Len(FileName) - InStrRev(FileName, ".") + 1) ' get the file extension with dot

'build the new path with output folder path and record number and date and extension
OutPath = OutFolder & "" & "Record" & RecordNo & " Attachment " & Format(Now(), "ddmmmyy") & FileExt

If (IsNull(Me.[Out_Path]) Or Me.[Out_Path] = "") Then Me.[Out_Path] = OutPath 'Allows the file save position to be relocated"

Debug.Print InPath
Debug.Print Out_Path

FileCopy InPath, Out_Path

Me!HyperlinkOUT = "#" & [Out_Path] & "#"

MsgBox "Copied file to archives " & vbCrLf & InPath & vbCrLf & OutPath
End If


End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:58
Joined
May 7, 2009
Messages
19,169
is this correct:
Code:
FileName = Right(InPath, Len(InPath) - InStrRev(InPath, "")) 'get the file name
should it be:
Code:
FileName = Mid(InPath, InStrRev(InPath, "\") + 1) 'get the file name
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
Arnelgp, I "found" much of this code & it seemed to work. Will substitute your code & give it a go. Ultimately I wish to be able to select a path for "File Data" & "Archive by either dragging files onto the hyperlink field & dropping or by browsing to the file locations. Appreciate your help.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
Tried it but didn't make a difference. I notice that the "OutPath" displays the full path including "user", but the "InPath" shows C..\..\ to start with. Does this make sense?
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
Hi,

Following is the original code. I tried it again on a networked PC It does not reliably return the complete file paths. Any Clues?

Code:
Private Sub HyperlinkIN_AfterUpdate() 'This code is used to allow a file to be dragged to a "saved" file
Dim InPath As String
Dim FileName As String
Dim OutFolder As String
Dim OutPath As String
Dim RecordNo As String
Dim FileExt As String

OutFolder = "c:\Users\Terry Hill\Desktop\DataDump"

InPath = Me!HyperlinkIN.Hyperlink.Address
RecordNo = Me!ID

If Len(InPath) > 0 Then
    FileName = Right(InPath, Len(InPath) - InStrRev(InPath, ""))  'get the file name
    FileExt = Right(FileName, Len(FileName) - InStrRev(FileName, ".") + 1) ' get the file extension with dot

'build the new path with output folder path and record number and date and extension
        OutPath = OutFolder & "Record " & RecordNo & " " & FileName & " " & Format(Now(), "ddmmmyy") & FileExt
        
        If IsNull(Me.[Out_Path]) Or Me.[Out_Path] = "" Then Me.[Out_Path] = OutPath 'Allows the file save position to be relcocated"
                            
        FileCopy InPath, Out_Path
    
    Me!HyperlinkOUT = "#" & Out_Path & "#"
    Me!HyperlinkIN = "#" & InPath & "#"

    MsgBox "Copied file to archives   " & vbCrLf & InPath & vbCrLf & OutPath
End If


End Sub
 
Last edited by a moderator:

isladogs

MVP / VIP
Local time
Today, 19:58
Joined
Jan 14, 2017
Messages
18,186
I've added code tags to your last post to make it more readable

Arnel's code suggestion is what I would use for a standard file path.
Hyperlink datatype fields are problematic and in many peoples' opinion best avoided.

Can you give an example of your InPath values so we can if necessary make a suitable suggestion.
Or change to use a standard text field.
 

June7

AWF VIP
Local time
Today, 11:58
Joined
Mar 9, 2014
Messages
5,423
Is HyperlinkIN a hyperlink type field in table? I tested the Address property and all it returns is file name, not full path string.

Have you step debugged code?

I don't use hyperlink type field because of its quirks. I save path string in a text field.
 
Last edited:

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
Hi, I've attached by test database. After learning this, I intend to roll it out into my project.

Appreciate your help as always.
 

Attachments

  • Drag&Drop.accdb
    916 KB · Views: 163

June7

AWF VIP
Local time
Today, 11:58
Joined
Mar 9, 2014
Messages
5,423
Okay, I get a path saved when browsing instead of just selecting a file in the current folder. But the path is abbreviated with just ..\ at beginning.

FileCopy is referencing field Out_Path but if you reference variable OutPath instead it doesn't error. In an existing record, Out_Path is not modified if it already has a value because your code expects Null or empty string. That will cause FileCopy error because path is not valid for user.

Step debug to see content of textboxes and variables as code executes.

Again, agree with Colin, advise not to use hyperlink field.
 
Last edited:

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
June7, my intention was to "capture" the actual save path used so that a user can always navigate to a saved file even if the save path is changed in the future (such as if I decide to store some archived items in a different location). Hence, populating the file [Out_Path]=[OutPath] (which could change) & checking to see whether it's null or not. I really wanted to incorporate a file drag & drop capability. Hope I made sense. Appreciate your efforts.
 

June7

AWF VIP
Local time
Today, 11:58
Joined
Mar 9, 2014
Messages
5,423
I have never been able to get file drag and drop to work within Access. Good luck.

If you want to allow Out_Path field to be edited then why does code only allow when it is Null?

Did you try changes suggested?
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
June7, my intention was to only allow a different Out_Path if it had not already included a path. I wanted the flexibility of being able to assign another "save" path without changing the "save" path for existing records. I thought!!!!!I heed you're warnings though. So I gather it may be best to avoid hyperlinks & put an "Open" button beside the navigation path string?
 

June7

AWF VIP
Local time
Today, 11:58
Joined
Mar 9, 2014
Messages
5,423
That reasoning doesn't really mean anything to me. It's your db, so if you understand why you are saving those various paths, then go ahead.

As for saving and opening links: Adding a link into text field would require code manipulating FileDialog. For opening I would use FollowHyperlink or Shell code. Code could even be in textbox Click or DoubleClick event and textbox formatted to look like hyperlink.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
Hi, I've done some more trials. It appears that when I drag from the desktop only, "Inpath" (on my original post) is returned, but missing the \desktop\ portion of the path & thus the crash. When I browse & select any other file anywhere but the desktop, the path is returned correctly & the function works. If, instead I browse to the desktop, to select the file it also crashes. Any Ideas?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:58
Joined
May 7, 2009
Messages
19,169
do you have code for the drag event.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
Anelgp, No, I'm just left clicking the file & dragging over the "HyperlinkIn" field on my form. As I said, it works in all instances except where I have a file on the desktop (where I expect most users to temporarily put a file) that I left click & drag. Most of my users have multiple monitors so in reality have the access form displayed on one & their desktop on the other. Appreciate your input.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:58
Joined
May 7, 2009
Messages
19,169
strange, when I drag from desktop to hyperlink field it is showing:

…\desktop\theFilenameHere

so I have the desktop part.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
arnelgp, When I do same, I get ..\documents\..! I'm using a networked laptop & windows 10. The code works for any other file location but desktop. Appreciate it.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 08:58
Joined
Apr 1, 2019
Messages
712
All, I tried the database on a different PC, a networked laptop & it would not return the correct file path at all! This is driving me mad!! Would be totally grateful if someone can assist with a work around.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:58
Joined
May 7, 2009
Messages
19,169
you can replace it in the code:

strOut= replace(strOut, "..", "c:")
 

Users who are viewing this thread

Top Bottom