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

    File checking (MSACCESS 2003)

    I am trying to join parts of a query which contain 1/2 or 3 linked tables to result in a final file name for shecking on a drive. However I cannot passed the first hurdle. The code results in a message saying the item is not in a collection. Is there a way to differntiate between two fields having the same name or something. I have gone wrong somewhere along the way. Any suggestions, thanks

    Mysql = "SELECT tblDirectory.SD1, tblDirectory.DirID, MainDirDocs.SD1, MainDirDocs.DirID"
    Mysql = Mysql & " FROM tblDirectory INNER JOIN MainDirDocs ON tblDirectory.DirID = MainDirDocs.DirID;"
    Set rst = db.OpenRecordset(Mysql, dbOpenDynaset)
    rst.MoveFirst
    Do While Not rst.EOF

    strFullName = rst("SD1")

    Me.BPP.AddItem strFullName ' This line put in to get visible clue into problem
    'Test if directory or file exists
    ' If FileOrDirExists(strFullName) Then
    ' ' DoCmd.Beep
    ' ' No action
    ' Else
    ' ' Not there so delete in table
    ' DoCmd.SetWarnings False
    ' rst.Delete
    ' DoCmd.SetWarnings True
    ''
    ' End If

    rst.MoveNext
    Loop
    Set rst = Nothing

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: File checking (MSACCESS 2003)

    You could use

    Mysql = "SELECT tblDirectory.SD1 as SD1a, tblDirectory.DirID as DirIDa, MainDirDocs.SD1 as SD1b, MainDirDocs.DirID as DirIDb "

    then

    strFullName = rst("SD1a")
    Regards
    John



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

    Re: File checking (MSACCESS 2003)

    Many thanks John, Happy New Year. Sorry I did not get straight back as everything started crashing around me. However your answer solved my problem so many thanks.

Posting Permissions

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