asked on

Access vba assign attachment to form control using ado recordset

Hi All,

I have an unbound form that displays company information to a user.

When the form opens, I populate it using the enclosed ado recordset routine.

The code works as expected but errors with "object does not support this method" on line 32 which is a field containing an attachment (in this case is a jpg logo file).

My form control (txtLogo) is also an attachment control type.

Sub populate_EntityCompany()

'***************************************************************************************
On Error GoTo Error_Handler

'declaring variables ->>
Dim rs As New ADODB.Recordset
Dim Conn As ADODB.Connection
Set Conn = CurrentProject.AccessConnection
Dim strSQL As String
Dim dbFailOnError, dbSeeChanges

strSQL = "SELECT tbl_entity.TradingName, tbl_entity.EntityType, tbl_entity.BusinessRegistrationNumber, tbl_entity.FirstName, " & _
"tbl_entity.LastName, tbl_entity.OfficeAddressID, tbl_entity.PostAddressID, tbl_entity.Phone, " & _
"tbl_entity.Mobile, tbl_entity.Fax, tbl_entity.Email, tbl_entity.Website, tbl_entity_setting.AutoBackup, tbl_entity.Logo " & _
"FROM tbl_entity INNER JOIN tbl_entity_setting ON tbl_entity.EntityID = tbl_entity_setting.EntityID " & _
"WHERE (((tbl_entity.EntityID)=" & TempVars!AppEntID & "))"
rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
    rs.MoveFirst
        Forms("frm_entity_setting_company").Controls("txtTradingName").Value = rs.Fields("TradingName").Value
        Forms("frm_entity_setting_company").Controls("cboEntityType").Value = rs.Fields("EntityType").Value
        Forms("frm_entity_setting_company").Controls("txtBusinessRegistrationNumber").Value = rs.Fields("BusinessRegistrationNumber").Value
        Forms("frm_entity_setting_company").Controls("txtFirstName").Value = rs.Fields("FirstName").Value
        Forms("frm_entity_setting_company").Controls("txtLastName").Value = rs.Fields("LastName").Value
        Forms("frm_entity_setting_company").Controls("txtFax").Value = rs.Fields("Fax").Value
        Forms("frm_entity_setting_company").Controls("txtPhone").Value = rs.Fields("Phone").Value
        Forms("frm_entity_setting_company").Controls("txtMobile").Value = rs.Fields("Mobile").Value
        Forms("frm_entity_setting_company").Controls("txtEmail").Value = rs.Fields("Email").Value
        Forms("frm_entity_setting_company").Controls("txtWebsite").Value = rs.Fields("Website").Value
        Forms("frm_entity_setting_company").Controls("chkSameAsOffice").Value = rs.Fields("PostCode").Value
        Forms("frm_entity_setting_company").Controls("txtLogo") = rs.Fields("Logo")
        Forms("frm_entity_setting_company").Controls("txtOfficeAddressID").Value = rs.Fields("OfficeAddressID").Value
        Forms("frm_entity_setting_company").Controls("chkBackup").Value = rs.Fields("AutoBackup").Value
        Forms("frm_entity_setting_company").Controls("txtPostAddressID").Value = rs.Fields("PostAddressID").Value
End If

Err_Handler_Exit:
    Set rs = Nothing
    Exit Sub

Error_Handler:
    If Err.Number = 0 Then
       Resume Err_Handler_Exit
    Else
       Call addDBLog(6, "Error " & Err.Number & " (" & Err.Description & ") in procedure populate_EntityCompany of Module bas_entity_setting")
          Resume Err_Handler_Exit
    End If
End Sub

Open in new window

Can someone help me get through this?

Thanks,
Lai

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Laila Jackson

ASKER

Hey Ryan,

I added the two lines of your solution but its providing an Error 13 "Type mismatch" on the second line.

Having checked the table, I found the attachment to be a png file not a jpg as I first mentioned, however, after checking at msdn for the properties, the site does not mention anything about different extension types, only the methods in which they reside in the table, i.e. embedded, linked, shared.

So, that said, I toggled between 0 and 1 on the first line but it still wont get past the error??

Ryan Chong

for your info, i try use the Linked option with above's code.

I tried linked to a png file and it's working fine.

Laila Jackson

ASKER

That wont work for me.

Is there anything you have added to the code that I have?

Forms("frm_entity_setting_company").Controls("txtLogo").DefaultPictureType = 2
Forms("frm_entity_setting_company").Controls("txtLogo").DefaultPicture = rs.Fields("Logo")

Open in new window

Ryan Chong

i tried this and it works for me for newly added attachment control:

Forms("Form1").Controls("Attachment9").DefaultPictureType = 1
Forms("Form1").Controls("Attachment9").DefaultPicture = "C:\Users\myname\Downloads\myimage.png"

Open in new window

if rs.Fields("Logo") returns a valid path, it should loaded the image.

Ryan Chong

if still doesn't work you may upload a sample here and we will try to diagnose

Laila Jackson

ASKER

Ryan,

I actually got it to work using the additional property .name

Forms("frm_entity_setting_company").Controls("Logo").DefaultPictureType = 0
Forms("frm_entity_setting_company").Controls("Logo").DefaultPicture = rs.Fields("Logo").Name

Open in new window

I will provide points to you anyway because if it were not for your answer, I would not have achieved this on my own.

Thanks,
Lai

Laila Jackson

ASKER

Great help as always. Thanks Ryan