Check for Existence of Files Prior to Populating a Table (1 Viewer)

Wayne

Crazy Canuck
Local time
Today, 06:02
Joined
Nov 4, 2012
Messages
176
I posted this earlier but got no response. Can anyone help point me in the right direction please?

I used to save pictures related to a job as an attachment in an attachment field. My database grew in size dramatically, so I set up a bound subform on my main Orders form to save each picture as a hyperlink. The form and subform are linked by the field "OrderNumber". The pictures for each order are saved in a subdirectory unique to that order number.

At first, I had to add each picture individually to the subform, but for large orders, that took time, as there can be anywhere from 1 to 50 pictures for each order. I used the following code to get all of the pictures at once and populate the table/subform. And it works well. However, if you click the button again, it adds them all again, and again - as many times as you click it. This can cause problems with other users. What I am looking for is way to first check if they exist in the table already, and if so, not to add it again. This is the code I am using to add the pictures:

Code:
Private Sub btnGetPictures_Click()

    On Error GoTo Err_btnGetPictures_Click

    Dim folderspec As String
    Dim fs As Object
    Dim f As Object
    Dim f1 As Object
    Dim fc As Object
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("tblJobPix")
    
    folderspec = "C:\FilePath\Job Pix\" & [Forms]![frmOrders]![CustNumber] & "\" & [Forms]![frmOrders]![OrderNumber] & " " & [Forms]![frmOrders]![ClientUserlastName]
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    
    For Each f1 In fc
        rs.AddNew
        rs.Fields("OrderNumber") = [Forms]![frmOrders]![OrderNumber]
        rs.Fields("ImageFilePath") = (folderspec & "\" & f1.Name)
        rs.Update
    Next
    Me.Requery
    
    Set rs = Nothing
    
Exit_btnGetPictures_Click:
    Exit Sub
    
Err_btnGetPictures_Click:
    MsgBox Err.Description, vbInformation, "Attention"
    Resume Exit_btnGetPictures_Click
    
End Sub


Should I be using an If Len statement first to check the existence of the file name first? And I am not sure how to write it. Any help here would be appreciated.

Wayne
 

Cronk

Registered User.
Local time
Today, 21:02
Joined
Jul 4, 2013
Messages
2,770
You could check if the record exists already in your recordset with
Code:
rs.findfirst "ImageFilePath" = chr(34) & folderspec & "\" & f1.Name & chr(34)
if rs.nomatch then
  <add your record code>

endif


But then again it would be easier just to create a unique index on the field. That way you could never have multiple copies through code or manual entry.
 

MarkK

bit cruncher
Local time
Today, 03:02
Joined
Mar 17, 2004
Messages
8,178
Here's what I would do, I would never save that filename data in a table, I would just show the user the current files in the folder as required, on demand. No need to synch, no need to error check, always up to minute. Just push those filenames as they exist right now into a listbox like....
Code:
Private Sub Command2_Click()
    Const PATH As String = "C:\YourFolder\"

    Dim f As Object
    
    Me.List0.RowSourceType = "Value List"
    With CreateObject("Scripting.FileSystemObject")
        With .GetFolder(PATH)
            For Each f In .Files
                Me.List0.AddItem PATH & f.Name
            Next
        End With
    End With

End Sub
IMO,
Mark
 

Wayne

Crazy Canuck
Local time
Today, 06:02
Joined
Nov 4, 2012
Messages
176
Thanks for the ideas. So Mark, you are saying to just populate a drop-down list with whatever is in the directory for that order number, and not to save the name in a table. If I do that, could I do it as a hyperlink that will open the picture that is selected from the drop-down list or listbox?

Wayne
 

MarkK

bit cruncher
Local time
Today, 03:02
Joined
Mar 17, 2004
Messages
8,178
So Mark, you are saying to just populate a drop-down list with whatever is in the directory for that order number, and not to save the name in a table.
Yes, exactly. If you save to a table, then every time, to be sure, you have to check if the table is accurate, or already saved, or whatever. Skip all that. Just read the files that are in that folder RIGHT NOW, and show those in a list for the user to pick from. Immediate. Always in synch. Always up to the minute accurate.

If I do that, could I do it as a hyperlink that will open the picture that is selected from the drop-down list or listbox?
I don't see why not, but I wouldn't. I would be more likely to handle the click event on the filename, either in a list or a combo, and use the VBA.Shell() function to open the file that was selected. I think that opens in whatever program is registered with windows to open that file type. Windows filenames can contain characters that kill hyperlinks, so I think hyperlinks are fragile to use on Windows file system objects. Your call though.

hth,
Mark
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:02
Joined
May 7, 2009
Messages
19,175
If you dont want duplicate imagefilepath, create an index with no duplicate on OrderNumber + ImageFilePath.
Use Sql instead of recordset to insert record:

Currentdb.Execute "Insert intotblJobPix (OrderNumber, ImageFilePath) Select " & Chr(34) & [Forms]![frmOrders]![OrderNumber] & Chr(34) & "," & Chr(34) & (folderspec & "" & f1.Name) & Chr(34)
 

MarkK

bit cruncher
Local time
Today, 03:02
Joined
Mar 17, 2004
Messages
8,178
arnelgp, maybe you didn't read the thread? You seem to be suggesting how to make a bad idea work better. Am I mistaken?
For what the OP is doing, what is the advantage in saving this data to a table?
Mark
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:02
Joined
May 7, 2009
Messages
19,175
The idea is that the table is portable with all the image ref intact. You can open the table without a form. On report for the table you dont need any code just to bring the image.
 

MarkK

bit cruncher
Local time
Today, 03:02
Joined
Mar 17, 2004
Messages
8,178
My understanding, and I could be wrong, was that the OP needs a selection tool for files in a folder for the user to interact with. If so, the simplest way to make that list always-accurate is to create it directly from the files in the folder, and to not use a table.
Perhaps I have misunderstood the OPs needs.
Mark
 

Wayne

Crazy Canuck
Local time
Today, 06:02
Joined
Nov 4, 2012
Messages
176
Thanks Arnel and Mark. For the short term, I used Cronk's idea for the Index with no duplicates allowed, and it solved that problem. But I do like Mark's idea for the list box, as it would always be current with what's in the picture directory. I can use code to view the pictures on an unbound form, or in the native app for images. Still working on that to see if I can make it all work. That's the part I like - trying make new ideas work. Thanks again for the ideas.
 

Users who are viewing this thread

Top Bottom