Access.2007.VBA.Bibl..

Extract Contact ID from file name, using Midand InStrfunctions to start at the beginning of the
number and end before the space following the number, if there is one.

strTest = Mid(String:=strFile, Start:=12, Length:=3)

intSpace = InStr(strTest, “ “)

If intSpace > 0 Then

lngContactID = CLng(Mid(String:=strTest, _

Start:=1, Length:=intSpace - 1))

Else

lngContactID = CLng(strTest)

End If

strSearch = “[ContactID] = “ & lngContactID

Debug.Print “Search string: “ & strSearch

strFileAndPath = strDocsPath & strFile

Search for matching Contact ID in table.

rstTable.MoveFirst

rstTable.FindFirst strSearch

If rstTable.NoMatch = True Then

strTitle = “Can’t find contact”

strPrompt = “Contact ID “ & lngContactID _

& “ not found in table; can’t add attachment”

GoTo NextDoc

Else

rstTable.Edit

Create recordset of attachments for this record, using the new Recordset2 type recordset.

Set rstAttachments = _

rstTable.Fields(“File”).Value

Turn off the error handler to prevent errors if the code attempts to add the same file twice; in this
case the Attachments recordset won’t be updated.

On Error Resume Next

With rstAttachments

.AddNew

.Fields(“FileData”).LoadFromFile _

(strFileAndPath)

.Update

.Close

End With

rstTable.Update

Debug.Print “Added “ & strFileAndPath _

& “ as attachment to Contact ID “ _

& lngContactID; “‘s record”

End If

Working with Files and Folders 9