Upload files to a DB table using a button on a form

[Pat Hartman]

Don't use the multi-select option. Just log one at a time.

 

[A380b747]

Why are you running a query to update the location of the file?

To update the table Gc_Eventos in which I am updating the field "Cont" and placing the location of the file there. But it doesn´t work anyway. I don´t know why is the cause of the error??

 

[A380b747]

Don't use the multi-select option. Just log one at a time.

Do I need to set it as FALSE or remove it from the code. If i removed it, would i need to remove or modificate the code reltated to "varItem"??

Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant

Set f = Application.FileDialog(3)
f.AllowMultiSelect = True (REMOVE??))
If f.Show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)

strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder" & strFolder & vbCLf & _
"File:" & strFile

txtLocation = strFolder + strFile

strSql7 = "UPDATE GC_Eventos SET Cont = " & Me.txtLocation & " WHERE Evento_ID = " & Me.Evento_ID & ""
Debug.Print strSql7
CurrentDb.Execute strSql7

Next
End If

Set f = Nothing

 

[Pat Hartman]

It is extremely hard to read code that is not formatted. Please use the code tool when uploading code.

Your logic is garbled. If you are selecting multiple files, you need to use an append query to add a new row for each file. If you only select one at a time, you don't need a query at all. You just put the path into the bound text field of your subform.

 

[A380b747]

It is extremely hard to read code that is not formatted. Please use the code tool when uploading code.

Your logic is garbled. If you are selecting multiple files, you need to use an append query to add a new row for each file. If you only select one at a time, you don't need a query at all. You just put the path into the bound text field of your subform.

I don´t want to add a new row since the row has already been created before. Just want to update the table with the new data using UPDATE command. Which part do i need to remove?

Private Sub Command870_Click()
Dim strSql7 As String

Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant

Set f = Application.FileDialog(3)
'f.AllowMultiSelect = True
If f.Show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)

strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder" & strFolder & vbCLf & _
   "File:" & strFile
 
txtLocation = strFolder + strFile

strSql7 = "UPDATE GC_Eventos SET Cont = " & Me.txtLocation & " WHERE Evento_ID = " & Me.Evento_ID & ""
          Debug.Print strSql7
          CurrentDb.Execute strSql7
 
Next
End If

Set f = Nothing
End Sub

 

[LarryE]

I was not aware of where these files were stored in the first place. If you want to store the location of the selected file, then just replace:
Application.FollowHyperlink SelectedFile
with
Me.txtLocation=SelectedFile

So you will need two VBA routines. One to locate and store the file location and the other to locate and open the file for viewing.

 

[Pat Hartman]

You can't store multiple links to documents in a single field. Each document link MUST be a separate row in a table.

tblDocuments:
DocID (autonumber PK)
PersonID (FK to the person the document relates to)
FileName
LoggedDT
ReviewedDT
....

use a subform to hold the links. Here's a form from one of my apps.

 

[Pat Hartman]

Thanks for the like @A380b747 but that doesn't help anyone who finds this thread later. If my solution helped you, please use your words and tell people how you implemented it.

 

[A380b747]

Thanks for the like @A380b747 but that doesn't help anyone who finds this thread later. If my solution helped you, please use your words and tell people how you implemented it.

Good news!! I could finally save the file location in the table!! Thanks for your help Pat! That step is done.

I could also manage to store the table on SharePoint instead of Ms Access, Access is only the front-end section of the program but the table is stored in other place.

Now, I´m trying to use this code to attach the file (not location) in the table but it is not working. Something is missing??' Could you help me with this??


field name: Contrato
Table name: GC_Eventos
Filepath / location= txtLocation

Dim db As Database
Dim rst_PDF As Recordset
Dim rst_Attachment As Recordset
Dim fld_att As Field

    Set db = CurrentDb
    Set rst_PDF = db.OpenRecordset("Select Contrato FROM GC_Eventos WHERE Evento_ID = Me.Evento_ID")
    Set fld_att = rst_PDF("Attachment")
    rst_PDF.MoveFirst

    Set rst_Attachment = fld_att.Value

    rst_PDF.Edit
    rst_Attachment.AddNew
    rst_Attachment("FileData").LoadFromFile rst_PDF!(Me.txtLocation)
    rst_Attachment.Update
    rst_PDF.Update
    rst_PDF.MoveNext

    rst_Facturas.Close
    db.Close

    Set fld_att = Nothing
    Set rst_Attachment = Nothing
    Set rst_PDF = Nothing
    Set db = Nothing

 

[Pat Hartman]

We've all told you to not store the file inside the database. If you want help, you are at least going to tell us what the error you are getting is.

 

[A380b747]

We've all told you to not store the file inside the database. If you want help, you are at least going to tell us what the error you are getting is.

I know and I agree with everyone here but the person who is responsible of this program wants to store the file inside the database using the code I have shared. That is what I´m trying to do.

Here is the error that comes out: Perhaps something is missing...

 

[strive4peace]

delete rst_PDF! -- you only need Me.txtLocation if that contains the full path and filename