Browse to file location & archive Data. (1 Viewer)

HillTJ

Registered User.
Local time
Yesterday, 21:41
Joined
Apr 1, 2019
Messages
536
Hi,after studying various routines I came up with a hybrid of my own. See attached. I decided that it was best to archive documents in a separate folder & not clutter Access. Now I'd like the routine to make a new folder every year to keep size down. This is my code so far. I'd appreciate it if anyone can review it & give me some direction. I've commented out the if statement that I was attempting to figure out for the time being. As always. Cheers

Code:
Private Sub Browse_To_File_Click()

    On Error GoTo Err_Handler
    
    Dim strFullpath As String
    Dim strFolder As String
    Dim strFile As String
    Dim intPos As Integer
    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 Archieve As String
    'Dim fsFolder As Object
    
    strFullpath = BrowseFile
    RecordNo = Me!BatchUniqueID
    Archieve = Archieve_Path ' gets the default path from Module "Constants" Currently C:\users\tandi\Archieve
    
    'Set fsFolder = CreateObject("Scripting.FileSystemObject") ' This routine is to create a new folder each year-- Does not work--
    'strFullpath = Archieve & Year(Date)
    'Debug.Print strFullpath
    'If fsFolder.FolderExists(strFullpath) = False Then
       'fsFolder.CreateFolder (strFullpath)
    'End If
       
        If strFullpath <> "" Then ' get folder and file names
        intPos = InStrRev(strFullpath, "")
        strFolder = Left(strFullpath, intPos - 1)
        strFile = Mid(strFullpath, intPos + 1)
        InPath = strFullpath
    
    End If
               
    If Len(InPath) > 0 Then
    
        FileName = Mid(InPath, InStrRev(InPath, "") + 1) 'get the file name
        FileExt = Right(FileName, Len(FileName) - InStrRev(FileName, ".") + 1) ' get the file extension with dot
             
        OutPath = [Archieve] & "" & FileName & 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"
     
        FileCopy InPath, Out_Path
    
        Me!Out_Path = "#" & [Out_Path] & "#"

        MsgBox "Copied file to archives   " & vbCrLf & InPath & vbCrLf & OutPath
     
    End If
     
Exit_Here:
    Exit Sub
    
Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here

End Sub
 
Last edited by a moderator:

June7

AWF VIP
Local time
Today, 00:41
Joined
Mar 9, 2014
Messages
4,386
In future, please post code between CODE tags to retain indentation and readability (although yours is not so bad). Maybe edit your post to fix

Try this to check if folder exists and create if doesn't:
Code:
strFullpath = Archieve & "\" & Year(Date)
If Dir(strFullpath) = "" Then MkDir(strFullpath)
You are misspelling "archive" as "archieve".
 
Last edited by a moderator:

HillTJ

Registered User.
Local time
Yesterday, 21:41
Joined
Apr 1, 2019
Messages
536
June7, i cannot spell nor code!. But I'm trying. Will let you know how i go. Thanks.
 
Last edited by a moderator:

isladogs

CID VIP
Local time
Today, 09:41
Joined
Jan 14, 2017
Messages
16,255
I've added code tags to post #1 using the # button on the toolbar. As you can see it makes the code much easier to read
 
Last edited:

HillTJ

Registered User.
Local time
Yesterday, 21:41
Joined
Apr 1, 2019
Messages
536
June7, with your code installed my routine works a treat! I changed it slightly so that the routine makes a "year" folder within the "Archive" folder (Note the spelling!). It opens the archived file 'onclick'. Fantastic. You may recall, I was unsuccessfully trying to get drag & drop to work previously & you warned me about it's perils!
One question though. I have the line;
If (IsNull(Me.[Out_Path]) Or Me.[Out_Path] = "") Then Me.[Out_Path] = OutPath
in my code. It was my attempt to stop the path to the archived file being overwritten. It prevents it but not how I imagined. I get an error52 'Bad File name or number'. Have I got the syntax wrong? Appreciate any assistance. I'd be totally happy to post my code if it can help anyone.
 

June7

AWF VIP
Local time
Today, 00:41
Joined
Mar 9, 2014
Messages
4,386
Syntax looks fine to me. Don't know why that line would error. Just saving a string into text field. It should not care what the string is if it is not too long for the field size.

Might provide your revised code.
 

HillTJ

Registered User.
Local time
Yesterday, 21:41
Joined
Apr 1, 2019
Messages
536
June7, Please see attached;

Code:
Option Compare Database

Private Sub Browse_To_File_Click()

    On Error GoTo ErrorHandler
    
    Dim strFullpath As String
    Dim strFolder As String
    Dim strFile As String
    Dim intPos As Integer
    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 Archieve As String
    Dim fsFolder As Object
    
    On Error GoTo ErrorHandler
    
    strFullpath = BrowseFile
    RecordNo = Me!AttachmentID
    Archieve = Archieve_Path ' gets the default path from Module "Constants" Currently C:\users\tandi\Archieve
    
    Set fsFolder = CreateObject("Scripting.FileSystemObject") ' This routine is to create a new folder each year-- Does not work--
    Archieve = Archieve & "" & Year(Date)
    
       If fsFolder.FolderExists(Archieve) = False Then
       fsFolder.CreateFolder (Archieve)
    End If
              
        If strFullpath <> "" Then ' get folder and file names
        intPos = InStrRev(strFullpath, "")
        strFolder = Left(strFullpath, intPos - 1)
        strFile = Mid(strFullpath, intPos + 1)
        InPath = strFullpath
    
    End If
               
    If Len(InPath) > 0 Then
        FileName = Mid(InPath, InStrRev(InPath, "") + 1) 'get the file name
        FileExt = Right(FileName, Len(FileName) - InStrRev(FileName, ".") + 1) ' get the file extension with dot
        OutPath = [Archieve] & "" & "Archive" & RecordNo & Format(Now(), "ddmmyy") & FileExt
         
    If (IsNull(Me.[Out_Path]) Or Me.[Out_Path] = "") Then Me.[Out_Path] = OutPath 'Allows the file save position to be relocated"
     
        FileCopy InPath, Out_Path
        Me!Out_Path = "#" & [Out_Path] & "#"

        MsgBox "Copied file to archives   " & vbCrLf & InPath & vbCrLf & OutPath
     
    End If
     
ExitError:
    Exit Sub
    
ErrorHandler:
  Select Case Err.Number
    Case 9999
        Resume Next
    Case 999
        Resume Next
    Case Else
        Call LogError(Err.Number, Err.Description, "Browse To Batch Archive")
        Resume ExitError
    End Select
 
End Sub
 

GinaWhipp

AWF VIP
Local time
Today, 04:41
Joined
Jun 21, 2011
Messages
5,881
Hmm, I would put a message box after OutPath and see what it returns.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:41
Joined
Sep 21, 2011
Messages
10,574
Wouldn't you want need a
Code:
"\"
at the end of your Archieve path?

Walk through the code line by line with F8 and inspect each variable.?

Or has the site just removed the \ as you did not put it in code tags initially?
 

HillTJ

Registered User.
Local time
Yesterday, 21:41
Joined
Apr 1, 2019
Messages
536
The code runs fine on a new 'browse' but i get the error message when i test to confirm the user cannot change the path by reselecting the 'browse' button on a record where the path has already been selected. I thought it was a syntax error. See my post #1 for details of the error message. The program resumes after this courtesy of the error handling code so no real drama, but not neat.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:41
Joined
Sep 21, 2011
Messages
10,574
The code runs fine on a new 'browse' but i get the error message when i test to confirm the user cannot change the path by reselecting the 'browse' button on a record where the path has already been selected. I thought it was a syntax error. See my post #1 for details of the error message. The program resumes after this courtesy of the error handling code so no real drama, but not neat.

Now thoroughly confused? :confused:

From #1
Hi,after studying various routines I came up with a hybrid of my own. See attached. I decided that it was best to archive documents in a separate folder & not clutter Access. Now I'd like the routine to make a new folder every year to keep size down. This is my code so far. I'd appreciate it if anyone can review it & give me some direction. I've commented out the if statement that I was attempting to figure out for the time being. As always. Cheers
 

HillTJ

Registered User.
Local time
Yesterday, 21:41
Joined
Apr 1, 2019
Messages
536
Gasman, the only problem with my code is that it returns error 52 when a user attempts to browse to a file where a record of a path already exists, that is change the file path field. Hence my inclusion of the is null code to prevent the control being overwritten. I don't know why.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:41
Joined
Sep 21, 2011
Messages
10,574
I would be inspecting all the variables in the debug window.?

You could put a MSGBOX/Debug.Print in the error handling section to dump out your variables if error 52 if you do not want to walk through the code line by line.?
 

Users who are viewing this thread

Top Bottom