Changing Image Path with VBA

[Nathan87]

Hello,

I have a form that I would like to update a picture on using VBA. The source of the picture path is in part a query that is not bound to the form. So far I have the following code that is pretty much working, but with a couple flaws.

Private Sub Form_Current()
    LoadDefaultPicture
End Sub

Sub LoadDefaultPicture()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim rs As DAO.Recordset
    Dim strFolderPath As String
    Dim strDefaultPictureName As String
    
    Set db = CurrentDb
    
    Set qdf = db.QueryDefs("DefaultPictureQry")
    qdf.Parameters("IDfilter").Value = Forms![ItemDetailTable]![ID]
    
    Set rs = qdf.OpenRecordset
    
    strFolderPath = Forms![ItemDetailTable]!AttachmentFolderPath
       
    strDefaultPictureName = rs.Fields("AttachmentName")
    
    Forms![ItemDetailTable]![DefaultPicture].Picture _
        = strFolderPath & strDefaultPictureName

End Sub

This is working. However, when I change the record the picture flashes the current picture once and then loads the new picture. It is like it reloads the current picture then loads the new one. I'm hoping there is a way to get rid of the flash.

Also, the code fails here:
strDefaultPictureName = rs.Fields("AttachmentName")
when the query does not return a record. I can definitely fix this by adding an if statement to check for a record, but I'm kind of perplexed at why it is failing at that line. I would expect it to assign an empty string to that variable name and then fail on the next command where I try and set the ".Picture" property of the image.

If anyone could shed some light on the couple of issues it would be a big help.

Thanks.

 

[MarkK]

strDefaultPictureName = rs.Fields("AttachmentName")

I would expect it to assign an empty string to that variable name

An empty string is something. No record is nothing. It is possible to have a valid record in a table that contains an empty string in every field, so a query that returns no records is not the same as a query that returns 5 rows of empty strings.
Common practice is to test for .eof.

    strFolderPath = Forms![ItemDetailTable]!AttachmentFolderPath
       
    Set rs = qdf.OpenRecordset
    If Not [COLOR="Red"]rs.eof[/COLOR] then 
        strDefaultPictureName = rs.Fields("AttachmentName")
        Forms![ItemDetailTable]![DefaultPicture].Picture = strFolderPath & strDefaultPictureName
    Else
        msgbox "New Record"
    End If

Try this for the flicker . . .

Private Sub Form_Current()
on error goto handler
    Echo False
    LoadDefaultPicture
final:
    Echo True
    Exit Sub
handler:
    msgbox err & " " & err.description
    resume final
End Sub

This turns off screen repainting for the whole application for the duration of the LoadDefaultPicture routine. We make sure we handle errors well otherwise we won't be able to interact with the application if Echo True never gets called.

Hope this helps,

 

[Nathan87]

Thanks a lot Mark. When you explain the empty string that makes a lot of sense. I'm not sure why that didn't occur to me.

As for the flicker fix, that seems like it should clear it up. I'll give it shot now.

Thanks!

 

[MarkK]

Isn't this the weird thing about knowledge . . .

I'm not sure why that didn't occur to me.

Once you know a thing, it's obvious, but before you know a thing it's completely impenetrable.

Have a good one,

 

[Nathan87]

For some reason the flicker is still there even with the echo set to false.

Could I be activating the "on current" event more than once by switching records? I'm changing records using the arrows at the bottom of the form. I would think advance it one record would only trigger 1 on current event, but maybe I'm wrong there.

 

[Nathan87]

It looks like I'm only calling Form_Current() once when switching records. I put a break point in the code at the Echo False line of the Form_Current() sub so that I could verify that.

 

[Nathan87]

It looks like the whole screen is flickering. I just noticed it's not just the picture that flashes. The text boxes with values also reload so it makes me think the record is changing and loading and then reloading the whole form when I change the image path with VBA.

Is there a way to change the image path prior to the new record loading so that it only loads once? Or, should I try and find a way to turn the Echo of before the the record switches?

 

[MarkK]

What happens if you comment out the picture change altogether? Do you still get flicker on Current? Find a way to verify what causes what by turning off big chunks of your process. What happens if you comment out the current event handler altogether? What happens on other forms when you change records?

 

[Nathan87]

With the picture change commented out it doesn't flicker at all. It looks like what is happening is the record is changing and loading with the old picture, then the picture changes and the whole form reloads. So the picture redraws twice, once with the old and then with the new picture. I tried experimenting with some of the other events to use for the picture change to see if I could catch the record change before it actually loads the new record, but haven't been successful.

Can you think of a different way of changing the picture that may be better? I'm starting think maybe I'm just going about it the wrong way from the get go. Maybe, I need to get the picture name in into the record source. I was trying to avoid that because I want some logic behind the picture choice, but maybe that is the easiest.