VBA Causes Access to crash

[brharrii]

I have a piece of code that I'm using to display an image on a report based on a path saved to each record. the code is:

Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.ImagePath) Then
 Me.ImgPic.Picture = "O:\Bellingham\Intranet\Production\Labels\No Label.bmp"
 Else
 Me.ImgPic.Picture = Me.ImagePath
 End If
End Sub

it's simple and it usually works great..... except when it doesnt. It seems like every few months the code crashes access and then never works again. When I debug, the part that is highlighted is:

 Me.ImgPic.Picture = Me.ImagePath

The only way i've found to correct it is to delete the report and the module and copy them back in from a backup database. it has become a somewhat stressful ordeal as it always seems to come up at the least opportune times and I wondered if anyone had an idea on what could be causing this code to crash or suggestions for stabalizing my database to prevent this from happening again.

Thanks

Bruce

 

[MikeLeBen]

Does the image path lead to a network resource?
Did you test Me.ImagePath's value ('debug.print Me.ImagePath' or by looking at the local variables window)?

 

[brharrii]

I haven't done that yet, I am not too familiar with debug techniques. Something I need to remedy

Where would I type 'debug.print me.imagepath?

 

[MikeLeBen]

Change your routine as follows:

Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.ImagePath) Then
 Me.ImgPic.Picture = "O:\Bellingham\Intranet\Production\Labels\No Label.bmp"
 Else
 Debug.print Me.ImagePath
 'Me.ImgPic.Picture = Me.ImagePath
 End If
End Sub

and make sure you have your immediate window visible (View -> Immediate Window), or something, I have the Italian version so the name might be slightly different..

 

[brharrii]

Now it's saying that I've entered an expression that has no value:

debug.print me.imagepath

 

[MikeLeBen]

Why don't you just assign the path to the variable without checking with IsNull()? That way you can make sure it will always hold a value - I'm not too keen on reports, but I am under the impression you would not need to be adding images with code?

 

[brharrii]

The reason for the code is that the images are not uploaded to the database. I did this to prevent the database from getting too bloated and running slowly. At the time that I set this up, I was under the impression that I would need code to achieve this. I should research it again though and see what other options I have.

What would the code look like with me.imagepath declared as a variable? I'd certainly be open to trying that to see if it helped improve performance.

Thanks again!

 

[MikeLeBen]

I think you can add images from remote paths with the report structure view (I am sure you can do that with forms, so don't take it for granted).

In case you want to code it anyway you can:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.ImgPic.Picture = "O:\Bellingham\Intranet\Production\Labels\No Label.bmp"

End Sub

 

[brharrii]

Thank you Mike, The problem is that the image is different depending on which record I'm calling up. Each record has a path saved with a unique image to that record. It needs to be displayed on the report that corresponds with the record.

 

[MikeLeBen]

No problem, mate. I assume you went for the coded solution

 

[brharrii]

The coded solution didn't give me the option to add the image when there is one saved on the record. imagepath = a field in my table.

When the field is populated, it is the path to find the image that needs to be used on the report. When the field is not populated, it uses a default path: "O:\Bellingham\Intranet\Production\Labels\No Label.bmp".

The idea is that each record has a label that goes with it. The label for that specific record needs to display on the report. It's dependent on the record from the report.

Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.ImagePath) Then
Me.ImgPic.Picture = "O:\Bellingham\Intranet\Production\Labels\No Label.bmp"
Else
Me.ImgPic.Picture = Me.ImagePath
End If
End Sub

 

[RainLover]

Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Insert a line after Option Compare Database
"Option Explicit" This should be standard for every Module.

Me.ImgPic.Picture = "O:\Bellingham\Intranet\Production\Labels\No Label.bmp"

Can you change No Label to NoLabel. Spaces can cause problems.

 

[RainLover]

I see you solved your problem while I was typing.

However my advice is standard and still applies.

 

[brharrii]

Hi RainLover,

I changed the code as you suggested and also changed the name of the label to NoLabel.bmp.

This seemed to work for now Maybe the space was causing problems. I'll keep an eye on it and post back if I run into this issue again.

Thank you!

 

[MikeLeBen]

Oh, I see. The more details you give, the easier it is for me (and others) to help you. I also wanted to ask you when is this sub run, upon clicking a button, or upon which event?

What if you were to use the following, instead:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyPath as String

MyPath = Nz(DLookup("[path]", "tblpaths", "[ID] = " & Me.ID), 0)

If MyPath = 0 Then
Me.ImgPic.Picture = "O:\Bellingham\Intranet\Production\Labels\No Label.bmp"
Else
Me.ImgPic.Picture = MyPath
End If

End Sub

You would have to make some adjustments to the DLookup function's parameters naming the field and table and ID field according to your table structure.

 

[RainLover]

If you are happy with what you have then that is fine.

However I can't see why you simply don't create a Link in the Table. So each Record has a different file that it refers to.

This would eliminate all code.

One last point is that your Path is too long. This will slow things down some. Create a new folder closer to the Root Directory.

 

[brharrii]

apparently I haven't been doing it that way because I like doing things the hard way

I just made a few changes per your recommendation and then deleted the VBA code and everything works great!

Thank you!

 

[RainLover]

Good to see.

Usually if you use a lot of code for something simple then look at alternatives.

 

[brharrii]

one more little detail. for some reason this setup works for the paths that i used sql to copy over from the old field. When I try to add a new path manually to the new field, the image won't populate on my form.

I setup the control by adding an image box to my form and setting the control source equal to the new hyperlink field [labelImage].

Any idea what I might be missing? i've checked and double checked the path and even copied one over manually from another record that I know works and it isn't showing up in the image box.

 

[RainLover]

Post an example of the link.

What Data Type did you use in the table.