Attachment data type and Control question using VBA

[johnnyBQue]

New to writing code, and I'm having difficulty figuring out attachments (seems others are too). What I'm looking to do is bring up a set of attachments when performing a search on an employees name or ID. I'm having issues because VB doesn't seem to want to work with attachment controls or fields too well and my ignorance isn't helping. Where I've used (example)



CurrentDb.Execute "UPDATE recEmpJD " & _
" Set EmpID=" & Me.txtID & _
"' HireDate='" & ME,txtHireDate & "'" & _
"' Depart='" & Me.cboDepart & "'" & _

I can't seem to figure out what identifier works for attachment control "att" ?? or am I totally wet and need to address attachments in a different manner? Seems like the attachment control (type) isn't compatible when used in a string with other field/data types.

any suggestion would be take most welcome

[ranman256]

Not sure what an attachment control is, but are you talking about an attachment FIELD?
If so, you treat the field as a recordset. (a recset in a recset)

Public Sub OpenAttach()
Dim att
Dim rst
Dim i As Integer
Set rst = CurrentDb.OpenRecordset("qsAtt")
Set att = rst.Fields("attachFld").Value   'it a recordset
With att
   While Not .EOF
           'Debug.Print att.Fields(2).Name, att.Fields(2).Value
        .Fields("FileData").SaveToFile "\\myserver\myfolder\rst.xlsx"
      .MoveNext
   Wend
End With

Set rst = Nothing
Set att = Nothing
End Sub

[ssanfu]

Maybe this link will help:
http://www.fmsinc.com/microsoftacces...mentFields.htm

[johnnyBQue]

I'm sorry guys but it looks like I'm not explaining myself correctly. The code below Works! very well...
Now what I'm having problems is how to enter an attachment field into code thats similar to below... Now I was told to treat it as a RS so I figure I can't use somthing like ", Resume='" & Me.AttResume & "'" & _ becasue this doesn't identify the attachment field as a attachment field? even though I name it and so forth...

thanks for your patience...

Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtID.Tag & "" = "" Then
'This is for insert new
'add data to table
CurrentDb.Execute "INSERT INTO recEmpPR(EmployeeID, LastName, MiddleInitial, FirstName, Gender, BirthDate, HomePhone, MaritalStatus, Address, City, State, ZIPCode) " & _
" VALUES(" & Me.txtID & ",'" & Me.txtFirstName & "','" & Me.txtMI & "','" & Me.txtLastname & "','" & Me.cboGender & "','" & Me.txtBirthDate & "','" & Me.txtPhNumber & "','" & Me.cboMaritalStatus & "','" & Me.txtAddress & "','" & Me.txtCity & "','" & Me.txtState & "','" & Me.txtZipCode & "')"
Else
'otherwise (Tag of txtID store the id of employee to be modified)
CurrentDb.Execute "UPDATE recEmpPR " & _
" Set EmployeeID=" & Me.txtID & _
", FirstName='" & Me.txtFirstName & "'" & _
", MiddleInitial='" & Me.txtMI & "'" & _
", LastName='" & Me.txtLastname & "'" & _
", Gender='" & Me.cboGender & "'" & _
", BirthDate='" & Me.txtBirthDate & "'" & _
", HomePhone='" & Me.txtPhNumber & "'" & _
", MaritalStatus='" & Me.cboMaritalStatus & "'" & _
", Address='" & Me.txtAddress & "'" & _
", City='" & Me.txtCity & "'" & _
", State='" & Me.txtState & "'" & _
", ZIPCode='" & Me.txtZipCode & "'" & _
" WHERE EmployeeID=" & Me.txtID.Tag
End If

'Clear Form
cmdClear_Click
'referesh data in list on form
subfrmEmpPR.Form.Requery
End Sub

[June7]

An attachment field is a type of multi-value field. Multi-value fields cannot be treated the same as other fields. As ranman pointed out, must open the attachment field as a recordset. It is considered a child or sub recordset.

1. INSERT or UPDATE record for the normal type fields

2. run more code to UPDATE the attachment multi-value field to add/edit content
Review https://www.accessforums.net/program...vba-21259.html

I NEVER use multi-value fields for normal data. I have used attachment field for embedding images into table only once - a very small db used once to generate a report then abandoned.