Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicating record (MSACCESS 2003)

    I have some code which I thought was workingokay, but now it is duplicaing the last record? I added the words THISONEXXXX so I could identify it in the table. Any clues? Thanks

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(MainDir)

    ' ==================================================
    ' Get the main directory names
    SQL = "Select * From tblDirectory"
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
    For Each sfl In fld.SubFolders

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

    If rst.NoMatch Then
    ' Not found, so dump main directory data into table
    rst.AddNew
    rst![SD1] = sfl.Name
    rst![SD1DOC] = sfl.Name 'MainDir & "" & sfl.Name
    PK = rst("DirID") ' Primary Key of tblDirectory
    rst.Update

    Else
    PK = rst("DirID") ' Primary Key of tblDirectory
    End If

    ' ==================================================
    ' Get the main directory documents here
    SQL2 = "SELECT * FROM MainDirDocs;"
    Set rst2 = db.OpenRecordset(SQL2, dbOpenDynaset)
    Set fld3 = fso.GetFolder(MainDir & "" & sfl.Name)

    For Each fil3 In fld3.Files

    ' Need to find if record already exists before adding duplicates
    rst2.FindFirst "SD1 = " & Chr(34) & fil3.Name & Chr(34)

    If rst2.NoMatch Then
    rst2.AddNew
    rst2![SD1] = fil3.Name & " THISONEXXXX"
    rst2![SD1DOC] = sfl.Name & "" & fil3.Name 'MainDir & "" & sfl.Name & "" & fil3.Name
    rst2("DirID") = PK
    rst2.Update

    Else
    ' Do nothing
    End If


    Next fil3

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicating record (MSACCESS 2003)

    The line

    If rst2.NoMatch Then

    means that the code below it is executed if NO record satisfying the condition was found. In that situation, the recordset is positioned at the last record. I think you want

    If Not rst2.NoMatch Then

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating record (MSACCESS 2003)

    Thanks Hans. I changed the line and the table is now full of duplicates. I was wanting the code below "If rst2.NoMatch Then" to add the record in the table if it did not exist. It seemed to work on all other records okay, but just one duplicates each time. I thought it might have been a problem in the table not storing enough characters but its not the case. ??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicating record (MSACCESS 2003)

    I apologize, I didn't read your original post carefully enough.
    But now I don't understand what you mean by "It duplicates the last record". <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating record (MSACCESS 2003)

    Thats okay. One of my records that exists in the table and is on the drive directory is not being found in my code, so it adds the record into the table again. It seems to evaluate all other records/files okay but it might be a coincidence but its always the last record? The filename is SANCHEZ Emilio - Arena Cup 2003 - ECS7678 English translatio.doc if thats any help. Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicating record (MSACCESS 2003)

    Have you checked that the file name is EXACTLY the same as the name in the table? If there is a difference of even one character (such as a space), Access wouldn't consider the names to match.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating record (MSACCESS 2003)

    Many thanks Hans. I was one digit out on on my tables text length, so it was saving it shorter than the filename itself, and continuing to add it. I have greatly improved the text length so it won't happen again. I thought I had properley checked it !! Many thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •