Automatic check for folder content?

Topic

New Reply

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.
:confused:

Any ideas greatly appreciated.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

1. ID (I’m not very creative) :rolleyes:
2. C:UsersBryanDocumentsMy ApplicationsAccess ApplicationsPersonal JournalDocuments

Reply | Quote

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:UsersBryanDocumentsMy ApplicationsAccess ApplicationsPersonal JournalDocuments”

    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

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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. 😀

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I tried John’s idea . . .

Code:

Private Sub Form_Load()
If Len(Dir(“C:UsersBryanDocumentsMy ApplicationsAccess ApplicationsPersonal JournalDocuments & 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)”)

Reply | Quote

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

Reply | Quote

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:UsersBryanDocumentsMy ApplicationsAccess ApplicationsPersonal JournalDocuments”
   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:UsersBryanDocumentsMy ApplicationsAccess ApplicationsPersonal JournalDocuments & Me.ID”)) = 0 Then
If Len(Dir(“C:UsersBryanDocumentsMy ApplicationsAccess ApplicationsPersonal JournalDocuments” & 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

Reply | Quote

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.

Reply | Quote

I did not actually test my code.

Needs to be

StrID = me.ID & “”

Reply | Quote

Perfect John! I did end up using RG’s solution in this case, but I just tried yours for another “popup” form in the same db and it’s perfect. Thanks for the help and taking the time to nurse me along. All testimony to the uniqueness of this Lounge and the awesome people who selflessly help the “saber-toothed squirrels” of the world. Thanks again! :cheers:

Reply | Quote

Brian,

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

[*]Open your database.
[*]Press Ctrl+F11 to open the VBA editor.
[*]Select Insert->Module from the menu.
[*]Copy and paste the VBA code from my previous post in the Code window.
[*]Click Save for safety.
[*]Open your form in Design Mode.
[*]Note the name of your checkbox control.
[*]Click the form properties selection box (circled in red on 1st graphic)
[*]Find the On Current property and click the drop down arrow.
[*]Click the button with the 3 dots …
[*]This will put you back in the VBA editor with a blank procedure call for the Form_Current() event.
[*]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.
[*]Click Save.
[*]X out of the VBA editor.
[*]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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

:thewave::thewave:

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!

Reply | Quote

RG

I thought maybe I broke something when I added a .gif image to the folder, and the Access form did not reveal that there was anything in there.

I then realized that the code only looks to see if there is a .pdf file present (which was all I had considered in the beginning). Is there a “simple” way to look for any kind of file there, or must they all be defined? (i.e. pdf). Surely there would be far too many types to define, so if not then, I will have to convert any images to pdf. That’s not an immense chore, but one I would have to remember to do.

I love this new functionality in the db, and it really makes life so much better even as it is, but I couldn’t help but see if there was a way to improve it even more. I am deeply grateful for all your help thus far and even satisfied as is.

Reply | Quote

RG
Is there a “simple” way to look for any kind of file there, or must they all be defined? (i.e. pdf).

Just change the *.pdf in the code to *.* and you’ll get all the files in the directory. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I do have to work sometimes.

John,

I know and I feel for you as a retired person. 😆 Actually what I was saying is that I knew you would know the proper event to use where I was casting around to find it, and believe me it took me a while! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I think you found the right event. If the form is always opened to a specific ID you could use the Load event (I think) but the Current is would allow you to move through different records and have it run for each record. That sounds better.

I find the prospect of retirement attractive. I work for myself so I can’t just resign and leave. I am trying to avoid taking on new work, and finish up as many things as I can, with a view to becoming “nearly retired”.

Reply | Quote

I find the prospect of retirement attractive. I am trying to avoid taking on new work.

John,

I hope for your sake Ted M. doesn’t see this post he’s a hard task master here as you know. He’s been after me to work like he does. So far I’ve resisted but as it has been said “resistance is futile“. 😆 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Awesome, simple​, and it works too! Thanks RG! :cheers:

Reply | Quote

You can also find out a lot of useful information with the Scripting Run Time Library
This is just a small amount of information

You might not need the StrID on the end of the Path.
I am not sure if strID was file or another Folder.

[Code]

Private Sub Form_Load()

Dim strPath as string
Dim strID as string
Dim oFS As Object, oFD as Object
Dim lngFiles as Long

strPath =”C:UsersBryanDocumentsMy ApplicationsAccess ApplicationsPersonal JournalDocuments”
strID= me.ID

Set oFS = CreateObject(“Scripting.RunTime”)

If oFS.FolderExists(strPath & strID) Then ‘Check Folder Exists
Set oFD = oFS.GetFolder(strPath & strID)
MsgBox “Folder Exists and has … ” & oFD.Files.Count & ” Files in it!”
Else
Msgbox “Folder Does Not Exist”
End If
Set oFD = Nothing
Set oFS = Nothing

End Sub

[/Code]

Haven’t tested this though.

Reply | Quote