How to change an image on a form programatically

[Petrol]

I want to distribute my DB to different "department s". Each will have their own BE data (all the same format and structure, but each with their own pwd) and all will use a common FE to access it. To give them a sense of ownership I want to display the name and logo of the department in the header of the switchboard form. The names and logos of each department are stored in successive rows of a BE table.

The name is no problem ... I simply use DLookup to extract the department name from the Departments table and set it into to Caption property of a label object in the form header. But I can't find anything equivalent to do about the image, which is an object attached to the same row of the table.

(I know conventional wisdom says "Don't store the image in the back end, link to it" but in this case there are only 20 of them and they are only small images (20-50Kb) and I want to distribute them with the back ends).

So how can I change an image dynamically?

[ADezii]

You can store the Logos (Graphic Files) in the Departments Table as BLOBs (Binary Large Objects) in an OLE Object Field along with associated Data. Depending on what Department is selected, you can then extract the Binary Data from the Field and write it to a File which can then be dynamically assigned to the Picture Property of an Image Control. This approach is a little complex but will not cause any bloat in the DB and requires no external Files. Another option may be to store the actual Graphic Files in a Folder along with their Path in a Departments Table Field. In this manner, they can again be dynamically loaded into the Image Control depending on the Department chosen.

[NeoPa]

Hi ADezii. I guess the real question is "Is it possible to assign the BLOB contents of a field into an Image Control either by using some sort of reference to that field in a Control Property or assigning the BLOB contents into one of the Control Properties as data?".

[Petrol]

Yes, NeoPa, that's exactly the real question - thank you for expressing it with the correct technical terminology.

ADezii, thank you for the suggestions. As NeoPa implies, it seemed a bit wasteful to have to write an image to a file and then read it back in again, when I have it sitting in the database all along. So I'd still prefer to assign the image to the Image Control directly if possible.

All the same, when I think about it I guess the file would only have to be written once when the department implements the database, and then read once each time they open it. So I've been trying to implement that idea, but I'm struggling because I'm confused reading about object variables and object expressions in the Microsoft documentation. I tried

Dim CommunName As String, strSQL As String, imgBadge As Image, BadgeFile As String, n As Integer

BadgeFile = EDBPath & "CommunBadge.dat"
n = FreeFile()
Open BadgeFile For Output As #n
'   Extract image of badge from the table and write it to a file
imgBadge = DLookup("[Badge]", "Departments", "[DepartmentName] = " & CommunName)
Print #n, imgBadge
Close #n

but got Error 91,Object variable not set. So I changed the assignment statement to a set statement

Set imgBadge = DLookup("[Badge]", "Departments", "[DepartmentName] = " & CommunName)

and got Error 424, Object required.
Could one of you please straighten out my thinking?

[ADezii]

@NeoPa:
Thanks for the clarification.

@Petrol:
To the best of my knowledge, there is no method by which a direct assignment of an Image in an OLE Object Field can be made to an Image Control. If the Image Control is bound to the Field, than that is a different matter.

[ADezii]

I have found a round-a-bout method to accomplish what you are asking. It is a little unorthodox, but given the small size of your Graphics, it may be feasible. Only you can decide.

1. Create a Form named frmLogos. frmLogos will contain nothing but Image Controls with Embedded Images for all your Departments named accordingly: imgMarketing, imgFinance, imgAdmin, imgProgramming, etc.
2. Open frmLogos in Hidden Mode.
3. Depending on what Department you choose, Load the PictureData of the corresponding Image Control in frmLogos to the Image Control (imgLogo) on your Main Form using the 'img' Prefix and Department Name.
4. Close frmLogos.
5. The Coding is minimal and works very well. The only overhead is setting up imgLogos with the Image Controls and strict Naming Conventions. Only you can decide if this will work for you.
6. Code Definition:
   

   Dim frm As Access.Form
   Const conDEPT = "Marketing"     'Simulates a Department
   
   DoCmd.OpenForm "frmLogos", acNormal, , , acFormReadOnly, acHidden
   Set frm = Forms("frmLogos")
   
   Me![imgLogo].PictureData = frm.Controls("img" & conDEPT).PictureData
   
   DoCmd.Close acForm, "frmLogos"

[NeoPa]

Just a thought ADezii, but whatever you're doing with multiple Controls on a Form could surely be done more straightforward ly with a single Control after navigating to the correct record first. It avoids much of the complication in your code no?

After all, we know there is already a table with such BLOB data contained therein.

[ADezii]

whatever you're doing with multiple Controls on a Form could surely be done more straightforward ly with a single Control after navigating to the correct record first. It avoids much of the complication in your code no?

Makes sense to me, not sure what I would do if you weren't there to point me in the right direction and keep me on the straight-and-narrow!(LOL). Just to clarify, are you referring to binding the Image Control in frmLogos to the OLE Object Field in tblLogos, navigating to the appropriate Record, then setting the PictureData Property of the Image Control on the Main Form to the that of the Image Control on frmLogos?

[valforchin]

You guys are very smart, your knowledge helped me solve my problem, thanks

[NeoPa]

Hi ADezii.

Not sure exactly what your code is doing TBF, but that sounds generally what I was talking about. I left the technical details to you as it seems you already had that covered. Certainly only having one Field in the table containing the BLOB info and getting that from the Form after navigating to the appropriate record.

That also leaves the code the same for each BE. Navigate to the record first then run common code.

[NeoPa]

Valforchin:
your knowledge helped me solve my problem, thanks

Nice to know. Thanks for posting :-)

[Petrol]

Hello again. My apologies for the long silence with no response from me ... unfortunately I don't get emails telling me when there's a new post to subscriptions I'm following, so I've only just discovered your posts (problem is with my email address as recorded in Bytes.com, which is wrong but which I can't correct).
Anyway, back to the subject:

Thank you both for your responses and suggestions. When I found them I thought I'd try ADezii's suggestion in post6 modified by NeoPa's comment in post 7, but I've been a bit unclear on how to do this. After some searching in MS documents I tried putting one of the departmental images as an unbound object in the form header and then changing the picture property dynamically:

Me.imgBadge.Picture = CommunCode

where CommunCode contains the name of thelogo image (which is the same as the abbreviated name of the department). Frustratingly, this works for just 4 of the 23 departments/logos. I can see nothing different about these 4 - all are png's of under 120Kb, all loaded the same way as attachments into consecutive rows on the Departments table. It makes no difference to the above behaviour whether they are set as Shared, Embedded or Linked. But when I go to the dropdown arrow for Picture in the object's Properties sheet, these 4 are the only ones listed. I suspect that if I could get the others there it would all work.

Incidentally, ADezii, you've made reference to OLE and BLOBs. I was under the impression that these were used to store images in earlier (pre-2007?) versions of Access, and that they now use Attachments and store the images in native (in this case .png) format. That's what my Access 365 seems to be doing, anyway.

[ADezii]

Attached is a clarification of what I was suggesting in Post# 6. Select a Department from the Combo Box to simulate loading the appropriate Logo into the Image Control.

I was under the impression that these were used to store images in earlier (pre-2007?) versions of Access, and that they now use Attachments and store the images in native (in this case .png) format.

The hugh advantage of storing Images as BLOBs, as I see it, is that they are stored in a Byte for Byte basis with little or no DB Bloat. I don't think the same can be said for Attachment Fields, but not really sure. The Version of Access, to the best of my knowledge, is irrelevant.

[Petrol]

Many thanks again, ADezii, for your help and for going to the trouble of coding your suggestion from post #6 for me. I must admit I hadn't actually tried to do this - when I said I was "unclear on how to do this" I was referring to modifying your suggestion along the lines suggested by NeoPa in post #7. I've since spent quite a bit of time trying to do so, but without success. The reason I wanted to do it that way was that I need to be able to get the images from a back end table, where they can be modified or added to if necessary, rather than hard coded into the front end. So in practice I'll probably use your first suggestion, in post #2.

I was in a quandary about what to propose as the "best answer". They're both good, but I'll mark the post #6 one as best because it's probably the most efficient, even though for my purposes I'll try to use the post #2 solution.

Incidentally, you said "The huge advantage of storing Images as BLOBs, as I see it, is that they are stored in a Byte for Byte basis with little or no DB Bloat." However, support.office. com says "Attachment s also store data more efficiently. Earlier versions of Access used a technology called Object Linking and Embedding (OLE) to store images and documents. By default, OLE created a bitmap equivalent of the image or document. Those bitmap files could become quite large — as much as 10 times larger than the original file." (https://support.office.com/en-us/art...1-7f15baad6dbd). The relevance of the Access version is that the Attachment format is only available in post 2007 Access (i.e. in .accdb files).

For interest, I tried converting one of the logos to various other formats to compare. The results were
.png - 109 Kb
.jpg - 23 Kb
.gif - 24 Kb
.bmp - 184 Kb.
I realise of course that some of these formats are lossy, but I couldn't see any obvious difference in the resultant files. (The original isn't particularly high resolution anyway!)

When I converted all 23 images from .png to .bmp the overall size went up from 1.99Mb to 3.54 Mb. Interesting!

[twinnyfo]

From what I understand about .png, it is essentially a reduced files size, but lossless .bmp. So, if you want the same resolution and clarity of a .bmp, but want/need to save space, convert to .png (if your applications can use these files).