Attribute VB_Name = "Search for File"

Option Compare Database

'The purpose of this module is to search for scanned pdfs inside their variable

'folders and store any not found, or naming conflicts, and output the details for

'manual solutions

'Call SearchForFile





Sub SearchForFile()

Dim fso

Dim file As String ' This will store the file path

Dim i As Variant ' i will be used to regulate the for loop

Dim LastBox As Integer 'this will control when the for loop stops

i = InputBox("First Box") 'setting i to first box

LastBox = InputBox("Last Box") 'set LastBox to the final box

Dim RecordTrack As Long 'this will store a variable to track which record we are on

RecordTrack = 1 'RecordTrack will start at 1

Set fso = CreateObject("Scripting.FileSystemObject")

Dim NumberOfFilesMissing As Integer

NumberOfFilesMissing = 0

Dim x As Integer

Dim LoopCount As Integer





While i <= LastBox 

    LoopCount = 1

    Dim RecordPerBox As Integer

    

    RecordPerBox = DCount("[ID]", "TestFileFinder4", "[Box #] =" & i)

    Dim FileNameArray() As Variant 'create array to store fileNames

    Dim FileNotFoundArray() As Variant

        While LoopCount <= RecordPerBox

            ReDim Preserve FileNameArray(1 To RecordTrack)

            FileNameArray(RecordTrack) = DLookup("[Expr1]", "TestFileFinder4", "[Box #] =" & i & "And [ID] =" & RecordTrack) 

            file = "\\Path\to\file\PDFs\Project" & " " & i & "\" & FileNameArray(RecordTrack) & ".pdf"

            

            If Not fso.FileExists(file) Then

                NumberOfFilesMissing = NumberOfFilesMissing + 1

                If (NumberOfFilesMissing >= 1) Then

                    ReDim Preserve FileNotFoundArray(1 To NumberOfFilesMissing)

                    'MsgBox file & " was not found.", vbInformation, "File Not Found"

                    FileNotFoundArray(NumberOfFilesMissing) = file

                End If

            Else

                'MsgBox "Headshot! Tango Down!"

            End If

            RecordTrack = RecordTrack + 1

            LoopCount = LoopCount + 1

        Wend

    i = i + 1

Wend

If NumberOfFilesMissing = 0 Then

    MsgBox "All Files Were Found."

Else

    x = 1

    For x = 1 To UBound(FileNotFoundArray)

        strNF = strNF & vbCrLf & FileNotFoundArray(x)

    Next

        MsgBox "Files Not Found:" & vbCrLf & strNF

End If

End Sub

The code as promised. A bit late but hey. I could use an alternative to MsgBox here:

Else

    x = 1

    For x = 1 To UBound(FileNotFoundArray)

        strNF = strNF & vbCrLf & FileNotFoundArray(x)

    Next

        MsgBox "Files Not Found:" & vbCrLf & strNF

To avoid the 250 character limit on the off chance more than ~20 files are found missing. Also wouldn't mind if it displayed the number of records checked. Or any file found in the directory but not in the database.

Ok. I got that running great. I will post my solution tomorrow for anyone else who wants it. I did have and issue while migrating the final product from a test database to our working database. Several of my queries changed and the vba code was a little off. I put it all right but I was wondering if Access references the current table/db/query etc in ways other than by name. My working database is an exact mirror of the test db as far as structure. And example:
SELECT * FROM Sheet1 INTO NewTable

Became

SELECT [*] AS Expr1 FROM [Sheet1] INTO NewTable

[code].DatabaseName = "\\BOK-PC\Z:\Public\database\login.mdb"[\code]
Take the colon out after Z. And POSSIBLY make it lower case.
If that doesnt work remove z altogether and use:

\\BOK-PC\Public\database\login.mdb

Without more information I can only shout suggestions...

I really cant.. as I dont know if you're trying to install this from the server or a workstation. And I dont know your server name or how the network is configured. But I can try. Right click on the database folder you would like to save your file into. Click on the "Proerties" option at the bottom. There should be something that says path or location. Copy and paste that here.

This link is a great resource for Perl beginners and the introduction answers your question thoroughly. Though the above post pretty much covered it.

http://www.perl.org/...beginning-perl/