forms Coding

[Jacob]

I am using Access 2003 and have this code in my DB to bring back a list of
PDF forms.

Dim mypath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSvc") ' open the table
mypath = "E:\Service Manuals\*.*"
MyName = Dir(mypath) ' Retrieve the first PDF file in that folder.
Do While MyName <> "" ' Start the loop.

rs.AddNew ' add a new record to the MyFiles table
rs!book = mypath ' set the value of the path
rs!book = MyName ' fill in the current filename
MyName = Dir ' Get next entry.
rs.Update ' actually write the record into the table


what I need to have it do is look in sub folders as well. What do I need to
add to this to make this happen? Any help is greatly appreciated.

 

[Albert D. Kallal]

You can use my code and go:

add to the top defs:

Dim mypath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

dim cPdfList as new collection
dim i as integer

mypath = "E:\Service Manuals\"
Call FillDir(mypath, cPDFlist, "*.pdf")\
for i = 1 to cPDFlist.Count
rs.AddNew ' add a new record to the MyFiles table
rs!book = mypath ' set the value of the path
rs!book = cPDFlist(i) ' fill in the current filename
rs.Update ' actually write the record into the table
next i

The problem with above is that the path name is hard coded. So, what we
really need is to change the above to strip out the actual path name. So, we
need to change:

rs!book = mypath ' set the value of the path
to:
rs!book = Left(cPDFlist(i), InStrRev(cPDFlist(i), "\"))

The filldir routine is posted after my signature, and can be placed in a
standard code module. It will recursive as many folders and even sub-folders
deep from the starting dir downwards.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist, "*.dbf")

MsgBox "there are " & dlist.Count & " in the dir"

lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub

Sub FillDir(startDir As String, strFil As String, dlist As Collection)

'build up a list of files, and then
'add add to this list, any additinal
'folders

Dim strTemp As String
Dim colfolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir & strFil)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

'now build a list of additional folders
strTemp = Dir(startDir & "*.*", vbDirectory)

Do While strTemp <> ""
If (GetAttr(startDir & strTemp) And vbDirectory) = vbDirectory Then
If (strTemp <> ".") And (strTemp <> "..") Then
colfolders.Add strTemp
End If
End If
strTemp = Dir
Loop

'now process each folder (recursion)
For Each vFolderName In colfolders
Call FillDir(startDir & vFolderName & "\", strFil, dlist)
Next vFolderName

End Sub

 

[Jacob]

Ok, using this method, I am getting a compile error. Says ByRef Argument
Type mismatch. Hmmm? I copied the FillDir function into a module. Placed the
other code in my form and changed the line of code you asked. any reason
this is happening?

 

[Albert D. Kallal]

Ok, using this method, I am getting a compile error. Says ByRef Argument
Type mismatch. Hmmm? I copied the FillDir function into a module. Placed
the other code in my form and changed the line of code you asked. any
reason this is happening?

Typo on my part:

Call FillDir(startDir, "*.pdf", dlist)

the *.pdf is the 2nd param...not the last

So, change:

Call FillDir(mypath, cPDFlist, "*.pdf")\

to

Call FillDir(mypath, "*.pdf", cPDFlist)

 

[Jacob]

Grrrr...Maybe I am doing something wrong. Here is my whole code under the
command button. It is meant to fill a list box with this data...I am sorry
for not including this to start with. The original code was filling a table
with this data and the list box was pulling from it. I hope I have not
struck your last nerve...


Private Sub Command98_Click()
Dim strInput As String, strMsg As String

strMsg = "Enter your password."
strInput = InputBox(prompt:=strMsg, Title:="Password Required")

If strInput = "xxxx" Then

DoCmd.Echo False, ""
DoCmd.OpenQuery "qryDelSvc", acViewNormal, acEdit
DoCmd.Echo True, ""

' Set the path.
Dim mypath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim cPdfList As New Collection
Dim i As Integer

mypath = "E:\Service Manuals\"

Call FillDir(mypath, "*.pdf", cPdfList)

For i = 1 To cPdfList.Count
' rs.AddNew ' add a new record to the MyFiles table
' rs!book = Left(cPdfList(i), InStrRev(cPdfList(i), "\")) ' set the
value of the path
' rs!book = cPdfList(i) ' fill in the current filename
' rs.Update ' actually write the record into the table
Next i


rs.AddNew ' add a new record to the MyFiles table
rs!book = mypath ' set the value of the path
rs!book = MyName ' fill in the current filename
MyName = Dir ' Get next entry.
rs.Update ' actually write the record into the table

End With
'Loop

Me.link.Requery

Else

MsgBox "You have entered an incorrect password"

End If
End Sub

 

[Jacob]

OK, I fixed it. It was my error. Thank you for your help, the code works
great now.

 

[Albert D. Kallal]

does the following code work behind a separate button:

Dim cPdfList As New Collection
dim mypath as string

mypath = "E:\Service Manuals\"
Call FillDir(mypath, "*.pdf", cPdfList
msgbox "there are " & cPDFlist.count & " files"

Also, while looking at code, from the menu do a debug->compile.

Remember, the FillDir code should be placed in a standard code module

Try changing:

Sub FillDir(startDir As String, strFil As String, dlist As Collection)

to

Public Sub FillDir(startDir As String, strFil As String, dlist As
Collection)


So, put the word public in front of the above defincation.

Also, after you modify any code, ALWAYS remember do a debug-compile BEFORE
you try and run that code. Compiling will catch many errors for you.

So, your code sould look like:

Private Sub Command98_Click()

Dim strInput As String, strMsg As String

strMsg = "Enter your password."
strInput = InputBox(prompt:=strMsg, Title:="Password Required")

If strInput = "xxxx" Then

DoCmd.Echo False, ""
DoCmd.OpenQuery "qryDelSvc", acViewNormal, acEdit
DoCmd.Echo True, ""

' Set the path.
Dim mypath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim cPdfList As New Collection
Dim i As Integer

mypath = "E:\Service Manuals\"

Call FillDir(mypath, "*.pdf", cPdfList)

For i = 1 To cPdfList.Count
rs.AddNew ' add a new record to the MyFiles table
rs!book = Left(cPdfList(i), InStrRev(cPdfList(i), "\"))
rs!book = cPdfList(i) ' fill in the current filename
rs.Update ' actually write the record into the table
Next i

Me.link.Requery

Else

MsgBox "You have entered an incorrect password"

End If

So, in place of the "dir loop", we are using FillDir that sends all file
names to cPdfList. We then process the list with out for...next. So, all of
our table adding code goes inside that for...next loop...you don't need the
stuff after as you had....

Also, there looks to be a problem with the oringal code:


rs!book = mypath ' set the value of the path
rs!book = MyName ' fill in the current filename

Note how the above code showeve mypath and then on the next line MyName into
the SAME field called

rs!book

So, the above can't be right. perahps the "rs!book = mypath" should be
"rs!Path = mypath"