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:
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
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