How do I Use the PictureData property in a macro?

Theo Fitchner
Theo Fitchner used Ask the Experts™
on
Hello everyone. I have an Access frontend linked to an MS SQL backend. An employees table has a field for each employee's photo (EmpPic). The data type (per Microsoft's advisory) is varbinary (MAX).

I am trying to use to PictureData property to set the value of a blank image box (Image81) to the content of the photo field. I can do it by VBA by typing:

Me.Image81.PictureData = Me.EmpPic.Value


However, I am trying to accomplish the same thing by a macro by using the SetValue action by typing

Set: [Form].[Image81]
To: [Form].[EmpPic]

When the macro runs, I get the error:

Access failed to evaluate one or more expressions because 'PictureData' was referenced in an expression. Only functions and properties that are considered to be safe are allowed in expressions when Access runs in sandbox mode.

How do I fix this? I would really love to do it via a macro. The entire Access frontend uses macros and no VBA. Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
use Picture instead of PictureData

SetValue
           [Forms]![NameOfForm].[Image81].[Picture]
            [Forms]![NameOfForm].[EmpPic]
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
SetValue is not a "safe" macro command because it can change data.  You may need to change the defaults to allow it (File, Options, Trust Center, Trust Center Settings, ...).  To show SetValue from the list to pick, enable Show All Actions from the macro design ribbon ribbon_Macro_Design_ShowAllActions.jpgThere is no reason not to use VBA if macros are going to do "unsafe" things anyway.  You lose a lot of power in an Access application without VBA.
Top Expert 2016

Commented:
it is not the SetValue that is triggering the unsafe message, it is the property "PictureData", so you need to use "Picture" instead.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
according to Microsoft, SetValue will not be allowed if the database is not trusted.
https://msdn.microsoft.com/en-us/library/office/ff820771.aspx
Top Expert 2016

Commented:
that is true, but even if you set Enable all macros, the "PictureData" property still raised the error
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
SetValue can't be used with objects

Author

Commented:
@Rey Obrero

I've tried using the Picture property.  When the macro is called, I get an error message stating

"The setting for this property is too long.  You can enter either 255 or 2048 characters for this property,  depending on the data type. "


I've tried using an absolute reference where the name of the form is used as well as a dynamic reference :Form.Image81

I can't figure out what I am doing wrong.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
Theo, since you have to trust the database anyway, why not use VBA?  Macros are extremely limited compared to the big world that opens with VBA.
Top Expert 2016

Commented:
@Theo,
post the macro that you use.

what is the value of  [EmpPic] ? is it a path to the picture?
what  is the name of the form?

Author

Commented:
@crystal (strive4peace)

The database is trusted.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
Theo, yes, I understand ~ please read my previous comments.

Rey brought up a point about storing path and filename instead of the actual picture itself in the Access database. That would be a good thing to do. For database 'attachments', I keep them in files and put a directory below the back-end, which may also have folders in it.  Then just a path relative to the back-end is stored in the database, along with the filename, usually in a Short Text field set to 255 (max size).  An Image control source can be a path\file.

Author

Commented:
@Rey Obrero

I have attached a snapshot of the macro. The SetValue action in question is highlighted/selected. I am storing the actual picture in the table and not the path to it. Thanks.
Faulty-Picture-Macro.JPG
Top Expert 2016

Commented:
just tested by using "C:\Users\Rey_PC\Pictures\james.png" as the value of   [EmpPic], and it work.

what is the value of your  [EmpPic] ?

Author

Commented:
@Rey Oberero

I'm not storing the path. I am storing the actual images in the database table. Hence the need for the varbinary (Max) field.
Top Expert 2016

Commented:
I never have store image to a table.
Are you using the Attachment data type?

Author

Commented:
@Rey Obrero

No, I'm not using the attachment data type. The Attachment data type is specific to local Access tables only. I'm storing my data in a database in  SQL Server.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
hi Theo,

what you are wanting to do is copy the value of one object (a picture) to another.  This cannot be done with a SetValue macro action, or in a database that is not trusted.  Why not simply bind an Image control to the data and then you don't have to worry about changing the source for the 'blank image box'?

Author

Commented:
@Crystal

The database is trusted. About binding an image control to the picture date field (EmpPic), I've tried that but it didn't work.

Or is they're anther image control?

As far as I know, there are three controls that can be used to display images. They are:

1. Image Control
2. Bound Control
3. Unbound Control

I've tried the first one. It's the one that allows you to pick an image from the file system. In design view, after placing the control on the form, I close the file Explorer dialog box that prompts me to select an image. - (I don't select an image) . I then enter the name of the field that contains the picture (EmpPic) as its control source.

When I switch to form view, the picture doesn't show. The image box itself doesn't show (likely because it contains no data). Even though, the EmpPic field does contain an employee photo.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
apparently image is not stored in a format that the Image control can render -- try the bound control.  

>> The database is trusted <<
that means it can run code and macros that change data -- so I think you mean 'not trusted'

Author

Commented:
Attached with this comment is a sample Access front end. It is linked to an online MS SQL db. The Edit Employee form (frmEditEmployeeInfo) is what I have a problem with. I repeat that the database is indeed trusted. The images were originally inserted with a 3rd party ActiveX control - AccessImagine. I have attached that as well because it needs to be installed.

The form loads the record of the employee whose ID I entered in the form header. The ActiveX control refuses to display the pic this way; hence my attempt at displaying it in an image control. In design view, you'll notice an image control under the ActiveX control. The load button beneath it uses VBA to show the pic of EmpPic in the image control.

I believe the AccessImagine control doesn't use an OLE wrapper when saving the image into the table.

You can download the AccessImagine ActiveX control from here - https://access.bukrek.net/download?from=cta

Thanks.
Employee-Photos-Test.zip

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial