If record exists, append table then move on to next

[Cassie_H]

Hello,

I have a database that checks the names of subfolders in a folder and then creates a table for them in the database, named tblTempProjects. What I then want it to do is check to see if these folder names already exist on another table, tblProjects, if the name does exist, then continue down the list of names, if it doesn't then append the table with the new folder name.

Both tables have the same fields, 'ProjectName' and 'Active'. They're both text and Boolean. Not sure what I'm doing wrong, not very good with Access at all!

I'm getting a runtime 3070 error when I'm trying to get it to search the tables. This is what I have so far:

Private Sub UpdateProjects_Click()
    
    Dim dbs               As Database
    Dim tdfNew          As TableDef
    Dim Folder           As String
    Dim strFind          As String
    Dim DirectoryName
    Dim sql
    Dim rsProject       As DAO.Recordset
    Dim rsTemp         As DAO.Recordset

    
    Set dbs = CurrentDb
    Folder = "C:\NAT\BUS\PAY\PAM\Database\QA\"
     
   For Each mytable In dbs.TableDefs
      If mytable.Name = "tblTempProjects" Then
         dbs.TableDefs.Delete "tblTempProjects"
      End If
   Next

   Set tdfNew = dbs.CreateTableDef("tblTempProjects")

   With tdfNew
     .Fields.Append .CreateField("ProjectName", dbText)
     .Fields.Append .CreateField("Active", dbBoolean)
   End With
   
   dbs.TableDefs.Append tdfNew

     DirectoryName = Dir(Folder, vbDirectory)
     Do Until DirectoryName = ""
         If DirectoryName <> "." And DirectoryName <> ".." Then
             If (GetAttr(Folder & DirectoryName) And vbDirectory) = vbDirectory Then
             sql = "INSERT INTO tblTempProjects ( ProjectName ) " & _
                 "SELECT '" & DirectoryName & "' AS Directory"
         CurrentProject.Connection.Execute sql
         End If
        End If
         DirectoryName = Dir
     Loop

Set rsProject = CurrentDb.OpenRecordset("tblProjects", dbOpenDynaset)
Set rsTemp = CurrentDb.OpenRecordset("tblTempProjects", dbOpenDynaset)

Do While Not rsProject.EOF
     rsTemp.MoveFirst
     
     Do While Not rsTemp.EOF
         strFind = rsProject!ProjectName
         rsTemp.FindFirst strFind
         If rsTemp.NoMatch Then
           rsProject.AddNew
             rsProject!ProjectName = rsTemp!ProjectName
           rsProject.Update
         End If
       rsTemp.MoveNext
     Loop

   rsProject.MoveNext
 Loop

End Sub

Assistance would be greatly appreciated!

 

[Micron]

The error is telling you what cannot be found, yes? Post the message and indicate on which line of code the error is raised.