Hi,
First of all I am really grateful for this forum as it is helping me a lot. I have this code, where I am trying to get a path from a button - publications and save the path to a table. The path is clearly read out well as it is being outputted when the button publications is clicked, however when the button save is clicked, somehow the filePath variable is blank. Any ideas why? I declared filePath as a global variable
Code:
Option Compare DatabaseOption Explicit
Public filePath As String
Private Sub btnSave_Click()
Dim refNo As String
Dim Volume As Integer
Dim dateStart As Date
Dim dateEnd As Date
Dim condition As String
Dim shelf As String
Dim publication As String
Dim sql As String
If (IsNull(Me.cmbNotary.Column(0)) Or IsNull(Me.txtVolume) Or IsNull(Me.txtDateStart) Or IsNull(Me.txtDateEnd) Or IsNull(Me.cmbShelf)) Then
MsgBox "Fields cannot be left blank"
Else
refNo = cmbNotary.Column(0)
Volume = Me.txtVolume
dateStart = Me.txtDateStart
dateEnd = Me.txtDateEnd
If (Not IsNull(Me.txtCondition)) Then
condition = Me.txtCondition
Else
condition = ""
End If
If (Not IsNull(Me.txtPublication)) Then
publication = Me.txtPublication
Else
publication = ""
End If
shelf = Me.cmbShelf.Column(0)
Dim lngRowsAffected As Long
MsgBox filePath
sql = "INSERT INTO tblNotaryIndex([NotaryRefNo], [Volume], [Date Start], [Date End], [Condition], [Publication], [Publication Link], [ShelfId]) " & _
"VALUES('" & refNo & "'," & Volume & ",#" & dateStart & "#,#" & dateEnd & "#, '" & condition & "', '" & publication & "','" & filePath & "','" & shelf & "')"
On Error GoTo ErrorMessage
CurrentDb.Execute sql, dbFailOnError
cmbNotary.Value = Null
Me.txtVolume = Null
Me.txtDateStart = Null
Me.txtDateEnd = Null
Me.txtCondition = Null
Me.txtPublication = Null
cmbShelf.Value = Null
filePath = ""
End If
Exit Sub
ErrorMessage:
MsgBox "Record already exists"
End Sub
Private Sub txtDateStart_AfterUpdate()
VerifyDates
End Sub
Private Sub txtDateEnd_AfterUpdate()
VerifyDates
End Sub
Private Sub VerifyDates()
If Not (IsNull(Me.txtDateStart) Or IsNull(Me.txtDateEnd)) Then
If Me.txtDateEnd < Me.txtDateStart Then
MsgBox "Date End must be after Date Start"
Me.txtDateEnd = Null
End If
End If
End Sub
Private Sub btnPublicationLink_Click()
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Dim filePath As String
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))
filePath = strFolder & strFile
MsgBox strFolder & strFile
Next
End If
Set f = Nothing
End Sub