VBA to Copy picture from one folder to another (1 Viewer)

[tucker61]

I have some code below that is called on Current Event of page, So when i refresh the page, the code automatically looks at the CatNo. and pulls back the picture from the network Drive, to a local drive, and it it cannot find the network drive - it justs gives me a No Picture.jpg.

I would like to change this so that instead of running on current, it looks at all jobs raised within the last week and copies the pictures over.

Where do i start.

    GetPicture Nz(tbCatNo, "")

Sub GetPicture(CatNo As String)
On Error GoTo Handler
Dim PicPath As String
Dim sURL As String
Dim sLocalFile As String
Dim sLocalFile1 As String
Dim i As Integer
    sLocalFile = Environ("Temp") & "\QAPic.jpg"
    sLocalFile1 = ImagePath1 & CatNo & ".jpg"
    
    PicPath = ImagePath & CatNo & ".jpg"
    If Dir(PicPath) <> "" Then
        SaveFile PicPath, sLocalFile1
    Else
        SaveFile CurrentProject.Path & "\nopicture.jpg", sLocalFile
    End If
    imgWeb.Picture = sLocalFile1
    
    DoEvents
Exit Sub
Handler:
If Err.Number = 70 Then Exit Sub
If Err.Number = 2220 Then
imgWeb.Picture = sLocalFile
Resume Next
End If

        Call LogError(Err.Number, Err.Description, "GetPicture 22", tbJobID)
        Forms!Frmmain.Visible = True
        Exit Sub
End Sub

 

[theDBguy]

Hi. You could create a query to list all the CatNo you want to move/check (within last week?) and then use a loop to go through the records in that query and pass each CatNo to your function.

 

[tucker61]

Thanks, I will have no issues with the query, just not sure how to loop through the records in the query. Any tips ?

 

[theDBguy]

Thanks, I will have no issues with the query, just not sure how to loop through the records in the query. Any tips ?

The basic structure is something like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("QueryName")

With rs
    Do While Not . EOF
        'Call your function here passing the current CatNo
        .MoveNext
    Loop
    .Close
End With

Set rs = Nothing
Set db = Nothing

Hope that helps...

 

[tucker61]

thanks for the above but still struggling to pass the detail from the query to the code. my query is called "Qry_GetPics", and this only contains 1 field "Cat_No"then i want to run the code below to copy the picture from one location to the next.

On Error GoTo Handler
Dim PicPath As String
Dim sURL As String
Dim sLocalFile As String
Dim sLocalFile1 As String
Dim i As Integer
    sLocalFile = Environ("Temp") & "\QAPic.jpg"
    sLocalFile1 = ImagePath1 & CatNo & ".jpg"
    
    PicPath = ImagePath & CatNo & ".jpg"
    If Dir(PicPath) <> "" Then
        SaveFile PicPath, sLocalFile1
    Else
        SaveFile CurrentProject.Path & "\nopicture.jpg", sLocalFile
    End If
    imgWeb.Picture = sLocalFile1
    
    DoEvents
Exit Sub
Handler:
If Err.Number = 70 Then Exit Sub
If Err.Number = 2220 Then
imgWeb.Picture = sLocalFile
Resume Next
End If

        Call LogError(Err.Number, Err.Description, "GetPicture 22", "")
        Forms!frmmain.Visible = True
        Exit Sub
End Sub