I'm having some trouble getting started. Here is what I'm trying to accomplish:
Allow users to upload multiple attachments (most likely photos) via an Access form.
Display a thumbnail on the form (for each attachment if possible).
Separately store the attachments in a folder hosted on our server, instead of Access. (The same server hosts the access database). I should note that this database will soon be converted to MSSQL.
The form actually a continuous form based on a query of a single table. Once the user saves the current record, the form is re-queried and displays a listing of all records in the table.
I also need thumbnails to be displayed here as well.
With Access, the most efficient way to store pictures (and for SQL Server as a remote back end, this is even more true) is to store the fully qualified file spec to the picture file, which will of course be expressed as a text string. Let Access worry about the graphics in the front end file.
For each image control on a form or report, there is a .Picture property. To put a picture in the image control, load that device:/path/name.type string (or the UNC version, \\server\path...path\name.type) to .Picture and do a form .Refresh to get it displayed.
For the upload you mentioned, you can create some VBA code that would be launched by a button-click. You can create a File System Object and ask it to trigger a file-picker dialog.
Once you have an FSO declared, you pick the file using the picker and then can use some simple string parsing to split out the path from the file name and type. Some of the methods of the FSO will do part or all of this parsing for you. (Hint: Do a Web lookup for File System Object and check out the stuff from the MSDN site.)
Next you generate the fully qualified file spec for the server folder based on the name you just parsed and the path to that hosted folder. In either case, the .Connect string for the back-end tables will contain the information about the host server.
Having both the original spec (from the picker) and the new spec (formed by some parsing) in string variables, you can easily use the FSO to launch a .FileCopy method to make a copy of the local file to the shared folder on your server.
Finally, you can store the new file spec as text in your DB.
Now, for this to be set up correctly in a form, you need the form to be bound and you can then have the FormCurrent routine load up whatever it needs to load up.
Note that you might have to look into condition formatting issues when using a continuous form. I have not tried something like you are trying so you might need to research that. Search this forum for "conditional formatting" as a way to find discussions on the method you would need to use. If I recall correctly, if you DON'T pay attention to the conditional formatting issues, then the last image you load is the only image you see because all continuous forms will use the last picture. But there are ways around that result, I just have not tried them because I never needed to do so.
if you DON'T pay attention to the conditional formatting issues, then the last image you load is the only image you see because all continuous forms will use the last picture. But there are ways around that result, I just have not tried them because I never needed to do so.
I believe thats with unbound controls on a continuous form. As long as the image control is bound to a field in the forms record set it should display correctly.
As I had never tried this, I gave a caveat on the subject of images in continuous forms. Thanks for clarification, moke! But I also have never tried to bind an image control to an underlying record. I usually took special action in the OnCurrent event.
For each image control on a form or report, there is a .Picture property. To put a picture in the image control, load that device:/path/name.type string (or the UNC version, \\server\path...path\name.type) to .Picture and do a form .Refresh to get it displayed.
For the upload you mentioned, you can create some VBA code that would be launched by a button-click. You can create a File System Object and ask it to trigger a file-picker dialog.
So far, I've added an image control to the form and set its Control Source as "Attachment1", a field in my table. I've searched for VBA code to allow a user to upload an attachment on click, but I don't believe I'm comprehending which is the correct one. i.e., I don't see the correlation on any that will let me choose the user to upload a file (picture/document) to a specified folder on the server. Furthermore, is there a way to auto-rename this file from the original name to prevent duplicates? Perhaps timestamp or add the record auto-number field?
Thanks for the attachments. I actually couldn't figure out how to apply them to my database, but I did find a youtube video that did mostly what I was trying to accomplish at https://youtu.be/pPpdKRUc21M.
At any rate, now I can get the image to populate the thumbnail and the original file path in the textbook beneath. I'm also able to copy this file to a destination path, but this is where I can use you all's help again..
1. I tried to DIM and SET my destination share path, but it errors out. So I just hardcoded it in the code (see code below).
2. When the file is copied, I changed the filename to equals "record ID + File1", but I need a way to append the original file extension.
3. I need to ensure the new file path and file name are copied back to the table for archival/reporting purposes. (Field name is File1 and set to Short Text.)
Here is my code so far.
Code:
Private Sub cmdBrowse1_Click()
Dim f As Object
Dim strfile As String
Dim sourceDir As String
Dim varItem As Variant
'Dim destDir As String
'Set destDir = "C:\apblotter\attachments\" '(This did not work)
Set f = Application.FileDialog(3)
f.allowMultiSelect = True
If f.show Then
For Each varItem In f.selectedItems
strfile = Dir(varItem)
sourceDir = Left(varItem, Len(varItem) - Len(strfile))
' MsgBox "Folder" & sourceDir & vbCrLf & _
' "File: " & strfile
FilePath1 = sourceDir + strfile
FileCopy Me.FilePath1.Value, "C:\apblotter\attachments\ & Me.ID & File1"
Next
End If
Set f = Nothing
End Sub
I'm now able to copy files from the source to destination on a file share B:\Attachments. Files are renamed successfully and include the file extension.
The remaining items are as follows:
1. I'm trying to store the new location and filename in the table for future reference in a field called "FilePath1". For some reason, the original location and filename are populating in the database table, instead of the new. I'm using "newFile1" to store this variable.
2. When I click save, information for "FilePath1" is populating in the previous record. I had to move my original code above the "Work with Attachments" section to get most working for the current record.
3. I can't seem to get a thumbnail of the image to populate the "cmdBrowse1," having the On Click event for "Browse for File." I'm using an Image Object with a pre-set picture as this button.
4. Lastly, I'm sure there's a better way to code this; in the end, I will have 4 separate cmdBrowse buttons (cmdBrowse1, cmdBrowse2, cmdBrowse3, cmdBrowse4), all doing the same function, allowing for 4 separate attachments with preview. I'm open for any suggestions to clean this up if needed. Thanks!
Here is my code, which contains some other functions based on the the form:
Code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Browse for File
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmdBrowse1_Click()
Dim f As Object
Dim strfile1 As String
Dim sourceDir1 As String
Dim varItem1 As Variant
' Open Folder Picker
Set f = Application.FileDialog(3)
f.allowMultiSelect = False
If f.show Then
For Each varItem1 In f.selectedItems
strfile1 = Dir(varItem1)
' Get source from selected file and set to sourceDir1
sourceDir1 = Left(varItem1, Len(varItem1) - Len(strfile1))
' Set filePath1 to source folder & file name
FilePath1 = sourceDir1 + strfile1
Next
End If
Set f = Nothing
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Save Button
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub btnSave_Click()
Dim db As Database
Dim rec As Recordset
Dim copyPath1 As String
Dim dotInpath1 As Integer
Dim fileExt1 As String
Dim extPath1 As String
Dim newFile1 As String
Dim fso1 As New Scripting.FileSystemObject
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from tblBlotter")
'test required fields
If IsNull(Me.txtEntryDate) _
Or IsNull(Me.txtEntryTime) _
Or IsNull(Me.opOType.Value) _
Or IsNull(Me.cboBadge.Value) _
Or IsNull(Me.txtIncidentDate.Value) _
Or IsNull(Me.cboLocation.Value) _
Or IsNull(Me.txtEmpID.Value) _
Or IsNull(Me.txtIncidentTime.Value) Then
MsgBox "Please fill in all required fields."
Else
'add records to table
rec.AddNew
rec("EntryDate") = Me.txtEntryDate
rec("EntryTime") = Me.txtEntryTime
rec("BadgeNum") = Me.cboBadge.Value
rec("EmpID") = Me.txtEmpID.Value
rec("IncidentDate") = Me.txtIncidentDate.Value
rec("IncidentTime") = Me.txtIncidentTime.Value
rec("Location") = Me.cboLocation.Value
rec("Response") = Me.txtResponse
rec("OType") = Me.opOType.Value
'Work with Attachments
' Set destDir to Attachments File Share
destDir = "B:\Attachments\"
' Set copyPath1 to new subfolder record ID variable at destination
copyPath1 = destDir & Me.ID & "\"
' Create Subfolder Record ID (specified above)
fso1.CreateFolder (destDir & Me.ID)
' Get file extension and set to "fileExt1"
extPath1 = Me.FilePath1.Value
dotInpath1 = InStrRev(extPath1, ".")
fileExt1 = Right(extPath1, Len(extPath1) - dotInpath1)
' Copy file from original file path1 to new subfolder path1 on B:\Attachments\%RecordID%\%FileName%
FileCopy Me.FilePath1.Value, copyPath1 & Me.ID & "_1." & fileExt1
' Set new file path and name to "newfile1"
newFile1 = copyPath1 & Me.ID & "_1." & fileExt1
'add records to table
rec("FilePath1") = newFile1
rec("FilePath2") = newFile2
rec("FilePath3") = newFile3
rec("FilePath4") = newFile4
rec.Update
MsgBox "Record saved."
'Requery
Me.Requery
'DoCmd.SetOrderBy "[ID]"
'Me.OrderByOn = True
'Me.OrderBy = "[EntryDate], [EntryTime] DESC"
'Me.Refresh
' Clear All Controls
Me.txtEntryTime = Now()
Me.EntryDate = Date
Me.txtIncidentDate = Null
Me.txtIncidentTime = Null
Me.cboBadge.Value = Null
Me.cboLocation.Value = Null
Me.opOType = Null
Me.txtEmpID = ""
Me.txtResponse = ""
Me.FilePath1 = ""
Me.FilePath2 = ""
Me.FilePath3 = ""
Me.FilePath4 = ""
End If
End Sub
While this is still true for the JET/ACE ( mdb/accdb) database formats, this is no longer true for all SQL and NoSQL database Servers. They are getting very efficient as storing files.
Note: If you are using the free version of some SQL Servers there is a size limit to the database.
I have done some testing with some SQL Servers. I was surprised to see how storing files in an SQL Server actually took less disk space and improved performance.
I have started storing a lot more stuff inside SQL Server databases.