Command Buttons

[Tezcatlipoca]

Quick question regarding form buttons

This query really isn't essential in any way, I'm just in the stage of trying to get my perfectly functioning database to do increasingly clever things.

Essentially, I have a database that stores details of students (course dates, names, etc.). Each of these students also has an invoice, which is written and stored as a word document and completely seperate from the database.

I'd like to try to link the two in such a way that a user of my database, when browsing the records, can simply click a button to open up the corresponding invoice.

I can setup such a button very easily, and point it at a particular file (in this case a Word document), but run into problems with how to do the button code. If I just stick it on the design of the form through which users browse the records, the button - and consequently targetted invoice - will remain the same for every record.

Is it possible to have something which enables the code behind the button to alter, depending upon which record is on screen?

 

[WayneRyan]

Tezcatlipoca,

Set app = CreateObject("word.Application")
app.Visible = True
app.Documents.Open (Me.FileName)

You just have to have a control with the name of their
file "Me.FileName".

hth,
Wayne

 

[Tezcatlipoca]

Thanks for reply, Wayne, but I'm not too sure I get what you mean. Surely if I insert that code then the button, regardless of the record being looked at, will always point to the same Word document.

For example, let's say I have a student registered called Rosencrantz. Rosencrantz has an invoice as a word document elsewhere on the hard drive.

If I add a button to the form through which my core data is filtered and displayed, then that same button exists on the form regardless of which record I go to. So even if I go to Guildenstern's record, clicking on my button will still open Rosencrantz's invoice.

 

[jfgambit]

Tezcatlipoca:

If you are storing all the invoices in the same directory and giving each the name of the student, for example:

C:\My Documets\Invoices\Smith.doc
C:\My Documents\Invoices\Jones.doc

and you have a field on your form with the Students name (i.e. a Textbox called LName)

then you can change the code to be:

Set app = CreateObject("word.Application")
app.Visible = True
app.Documents.Open "C:\My Documents\Invoices\" & me.LName & ".doc"

HTH

 

[Tezcatlipoca]

I see what you mean, and yeah, that probably would work. Unfortunately the invoices themselves are stored in various sub-directories, depending upon the course type.

It looks as though it isn't possible without shifting the documents around, which isn't worth doing just to get this button working. At the moment we log out of the databse and find the records manually. Since they're logically stored according to course type it only takes a few seconds...I was just lazily trying to get a button link running

Incidentally, what is this 'hth' business?

 

[jfgambit]

You could create a different command button for each subdirectory

C:\My Documents\Invoices\History\Smith.doc

would be opened by the cmdHist Command button

C:\My Documents\Invoices\Science\Smith.doc

would be opened by the cmdScience button

etc...

HTH = Hope That Helps

HTH...he he

(he he = a laugh)

 

[Tezcatlipoca]

Hmmm...I see what you mean, and again that would almost certainly work, except for the problem that the name field of the student is kept to a name format - and must remain so, whilst the name of the document relating to that student is nkept to an invoice number format - and must remain so, so that kind of kills any basis of comparison.

I've been doing a little nosing around this and other forums, and hear that it might be solvable by embedding the relevant document into the relevant record, which would involve almost no coding (with the exception, as I understand it, of some kind of OLE statement).

Am I right in thinking this?

 

[WayneRyan]

Tezcatlipoca,

If you have the form on a specific record, don't you have the
file name available in a control?

Where does the name of the document live?

Wayne

 

[Tezcatlipoca]

I have a form which is linked to the table which holds the data. The form just sets the various table fields out and makes them look pretty, and the same form is used for every single record, hence my adding any buttons (and associated code) to the form will cause that same button with the same code to appear on every record when users are viewing the database.

The master storage path for my invoices is C:\invoices\

This then holds a number of sub-directories, listed by course type. Each of these sub-directories holds the various invoice documents, all of which are named as numbers (such as 11432.doc, 84872.doc, and so on). These invoice numbers relate to coded information about how the invoice arrived, it's payment type, etc. Needless to say, I cannot alter the invoice names, and neither can I really alter the student names in the database to reflect the associated document names.

Again, is it possible to embed to certain records, which as I see it will swell the size of the database but would get around the problem of having differently named records and invoices.

 

[WayneRyan]

Is there anything in your database that links a record with
a particular word document?

Can you derive its name from your data.

Wayne

 

[Tezcatlipoca]

That's just it, there isn't. The only thing which really defines both the record and the invoice is the student name, but the invoice cannot be so named.

It struck me that I could add a new field to the table of data and call it something like "invoice". I could then give the invoice number (i.e. the name of the document) in this field, then knock up a text box on the form which related to this field. The problem lies in my invoice storage set up. This solution would only work if all the invoices were stored in the same directory. As they aren't, a multi-functional button existing on the form won't work for those invoices which are stored outside whichever directory I've given in the coding.

I also considered using windows shortcuts, all of which could be stored in the same directory, but which all of which actually point to the relevant invoice location. The problem is that Access looks for a file extension, so won't open a shortcut from what I can see.

It's for this reason that I'm wondering if I'd be better off trying to embed documents into the database.

 

[WayneRyan]

Tezcatlipoca,

No, I wouldn't embed them, you would not only increase the
size of your database, but also increase maintenance effort.

I would concentrate on finding a way to get the paths/names
into your database. They could live on invisible controls and
make it easy to launch word.

Wayne

 

[jfgambit]

Hey Guys...

Check this out....

Can you tell I'm bored???

 

[Tezcatlipoca]

Jeff, you are indeed the proverbial icing on the cake! This is exactly what I'm after. Thanks a lot.

 

[Tezcatlipoca]

Hhmmm...it would appear I have been a little to hasty in my jubilation.

The code works brilliantly, but when I try to incorporate it into my database I run into problems.

Essentially I have removed the ID field (since I don't need it), and changed the record source for the Student field from the table in your example (tblStudents) to the table in my database (tblCoredata).

Clicking on the 'Add Invoice' brings up the browser as per usual, but selecting a document brings up an error box saying:

Runtime Error '-2147352567 (80020009)'
You can't assign a value to this object

Clicking on 'debug' highlights the following line of code in yellow:

Me.Doc = LaunchCD(Me)

Obviously something has altered in the change from your database to mine, but I'm stumped as to what it is...

I don't know if it sheds light, but the text box which is to contain the hyperlink is displaying #Name? as a default.

 

[jfgambit]

Me.Doc is the name of the Field in the database I created. You need to change this to the Filed name that you have (the field where the hyperlink will be stored).

Did you add a field to your table to store the hyperlink (and did you set the DataType to be a Hyperlink)?

If so, that is the name of the filed...

me.Doc = launchCD(Me)
should be
me.NameofFieldtostorehyperlink = LaunchCD(me)

HTH

 

[Tezcatlipoca]

Hokay, I can get this to almost work now, but there are a couple of things I'm still a little uncertain on.

I include a copy of the form with which I'm using to add Invoices (hyperlinks to my word documents) to my records.

The table which holds all of the database information has had two fields added to it, 'Doc' and 'Doc2' respectively. Both are set to 'hyperlink' format. The reason for two fields (and, by extension, two invoices) is that some students are booked via agents, and so have two invoices (an Agent one and a Student one). Ultimately I need these to both be displayed along with the records.

My problems/queries at my current stage are as follows:

1) I can open up the attached form to set an invoice to a record. This aspect works fine, as does the binding of a hyperlink to a specific record. My problem here is that clicking on the hyper link (the Student one), does open the correct Word document, but does so behind Access, so I need to Alt+TAB to Word to view it. I'd preferably like the document to open over Access.

2) The second 'Add Invoice' button (the Agent one) does nothing, despite my copying the code from the first button and changing all instances of 'Doc' to 'Doc2'. What am I doing wrong?

3) Finally, I have some box boxes added to the form through which records are viewed, which use Doc and Doc2 as their sources, the idea being that users when navigating the records normally can simply click the hyperlink and open the invoice. At the moment, clicking on the hyperlink in these boxes does absolutely nothing. I think I need to set an 'onclick' event, but not too sure how I do it in such a way that it opens only what is contained in the box (i.e. the hyperlink changes with each record).

Thanks in advance for any help you can give!

 

[jfgambit]

View the code changes I made and you should see it working now. Also, I moved the opening of the word document to the OnDouble Click event of the textbox. I find this to be standard in most databases and applications...

I'm getting ready to duck the barage of responses that are going to come flying my direction saying "I use single click..."

 

[Tezcatlipoca]

Nope, double click is good for me And thanks for redoing that code for me, it now runs as it should.

With reference to my earlier query, would I be right in thinking that to activate the two text boxes (Doc and Doc2) on the form that users will be looking at, I need to add the following code to each of their 'ondblclick' events:

Private Sub Doc_DblClick(Cancel As Integer)
Application.FollowHyperlink Me.Doc
End Sub

Private Sub Text9_DblClick(Cancel As Integer)
Application.FollowHyperlink Me.Doc2
End Sub

 

[Mile-O]

Tezcatlipoca,

here's an example .zip of the other thing you messaged me about.