Sub Folder search at the click of a button

[mford]

Hi all, This is my first post on here so please be gentle

I am fairly new to Access and VBA, i have dabbled in the past years but recently diving into this.

I have written some code in a form to search for files in a specific location and display in box which works. What i dont know how to do is to make it search sub folders. Can anyone point me in the right direction?

Many Thanks

 

[jdraw]

Can you provide a sample, even if you make it up, to show us what you want?
Sample folder/subfolder; your code; expected result...

 

[mford]

What i have is a button that will automatically seach for an .xls file with a specific number from a text box 'SerialNumber' and display the reuslt in the seperate box 'results_dsiplay'.

currently i have only been able to specifically seach in a specified folder which i input in the form 'Month' & 'Year'

\\Server1\Results is my location to search which has sub folders of \year and \month

---------------------------------------

Private Sub resultsSEARCH_Click()

Dim i As Integer

Dim Coll_Docs As New Collection
Dim sMonth, sYear, Search_path, Search_Filter, Search_Fullname As String
Dim DocName As String

Search_path = "\\Server1\Results" ' where ?
Search_Filter = SerialNumber ' what ?
sMonth = Month
sYear = Year
Set Coll_Docs = Nothing

DocName = Dir(Search_path & "" & sYear & "" & sMonth & "" & Search_Filter & " *.xls")

Do Until DocName = "" ' build the collection
Coll_Docs.Add Item:=DocName
DocName = Dir
Loop
ATE_Results_Display = ""
If Coll_Docs.Count > 0 Then
'Tell user how many
ATE_Results_Display = "There were " & Coll_Docs.Count & _
" file(s) found." & vbCrLf & vbCrLf
'Load File Names into a ReDimensioning Array for every File Found
ReDim strFileNames(1 To Coll_Docs.Count)
'Tell User their Files Names
For i = 1 To Coll_Docs.Count
strFileNames(i) = Coll_Docs(i)
'Call Shell("explorer.exe ", Search_path, strFileNames, vbNormalFocus)

Results_Display = Results_Display & strFileNames(i) & vbCrLf
Next i
Else
'If no Files found, tell user
MsgBox "There were no files found."
End If

End Sub

-------------------------------------------

hope this makes sense

 

[jdraw]

I found this on the site
https://access-programmers.co.uk/forums/showpost.php?p=1074916&postcount=18

Also, this does not do what you may think

Dim sMonth, sYear, Search_path, Search_Filter, Search_Fullname As String

Access will default to variant type unless you explicitly dim a variable.
So in the statement above
sMonth, sYear, Search_path, Search_Filter will all be variants.

If you use

Dim sMonth as string, sYear as string, Search_path as string, Search_Filter as string, Search_Fullname As String

, then all will be string data types. You can also use Dim with individual statements eg
Dim sMonth as String
Dim sYear as String etc.


Hope it's helpful.