Solved Add object to an attachment on a form (1 Viewer)

chizzy42

Registered User.
Local time
Today, 11:56
Joined
Sep 28, 2014
Messages
115
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




Code:
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
 

chizzy42

Registered User.
Local time
Today, 11:56
Joined
Sep 28, 2014
Messages
115
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

AWF VIP
Local time
Today, 07:56
Joined
Jan 11, 2013
Messages
3,852
(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:
Code:
Set f = Application.FileDialog(1)
also note that the constant for filepicker is 3, not 1
 
Last edited:

chizzy42

Registered User.
Local time
Today, 11:56
Joined
Sep 28, 2014
Messages
115
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

Registered User.
Local time
Today, 11:56
Joined
Sep 28, 2014
Messages
115
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

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,358
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

chizzy42

Registered User.
Local time
Today, 11:56
Joined
Sep 28, 2014
Messages
115
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
 

chizzy42

Registered User.
Local time
Today, 11:56
Joined
Sep 28, 2014
Messages
115
Thanks again DBguy ill give that a go since you've recommended it
 

moke123

AWF VIP
Local time
Today, 07:56
Joined
Jan 11, 2013
Messages
3,852
Since I found this function, I thought I'd post it in case you work your way around to a vba way.

Code:
'----- 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

Registered User.
Local time
Today, 11:56
Joined
Sep 28, 2014
Messages
115
Thanks very much moke123 that's very useful to see how coding like this is properly done
 

moke123

AWF VIP
Local time
Today, 07:56
Joined
Jan 11, 2013
Messages
3,852
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

Registered User.
Local time
Today, 11:56
Joined
Sep 28, 2014
Messages
115
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.
 

Users who are viewing this thread

Top Bottom