Hello Guys,
I have a form with several different controls in it. Right now, I am trying to get a command button to allow the user to:
open a dialog box
choose a file
and then edit my recordset to add the new filepath to a field.
The thing is at the moment, nothing is adding. Can you take a look at this code and tell me what I am doing wrong?
Thank you
' Add a new certificate filepath
Private Sub cmdViewCert_Click()
Dim objCert As Object
Dim strFile As String
Dim strFolder As String
Dim strDoc As String
Dim varItem As Variant
Dim db As DAO.Database
Dim rs As Recordset
'Open my Recordset and make it editable
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_EngineerLic", dbOpenDynaset)
Set objCert = Application.FileDialog(3) 'Open the dialog box to choose the certificate
objCert.allowMultiSelect = True 'Allows the user to select multiple file from the file dialog box.
'Create my object
If objCert.Show Then
For Each varItem In objCert.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder" & strFolder & vbCrLf & "File: " & strFile
strDoc = strFolder + strFile
' If the opened Cert field is equal to the one selected in the listBox
'then the value of that field = to the new filepath
If rs.Fields("Cert") = Me.ListBoxStateLic.Column(3) Then
rs.Edit
rs.Fields("Cert") = strDoc
rs.AddNew
rs.Update
End If
Next
End If
rs.Close
Set objCert = Nothing
Set rs = Nothing
db.Close
End Sub
I have a form with several different controls in it. Right now, I am trying to get a command button to allow the user to:
open a dialog box
choose a file
and then edit my recordset to add the new filepath to a field.
The thing is at the moment, nothing is adding. Can you take a look at this code and tell me what I am doing wrong?
Thank you
' Add a new certificate filepath
Private Sub cmdViewCert_Click()
Dim objCert As Object
Dim strFile As String
Dim strFolder As String
Dim strDoc As String
Dim varItem As Variant
Dim db As DAO.Database
Dim rs As Recordset
'Open my Recordset and make it editable
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_EngineerLic", dbOpenDynaset)
Set objCert = Application.FileDialog(3) 'Open the dialog box to choose the certificate
objCert.allowMultiSelect = True 'Allows the user to select multiple file from the file dialog box.
'Create my object
If objCert.Show Then
For Each varItem In objCert.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder" & strFolder & vbCrLf & "File: " & strFile
strDoc = strFolder + strFile
' If the opened Cert field is equal to the one selected in the listBox
'then the value of that field = to the new filepath
If rs.Fields("Cert") = Me.ListBoxStateLic.Column(3) Then
rs.Edit
rs.Fields("Cert") = strDoc
rs.AddNew
rs.Update
End If
Next
End If
rs.Close
Set objCert = Nothing
Set rs = Nothing
db.Close
End Sub