Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Automatic check for folder content?

    Does anyone know of a way to see if a folder has any files in it?

    I have a form that uses folders (named after the record ID) to contain .pdf documents to support the record; not all records have supporting documents. I would like to have a way to indicate on the form whether there is anything in the associated folder, or even if there is an associated folder (generally, there would not be a folder if there are no documents).

    I have used a checkbox, but being human, checking it is often overlooked. I need something that automatically looks for the folder based on record ID, and then (by text box?) displays something to indicate the folder exists. There now, that should be clear as mud.


    Any ideas greatly appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You can use the Dir function to both check that a folder exists, and whether anything is in it.

    And you can use the Mkdir function to create a folder.
    Last edited by johnhutchison; 2011-10-11 at 18:37.
    Regards
    John



  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Wow, I'm really sorry about not getting back to you John. I was out of town for a few days seeing family and totally forgot about the post when I returned; I don't know why I didn't get an email on your post, I thought that was automatic.

    But, in any case, and being a VB dunce, I appreciate your links but I don't know enough to write my own code. Hans helped a bunch a few years past but I'm only now getting back to looking at it again and pretty much have forgotten everything (and I'm not just talking about code either). If you had some sample code I could adapt, that would be awesome, and truly appreciated. No expectations here of course, I am grateful for all that you (and so many others) do here in the Lounge.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    A couple of questions
    1. What is the field name of your Record ID?
    2. What is the base path where these folders are stored?

    This will help in writing some code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    1. ID (I'm not very creative)
    2. C:\Users\Bryan\Documents\My Applications\Access Applications\Personal Journal\Documents\
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bryan,

    This should at least get you started.
    Code:
    Public Function bJFiles( zFileID as String ) as Boolean
    
    '+---------------------------------------------------------------------+
    '| Call function from form: checkboxcontrol.value = bJfiles([ID]) |
    '| NOTE: the call is just air code you'll have to fill it in.               |  
    '+---------------------------------------------------------------------+
    
        Dim zJFilePath as String
    
        zJfilePath = "C:\Users\Bryan\Documents\My Applications\Access Applications\Personal Journal\Documents\"
    
        If Dir(zJfilePath & zFileId & "\*.pdf") = vbNullString then
          bJfiles = False  '*** No .pdf Files or zFileId directory does not exist ***
        Else
          bJfiles = True   '*** At least 1 .pdf File ***
        End If
    
    End Function   'bJfiles
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Thanks so much for the suggestion RG, I'll try working with it this evening when the phone isn't driving me nuts. But honestly I'm already a bit baffled; no matter, my signature explains it all.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brian,

    I tested the code in the function and it works correctly. What I'm having trouble with is finding a Form Event to hang the call on that will change when you navigate from record to record on the form. I can tie it to the GotFocus event of a field on a form and it works fine but I can't find one that works when you just navigate records. John H. where are you? I'm still working on it.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I tried John's idea . . .
    Code:
    Private Sub Form_Load()
    If Len(Dir("C:\Users\Bryan\Documents\My Applications\Access Applications\Personal Journal\Documents\ & Me.ID")) = 0 Then
        MsgBox "No supporting document(s)."
    Else
        MsgBox "Supporting documents exist."
    End If
    End Sub
    . . .but I get the same results regardless of [ID] number ("No supporting document(s)")
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I think I may like your idea better RG, but I just don't understand it, or where the code goes.
    Is bjFiles the name of a check-box?
    I don't see in the code anywhere where the [ID] is defined
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by RetiredGeek View Post
    John H. where are you?
    RG I do have to work sometimes.


    Code:
    Private Sub Form_Load()
       Dim strPath as string
       Dim strID as string
       strPath ="C:\Users\Bryan\Documents\My Applications\Access Applications\Personal Journal\Documents\"
       strID= me.ID 
        If Len(Dir(strPath & strID)) = 0 Then     
             MsgBox "No supporting document(s)." 
       Else     
            MsgBox "Supporting documents exist." 
       End If 
    End Sub

    All that I have really changed is this line. Should to move the me.ID outside of the double quotes.

    If Len(Dir("C:\Users\Bryan\Documents\My Applications\Access Applications\Personal Journal\Documents\ & Me.ID")) = 0 Then
    If Len(Dir("C:\Users\Bryan\Documents\My Applications\Access Applications\Personal Journal\Documents\" & Me.ID)) = 0 Then

    Your original question also talks about checking that the folder exists. This code would create the folder if it does not exist.

    if Len(Dir(strpath & strID, vbDirectory))=0 then
    MkDir(strPath & strID)
    end if
    Regards
    John



  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brian,

    Ok, I found the correct event: Form_Current. So here's what you do:

    1. Open your database.
    2. Press Ctrl+F11 to open the VBA editor.
    3. Select Insert->Module from the menu.
    4. Copy and paste the VBA code from my previous post in the Code window.
    5. Click Save for safety.
    6. Open your form in Design Mode.
    7. Note the name of your checkbox control.
    8. Click the form properties selection box (circled in red on 1st graphic)
    9. Find the On Current property and click the drop down arrow.
    10. Click the button with the 3 dots ...
    11. This will put you back in the VBA editor with a blank procedure call for the Form_Current() event.
    12. In between the Sub/End Sub lines add: me.CONTROL = bJFiles([ID]) where CONTROL is replaced by the name you noted in step 7 above.
    13. Click Save.
    14. X out of the VBA editor.
    15. Save your Form!

    Your form should now automatically update your check box everytime you change the record.

    Example of step 12:
    Code:
    Private Sub Form_Current()
       Me.Check0 = bJFiles([FName])
    End Sub
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I do have to work sometimes.
    LOL, I thought this was work John. Ha-ha, thanks for taking time out to address this. I did as you said, but I still get the "No supporting . . ." result even when there is a document in the folder. Trying RG's suggestion again. While you did actually address my question I kinda like the check-box idea for future sorting of records with docs, but we'll see.

  14. #14
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts


    Awesome RG! That's more than I was asking for and just what I need!

    Thanks for the hand-holding man I really appreciate it. Now I will create a text box with conditional formatting to appear any time the check box (hidden) is checked. Very cool man, very cool!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I did not actually test my code.

    Needs to be

    StrID = me.ID & "\"
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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