Add a file to an Attachment via VBA code

[reginaldmerritt]

You can add an Attachment though code via RecordSets.
Something like this

Dim MyRECORDSET As DOA.Recordset
Dim NumberOfRecords As Integer
Dim MyCounter as Integer

Set MyRECORDSET to Currentdb.OpenRecordset("MyTable")
NumberOfRecords = DCount("EmployeNumberPK","MyTable")
MyCounter = 0

MyRECORDSET.MoveFirst

For MyCounter = 1 to NumberOfRecords
   MyRECORDSET.Edit
   If MyRECORDSET("EmployeNumberPK") = Me.EmployeeNumberPK Then
       MyRECORDSET("EmployeePicture").LoadFile "C:\EmployeePictures\" & Me.EmployeeNumberPK & ".jpg"
   End If 
   MyRECORDSET.Update
Next

Is there another way to attach a file to an attachment data type field without using RecordSets?

Maybe something like

Me.EmployeePicture.Value = "C:\EmployeePictures\" & Me.EmployeeNumberPK & ".jpg"

With lots of records I find looping though RecordSets slows down the program.

Thanks.

[reginaldmerritt]

Apparently you can use an SQL Statement as a Recordset. If that's true I should be able to go straight to the record required and attach the file.

However, I'm having trouble the the syntax I think. I get an error message
To Few parameters. Expected 1

The code is

Dim CRBRecordSet As Recordset
Dim SQL As String

SQL = "Select * From CRB Where FormnNumber = '" & Me.FormNumber & "';"

' can use SQL for recordset
Set CRBRecordSet = CurrentDb.OpenRecordset(SQL)

If Not (CRBRecordSet.EOF And CRBRecordSet.BOF) Then
    QYRecordSet.Edit
    QYRecordSet.Fields("Attachments").LoadFromFile "\\Universe\SharedFolders\Scans\CRBApplications\" & Me.FormNumber & ".pdf"
    QYRecordSet.Update
End If

I'm guessing there is something wrong with the SQL statement but I can't find out what it is.

[reginaldmerritt]

Sorry, ignore last post. Misspelt Field in SQL statement.

Had to change the code slightly, was working from a bad example online. You can't trust everything you see ;)

Private Sub AttachScannedDocument()
Dim CRBApplicationRecordSet, ScannedApplicationRecordSet As Recordset
Dim SQL As String

SQL = "Select * From CRB Where FormNumber = '" & Me.FormNumber & "'"

' can use SQL for recordset to instantiate parent recordset
Set CRBApplicationRecordSet = CurrentDb.OpenRecordset(SQL)

'active edit mode
CRBApplicationRecordSet.Edit

' instantiate a child recordset
Set ScannedApplicationRecordSet = CRBApplicationRecordSet.Fields("Attachments").Value

'Add Scanned Appication
ScannedApplicationRecordSet.AddNew
ScannedApplicationRecordSet.Fields("FileData").LoadFromFile "Apollo.jpg" '"\\Universe\SharedFolders\Scans\CRBApplications\" & Me.FormNumber & ".pdf"
ScannedApplicationRecordSet.Update

'update parent recordset
CRBApplicationRecordSet.Update

Set CRBApplicationRecordSet = Nothing
Set ScannedApplicationRecordSet = Nothing

End Sub

I have two problems however.

The first time the procedure is run, it runs though with no errors. The second time I get an error message.
You cannot enter value because it duplicates and existing value in the attachment field.
How do I check to see if the value of the attachment field matches the one I'm trying to add?

More importantly the procedure does not attach the file. It seems to think that it has but it doesn't work. Any ideas where I'm going wrong here?