String Value not being assigned

[pubdude2003 via AccessMonster.com]

Here's an odd one:

On Error GoTo Err_GetFiles
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)

Somehow my code has broken, when I debug the StrFile = "" but the StrPath =
"c:/...."
What am I doing wrong?

 

[Rick Brandt]

Here's an odd one:

On Error GoTo Err_GetFiles
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)

Somehow my code has broken, when I debug the StrFile = "" but the
StrPath = "c:/...."
What am I doing wrong?

Where is strPath being set?

 

[pubdude2003 via AccessMonster.com]

"c:/MyQuotations"

 

[Douglas J. Steele]

Rick asked WHERE it's being set, not what value being used to set it. You
don't show its declaration nor setting its value in the snippet of code you
posted. If it's being set in another routine and strPath wasn't properly
declared as a public variable, the routine you posted will know nothing
about its value.

(BTW, for Windows, that should probably be "c:\MyQuotations\": the closing
slash matters)

 

[pubdude2003 via AccessMonster.com]

oopie, my bad.... I think, the entire sub is

Sub GetFiles(strPath As String)
On Error GoTo Err_GetFiles
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)
'Loop through the balance of files
Do
'check to see if you have a filename
If strFile = "" Then
GoTo ExitHere
End If
strDate = FileDateTime(strPath & strFile)
rs.AddNew
'to save the full path using strPath & strFile
'save only the filename
rs!FileName = strFile
rs!FileDate = strDate
rs.Update

'try for next filename
strFile = Dir()
Loop

ExitHere:
On Error Resume Next
Set rs = Nothing
MsgBox ("Directory list is complete.")
Exit Sub

being called from

Private Sub Command6_Click()
Dim loc As String
loc = BrowseFolder("Select Folder containing PDF's")

Call GetFiles(loc)
End Sub

 

[Rick Brandt]

pubdude2003 via AccessMonster.com wrote:


You need to add a trailing slash as Douglas suggested.

 

[pubdude2003 via AccessMonster.com]

exactly as you said guys, I guess I assumed that the BrowseFolder routine
tagged it on....

many thanks!

 

[Douglas J. Steele]

Actually, you shouldn't make assumptions about whether or not it puts a
final slash.

If you select a folder, the dialog doesn't put a final slash. However, if
you only select a drive, it does. In other words, you'll get "C:\", but only
"C:\Program Files"

I'd recommend adding the following to your existing routine:

If Right$(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

 

[pubdude2003 via AccessMonster.com]

terrific notion, thank you Doug!