Results 1 to 7 of 7
  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185

    Do While Process is saying I am missing a file

    When I go through the loop process to find all the files in the completed folder. It says the completed folder is missing a file for district 47, would you like to continue the consolidating approval files" Yes or No



    Code:
    Function CheckDMApprovals()
    DateXVar = InputBox("Enter Request Date: (Should be last Friday, " & Format(Date - 1 - Format(Date, "w"), "m/d") & ")")
    DateXVar = DateXVar + 7 - Format(DateXVar, "w")
    
    
    If IsDate(DateXVar) = False Or IsNull(DateXVar) = True Then
        MsgBox "Cancelled"
        End
    End If
    
    
    FolderVar = "G:\Teams and Projects\DSM Approval-Store Supply Orders\" & Format(DateXVar, "mm-dd-yy")
    strfile = dir("G:\Teams and Projects\DSM Approval-Store Supply Orders\" & Format(DateXVar, "mm-dd-yy") & "\District_??_??-??-??.xlsx")    ' - telling process which type of file names to look for ("?" is a wild card character)
     
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE [DM-ApprovalFiles].* FROM [DM-ApprovalFiles];"
    DoCmd.SetWarnings True
         
        '**  Begin the loop process.
        Do While Len(strfile) > 0  ' - Len() returns the number of characters in "strfile".  As long as it finds a file, this will be >0
            
            If FileOrDirExists(FolderVar & "\COMPLETED\" & strfile) = False Then
                AnswerVar = MsgBox("The 'Completed' folder is missing a file for district " & Mid(strfile, 10, 2) & vbNewLine & "Would you like to continue consolidating approval files?", 4, "MISSING " & Mid(strfile, 10, 2))
                If AnswerVar <> 6 Then
                    MsgBox "Approval file consolidation ended."
                    End
                Else
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL "UPDATE ReqLists INNER JOIN DistrictTable ON ReqLists.Store = DistrictTable.Store SET ReqLists.[AllowedQty-DM] = IIf([ReqLists]![AllowedQty-DM] Is Null,0,[ReqLists]![AllowedQty-DM]), ReqLists.[AllowedQty-StoreOps] = IIf([ReqLists]![AllowedQty-StoreOps] Is Null,0,[ReqLists]![AllowedQty-StoreOps]) WHERE (((ReqLists.Date)=#" & DateXVar - 1 & "#) AND ((DistrictTable.District)=" & Mid(strfile, 10, 2) & "));"
                    DoCmd.SetWarnings True
                End If
            Else
                DoCmd.SetWarnings False
                DoCmd.RunSQL "INSERT INTO [DM-ApprovalFiles] ( FileName, District ) SELECT '" & FolderVar & "\COMPLETED\" & strfile & "' AS Expr1, " & Mid(strfile, 10, 2) & " As Expr2;"
                DoCmd.SetWarnings True
            End If
            
            strfile = dir
            ' - resets the "strfile" to the next file in the folder, if there is one
        Loop ' - restart loop.
        
    x = Shell("C:\Program Files\Microsoft Office\Office14\EXCEL.EXE  K:\Departments\Shipping\Eric\StoreSupplyProject\ApprovalFiles\StoreOpsApproved.xlsm", vbMaximizedFocus)
    
    
    End Function
    
    
    
    
    
    
    
    
    Function FileOrDirExists(PathName As String) As Boolean
         'Macro Purpose: Function returns TRUE if the specified file
         '               or folder exists, false if not.
         'PathName     : Supports Windows mapped drives or UNC
         '             : Supports Macintosh paths
         'File usage   : Provide full file path and extension
         'Folder usage : Provide full folder path
         '               Accepts with/without trailing "\" (Windows)
         '               Accepts with/without trailing ":" (Macintosh)
         
        Dim iTemp As Integer
         
         'Ignore errors to allow for error evaluation
        On Error Resume Next
        iTemp = GetAttr(PathName)
         
         'Check if error exists and set response appropriately
        Select Case Err.Number
        Case Is = 0
            FileOrDirExists = True
        Case Else
            FileOrDirExists = False
        End Select
         
         'Resume error checking
        On Error GoTo 0
    End Function
    This is the specific code where it crashed,

    Code:
    If FileOrDirExists(FolderVar & "\COMPLETED\" & strfile) = False Then
                AnswerVar = MsgBox("The 'Completed' folder is missing a file for district " & Mid(strfile, 10, 2) & vbNewLine & "Would you like to continue consolidating approval files?", 4, "MISSING " & Mid(strfile, 10, 2))
                If AnswerVar <> 6 Then
                    MsgBox "Approval file consolidation ended."
                    End
                Else
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL "UPDATE ReqLists INNER JOIN DistrictTable ON ReqLists.Store = DistrictTable.Store SET ReqLists.[AllowedQty-DM] = IIf([ReqLists]![AllowedQty-DM] Is Null,0,[ReqLists]![AllowedQty-DM]), ReqLists.[AllowedQty-StoreOps] = IIf([ReqLists]![AllowedQty-StoreOps] Is Null,0,[ReqLists]![AllowedQty-StoreOps]) WHERE (((ReqLists.Date)=#" & DateXVar - 1 & "#) AND ((DistrictTable.District)=" & Mid(strfile, 10, 2) & "));"
                    DoCmd.SetWarnings True
                End If
            Else
    This is the file that is giving me fits

    District_47_10-10-16 .xlsx

  2. #2
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    It is in the same folder as all the other files that i loops through. It just always seems to stop when it tries to find this District 47 file. Are there any reasons why this would be happening.

    Thanks,
    Nick

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    District_47_10-10-16 .xlsx

    Is that a space between 16 and .xlsx? See if that matches the actual file name and maybe remove the space in both?

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    While you're following up on the suggestion, note this. If a procedure with an error handler calls CheckDMApprovals, Access will go 'upstream' looking for an active procedure that has one, so maybe you're ok. If there isn't one and your function errors out, you can leave warnings turned off. For instance, suppose this returns an error for any reason (design changes on a table/field, disconnection from the network where the BE tables a kept, whatever)
    DoCmd.RunSQL "UPDATE ReqLists INNER....
    as written, your warnings will remain turned off. You should do one or both of two things when altering environment settings such as this or turning the mouse pointer to an hourglass for example:
    1) use the Database.Execute method with dbFailOnError (wrapped in a Transaction if you want to roll back changes to data [not files]) so that you don't have to turn warnings on and off.
    2) Almost always have an error handling block to ensure environment settings are reset and you clean up memory and close recordsets.
    I structure things like this:

    SomeFunction() (or Sub)
    Dim stuff

    On Error GoTo errHandler
    Do stuff...

    exitHere:
    clean up, close recordsets, reset environment settings, etc.
    Exit Function

    errHandler:
    message boxes, whatever is needed to deal with the error.
    If it results in suspension of the procedure rather than returning to
    some point at/near where the error occurred, end handler with

    Resume exitHere

    End Function
    Note how normal execution will end at the Exit Function line, otherwise things are cleaned up in spite of any error that may result in termination.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Along with that, the top two lines of EVERY code module should be:
    Code:
    Option Compare Database
    Option Explicit
    I couldn't get your code to execute without major changes because there are not any declared variables.
    It looks like every variable in the function is a variant type. (unless you have a lot of global variables declared)

  6. #6
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Thanks Bulzie for the catch. It was a space before the file extension. Thank you everyone for helping.

  7. #7
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Quote Originally Posted by ssanfu View Post
    Along with that, the top two lines of EVERY code module should be:
    Code:
    Option Compare Database
    Option Explicit
    I couldn't get your code to execute without major changes because there are not any declared variables.
    It looks like every variable in the function is a variant type. (unless you have a lot of global variables declared)
    Sorry I didn't add the entire module. For my future posts I should include any variables? So they can run my code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-27-2016, 02:00 AM
  2. Replies: 4
    Last Post: 04-06-2016, 07:16 AM
  3. MsysDb file missing in -Access 2007
    By danvadis in forum Forms
    Replies: 1
    Last Post: 03-15-2016, 06:44 AM
  4. Missing .mdw file for inherited MS Access 2007 DB
    By NewMexicoNovice in forum Security
    Replies: 4
    Last Post: 05-07-2013, 01:55 PM
  5. Missing file
    By faceofevil in forum Access
    Replies: 2
    Last Post: 11-01-2010, 10:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums