Fixing repeating codes...HELP

Ok, I understand where you are going with this...I think. I can move all the
PDF files in to one folder if it will help make my life easier on this end.
If I moved them all to one folder, created a list box on a form, how would I
add the code to work with it there. I am sorry that I am not so good with
VBA. Thank you again for all the time your spending with me on this.

Jacob



"John W. Vinson" wrote in message
...
On Mon, 22 Sep 2008 10:51:01 -0400, "Jacob" wrote:

I have read over the help file and dont mind admitting I am a little lost
on
how to go about this. From the sounds of it, you are pointing me in the
right direction. I do have a long list of files in different folders I
need
to display and would the user to be able to select the one they want.
Currently I am placing all my code behind each button for each file. Can
you
help walk me through this process? Thank you in advance for your
assistance.

It is indeed a bit tricky - the Dir function is peculiar in that it
"remembers" what was called previously. I have no way to know how your
folder
structure is set up or how you want the users to pick folders or files so
I
can't give you exact directions. Might the folder be anywhere (C:\Program
Files, C:\Windows, ...????) or is there some parent folder with
subfolders?
What are you starting with?

Just as an example to put all the .pdf filenames in the directory
C:\Documents and Settings\John\My Documents\PDFFiles into a Table named
MyFiles with fields FileID (autonumber), Pathname, and Filename, you could
use
code like:

' Set the path.
Dim MyPath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db=CurrentDb
Set rs = db.OpenRecordset("MyFiles") ' open the table
MyPath = "C:\Documents and Settings\John\My Documents\PDFFiles\*.pdf"
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!Path = MyPath ' set the value of the path
rs!Filename = MyName ' fill in the current filename
MyName = Dir ' Get next entry.
rs.Update ' actually write the record into the table
Loop


This will loop through all the .pdf files in the chosen directory and
write
the name into the file. You will also need to loop through the folders and
subfolders, I presume.

--

John W. Vinson [MVP]

On Mon, 22 Sep 2008 14:16:04 -0400, "Jacob" wrote:

Ok, I understand where you are going with this...I think. I can move all the
PDF files in to one folder if it will help make my life easier on this end.
If I moved them all to one folder, created a list box on a form, how would I
add the code to work with it there. I am sorry that I am not so good with
VBA. Thank you again for all the time your spending with me on this.

It doesn't matter much where the *FILES* are stored. The key point is that you
want the names (and, if you have multiple folders, the pathnames) of the files
in an Access Table.

You would then use a Listbox or - better, since it lets you autocomplete the
name, combo box - based on this table of filenames. Rather than your code
having the filename edited into the vba code, you would simply pick up the
filename from the value of the combo box:

Application.FollowHyperlink "Me!comboboxname"
--

John W. Vinson [MVP]

OUTSTANDING...Now, so I understand, I place this code in to a new module,
and how do I call it up?




"John W. Vinson" wrote in message
...
On Mon, 22 Sep 2008 10:51:01 -0400, "Jacob" wrote:

I have read over the help file and dont mind admitting I am a little lost
on
how to go about this. From the sounds of it, you are pointing me in the
right direction. I do have a long list of files in different folders I
need
to display and would the user to be able to select the one they want.
Currently I am placing all my code behind each button for each file. Can
you
help walk me through this process? Thank you in advance for your
assistance.

It is indeed a bit tricky - the Dir function is peculiar in that it
"remembers" what was called previously. I have no way to know how your
folder
structure is set up or how you want the users to pick folders or files so
I
can't give you exact directions. Might the folder be anywhere (C:\Program
Files, C:\Windows, ...????) or is there some parent folder with
subfolders?
What are you starting with?

Just as an example to put all the .pdf filenames in the directory
C:\Documents and Settings\John\My Documents\PDFFiles into a Table named
MyFiles with fields FileID (autonumber), Pathname, and Filename, you could
use
code like:

' Set the path.
Dim MyPath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db=CurrentDb
Set rs = db.OpenRecordset("MyFiles") ' open the table
MyPath = "C:\Documents and Settings\John\My Documents\PDFFiles\*.pdf"
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!Path = MyPath ' set the value of the path
rs!Filename = MyName ' fill in the current filename
MyName = Dir ' Get next entry.
rs.Update ' actually write the record into the table
Loop


This will loop through all the .pdf files in the chosen directory and
write
the name into the file. You will also need to loop through the folders and
subfolders, I presume.

--

John W. Vinson [MVP]

When I try to run this, it stops on the line

rs!Path = MyPath ' set the value of the path

with the error message of "Item not found in this collection" any help on
it?




"Jacob" wrote in message
...
OUTSTANDING...Now, so I understand, I place this code in to a new module,
and how do I call it up?




"John W. Vinson" wrote in message
...
On Mon, 22 Sep 2008 10:51:01 -0400, "Jacob"
wrote:

I have read over the help file and dont mind admitting I am a little lost
on
how to go about this. From the sounds of it, you are pointing me in the
right direction. I do have a long list of files in different folders I
need
to display and would the user to be able to select the one they want.
Currently I am placing all my code behind each button for each file. Can
you
help walk me through this process? Thank you in advance for your
assistance.

It is indeed a bit tricky - the Dir function is peculiar in that it
"remembers" what was called previously. I have no way to know how your
folder
structure is set up or how you want the users to pick folders or files so
I
can't give you exact directions. Might the folder be anywhere (C:\Program
Files, C:\Windows, ...????) or is there some parent folder with
subfolders?
What are you starting with?

Just as an example to put all the .pdf filenames in the directory
C:\Documents and Settings\John\My Documents\PDFFiles into a Table named
MyFiles with fields FileID (autonumber), Pathname, and Filename, you
could use
code like:

' Set the path.
Dim MyPath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db=CurrentDb
Set rs = db.OpenRecordset("MyFiles") ' open the table
MyPath = "C:\Documents and Settings\John\My Documents\PDFFiles\*.pdf"
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!Path = MyPath ' set the value of the path
rs!Filename = MyName ' fill in the current filename
MyName = Dir ' Get next entry.
rs.Update ' actually write the record into the table
Loop


This will loop through all the .pdf files in the chosen directory and
write
the name into the file. You will also need to loop through the folders
and
subfolders, I presume.

--

John W. Vinson [MVP]

On Tue, 23 Sep 2008 11:56:14 -0400, "Jacob" wrote:

When I try to run this, it stops on the line

rs!Path = MyPath ' set the value of the path

with the error message of "Item not found in this collection" any help on
it?

It's looking for a field named Path in your table. If the field does not exist
or has a different name you'll get this error. Match the fieldname in the
table to the rs!name and you'll be ok.
--

John W. Vinson [MVP]

GRRRR!!!! here is how I have it and I cannot see where to place the field
name...PLEASE ADVICE??? I am very sorry to keep bugging you....



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 = "C:\Data\*.pdf"
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!Path = MyPath ' set the value of the path
rs!FileName = MyName ' fill in the current filename
MyName = Dir ' Get next entry.
rs.Update ' actually write the record into the table




"John W. Vinson" wrote in message
...
On Tue, 23 Sep 2008 11:56:14 -0400, "Jacob" wrote:

When I try to run this, it stops on the line

rs!Path = MyPath ' set the value of the path

with the error message of "Item not found in this collection" any help on
it?

It's looking for a field named Path in your table. If the field does not
exist
or has a different name you'll get this error. Match the fieldname in the
table to the rs!name and you'll be ok.
--

John W. Vinson [MVP]

OMG!!! LOL, I think I have been looking at it too long. I found it. Thank
you very much, I got it working now so I will continue to play with it.
TAHNKS SOOOO MUCH!!!
"Jacob" wrote in message
...
GRRRR!!!! here is how I have it and I cannot see where to place the field
name...PLEASE ADVICE??? I am very sorry to keep bugging you....



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 = "C:\Data\*.pdf"
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!Path = MyPath ' set the value of the path
rs!FileName = MyName ' fill in the current filename
MyName = Dir ' Get next entry.
rs.Update ' actually write the record into the table




"John W. Vinson" wrote in message
...
On Tue, 23 Sep 2008 11:56:14 -0400, "Jacob"
wrote:

When I try to run this, it stops on the line

rs!Path = MyPath ' set the value of the path

with the error message of "Item not found in this collection" any help on
it?

It's looking for a field named Path in your table. If the field does not
exist
or has a different name you'll get this error. Match the fieldname in the
table to the rs!name and you'll be ok.
--

John W. Vinson [MVP]

One more question....I added the code (Application.FollowHyperlink
"Me!link") in my list box but when I try to open the referenced PDF file, I
first get a message box warning me about opening files then I get a runtime
error 490 and says it cannot open the file....any suggestions on this one?





"John W. Vinson" wrote in message
...
On Mon, 22 Sep 2008 10:51:01 -0400, "Jacob" wrote:

I have read over the help file and dont mind admitting I am a little lost
on
how to go about this. From the sounds of it, you are pointing me in the
right direction. I do have a long list of files in different folders I
need
to display and would the user to be able to select the one they want.
Currently I am placing all my code behind each button for each file. Can
you
help walk me through this process? Thank you in advance for your
assistance.

It is indeed a bit tricky - the Dir function is peculiar in that it
"remembers" what was called previously. I have no way to know how your
folder
structure is set up or how you want the users to pick folders or files so
I
can't give you exact directions. Might the folder be anywhere (C:\Program
Files, C:\Windows, ...????) or is there some parent folder with
subfolders?
What are you starting with?

Just as an example to put all the .pdf filenames in the directory
C:\Documents and Settings\John\My Documents\PDFFiles into a Table named
MyFiles with fields FileID (autonumber), Pathname, and Filename, you could
use
code like:

' Set the path.
Dim MyPath As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db=CurrentDb
Set rs = db.OpenRecordset("MyFiles") ' open the table
MyPath = "C:\Documents and Settings\John\My Documents\PDFFiles\*.pdf"
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!Path = MyPath ' set the value of the path
rs!Filename = MyName ' fill in the current filename
MyName = Dir ' Get next entry.
rs.Update ' actually write the record into the table
Loop


This will loop through all the .pdf files in the chosen directory and
write
the name into the file. You will also need to loop through the folders and
subfolders, I presume.

--

John W. Vinson [MVP]

On Tue, 23 Sep 2008 12:54:28 -0400, "Jacob" wrote:

One more question....I added the code (Application.FollowHyperlink
"Me!link") in my list box but when I try to open the referenced PDF file, I
first get a message box warning me about opening files then I get a runtime
error 490 and says it cannot open the file....any suggestions on this one?


I'd suggest reposting with a new thread. I have not used .pdf files as
hyperlinks and don't have any suggestions! It's not really an Access problem,
though; the error is presumably coming from whatever program you have defined
as the default program for the .pdf extension.
--

John W. Vinson [MVP]