I would like to use a command button to open a selected MS-Word document from a group of possibilities. I have a form that displays information about various generating plants. By previously selecting the plant, the form opens with data for the selected plant. Because there is a large amount of data about each plant, I use a tab control to group the data into various categories. Even this arrangement is insufficient to display all the data and I use hyperlinks to open some ancillary files. In my opinion, a command button gives a more pleasing and professional looking form. Furthermore, if I can do this, I will have learned a useful technique.
My approach is to have the command button invoke a macro that runs the word processing application. What I do not know is how to pass the macro a file name concatenated with the path to the file. If anyone has a suggestion, I am a willing student. For me, this is unknown territory so I will be tediously ignorant (but I am tenacious so it will eventually work).
As an alternative, I would consider another approach. It may be that macros simply will not work in this manner. I know I can use a hyperlink but using a command button, with or without a macro, is a spiffier solution.
There's not really enough detail for me to give specifics here, but concatenating is simple enough. Place anything you want literally in quotes, and wrap variables with ampersands. For example, you have a category and a filename;
RM,
Thanks for your note. Tell me what else you need to know. The basic scheme is there are only two form in my database. The first form has 125 command buttons, one for each plant. Clicking on any one of the cmd btns branches the user to the second form by invoking a macro specific for that plant. The second form opens with that plant's information displayed. Each plant has additional information stored in various files, mostly *.pdfs. What I would like to do is, in the second form, click on a cmd btn that opens a MS-Word file specific to the selected plant. I thought initially of using a macro to run the application but then I would have to pass a file name and path to the macro to get the correct file to open. Is there a way to do this or should I be thinking of other approaches? Thanks.
RM,
Thanks for your note. Tell me what else you need to know. The basic scheme is there are only two form in my database. The first form has 125 command buttons, one for each plant. Clicking on any one of the cmd btns branches the user to the second form by invoking a macro specific for that plant. The second form opens with that plant's information displayed.
Ouch! I think it would be much easier to have the user select a plant from dropdown list. First, you won't have to create 125 buttons. Secondly, if you want to add a plant, you just add the plant to the table and its automatically updated. Also, it allows you to keep the order of the plants much more easily. Imagine you want to insert a plant at number 50... you now have to move all your command buttons to get it into place.
slrphd said:
Each plant has additional information stored in various files, mostly *.pdfs. What I would like to do is, in the second form, click on a cmd btn that opens a MS-Word file specific to the selected plant. I thought initially of using a macro to run the application but then I would have to pass a file name and path to the macro to get the correct file to open.
Not knowing what tables you have, or more about your data its a little difficult to advise you. You will need a Plant table, with the plants name and various details. You should give each plant a unique number to identify it to make things easier. You will also then need a table with details of all the documents you have for each plant.
slrphd said:
Is there a way to do this or should I be thinking of other approaches?
Quite honestly I am not sure whether this is the best way to do this. I have a few questions;
1. How much detail do you have stored for each plant? Address? Manager? Phone? Is this information stored anywhere else you can access, and could you use this plant information elsewhere? How often are plants added/deleted/updated?
2. How many documents do you have for each plant? Over how many directories are they stored? How often are the added/deleted/updated?
Other options you might want to consider is a spreadsheet, or even a simple HTML page.
RM,
Well, you’re right about the ouch part but I do not get to make all the decisions. My generating stations are U.S. nuclear power plants; the last one came on line in the 90’s and no new ones are on order so the data is not very dynamic to say the least. I have several tables with details of plant descriptions, plant administrations, some design details and so forth. That type information is stored in tables. I have other design details, usually engineering drawings, stored in *.pdf files which I call up by using hyperlinks. Each plant has seven or eight drawing files supporting it and all these addresses are stored in tables as hyperlinks. Each plant has a DOE code number, which is what I use to coordinate most data.
An important piece of data for the program is the details of the exact route to be used for transporting spent fuel. These details are stored in a MS-Word file. It occurred to me that I could put the path and filename in a table and the then write a macro containing a large switch statement that would key on the DOE code number and assign the corresponding path and filename to a variable in the macro. That way, a command button would invoke a macro that would run the application to open a specific file. What do you think? This would be a long way to go about it but it might work. At least it should keep me from wondering the streets nights.
I agree with reclusivemonkey that a dropdown list for the plants is probably a much better approach than all those buttons.
Perhaps you should create a second table which will hold the path and filename for the text documents, and create a one-to many-relationship.
Then create a subform for this table on the main form and set the link child/masterfields. That way you can link to as many documents for each plant as you want.
There is a fairly easy way to retrieve and store the path and filename to a doc in your table. Please check out the link below (with thanks to GHudson).
No need to use a hyperlink field, just store the info in a text field and use the application.followhyperlink method. It is easy to ad this to command button on the form.
If you don't want the user to see the text box you can hide it, and just show the command button that opens the file.
One more advice, it's best to stay away from macros as much as possible,
but use code instead. It more efficient and the big advantage is that with code you can manage error handling.
Ok I think I have a better idea now of your database Steven. There are two ways to accomplish what you want. You could indeed use VBA to write a macro to open the relevant word document. However, you don't need to open Word to open a word document; simply opening the document will open the relevant application (at least for documents with known extensions). You could also do this simply from a hyperlink. If you create a hyperlink field, and add a file path on your system, clicking on this will open the document in the right application. You can then alter the formatting of the hyperlink field in a report (make it embossed) and it will look like a button with the name and path of the document on it. If you really still want to follow the macro/button method I think I can give you enough code to get you where you want to be. I won't be in work for much longer though (I'm in the UK here) and only have *NIX boxen at home so it could be tomorrow when I reply.
trucktime,
Thanks for the advice. As you can see, ideas are coming faster than I can try them. I downloaded the sample and will spend time analyzing it.
To trucktime and Reclusive Monkey,
I worked out a solution to the problem I was describing yesterday. It is ugly and goes against some of the advice I received. It does have one virtue: it works.
I wrote a macro and in the macro list set the condition to be equal to the plant ID number. Then, set the action to RunApp. In the command line for the macro enter the name of the executable file and the path to the file to be opened. I said it was ugly.
This approach has several drawbacks and I will change it (yeah, right) when I learn to code better. For the moment, this band-aid keeps me going. Thanks for all your help and suggestions.
To trucktime and Reclusive Monkey,
I worked out a solution to the problem I was describing yesterday. It is ugly and goes against some of the advice I received. It does have one virtue: it works.
Anytime When I first started out in computers, I asked someone who I much admired if what I had done was "right". He asked if it worked, and when I replied yes, he said that it had been done right then! There are so many ways to do even the simplest things on a computer there is no "right" way to do it, I tend to think the approach is more important. As you've managed to come to the solution pretty much on your own (not code pasted for you to simply pop in!) you'll have learned more than you realise. As trucktime says, now it works, you can take a copy of that and bash away at it to make it nicer, and maybe look at bits of your code you can use for other things. It can be fun!
Dear experts. Now this one here seems to be the same tiny (but unsolvable?) problem like the two that have been posted before. Please have another look at "button to open up MS Fax & inserts contacts fax number from form"
dated 12-04-2004. Well, fspc puts it very simple and as well as he (or her) and I can't quite believe that there shoudn't be a simple solution to it: We are looking for the code of a command button in a form, which will open up an external application, retrieving an argument from a field in the form. Please just say so if it is impossible, so that we stop searching for it.
Thanks for your offer, slrphd. An ugly solution I got myself. Any old macro-tool can handle my simple problem: Copy a field expression - open a external application - open the appropriate file. That's all. Just Access doesn't seem to be able to do this. What we are looking for is the code for a command button in a form. (somehow of the following type) :
Private Sub Comando2_Click()
On Error GoTo Err_Comando2_Click
You must use the quadruple quotes to enclose the file path in double quotes if there is a space in the file name. If there is no space they are optional, but shouldn't cause any harm.
(NB: there is a deliberate space between ".exe" and the next double quote)
Also:-
Use Named Constants when available, as this will make it easier to work out what the code is doing when you come back to it a year (or a month) later
eg
Call Shell(stAppName, vbMaximizedFocus)
instead of
Call Shell(stAppName, 3)
And, FYI, the "Call" is not necessary; you can just use
Shell stAppName, vbMaximizedFocus
(don't put the braces when not using "call").
when the exact file name to be opend is to be found in the data base field "Picture-Ident" in the data base form "Stamps" in the data base "Celebrities"
A typical Value in the field "Picture-Ident" would be: "E:\Pictures\Baldwin 21951.jpg"
First thing; using dashes in a field name is a bad idea. If you have control over that, I strongl recommend you change it now. Also do not use embedded spaces in object or field names, and I also avoid underscore characters.
You obviously have control over the (text box) control name. Set the name of the text box control that is bound to the field (hopefully "formerly known as") Picture-Ident to txtPictureIdent. Then the following code should work.
Code:
Dim stAppName As String
stAppName = "E:\Irfanview\i_view32.exe " & """" & Me.txtPictureIdent & """"
MsgBox "I am about to shell the following:-" & vbCrLf & stAppName, vbOKOnly + vbInformation, "Here we go !"
Shell stAppName, vbMaximizedFocus
O.K. gents, here's my demo data base plus 1 jpg-picture which should open on cklick of the command button. He who solves my problem I will worship for the rest of my life (which is not that much any more, unfortunately)
The problems are:-
1) Text1 is not the name of the control that has the path to the picture file.
2) you do not have the correct format, using quote requirements as detailed in my previous post.
(The ".value" is optional - I didn't include it in my previous post, but either way it should not affect your purpose.)
If you still get "Invalid Procedure Call or Arguement" error message, then there is possibly something wrong with your exe path. I don't have this exe, so I used the following line in its place:-
(Worked for me, using ACCESS 2003 / WIN XP Pro SP2.)
HTH.
Towards your other problems... Your database design could do with some help in a number of ways. I offer the following suggestions: (all "IMHO")
Table Persons should have a Primary Key. Your "Sys-Nr" field would do for this. In design view - select this field and then click on the little key icon on the toolbar to set this as the table's Primary Key.
Avoid using embedded spaces in object (e.g. tables, queries, forms etc) and field names
Avoid using special characters (such as dashes, slashes, and underscores etc) in object and field names. e.g. in Persons table change "Sys-Nr" to "SysNr"
Mine Deutsch is nicht zehr gut, aber...
Geburtstag/Todestag - Store these as a single field, with a data type of Date/Time
What is "Oder", as in Geburts-Oder & Todes-Oder ??
You might want to read up on database normalization. e.g. it is bad practice to type "New York" into the database dozens of times.
You may also want to read up on Many to Many relationships. You would be better served by using one for Person to Nationality relationship, with a Nationality table housing data for your current N1 (& N2) and "Nation 1" (& "Nation 2") fields.
A many to many relationship is also called for "Tätigkeit". I also guess that your "Tät-igk-eit" field is related to the "Tätigkeit 1"(etc) fields, if so they belong in the same base table. What would you do in your current structure if a person had 4 "Tätigkeit"s ?
You will then need junction table(s) to relate the two tables involved in the Many to many relationships.
Is "Alter" the person's age when they died ? If so you should not store this, a such, rather calculate it based on th birth and death dates.
What is "Jahrhundert" ? Is this the century they were born in? If so, again, don't store this, as it can be calculated from the date of birth.
I note you have worked towards normalising your Prize data; and this is a good thing . You may want to apply the same concept to your "Werke" data also.
Have a look at the indexes to the two tables. To do this thoroughly, click on the Indexes button on the toolbar; and have a think about what is going on there. Looks like some indexes may have been created along the way that are no longer desireable ????
Name your objects on your forms consistently. There are several naming conventions that various people use, and it is a matter of personal preference. I (somewhat loosely) adopt "Hungarian Notation". Therefore the name of all my textboxes always begins with the prefix "txt". If I place a control on a form that is bound to a field named "FirstName" that text-box would be named "txtFirstName". Such habits make life a lot easier down the track! (It can also keep you out of particular trouble with reports & calculated sums etc. )
