How to change an image on a form programatically

[Petrol]

I want to distribute my DB to different "departments". 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 straightforwardly 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 straightforwardly 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 "Attachments 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).

[ADezii]

Here is a Demo that I actually created for another User illustrating many aspects of working with BLOBs. The original Code is by Alan Warren to which I made some modifications. In the Demo you can write Images to an OLE Object Field, display a BLOB Image, retrieve Images to a Temporary Folder, display File Properties of a stored Image, Edit a BLOB Image, etc. Hopefully, you will find it somewhat useful.

[ADezii]

As an additional note, I tested the Demo to see if it will work with *.png Files, and it does.

[Petrol]

Well they say you live and learn, and I'm certainly learning. The BLOBs demo looks very interesting, and I'll try to study it in depth.

[Petrol]

Well surprisingly, it turns out that all I had to do was put each of the .png images on a form somewhere (this was the clue I got from ADezii's suggestion -thanks so much!), and then set the picture property of the logo image to the name of the desired image:

Me![ImgLogo].Picture = conDept
Me.Refresh

But then I was able to ignore the form and never reference it ... in fact, I even deleted it and the thing still works. The DB engine must have hidden the images away somewhere where it could retrieve them by name on request.
Thanks again.

[NeoPa]

Nice work Petrol. I love your thinking. However ...

While this all works very nicely, there are times when a database gets corrupted beyond repair, that you may want to recreate the database from scratch and import across the various objects from the original that are still uncorrupted. While you can also do this with an older, saved, version if you have one, it's more common to start from scratch. If you were to do this without the original Form that has the Image controls on then you would be likely to come unstuck.

I suggest you would be better served by keeping that Form around but with a name or comment value to indicate why.

[Petrol]

Good point, though I shudder at the thought; however, I suspect that it was the act of placing the images on the form, rather than the existence of the form itself, that caused Access to hide copies down in its vault somewhere. I still have the original .png's from which I created the form, and they are in a file outside the DB which could get corrupted, so that's the safer option, n'est-ce pas?

[Petrol]

Incidentally, I received an email notification of NeoPa's last post - the first one I have received for this string - even though it's addressed to an email address I don't have! In fact, it's at least a year since I had received a system-generated email from Bytes. I know this isn't a forum about emails, but there's obviously just as much I don't understand about emails as about Access LOL.

[NeoPa]

Interesting. I suspect the owner's been tweaking things again. I too have started receiving emails recently. I suppose we can also revisit our settings any time so go there if you're unhappy with how things are now.

As for the images being stored, I expect that would also happen automatically when you import the form so there is sense in keeping it. Obviously you can work with just the files in the background if you're confident that when the time comes that you need to do this you will remember all the details of this situation and not end up scratching your head wondering why these images, that used to work fine, suddenly aren't working in your new imported database.

[Petrol]

True. As always, the key is documentation.
Anyway, it's all working now, so thanks to you and especially ADezii for all the help.