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

    VBA Query not working (MSACCESS2003)

    I have some code that checks documents residing in a subdirectory of a subdirectory, however it does not seem to find the documents and adds duplicates? Where am I going wrong, thanks

    SQL6 = "Select * From SubDirectorySubDocs"
    Set rst6 = db.OpenRecordset(SQL6, dbOpenDynaset)

    ' THESE ARE THE SUBDIRECTORIES OF SUBDIRECTORIES

    For Each fil In fld.Files

    rst6.FindFirst "SD3 = " & Chr(34) & fil.Name & Chr(34)

    If rst6.NoMatch Then

    ' Dump data to table
    rst6.AddNew
    rst6("SD3") = JPPPP & "" & AJP
    rst6![SD3DOC] = fil.Name
    rst6("DirID") = PPK
    rst6.Update

    Else
    ' Documents in table

    End If
    Next fil

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

    Re: VBA Query not working (MSACCESS2003)

    You store the names in the SD3DOC field but you search for them in the SD3 field; I think you should search in the SD3DOC field.

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

    Re: VBA Query not working (MSACCESS2003)

    Hi Hans. The data in the table is:

    SD3 = kocuments1Test1Test2Test3 ie the path
    SD3DOC = BOURY Hugues - ECS 13396.doc ie the document name

    Regards

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

    Re: VBA Query not working (MSACCESS2003)

    For Each fil In fld.Files

    looks at files, not at subfolders.

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

    Re: VBA Query not working (MSACCESS2003)

    You are correct Hans, many thanks once again. Regards

Posting Permissions

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