How to check if field value exists using openrecordset? (1 Viewer)

[Maazter]

Hi everyone,

I am new to access programmers and to vba.

I am storing values of pictures and the location of them in a table, this works fine!... using OpenRecordset. The problem is that when the function is called to store the information, it just keeps adding the same values of each file in the folder over and over again in a word "Duplicating" the information.

I have tried various methods using the OpenRecordset, but cannot seem to find the correct manor of applying the code.

Below is the function I have for storing the data...any help would be greatly appreciated.

Public Sub GetFilesNamesFromFolder(strFolderPath As String)
On Error GoTo ErrorHandler

   Dim objFSO As Scripting.FileSystemObject
   Dim objFolder As Scripting.folder
   Dim objFile As Scripting.File
   Dim objFile2 As Scripting.File
   Dim strSQL As String
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim strPrompt As String
   Dim strTitle As String
   Dim strTable As String
   Dim intMissingCount As Integer
   Dim strClientID As String
   Dim fFile As String

   
   strTable = "Document"
   strClientID = Forms!Addpics!KlantNr
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
   
   Set objFolder = objFSO.GetFolder(strFolderPath)
 
   
   If Not objFSO.FolderExists(strFolderPath) Then

      strPrompt = "Please enter a valid folder path"
      strTitle = "Folder path not found"
      MsgBox strPrompt, vbCritical + vbOKOnly, strTitle
      GoTo ErrorHandlerExit
         
   Else
                                 

      For Each objFile In objFolder.Files
      
         'If rst![SourceFilename] = objFile Then
         
         'rst.FindNext (objFile)
      
         'Else

         rst.AddNew
         rst![SourceFilename] = objFile.Name
         rst![tDir] = objFile.path
         rst![ProjectFolder] = objFolder.ParentFolder.Name
         rst![ProjectSubFolder] = objFolder.Name
         rst![ClientID] = strClientID
         rst![DateChecked] = Date
         rst.Update
         
   
       Next objFile
                           
 End If

        Set rst = Nothing
        Set dbs = Nothing
   
 'End If
 
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

:banghead:

 

[namliam]

Try the dcount or dlookup funtion for this

 

[smig]

since you exit sub if the If is true there is no need for the Else part.

Do you get several records added, or only one ?

 

[Maazter]

Hi Guys,

Thank you very much for your quick response

I get one extra file added for each file in the selected directory...So if I load the same directory each time, it will load the same files that are in that directory and add them again to the table.

ie I open that folder with the function 3 times, then it will have "3" times all of the files in that directory stored in the table.

Appart from that everything works fine.

Any example would be greatly appreciated!

I have tried various methods using the "If rst!Fieldname = ObjFile.Name Then" and so one, but this just gives me various problems depending on how I apply it.

Thnx in advance.

 

[CJ_London]

in your table, set the fieldname field indexing to index-duplicates not allowed

 

[Maazter]

Hi again,

Thank you CJ_London for your answer.

I have done this... and added an error control to end if it should throw that error in this case "3022" duplicate value is passed.

is this and effective way to solve this? or is it still better to find a way of coding it to control for duplicate values.

In other words will this method slow down searching as the database grows?

here is the updated code that now works using the "Yes No duplicates" for the Field indexing and end on error code 3022.

Public Sub GetFilesNamesFromFolder(strFolderPath As String)
On Error GoTo ErrorHandler

   Dim objFSO As Scripting.FileSystemObject
   Dim objFolder As Scripting.folder
   Dim objFile As Scripting.File
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim strPrompt As String
   Dim strTitle As String
   Dim strTable As String
   Dim strClientID As String
   Dim sfieldName As String
   Dim tEnd As String

   strTable = "Document"
   strClientID = Forms!Addpics!KlantNr
   
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
   Set objFolder = objFSO.GetFolder(strFolderPath)
 
   
   If Not objFSO.FolderExists(strFolderPath) Then

      strPrompt = "Please enter a valid folder path"
      strTitle = "Folder path not found"
      MsgBox strPrompt, vbCritical + vbOKOnly, strTitle
      GoTo ErrorHandlerExit
         
   Else

   
         For Each objFile In objFolder.Files
      
         sfieldName = objFile.Name
         
         rst.AddNew
         rst![SourceFilename] = objFile.Name
         rst![tDir] = objFile.path
         rst![ProjectFolder] = objFolder.ParentFolder.Name
         rst![ProjectSubFolder] = objFolder.Name
         rst![ClientID] = strClientID
         rst![DateChecked] = Date
         rst.Update
         
   
       Next objFile
                                
         

End If
        Set rst = Nothing
        Set dbs = Nothing
   
 
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   If Err.Number = 3022 Then ' To end if the function throws duplicate value
   GoTo tEnd
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
tEnd:
   Resume ErrorHandlerExit
End If
End Sub

 

[smig]

OK, now I understand what is the problem

The only option I see is having a field storing the folder and the file name as long string.
Set this field as no dupliacates.

 

[namliam]

Try the dcount or dlookup funtion for this

Try using the dcount or dlookup function for this

 

[Maazter]

Thank you namliam

The Problem is i am not sure where to place th code "Still learning Coding" sorry for being naive.

Should i do this when the "for each" function starts or should I do it before the for "for each" starts? and then assign to a variable and pass it through?.

Thank you aggain for your support on this matter.

 

[Maazter]

OK, now I understand what is the problem

The only option I see is having a field storing the folder and the file name as long string.
Set this field as no dupliacates.

Hi Smig,

Yes that is what I have done exactly that and it works! I just wanted to know if there was a better way of doing this with code, without setting the field as No Duplicates.

As namliam states with Dlookup function, the problem is I am not exactly sure on where to place the Dlookup function...

ie whilst the...

 For Each objFile In objFolder.Files
      
         rst.AddNew
         rst![SourceFilename] = objFile.Name
         rst![tDir] = objFile.path
         rst![ProjectFolder] = objFolder.ParentFolder.Name
         rst![ProjectSubFolder] = objFolder.Name
         rst![ClientID] = strClientID
         rst![DateChecked] = Date
         rst.Update
          
       Next objFile

is running... or before the "For each" statement, as another "If" clause prior to it?

Again sorry for my naive questions, still learning at least trying to learn it correctly

Thnx Again both of you.

 

[namliam]

well I would do something like

If Dcount() > 0 then 
 ' filename already exists, do nothing
else
   rst.Addnew
...........
...etc...
...........
endif

I will leave it up to you to build the DCount function though!

 

[Maazter]

Hi again namliam

I always get argument not optional when I try this, is the a problem with where I place the code, or am I forgetting to declare something.

Tried the code as follows...

For Each objFile In objFolder.Files

If DCount() > 0 Then
' filename already exists, do nothing
Else


rst.AddNew
rst![SourceFilename] = objFile.Name
rst![tDir] = objFile.path
rst![ProjectFolder] = objFolder.ParentFolder.Name
rst![ProjectSubFolder] = objFolder.Name
rst![ClientID] = strClientID
rst![DateChecked] = Date
rst.Update



Next objFile

 

[namliam]

you are missing the "End If" after the rst.update

Plus you need to tell DCount what to do...
try having a look here:
http://www.techonthenet.com/access/functions/domain/dcount.php
or here:
http://msdn.microsoft.com/en-us/library/office/ff191865(v=office.15).aspx

And see how far you get