Time Stamp Problem....Can;t get the module to work!!!

[mbarnett]

Hi All,

I'm trying to get the TimeStamp of Files I'm importing into my Table tblFileNames...In the table I have the path and File name in the third column I want to know the Time Stamp of each file. I tried the below function, but I keep getting an error message...I would appreciate your help -

Private Sub Form_MyTimeLoad()

Dim db As Database
Dim rst As Variant
Dim NewFile As Variant

DoCmd.Hourglass True
DoCmd.SetWarnings False

Set db = CurrentDb
Set rst = db.TableDefs("TblFileNames").OpenRecordset(dbOpenTable)
rst.MoveFirst
Do Until rst.EOF

NewFile = SaveFileDetails(rst("ImportPath"), rst("ImportName"))

rst.Edit
rst("RunTime") = FileDateTime(rst("ImportPath") & rst("ImportName"))
rst.Update
rst.MoveNext
Loop
DoCmd.Hourglass False
DoCmd.SetWarnings True
End Sub
Function SaveFileDetails(strFilePath As Variant, strFileName As Variant)
'Pass the path to the file, as well as the
'file name, to this function

Dim strFileDate As String
Dim strSQL As String

If strFileName = "" Then
Exit Function
End If

'Get the file date and time
strFileDate = FileDateTime(strFilePath & strFileName)

'Format the date as required for your table
strFileDate = Format$(strFileDate, "dd/mm/yyyy")

strSQL = "INSERT INTO tblFileNames.[Run Time]"
strSQL = strSQL & " VALUES ('" & strFileName & "', '"
strSQL = strSQL & strFileDate & "')"

DoCmd.SetWarnings (False)
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings (True)

End Function

 

[HarleyQuinn]

I would guess the problem comes here:

strSQL = "INSERT INTO tblFileNames.[Run Time]"
strSQL = strSQL & " VALUES ('" & strFileName & "', '"
strSQL = strSQL & strFileDate & "')"

It would appear that you are trying to put two values (seperated by the ",") into one field "Run Time".
Try something like:

strFileName = strFileName & " " & strFileDate

"INSERT INTO tblFileNames.[Run Time]"
strSQL = strSQL & " VALUES ('" & strFileName & "')"

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Sorry, in my post the line

"INSERT INTO tblFileNames.[Run Time]"

should read

strSQL = "INSERT INTO tblFileNames.[Run Time]"

Harleyquinn

---------------------------------
For tsunami relief donations

 

[mbarnett]

Hi Harleyquinn,

Still getting error - Run-Time error'3024':

Could not find fil 'Y:\db\tblFileNames.mdb'.

 

[HarleyQuinn]

Silly question I know but, is that the correct path to the DB???

Harleyquinn

---------------------------------
For tsunami relief donations

 

[mbarnett]

No

 

[HarleyQuinn]

Is it working if you put the correct path to the DB in???

Harleyquinn

---------------------------------
For tsunami relief donations