Fixing repeating codes...HELP

  • Thread starter Thread starter Jacob
  • Start date Start date
J

Jacob

I have this code set in a DB where I look up files on my HD. It is set
behind a cmdbutton for each file I need to open.

Dim strFileName As String
Dim strFolder As String

strFileName = "Logiq3.pdf"
strFolder = "E:\Service Manuals\GE\"

Application.FollowHyperlink strFolder & strFileName


The problem is there are a ton of files and the location folder is repeated
many times. Is there a way I can shorten this code?
 
On Fri, 19 Sep 2008 08:08:05 -0400, "Jacob" <[email protected]>
wrote:

Use a global constant. At the top of a standard module write:
Global BASE_FOLDER As String = "E:\Service Manuals\GE\"

Then in your code:
Application.FollowHyperlink BASE_FOLDER & strFileName

-Tom.
Microsoft Access MVP
 
I have this code set in a DB where I look up files on my HD. It is set
behind a cmdbutton for each file I need to open.

Dim strFileName As String
Dim strFolder As String

strFileName = "Logiq3.pdf"
strFolder = "E:\Service Manuals\GE\"

Application.FollowHyperlink strFolder & strFileName


The problem is there are a ton of files and the location folder is repeated
many times. Is there a way I can shorten this code?
Click to expand...

Do you have a *different command button for EACH FILE!?*

Ouch.

If so, change your logic. Use some (probably pretty simple) code using the
Dir() function to fill a table with all of the desired filenames, then use a
Combo Box (or two, maybe one for folders and one for filenames) to allow the
user to select a file - and then use the afterupdate event of the combo to
display the file's contents.
 
Global can be used for defining variable type (dim for local only). I don't
know what the equivalent is for constant.
 
How would the dir function do this?
John W. Vinson said:
Do you have a *different command button for EACH FILE!?*

Ouch.

If so, change your logic. Use some (probably pretty simple) code using the
Dir() function to fill a table with all of the desired filenames, then use
a
Combo Box (or two, maybe one for folders and one for filenames) to allow
the
user to select a file - and then use the afterupdate event of the combo to
display the file's contents.
Click to expand...
 
How would the dir function do this?
Click to expand...

Open the VBA editor (by typing Ctrl-G for example), press F1, and search for
"Dir". There's an example in the help file for how to loop through all the
files in a directory.
 
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.
 
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.
Click to expand...

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.
 
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
 
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.
Click to expand...

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"
 
OUTSTANDING...Now, so I understand, I place this code in to a new module,
and how do I call it up?
 
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 said:
OUTSTANDING...Now, so I understand, I place this code in to a new module,
and how do I call it up?
Click to expand...
 
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?
Click to expand...

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.
 
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
 
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 said:
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
Click to expand...
 
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?
 
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?
Click to expand...

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.
 
Back
Top