Can't figure out how to move attachments from one record to another

[mcomp72]

On one of my forms, I want to add a "Duplicate" button, so that it will duplicate some of the contents of the current record into a new record. I know how to do this with information that is contained in text boxes. However, one field on my form is an attachment field. I can't figure out how to copy whatever is in the attachment field into a variable and then put it in the new record.

Here's the code I have written to move the rest of the data in the record.

Option Compare Database
Option Explicit

Private Type BoxRentalInfo
    TrueFalse As Boolean
    Amount As Single
    AcctCode As String
    DayOrWeek As String
    Notes As String
    Inventory As String
End Type

Private Sub DuplicateButton_Click()

Dim EmployeeID As Integer
Dim PositionID As Integer
Dim PositionAcctCode As String
Dim PositionCoveredByUnion As String
Dim Rate As Single
Dim Guarantee As Single
Dim StartDate As Date
Dim Term As String
Dim TopOfStartForm As String
Dim BoxRental As BoxRentalInfo

If Me.Dirty Then Me.Dirty = False

EmployeeID = Me.EmployeeID
PositionID = Me.PositionID
PositionAcctCode = Me.PositionAcctCode
PositionCoveredByUnion = Me.PositionCoveredByUnion
Rate = Me.Rate
Guarantee = Me.Guarantee
StartDate = Me.StartDate
Term = Me.Term
TopOfStartForm = Me.StartFormCheckBox

If Me.List45 = "Yes" Then
    BoxRental.TrueFalse = True
    BoxRental.Amount = Me.BoxRentalAmount
    BoxRental.AcctCode = Me.BoxRentalAcctCode
    BoxRental.DayOrWeek = Me.BoxRentalDayWeek
    BoxRental.Notes = Me.BoxRentalNotes
    BoxRental.Inventory = Me.BoxRentalInventory
Else
    BoxRental.TrueFalse = False
End If

'this creates the new record
DoCmd.GoToRecord , , acNewRec

'this copies the information into the new record
Me.EmployeeID = EmployeeID
Me.PositionID = PositionID
Me.PositionAcctCode = PositionAcctCode
Me.PositionCoveredByUnion = PositionCoveredByUnion
Me.Rate = Rate
Me.Guarantee = Guarantee
Me.StartDate = StartDate
Me.Term = Term
Me.StartFormCheckBox = TopOfStartForm

If BoxRental.TrueFalse = True Then
    Me.BoxRentalYesNo = "Yes"
    Me.BoxRentalAmount = BoxRental.Amount
    Me.BoxRentalAcctCode = BoxRental.AcctCode
    Me.BoxRentalDayWeek = BoxRental.DayOrWeek
    Me.BoxRentalNotes = BoxRental.Notes
    Me.BoxRentalInventory = BoxRental.Inventory
Else
    Me.BoxRentalYesNo = "No"
End If

If Me.Dirty Then Me.Dirty = False

End Sub

Anyone know the syntax I would use to copy whatever data is in the attachment field into the new record?

[June7]

Export the file to external location then insert to another record.
Review https://www.fmsinc.com/MicrosoftAcce...mentFields.htm

Why embed documents within table? Attachments chew up Access 2GB file size limit.

[mcomp72]

There are very few instances where a file would need to be attached. But in the rare instances it will be, it will make the process of keeping track of them easier if they are part of the database file.

I took a look at the site you linked to, but in order to get the full code needed to do what they are showing, I would have to spend money. Not able to do that right now, unfortunately.

I've read several posts from various sites to see if I can figure out how to write the code myself, but no such luck. Here's what I have. Maybe someone can enlighten me.

Private Sub DuplicateButton_Click()

Dim FieldOfRecord As Control

Set FieldOfRecord = Me.Controls("Attachments")

PathToUse = CurrentProject.Path & "\TempAttachmentFolder\"

While Not FieldOfRecord.EOF
        FieldOfRecord("FileData").SaveToFile PathToUse
        FieldOfRecord.MoveNext
Wend

'this creates the new record
DoCmd.GoToRecord , , acNewR

'This saves the attachments that were copies into the temp folder into the Attachments field of the new record
FieldOfRecord = Me.Attachments
FieldOfRecord("FileData").LoadFromFile "*.*"

End Sub

I'm getting Run-time error 438: Object doesn't support this property or method on the following line:
While Not FieldOfRecord.EOF

I'm not sure how else to get it to go through the entire field to make sure it copies all of the attachments that might be there.

And since my code is stopping there, I have no idea if the rest of what I've written will work. If anyone has any thoughts, I'm all ears.

[knarfreppep]

Can't imagine why you would want to embed files in the way you seem to mean.

Why not a text field containing the file location?

E.G. -

PrisonerID, Name, RapSheet
35, "R Moore", "C:\Prisoners\RapSheets\00035_RapSheet_MooreR. pdf"

or, if all RapSheets will be stored in a particular folder -

35, "R Moore", "00035_RapSheet_MooreR.pdf"


Then, if you want to copy record 35 to say record 72 you just need to manipulate "00035" into "00072" and copy or re-name the PDF to its new name.

Then, you can write code to open the PDF if the RapSheet text box is say double-clicked.

I've done this ... works like a dream.

[June7]

Sorry, I didn't read that page thoroughly. I've just been able to get code examples from FMS in the past so assumed it was all there. So look at https://msdn.microsoft.com/en-us/lib.../ff197737.aspx and http://sourcedaddy.com/ms-access/wor...nt-fields.html (repeats the MSDN code but the additional narrative may be helpful).

[mcomp72]

I decided to try to figure out how to do the attachments using text field to reference the file location, as the two of you suggested. It took some doing, but I finally figured it out. Now I have no more attachments!