Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Error 3622

  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I am trying to put the finishing touches to a piece of vba that was created over a year ago. The vba looks at fields within a query, where there are file paths and names for pictures and then copy's a renamed version to a different directory.

    Since this was created, I have migrated the backend to SQL, which is what seems to be causing the problem.

    The code is:
    Code:
     ' Change the constants as needed
      ' Name of the table
      Const strTable = "qryRightmoveFeedExport"
      ' Name of ID field
      Const strIDName = "PropertyID"
      ' Prefix of field names
      Const strPrefix = "Media_Image_"
      ' Lowest number for field
      Const intMin = 0
      ' Highest number for field
      Const intMax = 9
      ' Path of destination folder - MUST end in backslash
      Const strFolder = "\\Server\Database\Rightmove\Photos\"
    
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      Dim strID As String
      Dim strField As String
      Dim strOld As String
      Dim strNew As String
      Dim i As Integer
      Dim intPosDot As String
      Dim strExt As String
    
      On Error GoTo ErrHandler
    
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
      Do While Not rst.EOF
        strID = rst.Fields(strIDName)
        SysCmd acSysCmdSetStatus, "Processing record ID " & strID
        For i = intMin To intMax
          strField = strPrefix & Format(i, "00")
          If Not IsNull(rst.Fields(strField)) Then
            strOld = rst.Fields(strField)
            intPosDot = InStrRev(strOld, ".")
            strExt = Mid(strOld, intPosDot)
            strofficeid = DLookup("Branch_ID", "qryRightmoveFeedExport", "PropertyId= " & strID)
            strNew = strFolder & strofficeid & "_" & strID & "_IMG_" & Format(i, "00") & strExt
            FileCopy strOld, strNew
          End If
        Next i
        rst.MoveNext
      Loop
    
    ExitHandler:
      On Error Resume Next
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
      Exit Sub
    
    ErrHandler:
      msgbox Err.Description, vbExclamation
      Resume ExitHandler
    The error occurs on: Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)

    Any ideas?
    Best Regards,

    Luke

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The only thing I see that is different from what we typically do is that we would add dbSeeChanges to your set statement - it would look like:
    Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset, dbSeeChanges)
    If that doesn't fix the error, and it seems it may be a long shot, then I would suspect some issue with your query that is being set as the target for strTable.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Worked a treat! Thank you.
    Best Regards,

    Luke

  4. #4
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On some of the files that it has to move, if the file path is not valid and the file does not exist it comes up with an error / debug screen, could I get it to move onto the next one, or even better create a log file of the incomplete ones?

    I idea is that this will be left unattended.
    Best Regards,

    Luke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could handle this in the ErrHandler: section.
    Temporarily change the line

    MsgBox Err.Description, vbExclamation

    to

    MsgBox "Error " & Err & ": " & Err.Description, vbExclamation

    When the error occurs, you'll see its error number. Write it down.

    You then add code to handle the "file not found" error, for example:

    Code:
    Select Case Err
    Case 53 ' File not found
     Resume Next
    Case Else ' Other errors
     Resume ExitHandler
    End Select

  6. #6
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You could handle this in the ErrHandler: section.
    Temporarily change the line

    MsgBox Err.Description, vbExclamation

    to

    MsgBox "Error " & Err & ": " & Err.Description, vbExclamation

    When the error occurs, you'll see its error number. Write it down.

    You then add code to handle the "file not found" error, for example:

    Code:
    Select Case Err
    Case 53 ' File not found
     Resume Next
    Case Else ' Other errors
     Resume ExitHandler
    End Select

    So should the error handler look like:

    ErrHandler:
    msgbox "Error " & Err & ": " & Err.Description, vbExclamation
    Select Case Err
    Case 53 ' File not found
    Resume Next
    Case 76 ' Path not found
    Resume Next
    Case Else ' Other errors
    Resume ExitHandler
    End Select
    Resume ExitHandler
    Best Regards,

    Luke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, but you don't need the last Resume ExitHandler after End Select, it will never be executed.
    Whether you want to keep the MsgBox is up to you.

  8. #8
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Still keeps coming up with the debug screen and not resuming the next process
    Best Regards,

    Luke

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code is as follows:

    Dim dbs3 As DAO.Database
    Dim rst3 As DAO.Recordset
    Dim strID3 As String
    Dim strField3 As String
    Dim strOld3 As String
    Dim strNew3 As String
    Dim i3 As Integer
    Dim intPosDot3 As String
    Dim strExt3 As String

    On Error GoTo ErrHandler

    Set dbs3 = CurrentDb
    Set rst3 = dbs3.OpenRecordset(strTable, dbOpenDynaset, dbSeeChanges)
    Do While Not rst3.EOF
    strID3 = rst3.Fields(strIDName3)
    SysCmd acSysCmdSetStatus, "Processing record ID " & strID3
    For i3 = intMin3 To intMax3
    strField3 = strPrefix3 & Format(i3, "00")
    If Not IsNull(rst3.Fields(strField3)) Then
    strOld3 = rst3.Fields(strField3)
    intPosDot3 = InStrRev(strOld3, ".")
    strExt3 = Mid(strOld3, intPosDot3)
    strofficeid3 = DLookup("Branch_ID", "qryRightmoveFeedExport", "PropertyId= " & strID3)
    strNew3 = strFolder3 & strofficeid3 & "_" & strID3 & "_IMG_" & Format(i3, "00") & ".jpg"
    FileCopy strOld3, strNew3
    End If
    Next i3
    rst3.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub


    ErrHandler:
    msgbox "Error " & Err & ": " & Err.Description, vbExclamation
    Select Case Err
    Case 53 ' File not found
    Resume Next
    Case 76 ' Path not found
    Resume Next
    Case Else ' Other errors
    Resume ExitHandler
    End Select
    Resume ExitHandler


    Exit Sub
    End Sub
    Best Regards,

    Luke

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You're using variables dbs3 and rst3, so in the ExitHandler section you should use dbs3 and rst3 instead of dbs and rst.


  11. #11
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    still getting the vb debug window coming up regardless of making those alterations.
    Best Regards,

    Luke

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    On which line? What is the error message?

  13. #13
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    FileCopy strOld3, strNew3


    You get the debug msg box, with the error code 53 in.
    Best Regards,

    Luke

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What happens if you remove or comment out the line

    msgbox "Error " & Err & ": " & Err.Description, vbExclamation


  15. #15
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The same error message. It is as though it is not looking at the error handler.
    Best Regards,

    Luke

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •