Help! Got a Brain Freeze!

[MBSNewbie]

Hi Everyone,
I know the answer to this is going to be a simple one, but I have writers
block over here so I'm hoping someone can pull me out!

I want Access to look and see if 3 files exist on the fileserver and return
the create dates of those files.
I can do it for each record individually if I add it to the GotFocus, but
then I have to manually click on each record to get an update.

I can't figure out how to add it as a query, also can't figure out how to
loop it so I'm out of options!
-----------------------------------------------------------------------------------------------------------------------------------
Here is what I have so far, but of course, it only updates the first record
in the list: (Clear as mud right?)

Private Sub Form_Load()

Dim oFSO As Object
Dim oF As Object
Dim strFile As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
strFile = "S:\A&P\BOL\BOL" & Trim(Me![CSTPONBR]) & ".pdf"
If oFSO.FileExists(strFile) = True Then
Set oF = oFSO.GetFile(strFile)
BOLStatus = oF.DateCreated
Else
BOLStatus = ""
End If

Set oF = Nothing
Set oFSO = Nothing

Set oFSO = CreateObject("Scripting.FileSystemObject")
strFile = "S:\A&P\POD\POD" & Trim(Me![CSTPONBR]) & ".pdf"
If oFSO.FileExists(strFile) = True Then
Set oF = oFSO.GetFile(strFile)
PODStatus = oF.DateCreated
Else
PODStatus = ""
End If

Set oF = Nothing
Set oFSO = Nothing

Set oFSO = CreateObject("Scripting.FileSystemObject")
strFile = "S:\A&P\PO\PO" & Trim(Me![CSTPONBR]) & ".jpg"
If oFSO.FileExists(strFile) = True Then
Set oF = oFSO.GetFile(strFile)
POStatus = oF.DateCreated
Else
POStatus = ""
End If

Set oF = Nothing
Set oFSO = Nothing


End Sub

 

[Stefan Hoffmann]

hi,

as Q said: Grow some hair!

I want Access to look and see if 3 files exist on the fileserver and return
the create dates of those files.
I can do it for each record individually if I add it to the GotFocus, but
then I have to manually click on each record to get an update.
I can't figure out how to add it as a query, also can't figure out how to
loop it so I'm out of options!

Use a public function in a normal modul:

Public Function FileDate(AFilename As String) As DateTime

On Local Error Resume Next

Dim oFSO As Object
Dim oF As Object
Dim strFile As String

FileDate = #1900/01/01#

Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(AFilename) Then
Set oF = oFSO.GetFile(AFilename)
FileDate = oF.DateCreated
End If

Set oF = Nothing
Set oFSO = Nothing

End Function

Use it in your query or record source:

SELECT *,
FileDate("Path\BOL" & Trim(Me![CSTPONBR]) & ".pdf") As FileDate
FROM Table


mfG
--> stefan <--

 

[MBSNewbie]

Hi Stefan,
Thanks for the response, I went that way before, but for some reason I
can get the function to work.
maybe I'm putting it in the wrong spot?

I created a new module and pasted this into it:

Public Function FileDate(AFilename As String) As DateTime

On Local Error Resume Next

Dim oFSO As Object
Dim oF As Object
Dim strFile As String

FileDate = #1/1/1900#

Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(AFilename) Then
Set oF = oFSO.GetFile(AFilename)
FileDate = oF.DateCreated
End If

Set oF = Nothing
Set oFSO = Nothing

End Function

Then I created a simple query, but I get a compile error.
SELECT *, FileDate("S:\A&P\PO\PO200610469.jpg") AS FD
FROM [A&POrders];

I get back:
Compile error . in query expression 'FileDate("S:\A&P\PO\PO200610469.jpg")'

PS. (Growing Hair as fast as possible?)

hi,

as Q said: Grow some hair!

I want Access to look and see if 3 files exist on the fileserver and
return the create dates of those files.
I can do it for each record individually if I add it to the GotFocus, but
then I have to manually click on each record to get an update.
I can't figure out how to add it as a query, also can't figure out how to
loop it so I'm out of options!

Use a public function in a normal modul:

Public Function FileDate(AFilename As String) As DateTime

On Local Error Resume Next

Dim oFSO As Object
Dim oF As Object
Dim strFile As String

FileDate = #1900/01/01#

Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(AFilename) Then
Set oF = oFSO.GetFile(AFilename)
FileDate = oF.DateCreated
End If

Set oF = Nothing
Set oFSO = Nothing

End Function

Use it in your query or record source:

SELECT *,
FileDate("Path\BOL" & Trim(Me![CSTPONBR]) & ".pdf") As FileDate
FROM Table


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I created a new module and pasted this into it:

Public Function FileDate(AFilename As String) As DateTime
End Function

Compile error . in query expression 'FileDate("S:\A&P\PO\PO200610469.jpg")'

It's a typo in the function declaration, it must be:

Public Function FileDate(AFilename As String) As Date
'code
End Function



mfG
--> stefan <--

 

[MBSNewbie]

Thanks Stefan,

That did the trick! It works great!