Embed image into Table using Button (VBA)

[f15e]

Before I proceed, I know many say not to embed images b/c of size issues but in my case, I will have a max of three images (1) 'Image Not Available', 2) Client logo, and 3) Company logo images) that I want to embed in a table. I don't want to link them. Once embedded in the table, I want to allow the user to select the image they want to import (embed) into the table. FYI, images will be overwritten so they won't be able to add as many as they like.



With this said, I thought I embedded the images correctly on my personal computer but when I ran the DB on my work computer, an error message popped up stating image location could not be found. The location it was looking for was the path on my personal computer. Anyway, I want to properly embed the images and be able to use as needed without worrying about linking them. Also, the user with click the button to open a file browser where they will chose the image from a location on their computer. I would greatly appreciate any help you could provided. Thanks.

[ranman256]

put an OLE field into the table.
in a form, drop an image control and link it to the field.
paste the image into the record via the form.

[f15e]

ranman256, your solution sounds like the user would still have to go behind the scenes of Access to prepare the image for embedding. I need it to be completely automated as if the user has absolutely no Access skills and would only be using buttons, etc, on the UI screen.

I believe the images are embedded but I'm not sure how to link the image control to the embedded image in my table. See pic of image table. It's set as an OLE Object field and showing as binary data.



Below is the code I'm using to fill my image control with the table image but once I open the DB on a different computer, I will get an error that the image path can't be found.
************************************************** ******************************************
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [CLIENT_LOGO] FROM [Client_Logo_Tbl] WHERE [ITEM_NO] = " & 2)
Forms![Main_Menu_Frm].Form.[Client_Logo_img].Picture = rs.Fields("CLIENT_LOGO")
rs.Close
Set rs = Nothing
************************************************** ******************************************

I am trying to automate the image import/embed into the table and then use the image in an image control. I need to be able to have the user click a button, a file dialog box opens and the user selects the image to import from somewhere on his compter, the image is embedded into a field in the table, and no matter what computer the DB is on, the images will work b/c they're embedded and not linked to an image externally. I don't need help with the dialog box code; I was just explaining the process.

Please help me properly embed and image so it's permanently in the table (unless deleted) and how to link it to an image control. Thank you.

[ranman256]

user cant copy and paste? Thats all the user needs to do.

[ssanfu]

What is the data type of the field "Client_Logo"?

When I used OLE OBJECT, the path to the picture appeared to be saved.
When I used ATTACHMENT, the picture was embedded in the table. I closed Access, changed the folder name, opened Access and opened the form that displayed the attachment. The picture still displayed - even with the changed path.


I didn't use an image control, I just added the attachment type field to the form.

[f15e]

The data type is OLE Object. I will jack around with your suggestion and see what progress I get. Thanks.

[f15e]

Ok so I changed the data type from OLE to Attachment. Below is the code I'm using to attach (embed) and display the image:

'************************************************* **********************
qry = "SELECT [Iv_AGA_LOGO] FROM [Iv_AGA_Logo_Tbl] WHERE [ITEM_NO] = " & 1

Set db = CurrentDb
Set rsParent = db.OpenRecordset(qry, dbOpenDynaset)

rsParent.Edit

Set rsChild = rsParent.Fields("Iv_AGA_LOGO").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile (filePath)

rsChild.Update
rsParent.Update

' Here is where I am displaying the image in an Image Control
Me.iv_logo.Picture = DLookup("Iv_AGA_LOGO", "Iv_AGA_Logo_Tbl", "ITEM_NO = " & 1)

MsgBox "Iv-AGA logo successfully added.", vbInformation, "LOGO ADDED"

'************************************************* **********************

So with the code above, am I actually embedding the image? It works just after I import and embed the image but if I close out the DB and reopen it, I get the following error:


I have to remove and import the image again for it to work but if I close the DB, I have to start all over again. Could someone please advise as to what I'm doing wrong?

Thank you!

[f15e]

Sorry but Error 2220 is not coming from the code I recently added on my previous post. FYI, I don't want to have to worry about placing files in a folder and linking them b/c the DB may be used on several computers and not all have access to the same server locations.

So where Error 2220 is coming from is when the form is loaded, I call Sub Check_Logo_Exist() to load the existing images embedded in my table. See code below. To provide you with how my table is layed out, I have two tables each storing 2 images. The fields for each table are ITEM_NO (field 1), the attachment field (2), and DESCRIPTION (field 3). I could put the two tables as one, but for now I'll keep them as is.

In the code, I first check to see is the attachments in row 1 of field 2 in both tables exist. If iv_img_cnt = 1 and client_img_cnt = 1, the image exists and loaded into the corresponding Image Control (Me.controlName.Picture). If = 0, then load the default image which is an image with statement "NO IMAGE AVAILABLE" which is located in row2 , field 2 in both tables.

Error 2220 pops up in the code where I am setting the Image Control equal to the DLookup satement. If the image is embedded and I'm specifying the location in the table, why am I getting this error? I know the image is embedded and not linked b/c I opened the DB table, right-clicked on the attachment to open Manage Attachments window, and then clicked to open the image and it opened fine.

If I remove the image from the attachment data type field and then add it back, it works perfectly. As soon as I close out the DB, reopen it and then run it, I get the 2220 error.
Thanks for your help in advance!!!
'************************************************* ****************************************
Private Sub Check_Logo_Exist()

Dim iv_img_cnt, client_img_cnt As Integer

iv_img_cnt = DCount("[Iv_AGA_LOGO]", "Iv_AGA_Logo_Tbl", "ITEM_NO = " & 1) ' Check for Iv-AGA logo in row 1
client_img_cnt = DCount("[CLIENT_LOGO]", "Client_Logo_Tbl", "ITEM_NO = " & 1) ' Check for Client logo in row 1


If iv_img_cnt = 1 Then
'Project logo used by Iv-AGA

Me.iv_logo.Picture = DLookup("Iv_AGA_LOGO", "Iv_AGA_Logo_Tbl", "ITEM_NO = " & 1) ' error occurs here if = 1
Else
Me.iv_logo.Picture = DLookup("Iv_AGA_LOGO", "Iv_AGA_Logo_Tbl", "ITEM_NO = " & 2) ' ' error occurs here if = 0
End If

If client_img_cnt = 1 Then
'Project logo used by Client
Me.Client_Logo_img.Picture = DLookup("CLIENT_LOGO", "Client_Logo_Tbl", "ITEM_NO = " & 1) ' error occurs here if = 1
Else
Me.Client_Logo_img.Picture = DLookup("CLIENT_LOGO", "Client_Logo_Tbl", "ITEM_NO = " & 2) ' error occurs here if = 0
End If

End Sub
'************************************************* ****************************************

[f15e]

I finally figured this out. I changed my Check_Logo_Exist() sub-routine. I am using attachment controls instead of Image controls and create queries depending on the existence of the logos in my tables. It now works as I want it to.

My sub-routine has been changed as shown below (I changed some of my variable and table field names BTW):

'************************************************* ****************************************
Private Sub Check_Logo_Exist()

Dim comp_img_cnt, client_img_cnt As Integer
Dim proj_logo_qry As String

' Check for Company logo in row 1. If DCount = 1, logo exists; If DCount = 0, doesn't exist and load with default image
comp_img_cnt = DCount("[COMP_LOGO]", "Company_Logo_Tbl", "ITEM_NO = " & 1)

' Check for Client logo in row 1. If DCount = 1, logo exists; If DCount = 0, doesn't exist and load with default image
client_img_cnt = DCount("[CLIENT_LOGO]", "Client_Logo_Tbl", "ITEM_NO = " & 1) ' Check for Client logo in row 1

If comp_img_cnt = 1 And client_img_cnt = 1 Then

'Load Company and Client Logos
proj_logo_qry = "SELECT Company_Logo_Tbl.COMP_LOGO, Client_Logo_Tbl.CLIENT_LOGO " & _
"FROM Company_Logo_Tbl, Client_Logo_Tbl " & _
"WHERE Company_Logo_Tbl.ITEM_NO = " & 1 & " And Client_Logo_Tbl.ITEM_NO = " & 1

Set Me.Recordset = CurrentDb.OpenRecordset(proj_logo_qry, dbOpenDynaset)

ElseIf comp_img_cnt = 1 And client_img_cnt = 0 Then

'Load Company Logo but Client with default Image
proj_logo_qry = "SELECT Company_Logo_Tbl.COMP_LOGO, Client_Logo_Tbl.CLIENT_LOGO " & _
"FROM Company_Logo_Tbl, Client_Logo_Tbl " & _
"WHERE Company_Logo_Tbl.ITEM_NO = " & 1 & " And Client_Logo_Tbl.ITEM_NO = " & 2

Set Me.Recordset = CurrentDb.OpenRecordset(proj_logo_qry, dbOpenDynaset)

ElseIf comp_img_cnt = 0 And client_img_cnt = 1 Then

'Load Company Logo with default image and Client with logo
proj_logo_qry = "SELECT Company_Logo_Tbl.COMP_LOGO, Client_Logo_Tbl.CLIENT_LOGO " & _
"FROM Company_Logo_Tbl, Client_Logo_Tbl " & _
"WHERE Company_Logo_Tbl.ITEM_NO = " & 2 & " And Client_Logo_Tbl.ITEM_NO = " & 1

Set Me.Recordset = CurrentDb.OpenRecordset(proj_logo_qry, dbOpenDynaset)

ElseIf comp_img_cnt = 0 And client_img_cnt = 0 Then

'Load both Company and Client with default images
proj_logo_qry = "SELECT Company_Logo_Tbl.COMP_LOGO, Client_Logo_Tbl.CLIENT_LOGO " & _
"FROM Company_Logo_Tbl, Client_Logo_Tbl " & _
"WHERE Company_Logo_Tbl.ITEM_NO = " & 2 & " And Client_Logo_Tbl.ITEM_NO = " & 2

Set Me.Recordset = CurrentDb.OpenRecordset(proj_logo_qry, dbOpenDynaset)

End If
'************************************************* ****************************************