Add muliple files / records to a table at one time (files selected by using the file picker)

[Moss100]

Hello - I have some test code that allows me to add a file path to a table - this works ok (i'm sure it could be improved - suggestions welcome!).

What I really want to do is to choose multiple files and add them to the table. I want each file path to appear in a new record in the table. I guess I need some kind of looped code. Please could someone modify my code so that multiple selected files are added to the table.

Many thanks Mark

Current code:

Private Sub btn_Add_File_DblClick(Cancel As Integer)

 'Open the File Dialog so a file can be selected
  Dim objDialog As FileDialog
  Dim strPickedFile As String
  
 'Open the file picker
  Set objDialog = Application.FileDialog(msoFileDialogOpen)
  With objDialog
    .InitialFileName = "c:\temp"                                            'Sets default opening location
    .AllowMultiSelect = True                                                'Sets whether multiple files can be selected
    .Title = "Select File"                                                  'Picker title
    .Filters.Clear                                                          'Clears filters
    .Filters.Add "All Files", "*.*"                                         'Sets all files filter option
    .Filters.Add "Picture Files", "*.Jpg"                                   'Sets JPEG files filter option
    .Filters.Add "PDF Files", "*.pdf"                                       'Sets PDF files filter option
     
    'open dialog and result comes back from .show as true or false
    If .Show = True Then
      strPickedFile = .SelectedItems.Item(1)
      Else
      strPickedFile = ""
    End If
    
  End With

    
  'Check to see if a file has been selected else Exit sub
   If strPickedFile = "" Then
      MsgBox "No FILE was selected - Please try again", vbExclamation + vbOKOnly, "NO FILE-(msg)"
      Exit Sub
   End If
  
  
  'Add selected File to Table tbl_File
  Dim objRS As DAO.Recordset
  Set objRS = CurrentDb.OpenRecordset("tbl_File", dbOpenDynaset)
  
  With objRS
    .AddNew
    .Fields("FilePath") = strPickedFile
    .Update
  End With
            
  objRS.Close
  Set objRS = Nothing
    
  'Sets txt_File_Selected to null
  strPickedFile = ""
    
  
End Sub

 

[Andrzejek]

Consider this:

...[green]
    'open dialog and result comes back from .show as true or false[/green][blue]
    Dim X As Integer[/blue]
    If .Show = True Then[blue]
        For X = 1 To .SelectedItems.Count
            MsgBox .SelectedItems.Item(X)
        Next X[/blue][green]
      'strPickedFile = .SelectedItems.Item(1)[/green]
    Else
      strPickedFile = ""
    End If

...

---- Andy

There is a great need for a sarcasm font.

 

[Moss100]

Andy - thank you very much for your help. My coding is pretty basis.

With your help I have put togther the following. I wonder if you could have a look - it works, but I'd guess it could be made better or more robust?

Many thanks

Mark

Private Sub btn_Add_File_DblClick(Cancel As Integer)

 'Open the File Dialog so a file can be selected
  Dim objDialog As FileDialog
  Dim strPickedFile As String
  
 'Open the file picker
  Set objDialog = Application.FileDialog(msoFileDialogOpen)
  With objDialog
    .InitialFileName = "c:\temp"                                            'Sets default opening location
    .AllowMultiSelect = True                                                'Sets whether multiple files can be selected
    .Title = "Select File"                                                  'Picker title
    .Filters.Clear                                                          'Clears filters
    .Filters.Add "All Files", "*.*"                                         'Sets all files filter option
    .Filters.Add "Picture Files", "*.Jpg"                                   'Sets JPEG files filter option
    .Filters.Add "PDF Files", "*.pdf"                                       'Sets PDF files filter option
     
 'Open dialog and result comes back from .show as true or false
    Dim X As Integer
    If .Show = True Then
        For X = 1 To .SelectedItems.Count
        strPickedFile = .SelectedItems.Item(X)
 'Add selected File to Table
   Dim objRS As DAO.Recordset
   Set objRS = CurrentDb.OpenRecordset("tbl_File", dbOpenDynaset)
   With objRS
     .AddNew
     .Fields("FilePath") = strPickedFile
     .Update
   End With
     
    objRS.Close
    Set objRS = Nothing
            
   Next X
 
   Else
     strPickedFile = ""
   End If

  End With

End Sub

 

[Andrzejek]

So you have a table tbl_File with just one field named FilePath?

Do you want/need to check if this record already exists in this table? Or, does not matter, you want to INSERT selected file path/name to this table.


---- Andy

There is a great need for a sarcasm font.

 

[Moss100]

Yes the table has [ID] field and [FilePath] field.

ALthough at the moment it does not matter if the file is already in the table, I would be interested to see how you would check for this.

Thansk for your help with this

Mark

 

[Andrzejek]

If "it does not matter if the file is already in the table", I would do something like this (code not tested)

...
 'Open dialog and result comes back from .show as true or false
    Dim X As Integer
    Dim strSQL As String
    If .Show = True Then
        For X = 1 To .SelectedItems.Count
            strSQL = INSERT INTO tbl_File (FilePath) VALUES '" & .SelectedItems.Item(X)
 & "'"
            DoCmd.RunSQL strSQL
        Next X
    End If
...

To check if the file path is already in the table, I would create a recordset:
[tt]
SELECT * FROM tbl_File [/tt]

and before INSERT statement, I would Filter this recordset to see if there is a record with that file path/name. If NOT, run the INSERT statement.



---- Andy

There is a great need for a sarcasm font.