Error handling? Don't show picture when text is NULL

[Big Pat]

Records in my database contain (among other things) the paths to two jpegs, "before" and "after" pictures. In any given record, one or both of these may be null.

My report prints one record to a page and prints the pictures using the following code which I adapted from something I found before on this site.

Private Sub Report_Page()

' Two "pairs" of controls:
' 1a txtPhotoPath     1b  RiskPhoto
' 2a txtAFTERPhoto   2b AFTERPhoto 
'
'  1a and 2a are from the report's underlying query
'  1b and 2b are images on the report

On Error GoTo Err_cmdClose_Click

    'set picture paths
    Me.RiskPhoto.Picture = Me.txtPhotoPath
    Me.AFTERPhoto.Picture = Me.txtAFTERPhoto
    
  
Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    If Err.Number = 2220 Then 'can't find the file
        Resume Next
    ElseIf Err.Number = 94 Then 'invalid use of null
        Me.RiskPhoto.Picture = ""         ' <--  Do these lines mean the 
        Me.AFTERPhoto.Picture = ""        ' <--   picture will be blank?
        Resume Next                         '  That's what I'm trying to do!
    Else
        MsgBox Err.Description
        Resume Exit_cmdClose_Click
    End If

End Sub

I have two test records in my database. Record1 has both paths completed; Record2 has only the "Before" path completed. However, when I print the report, the "After" picture from Record1 is printed on BOTH pages.

So the picture needs to be "blanked" or "nulled" or whatever you can do to pictures, if the relevant path is null. I thought that's what the code did, but evidently not.

A previous suggestion was to use an image with the words "No Image selected" or something like that, but I have two problems with that:
1) I can't expect the user to have to specify a picture to say, in effect, "There is no picture".
2) I can't set the path to the "No Image selected" picture by default, because its location will be different on every machine and this needs to work on several standalone PCs, mainly in other firms.

I'm convinced there's a fairly simple correction needed to the code to make it do what I want. Put simply, if any record has only one picture, I want a blank space to be left on the page in it's place. But the picture from the previous record is being retained somehow.

I also tried this...

ElseIf Err.Number = 94 Then 'invalid use of null
        Me.RiskPhoto.Visible = False         
        Me.AFTERPhoto.Visible = False        
        Resume Next

...and I thought I was onto something but I couldn't make it work. I don't really DO code, I'm afraid

Which is why I need help!!

 

[RuralGuy]

I would resolve the issue Before the error:

'set picture paths
If Len(Me.txtPhotoPath & "") = 0 Then
   Me.RiskPhoto.Picture = ""
Else
   Me.RiskPhoto.Picture = Me.txtPhotoPath
End If
If Len(Me.txtAFTERPhoto & "") = 0 Then
   Me.AFTERPhoto.Picture = ""
Else
   Me.AFTERPhoto.Picture = Me.txtAFTERPhoto
End If

 

[Big Pat]

Thanks for the suggestion but I'm afraid it has had no visible effect. The After picture for Record1 is still being printed as the After picture for Record 2, which doesn't have its own.

It seems that when Me.AFTERPhoto.Picture = "", Access just uses the previous picture.

But maybe I misunderstood where your code goes. I just replaced these two lines from my original post

Me.RiskPhoto.Picture = Me.txtPhotoPath
Me.AFTERPhoto.Picture = Me.txtAFTERPhoto

Do I need to take out the whole error-handling section too? Or anything else? Sorry to be dense with this stuff!

 

[RuralGuy]

You did exactly what I was suggesting. What version of Access are you using? What kind of control are you using in the report for the pictures?

 

[Big Pat]

I'm using Access 2007 but this particular database is saved in Access 2002-2003 file format. Could that be relevant?

I'm using Image controls in the report
Control source .... nothing i.e unbound
Picture...............(none)
Picture type........linked

The database is very big and even zipped I don't think it will be small enough to attach so you can get a better look.

 

[RuralGuy]

How about using one of the free file hosting sites?

 

[Big Pat]

Right....just spent the last hour or so stripping out all the non-relevant stuff to get it down to a smaller size. (Even remembered to do this on a COPY not the real version. It wouldn't be the first time I'd screwed up so catastrophically!!)

It's possible you'll get the odd error here or there, because various subforms and other reports no longer exist in this version. But I think it works OK.

I've added some labels in red, to explain what to do. Thank you.

 

[RuralGuy]

I see the problem you have described but do not have a solution just yet. Frankly, I don't work with pictures very much. I'll see if I can get some other help.

 

[RuralGuy]

Instead of fighting the Picture property, just manipulate the Visible property.

'set picture paths
If Len(Me.txtPhotoPath & "") = 0 Then
   Me.RiskPhoto.Visible = False
Else
   Me.RiskPhoto.Visible = True
   Me.RiskPhoto.Picture = Me.txtPhotoPath
End If
If Len(Me.txtAFTERPhoto & "") = 0 Then
   Me.AFTERPhoto.Visible = False
Else
   Me.AFTERPhoto.Visible = True
   Me.AFTERPhoto.Picture = Me.txtAFTERPhoto
End If

You will also need to set those hidden TextBoxes to invisible as well. They can stay InVisible and still be read. It is also not a particularly good idea to name a control the same and the field to which it is bound, event though it is the default assigned by Access.

 

[Big Pat]

Wow, that worked!

I had previously attempted something along those lines but not in conjuction with your technique of checking the length of txtPhotoPath etc. That seems to be what's done the trick.

By hidden text boxes, you mean the ones behind the pictures? Do I really need to make those invisible too? They don't have any borders or anything, so if they're null, they're already "invisible", in the physical sense if not the Access sense.

As for not naming the control the same as the field to which it's bound, I have heard others mention that but I never really understood it. Amateurs like me always stick with the defaults! Is it because when you get into coding it can get ambiguous? Is it the done thing to prefix them with the type of control as in txt, img, cbo, etc?

Anyway, I can't thank you enough for this. Now I just have to copy the relevant bits back into my real database and pray I don't get any of that wrong.

Enjoy the rest of your Sunday. Not much of it left here, but at least I can kick off Monday with one less problem!

 

[Max D]

Big Pat, you can bind Image control to database field with filename in Access 2007.
You don't need any scripting here.

 

[Simon_MT]

I create a Function for the from to collect the Images

Function WhateverForm_GetImages()
 
    Call GetRiskImage
    Call GetAterImage
 
End Function

Then create a function for each Image

Function GetRiskImage()
Dim FullPath As String
    With CodeContextObject
        FullPath = .[Risk Image File]
        If Dir([FullPath]) <> Empty Then
            .[RiskImage].Visible = True
            .[RiskImage].Picture = FullPath
        Else
            .[RiskImage].Visible = False
        End If
    End With
End Function

Function GetAfterImage()
Dim FullPath As String
    With CodeContextObject
        FullPath = .[After Image File]
        If Dir([FullPath]) <> Empty Then
            .[AfterImage].Visible = True
            .[AfterImage].Picture = FullPath
        Else
            .[AfterImage].Visible = False
        End If
    End With
End Function

The trick here is to use the Dir command and to set the respective image control to invisible if it not found.

Simon

 

[RuralGuy]

There are times in coding when you want to reference both the control and the field in the table and having them named the same creates a problem.