Resume Next and Remain in the loop

[bee55]

Hello

i have the following code to get file list from specified directory:

Dim db As DAO.Database
Dim TB1 As DAO.Recordset
Dim p As String, x As Variant

 Set db= CurrentDb
 Set TB1 = db.OpenRecordset("Table1")

    p = "E:/Reports /*.xlsx"
    x = GetFileList(p)
    
Select Case IsArray(x)
        Case True 
              For I = LBound(x) To UBound(x)
                With TB1
                    .AddNew
                    !File_Name = x(I)
                    .Update
                End With
             Next I
        Case False
            MsgBox "No matching(.xlsx) files"
 End Select

the field "File_Name " is not set to accept duplicate
the above code work fine , but when it find the file name exist on the TB1 it
return an error
i need the code if find any error continue to retrive other files on the folder
not exit loop
and also i don't want to make check if file exist it will lower the speed of code

 

[namliam]

Are you looking for:
On Error Resume Next

Plus there shouldnt be duplicate file names to start with...

 

[BlueIshDan]

Try this out

Public Sub Wtf()

    Dim TB1 As Recordset: Set TB1 = CurrentDb.OpenRecordset("Table1")
    Dim p As String: p = "E:/Reports /*.xlsx"
    Dim fileList As Variant: fileList = GetFileList(p)
    
    If IsArray(fileList) Then
    
        For i = 0 To UBound(fileList)

            If DCount("File_Name", "Table1", "File_Name=""" & fileList(i) & """") = 0 Then

                TB1.AddNew
                TB1!File_Name = fileList(i)
                TB1.Update

            End If

        Next i
        
    Else: MsgBox "No matching(.xlsx) files"
    End If
    
End Sub

 

[bee55]

Are you looking for:
On Error Resume Next

exactly i need the code on error to resume next but not all error only duplication error

Are you looking for:

Plus there shouldnt be duplicate file names to start with...

i always run the function on more than one folder. so the duplicate files often appear

 

[bee55]

Try this out

Public Sub Wtf()

    Dim TB1 As Recordset: Set TB1 = CurrentDb.OpenRecordset("Table1")
    Dim p As String: p = "E:/Reports /*.xlsx"
    Dim fileList As Variant: fileList = GetFileList(p)
    
    If IsArray(fileList) Then
    
        For i = 0 To UBound(fileList)

            If DCount("File_Name", "Table1", "File_Name=""" & fileList(i) & """") = 0 Then

                TB1.AddNew
                TB1!File_Name = fileList(i)
                TB1.Update

            End If

        Next i
        
    Else: MsgBox "No matching(.xlsx) files"
    End If
    
End Sub

this solved the issue, but if i find a code to resume next on the duplication error only will be more better

 

[Solo712]

this solved the issue, but if i find a code to resume next on the duplication error only will be more better

I would stick with BlueishDan's solution. It's cleaner. If you use the Resume Next, route you will have to test if error occured and clear it if it did which adds to your cycles.

Best,
Jiri

 

[namliam]

Well your best solution would NOT be either mine or Dan's, instead of having a filename that is "Unique" you should have a check on Folder + filename, since that is your true requirement.

Now you are just losing filenames from folders that you might be intrested in