Theo Fitchner
asked on
How do I Use the PictureData property in a macro?
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.
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.
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 
There 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.
There 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.
it is not the SetValue that is triggering the unsafe message, it is the property "PictureData", so you need to use "Picture" instead.
according to Microsoft, SetValue will not be allowed if the database is not trusted.
https://msdn.microsoft.com/en-us/library/office/ff820771.aspx
https://msdn.microsoft.com/en-us/library/office/ff820771.aspx
that is true, but even if you set Enable all macros, the "PictureData" property still raised the error
SetValue can't be used with objects
ASKER
@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.
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.
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.
@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?
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?
ASKER
@crystal (strive4peace)
The database is trusted.
The database is trusted.
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.
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.
ASKER
@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
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
just tested by using "C:\Users\Rey_PC\Pictures\
what is the value of your [EmpPic] ?
what is the value of your [EmpPic] ?
ASKER
@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.
I'm not storing the path. I am storing the actual images in the database table. Hence the need for the varbinary (Max) field.
I never have store image to a table.
Are you using the Attachment data type?
Are you using the Attachment data type?
ASKER
@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.
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.
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'?
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'?
ASKER
@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.
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.
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'
>> The database is trusted <<
that means it can run code and macros that change data -- so I think you mean 'not trusted'
ASKER
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
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
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
SetValue
[Forms]![NameOfForm].[Imag
[Forms]![NameOfForm].[EmpP