Update attachment field in existing record

[incipientclarity]

Hi everyone,

I've created a form that collects inputs from users and adds these inputs as a new record in a specified table.

This was done through executing a mySQL code instead of using bound elements. I've chosen to do so because I needed some of the form elements to be populated by another table based on the values of some other form elements supplied by the user.



Unfortunately, using the mySQL method does not allow me to add attachments (no multivalued fields allowed).

Can anyone please advise on how I can allow users to add attachments?

What I've done so far is to write some code to Find The Record in the table that is collecting all these inputs. Finding the record was successful. I just can't seem to get the LoadFromFile command to work though.

Hope someone can shed some light on this please.

Thank you!

DoCmd.RunSQL strSQL
    
    Set dbsAdjTable = CurrentDb
    Set rst = dbsAdjTable.OpenRecordset("AdjustmentT", dbOpenTable)
    Set dialog = Application.FileDialog(msoFileDialogFilePicker)
         

        With rst
        .Index = "ID"
        .Seek "=", txtRecordID
        .Edit
        Application.FileDialog(msoFileDialogOpen).Show
        strLoadFile = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)

        .Fields("AttachmentUpload").LoadFromFile (strLoadFile)
        .Update 
        End With

[ItsMe]

Here is some example code to interact with the attachment field.
https://www.accessforums.net/access/...tml#post221634

[incipientclarity]

Here is some example code to interact with the attachment field.
https://www.accessforums.net/access/...tml#post221634

Thanks, ItsMe! I've actually read that post before but it doesn't quite provide the solution I'm looking for.

I'm trying a different approach, but am stuck:
I've written code such that a folder is created and named according to the record ID. What I am stuck at is to save the file that a user has selected from FilePicker to the newly created folder.

Any advice on how I can proceed with the code, please?

Thank you!

    strFileDestination = "C:\Documents\Access Project\" & FolderID


    If Dir(strFileDestination, vbDirectory) = "" Then
        MkDir strFileDestination
    End If


    MsgBox "Select file for uploading."
    Application.FileDialog(msoFileDialogFilePicker).Show
    strFileOrigin = Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Item(1)

[ItsMe]

msoFileDialogFilePicker will allow the user to select a file rather than a folder, so that is good. And you have the result stored in your string variable, strFileOrigin. This is good too.

Now you need to copy and paste your file that is identified in strFileOrigin. The main problem I see is that you do not know the file name or extension type chosen by the user. You could parse the name and extension from strFileOrigin. Alternatively, you could give the file your own name. The issue with naming the file is getting the correct extension type. So you could employ a filter to your FileDialog.

Let's start with some declarations to get the FileDialog created and then set its properties.

If you notice in the example I linked to, there is not a very obvious declaration
With Application.FileDialog(msoFileDialogFolderPicker)

the filesystem object is held in memory using With

So let's do it a little differently. Something like

Dim objDialog As FileDialog
Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
objDialog.AllowMultiSelect = False
objDialog.Filters.Clear
objDialog.Filters.Add "Excel", "*.xlsx"
objDialog.Show

Now we have an object that will return a string. Let's make sure we have a bucket to store it in.

Dim strFileOrigin As String
strFileOrigin = ""
strFileOrigin = objDialog.SelectedItems(1)

Now we need to copy it and paste it into another folder. For this we will need a reference to the Microsoft Scripting Runtime.

'Create a full path and add the file name to the string
strFileDestination = "C:\Documents\Access Project\" & FolderID
If Dir(strFileDestination, vbDirectory) = "" Then
    MkDir strFileDestination
End If
strFileDestination = strFileDestination & "\MyCopiedFile.xlsx"
'Now we can create a new file system object to copy the User selected file and paste it
'to the new folder we just made
Dim fso As New FileSystemObject
fso.CopyFile (strFileOrigin, strFileDestination, True) 'with overwrite existing file as true

See if you can test the above code and get it to work

[incipientclarity]

Thanks, ItsMe! I managed to get it to work with your suggestions. Really appreciate your help in this!

[ItsMe]

Glad to hear