Table and drive comparison 1

[Domino2]

I have a table that gets filled with directory names and files. However I am stuck as to combine two fields into finding whether:
1.The record is in the table
2.The record is not in the table
3.The record is in the table but no longer on the drive

If someone could show me a straightforward direction I would be very appreciative. Thanks


For Each sfl In fld.SubFolders ' loop main directory

' Loop to check if any documents in main folder

For Each fil In sfl.Files

NEED TO CHECK HERE BEFORE ADDING OR DELETING

rst.AddNew
rst("MDIR") = UCase(sfl.Name) & "\"
rst("Document") = fil.Name
rst.Update

Next fil

 

[Remou]

1.The record is in the table
You appear to have a recordset (rst), so you can use FindFirst on that. If NoMatch is True ...

2.The record is not in the table
Then the file name/ path record is not in the table.

3.The record is in the table but no longer on the drive
You can use the FileSystemObject, which you already seem to be using (sfl.Files) or Dir.

If you post more complete code, it may be possible to say where the problems are occurring.

 

[Domino2]

Thanks Remou, will use findfirst and If FileOrDirExists(xxx)so this should get me my result.

 

[Domino2]

Small problem. My code finds the file, but how can I change the syntax so it also coniders the directory MDIR?
Thanks

For Each sfl In fld.SubFolders ' loop main directory

' Loop to check if any documents in main folder

For Each fil In sfl.Files

' Need to find if record already exists before adding duplicates
rst.FindFirst "Document = " & Chr(34) & fil.Name & Chr(34)

If rst.NoMatch Then
' Not found - Put into table
DoCmd.Beep
rst.AddNew
rst("MDIR") = UCase(sfl.Name) & "\"
rst("Document") = fil.Name
rst.Update
Else
' Found - Do not duplicate
End If

Next fil

 

[Remou]

How about:

rst.FindFirst "MDIR='" & Replace(sfl.Name,"'","''") _
& "' AND Document = '" & Replace(fil.Name,"'","''") & "'"

The replace function is in case any name contains a single quote, it does not appear that way in the file.

 

[Remou]

Drat. Forgot the slash.

rst.FindFirst "MDIR='" & Replace(sfl.Name,"'","''") & "\" _
& "' AND Document = '" & Replace(fil.Name,"'","''") & "'"

 

[Domino2]

Perfect. Many thanks.