Solved Add object to an attachment on a form

[chizzy42]

Hi All, Hope all is well. Im trying to add an object selected from a dialog box into an attachment box on a form with the intention of then saving the object to the attachment field in the table. Ive read this isnt the thing to do, but im trying it for two purposes (1) I don't trust the few users to put an object in a common folder and not overwrite the object name eg an image titled image1 into a folder and overwriting one that is already there..and (2) its not as straight forward as a thought and hopefully learn something.

My first problem is i have a test form with an attachment box on it called a2. I'm trying to insert an object with the code below and getting the error runtime error 438 object doesn't support this property or method no matter what object i try eg .xls , .png . I ve used attachments before when they were bound and it was just a matter of clicking on the paper clip and adding. please see the code any pointers gratefully received

Set f = Application.FileDialog(1)
f.AllowMultiSelect = False
If f.Show Then
For Each varitem In f.SelectedItems
strFile = Dir(varitem)

MsgBox strFile 'checks the object name
strfolder = Left(varitem, Len(varitem) - Len(strFile))
MsgBox "Folder" & strfolder & vbCrLf ' checks the path

Me.a2 = strfolder + strFile

Next
End If
Set f = Nothing

 

[theDBguy]

Check this out...

Work with attachments in DAO | Microsoft Docs

 

[chizzy42]

Thanks for the link dbguy i had tried this but had an error 91 object variable or with block variable not set at this line so thought i was missing something and was trying a different approach to manually enter the file and then try and insert it in under criteria

Instantiate the parent recordset.
Set rsEmployees = db.OpenRecordset("imagelist")

 

[moke123]

(1) I don't trust the few users to put an object in a common folder and not overwrite the object name eg an image titled image1 into a folder and overwriting one that is already there

There are ways around that. Since your already using a filepicker you could either leave the file where ever it is and use the path to make a copy of the file in a common folder or just move the file to a common folder. You can test if a file name exists before moving/copying and rename it if needed.

edit:

Set f = Application.FileDialog(1)

also note that the constant for filepicker is 3, not 1

 

[chizzy42]

Hi moke123, if i get this working i might try both (hyperlinked and record attached), but i think the file attached to the database is safer for the users as it locks the file to the record , i tried changing the filepicker to 3 but the error was the same

 

[chizzy42]

Hi, i got round this by making the attachment boxes bound to the table so that that attachment boxes work as normal when clicked (ie the paper clip appears and allows an attachment to be made) and if anything is added the attachment links to the correct field...thanks for your suggestions

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[chizzy42]

Thanks dbGuy, my main holdback is I need to get a better understanding of vba from the bottom up instead of trying to patch it together

 

[theDBguy]

Thanks dbGuy, my main holdback is I need to get a better understanding of vba from the bottom up instead of trying to patch it together

Maybe you could start with this.

Learn VBA By Crystal

 

[chizzy42]

Thanks again DBguy ill give that a go since you've recommended it

 

[theDBguy]

Thanks again DBguy ill give that a go since you've recommended it

Should give a good start. Good luck!

 

[moke123]

Since I found this function, I thought I'd post it in case you work your way around to a vba way.

'----- code to browse, select file and attach to access table
'----- Thanks a lot for earlier submissions, I have just put together all related codes
'----- the code is to add attachments to the attachment field in Ms Access Table. If you don't want to bring up built in form, you can use this code to browse the file and attach
'------ please add this code as Module in Ms Access.
'------ being a public function, you call this code from any form just by adding 4 parameters  for example  Table name is EmpMaster, Attachment field name is Empcertificate, name of the ID field is EmpID, record ID number say 101
'----- Add_Attachment "EmpMaster", "Empcertificate", "EmpID", 101
'---- it works for me


Public Function Add_Attachment(strTableName, strAttachField, strIDfield As String, i As Long)

'------------ code to browse file and select file to attach
    Dim fd As FileDialog
    Dim oFD As Variant
    Dim strFileName As String

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Title = "Choose File"
        .InitialView = msoFileDialogViewDetails
        .Show

        For Each oFD In .SelectedItems
            strFileName = oFD
        Next oFD
        On Error GoTo 0
    End With

    Set fd = Nothing

'------------ from here code for file attachment process

    Dim cdb As DAO.Database, rstMain As DAO.Recordset, rstAttach As DAO.Recordset2, _
        fldAttach As DAO.Field2
    Set cdb = CurrentDb
    Set rstMain = cdb.OpenRecordset("SELECT " & strAttachField & " FROM " & strTableName & " where " & strIDfield & "= " & i, dbOpenDynaset)

    rstMain.Edit
   Set rstAttach = rstMain(strAttachField).Value
    rstAttach.AddNew

    Set fldAttach = rstAttach.Fields("FileData")

    fldAttach.LoadFromFile strFileName
    rstAttach.Update
    rstAttach.Close
    Set rstAttach = Nothing
    rstMain.Update
    rstMain.MoveNext
rstMain.Close
Set rstMain = Nothing
Set cdb = Nothing
End Function

 

[chizzy42]

Thanks very much moke123 that's very useful to see how coding like this is properly done

 

[moke123]

Couldn't tell you if its properly done, only that I found it and it appears to work. I never use attachment fields and I doubt you will either once you see how they effect your DB.

 

[chizzy42]

Now that I've got more reading into it, it does seem to be a no no attaching files to the table which I get due to the bloating of the dB and the 2Gb limit. It was just interesting trying.