Help with checking if files exist (1 Viewer)

alonzo

New member
Local time
Today, 05:23
Joined
Jun 7, 2016
Messages
4
The database I am working in houses various information related to the company's manufacturing processes. My current project is dealing with cutting tools related to machining operations. Each tool has a unique tool built sheet, housed on our network. I would like to determine which tools do not have a build sheet. I have no idea how to go about this.

The database contains a table, listing each tool and other information about such. Each tool has a unique name, such as "CG4023" or "MS8260". The tool build sheets are PDFs and have the same name. It is not uncommon for new tools to be created. Also, some of the tools listed are no longer used. There is already a reporting system in place, relaying when tools are no longer needed, and can be removed from the plant floor.

Once this check is working, I would like to create a report that can be run to show which tools still need a build sheet. If possible, I would like to only look at the tools that are currently being used. I do have a query for this already. This report would need to be able to run often, as the active tool list is constantly changing. I'm just not sure how to go about checking if the files are there.

Any advice on this would be greatly appreciated. I will do my best to provide any other information that is needed. If its of use to know, I did not build this database, but am now the only person who changes / maintains it. However, other people do change information through forms.

Thanks!
 

alonzo

New member
Local time
Today, 05:23
Joined
Jun 7, 2016
Messages
4
This is helpful. However, I'm not sure how to cycle through and check all the full list. Also, where would it be best to store the result? Would it make sense to add a field to the table for whether or not the tool has a build sheet?
 

GohDiamond

"Access- Imagineer that!"
Local time
Today, 05:23
Joined
Nov 1, 2006
Messages
550
Make a Table with 1 field to capture File Names
For example: tempFileNames (Field: BuildSheet)

Put these pieces of code in a module. IsBlank() is good for lots of things.

Code:
Function checkFilesIn(FolderPath)
Dim FileName As String, strSQL As String

[COLOR="Green"]' Pick the First FileName in the chosen Folder[/COLOR]
    FileName = Dir(FolderPath)
[COLOR="green"]' Loop thru the FOLDER addressing each filename in order from First to EOF[/COLOR]
    While Not isBlank(FileName)
    [COLOR="green"]' Put the Existing BuildSheet Name into a temporary table[/COLOR]
    strSQL = "INSERT INTO tempFileNames (BuildSheet) VALUES('" & FileName & "');"
    CurrentDb.Execute strSQL

    [COLOR="Green"]' Get the next file[/COLOR]
        FileName = Dir()
    Wend

End Function


Code:
[COLOR="Green"]'PUBLIC FUNCTIONS
'http://stackoverflow.com/questions/1392917/proper-way-to-check-if-an-unbound-control-has-a-value
'-----------------------------------------------------------------------------'
' True if the argument is Nothing, Null, Empty, Missing or an empty string.   '
'-----------------------------------------------------------------------------'[/COLOR]
Public Function isBlank(arg As Variant) As Boolean
    Select Case VarType(arg)
        Case vbEmpty
            isBlank = True
        Case vbNull
            isBlank = True
        Case vbString
            isBlank = (arg = vbNullString)
        Case vbObject
            isBlank = (arg Is Nothing)
        Case Else
            isBlank = IsMissing(arg)
        End Select
End Function
All the names of all the files in that folder will be in the tempFileNames table.

Then compare the Temporary List of BuildSheet Files found with the active Tools list from your query where BuildSheet is Null.

Hope that helps,
Cheers!
Goh
 

moke123

AWF VIP
Local time
Today, 05:23
Joined
Jan 11, 2013
Messages
3,852
you didnt say what your directory structure is. are all the files in one directory? a master directory with subfolders?
 

Simon_MT

Registered User.
Local time
Today, 09:23
Joined
Feb 26, 2007
Messages
2,177
I simply use DIR as I know where to find the files and filename is associated with an ID. I then have a flag, in my case for an image, and set it to True if found or False if not.

Simon
 

alonzo

New member
Local time
Today, 05:23
Joined
Jun 7, 2016
Messages
4
Thank you all for your help! I was able to get this to work as needed using the advice from Goh.
 

Users who are viewing this thread

Top Bottom