Use Access to search for files in a network location

[dawnpgeo]

Here it is. I've made some adjustments.

Changing my field name from "PartNumber" to "PartNum" removed one error. Maybe the field name was too long. ??

It still has an issue with the FileExists function for some reason. Based on my research there should be something before FileExists called FileSystemObject. ??

Thanks so much,
Dawn

[Robeen]

I have to apologize again.

I forgot to give you this function that you will need to make this work.
Please accept my apologies for a second omission on my part!!
Just copy & paste this function in under your existing code.

Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    '***Not being used in production.***
    
    Dim lngAttributes As Long
    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If
    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

My 'On Current' Event code is as follows:

Private Sub Form_Current()
Dim strPartNumber As String, strPath As String, strFile As String, strPathFile As String

strPath = "C:\"

Me.PartNumber.SetFocus
strPartNumber = Me.PartNumber.Text           'This assumes your Part Number field is named 'PartNumber'.
strFile = strPartNumber & ".txt"               'Put the correct file extension in where I put ".txt"
strPathFile = strPath & strFile

'MsgBox strPathFile                              'I just put this in to see if I was getting the correct path & file name.

If FileExists(strPathFile) Then
Me.HasFile.SetFocus                               'This assumes that your check box on the Form is named 'HasFile'
    Me.HasFile.Value = True
Else
Me.HasFile.SetFocus                              
    Me.HasFile.Value = False
End If

End Sub

Let me know if this gets you closer!!
Again - sorry I didn't run the code before I gave it to you!!

[Robeen]

I just put my changes into the DB you posted and ran your Form.
It is working fine now.
I think if you just put that FileExists() function in place - it will work fine now.

Again, my apologies!!

[dawnpgeo]

That DEFINITELY is closer. If I click a PartNum field that has a matching file it will then check the box. That works for new records.

So, how can I get it to go thru existing PartNum data and check it.

Thanks again for your help.

Dawn

[Robeen]

When I opened your Form, I realized that it is in Datasheet Mode & I changed it to Single Form mode [in the Property Sheet -> Format Tab].
That way - whenever I switch to a different existing record - Access automatically checks or unchecks the checkbox - depending on whether a matching file is found.

So - now that we've got that working, what do you need next?

[dawnpgeo]

My file names are not an identical match. Can you use wildcards? For example, my part number might be 827-5643-001 and my drawing is saved as 827-5643.

How can we handle that?

Thanks so much,
Dawn

[Robeen]

Do your Drawing file names have a standard naming convention?
For example, are they always going to be a certain number of characters long [827-5643 - is 8 characters]?
Are those 8 characters ALWAYS going to match the first 8 characters of the Part Numbers?

In order for VBA logic to be dependable, you have to start with SOMETHING that is consistent and predictable in the File Name/Part Number match-up.
When you say 'might be', it becomes difficult to program for that because how are you going to pin it down to specific code?

So - can you narrow it down more for me?
What I need to know is the EXACT correlation between Part Number and File Name.

If it is possible for me [someone not familiar to your system] to follow written instructions to find out if a Part Number has a Drawing - then we can probably write Code for the process.
But you'll have to give me those details.

[dawnpgeo]

Our part numbers are mostly XXX-XXXX.pdf format. We also have XXXXXX.pdf and XX-XXXXXX.pdf formats. I hope that explains it. I would say that 75% of them are XXX-XXXX.pdf.

[Robeen]

My first advice is to standardize the names of the Drawing Files.
It really should follow a consistent pattern.

But I know we don't always have control over that - so . . .

So - your PartNumbers are always XXX-XXXX - right?
But your file names can be:
XXX-XXXX
or
XXXXXX.pdf
or
XX-XXXXXX.pdf
Right?

When it is XXXXXX.pdf, how do those numbers relate to the PartNumber?
Is it the first 6 digits of the PartNumber - with the '-' removed?

And what about when it is XX-XXXXXX.pdf?

You will have to create a different version of the PartNumber for each possible variation of your file names.
It will be a little on the tricky side for you if you have not done string manipulation before in code.
BUT . . . as long as you know each possible variation of the Drawing file name - and how the PartNumber relates to it - it is do-able.

What you'll have to do is pass the FileExists() function each possible variation of the File Name that comes from your PartNumber field on the Form.

If you will tell me what you need to do to the PartNumber for each variation of the Drawing file name - we will get this figured out.
You can also Google MS Access String functions as well - [looking for functions like Len(), Left(), Mid() . . . ] to see how you can 'massage' your PartNumber into the format you need to search for the Drawing file.