Load attachments via VBA (1 Viewer)

[freddykat]

I
I'm trying to load the max of 8 attachements on the specified form.
I've this code so far?

'FILE DIALOG OPENS UPLOAD PANEL
Private Sub Comando715_Click()
Call Selectfile
End Sub
'FUNÇÃO CAIXA DE SELEÇÃO
Public Function Selectfile() As String
Dim Fd As FileDialog
Dim Filtro As FileDialogFilters
Set Fd = Application.FileDialog(msoFileDialogOpen)
With Fd
    'FILTRO DE IMAGENS
    Set Filtro = .Filters
    With Filtro
    .Clear
    .Add "Imagens", "*.jpeg;*.jpg"
    End With
    'NÃO PERMITIR MULTI-SELEÇÃO
    .AllowMultiSelect = True
    'DEFINE O NOME DA CAIXA DE SELEÇÃO
    .Title = " Por favor introduza as fotos da peça"
    If .Show = True Then
    'CODIGO DE COLOCAÇÃO DE ANEXO EM FORMULÁRIO
        Selectfile = .SelectedItems(1)
    Me.Anexo412 = Selectfile
    Else
    MsgBox "Clicou no botão cancelar ao escolher imagem."
        Exit Function
    End If
    Set Fd = Nothing
End With
End Function

I think the code is wrong in here.

Me.Anexo412 = Selectfile

I tryed this one but only saved the default picture of the attachments.

Me.Anexo412.Defaultpicture = Selectfile

Any ideas?!

 

[vbaInet]

Welcome to AWF freddykat!

First of all, SelectedItems is a collection so you will need to loop through that:

Dim SelItem As Variant

For Each SelItem In .SelectedItems
    ... do what you want with SelItem ...
Next

SelItem will return the path to each selected file.

You're then trying to load that file into the attachment field right? That's done through Recordset2. However, have you considered saving the path to the file instead of attaching it to the db? That's a much better practice.

 

[freddykat]

Welcome to AWF freddykat!

First of all, SelectedItems is a collection so you will need to loop through that:

Dim SelItem As Variant

For Each SelItem In .SelectedItems
    ... do what you want with SelItem ...
Next

SelItem will return the path to each selected file.

You're then trying to load that file into the attachment field right? That's done through Recordset2. However, have you considered saving the path to the file instead of attaching it to the db? That's a much better practice.

Im quite new in VBA programming.
Do you have an idea how to compile the code?
I think you´re right concerning the SelItem, but even with only one picture i cant attach it directly, how can i do it?
I think i could have the path but i need then the photos to be renamed and copyed to a specified location.
Can you help me with that? I can make a donation for your help

 

[vbaInet]

Yes the photos will need to be in a specific location. Is this not possible?

Add a reference to Microsoft Office X.0 Object Library (where X is the version of office you have installed. If you don't find it in the default location, you may find the dll here:
C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\MSO.DLL

That path is for Office 2010. Once you've done that I'll show you code that does what you ask.

 

[freddykat]

Yes the photos will need to be in a specific location. Is this not possible?

Add a reference to Microsoft Office X.0 Object Library (where X is the version of office you have installed. If you don't find it in the default location, you may find the dll here:
C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\MSO.DLL

That path is for Office 2010. Once you've done that I'll show you code that does what you ask.

Well i have the x.14 object library and i can open the filedialog but i cant find the code you talked about in access. Well listening to your advice i need it to do a few things upon attachement.
1º Rename the file as same as the number of the auto parts. Ex.part1_1;part1_2
(This number have to be lookuped on the form in textfield "Texto694"

2º I need it to copy the file/files so a specified location and add the path to ms access.

Im having a very hard time figure it out how to do it.

Thanks for support

 

[vbaInet]

Paste the following code in the form module:

Private Sub SaveToAttachmentField()
    Dim rsRecord    As DAO.Recordset
    Dim rsAttach    As DAO.Recordset2
    Dim fldAttach   As DAO.Field2
    Dim dlgOpen     As Office.FileDialog
    Dim selFile     As Variant
   
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    With dlgOpen
        .Title = "Select files to attach to record..."
        .ButtonName = "Select File(s)"
        .AllowMultiSelect = True
'        .InitialFileName = "Path:\to\default\folder"
'        .Filters.Add "Text", "*.txt", 1
        
        If .Show <> 0 Then
            Me.Dirty = False
            
            Set rsRecord = Me.RecordsetClone
            With rsRecord
                .Bookmark = Me.Bookmark
                .Edit
                
                Set rsAttach = .Fields("[COLOR="Blue"]AttachmentField[/COLOR]").Value
                With rsAttach
                    For Each selFile In dlgOpen.SelectedItems
                        .AddNew
                        .Fields("FileData").LoadFromFile selFile
                        .Update
                    Next
                End With
                
                .Update
            End With
        End If
    End With
    
    rsRecord.Close
    Set rsRecord = Nothing
    Set rsAttach = Nothing
    Set dlgOpen = Nothing
End Sub

... update the blue bit with the name of the attachment field.

Call the Sub in the Click event of the button that you want to use to run this procedure.

 

[freddykat]

Paste the following code in the form module:

Private Sub SaveToAttachmentField()
    Dim rsRecord    As DAO.Recordset
    Dim rsAttach    As DAO.Recordset2
    Dim fldAttach   As DAO.Field2
    Dim dlgOpen     As Office.FileDialog
    Dim selFile     As Variant
   
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    With dlgOpen
        .Title = "Select files to attach to record..."
        .ButtonName = "Select File(s)"
        .AllowMultiSelect = True
'        .InitialFileName = "Path:\to\default\folder"
'        .Filters.Add "Text", "*.txt", 1
        
        If .Show <> 0 Then
            Me.Dirty = False
            
            Set rsRecord = Me.RecordsetClone
            With rsRecord
                .Bookmark = Me.Bookmark
                .Edit
                
                Set rsAttach = .Fields("[COLOR=Blue]AttachmentField[/COLOR]").Value
                With rsAttach
                    For Each selFile In dlgOpen.SelectedItems
                        .AddNew
                        .Fields("FileData").LoadFromFile selFile
                        .Update
                    Next
                End With
                
                .Update
            End With
        End If
    End With
    
    rsRecord.Close
    Set rsRecord = Nothing
    Set rsAttach = Nothing
    Set dlgOpen = Nothing
End Sub

... update the blue bit with the name of the attachment field.

Call the Sub in the Click event of the button that you want to use to run this procedure.

Well I put your code but it give me an error on the beginning of the code. :banghead::banghead:
In this part:

Dim rsRecord As DAO.Recordset

"

Error the defined type by user was not defined"

I compiled the code this way.

Private Sub SaveToAttachmentField()
    Dim rsRecord As DAO.Recordset
    Dim rsAttach As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim dlgOpen As Office.FileDialog
    Dim selFile As Variant
    
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    With dlgOpen
        .Title = "Selecionar fotos para adicionar ao registo"
        .ButtonName = "Selecione ficheiro(s)"
        .AllowMultiSelect = True
        .InitialFileName = "Path:\to\default\folder"
        .Filters.Add "Imagens", "*.jpeg;*.jpg", 1
        If .Show <> 0 Then
        Me.Dirty = False
    Set rsRecord = Me.RecordsetClone
        With rsRecord
        .Bookmark = Me.Bookmark
        .Edit
        Set rsAttach = .Fields("Anexo412").Value
            With rsAttach
            For Each selFile In dlgOpen.SelectedItems
                .AddNew
                .Fields("FileData").LoadFromFile selFile
                .Update
            Next
                End With
                .Update
            End With
        End If
    End With
    
    rsRecord.Close
    Set rsRecord = Nothing
    Set rsAttach = Nothing
    Set dlgOpen = Nothing
End Sub

What im doing wrong? Thanks

UPDATE: Well after some research I found this... Could be the solution?
In the attachment field should I put "Me.Anexo412" or only "Anexo412"

 

[freddykat]

I noticed now that DAO360.dll was missing.
I download it but now the error is in line

Dim rsAttach As DAO.Recordset2

Im scratching my head figure it out..... I will have white hair very soon because of access

 

[vbaInet]

Add a reference to Microsoft DAO X.X Object Library.

In the future, when you encounter an error, please state the full error message and error number. So what is the error message?

 

[freddykat]

Add a reference to Microsoft DAO X.X Object Library.

In the future, when you encounter an error, please state the full error message and error number. So what is the error message?

I cant add the reference with this project open, only when a blank project is open. Its supposed to be like that? It pop ups a error in form view saying "Compilation Error: The defined type by user was not defined"
I allready add microsoft DAO 3.6 Object Library.:banghead:
Maybe I´ve misspelled wrong because as my ms access is in Portuguese sometimes its difficult to show the problem.
Im sending a print screen so you can see, unfortunately is in Portuguese so bare with me.
Thanks

I can send you the project if you find it useful

 

[vbaInet]

Ok, Recordset2 error requires this reference:

Microsoft Office 14.0 Access database engine Object Library

 

[freddykat]

Ok, Recordset2 error requires this reference:

Microsoft Office 14.0 Access database engine Object Library

But its supposed to have to open a blank project to add the reference? I dont know if it is normal. Im gonna test.

 

[vbaInet]

Stop your code and add the reference.

 

[freddykat]

When i have a error when i try to add a reference to the project saying that i have a name in conflict, with module, project or library already existing. And didn't add the reference
Right now I have this references: Visual basic for applications, Microsoft access 15.0 Access database engine object and Microsoft DAO 3.6 object library
Should i delete the 15.0 and then add the 14.0?
How do i stop the code? Sorry but im a beginner.
I noticed now that it tell me that im using so i cant take 15.0 out

 

[vbaInet]

Leave both references. You will find a stop button in the Code window, it looks like the kind of Stop button you see on a DVD player.

 

[freddykat]

Leave both references. You will find a stop button in the Code window, it looks like the kind of Stop button you see on a DVD player.

I cant choose 14.0. The system is telling me that name is in conflict.:banghead::banghead::banghead:

 

[vbaInet]

When I stated that you should leave both references, I was referring to the DAO reference and the Access Database Engine reference. 15 should have Recordset2.

Did you Debug > Compile?

 

[freddykat]

Somewhow the problem is a conflict with DAO 3.6 and it cant coexist dao 3.6 with 14.0 access database.
I deleted the DAO 3.6 and now the error is more down in line 4 "Dim dlgOpen As Office.FileDialog"
Say type not defined by user....

 

[freddykat]

I cant compile because of the error i talked about

 

[vbaInet]

Good catch, Access Engine reference contains the DAO libraries. You need to add the Office reference as mentioned in post #4.