Set MS-Access Image Control Source from Attached Image Using VBA

1
votes

I'm adding a photo to form to display for each user with other info of that user. I created a tabbed form, on page one I select a user and press a button that runs the following code:

Private Sub Command106_Click()
Dim qry_rs As DAO.Recordset
Dim qry_db As Database
Set qry_db = CurrentDb
SQLString = "SELECT [Clients Info].* FROM [Clients Info] WHERE ((([Clients Info].Info_Client)='" & [Forms]![Form-1]![Combo13_PageOne_Name] & "'));"
Set qry_rs = qry_db.OpenRecordset(SQLString)
Forms![Form-1].[Info_Member].Value = qry_rs![Info_Member]
Forms![Form-1].[Info_Tower].Value = qry_rs![Info_ID]
End Sub

I tried using the same way that I set the values of that textboxes in setting an image control value

Forms![Form-1].[Info_Picture].Value = qry_rs![Info_UserIDPhoto]

But it's not working, can anyone help? I receive this message:

Run time error 2465: Microsoft Office Access can't find the field "|" referred to in your expression

I'm using Office 2007, on Windows 7

2
vbams-accessattachment

2 Answers

1
votes

To my knowledge, there is no way to refer to the ControlSource property through vba. However, using the property sheet in Design View works PERFECTLY. Use the value of your combo box as the criteria in a DLookup expression.

Enter this as the ControlSource of your image control:
=DLookUp("[YourAttachmentField]","[YourTable]","[YourNameField] = '" & [YourComboBox]. [Column] (x) & "'")

(Make sure you're referencing the correct column; I'm just using x as an example.)
This won't work if you alter the attachment's name or location AFTER it's been attached, so if any changes are made you must reattach.

0
votes

VBA allows you to set the Picture property:

Forms![Form-1].[Info_Picture].Picture=qry_rs![Info_UserIDPhoto]

The Picture property value must be a file name--or complete path and file name if not found in the default folder set in Access options.

When set from VBA, the value may be a statement or variable whose value is a (path and) file name. In the case above, the field [Info_UserIDPhoto] would contain the complete path and file name of each user's ID photo.

(Unlike the ControlSource property, you cannot enter a variable or field name for this property directly in the design view property page.)