Print from list (2000/9.0.3821 SR1)

Topic

New Reply

My database includes a table that lists all the 234 non-credit courses we offer. Each course includes a handout–a booklet of from 1 to 20+ pages–for the student to use. We maintain those handouts as Word documents. The table includes a field formatted as hyperlink–click on any item in that field, and it launches Word and displays the handout. From here we can print the handout, of course, but….

I want to build a form that includes a list box to display all these handouts and a command button to print handouts. The user would select one or more handouts, then click on the “Print selected handout(s)” command button to print them. So far, I’ve gotten the list to display just fine. However, when I click on the command button, it prints the form itself, not the handout(s) I selected! I’m really new at this, and I know I need to tell the command button exactly what to print. I just can’t figure out how.

If I may complicate this question just a little more, I’d also like to have a way to designate how many copies to print. Thanks!

Reply | Quote

Replies

Lucas,

The print options in Access are all for printing Access objects, not Word documents. Your best bet is probably to start Word and print the documents one by one from Word.

You can put a text box on the form with the list box in which the user can enter the number of copies (or you can create a combo box for it).

You can control Word from Access by using Automation:

1. In any module, select Tools/References… and set a reference to the Microsoft Word 9.0 Object Library.
2. In the OnClick routine of the “Print” command button, use code like the following. You’ll have to adapt it, of course. The name of the list box is lstDocuments, and I have assumed that the list box contains the document names; they are all in a folder C:Docs. The name of the text box for the number of copies is txtCopies, and the name of the command button is cmdPrint.

   Private Sub cmdPrint_Click()
   Const strPath = “C:Docs”
   Dim wrdApp As New Word.Application
   Dim wrdDoc As Word.Document
   On Error GoTo Err_Handler
   Dim i As Integer
   For i = 0 To lstDocuments.ListCount – 1
   If lstDocuments.Selected(i) Then
   wrdApp.PrintOut FileName:=strPath & lstDocuments.ItemData(i), Copies:=Me.txtCopies
   End If
   Next i
   Exit_Handler:
   On Error Resume Next
   wrdApp.Quit SaveChanges:=False
   Set wrdApp = Nothing
   Exit Sub
   Err_Handler:
   MsgBox Err.Description, vbExclamation
   Resume Exit_Handler
   End Sub

HTH,
Hans

Reply | Quote

Hello, Hans: Your reply helps tremendously! You are quite correct about printing the documents directly from Word. We’ve been doing that for years, and that approach certainly works. However, I’m trying to build a comprehensive system where our users can do just about everything we do every day via one interface, which I’m building in Access. Printing handouts through this system will simplify our tasks and maybe save a little time.

Anyway, I’ve modified your code, and it looks like it should work exactly as I envisioned. Except for one little detail. You assume–and rightly so–that my list box (lstDocuments) contains the document names. Well, yes…but not exactly, and I believe that’s why my cmdPrint does not execute properly. I populated the list box via a query. The table of courses has many fields, two of which I pulled for the query: the course number (I call it SortNbr, formatted as number so as to put the list in numerical order) and Title. The actual file names for the handouts (documents) read a little differently, and therein lies my problem (or so I think).

Here are the differences:

Document number and name as they appear in lstDocuments: [first field] 202 [second field] A Class Divided
Same document (handout) as it appears in the the actual directory: 202 – A Class Divided.doc

So it appears I need a way to help Access find the file. I have another field in the basic table with the exact same course numbers, but formatted as hyperlink. I considered using the hyperlink field in lstDocuments, but if the user clicks on one document number, he’ll immediately launch that document. I want to be able to select more than one document at a time. The user can select, say, a half dozen handouts, tell it to print 10 copies each and walk away while the printer does the work. Thanks!

Reply | Quote

Can you store the name of the document file in your table?

Reply | Quote

Thanks, Paul. Just now, I added a field (FilePath) to the courses table, and in that field I entered just the name of the document. That doesn’t work. Guess I need to include the full file path…? But before we go further with this matter, I now have a much more urgent problem that developed as I tried to fix the previous problem.

Somehow in the process of doing the above, a single record–just one of the 234–got badly screwed up. Some of the characters in the Title field got lost, the hyperlink field emptied, and one or two other fields show some damage. (Again, this applies to one record only–not the entire table, thank goodness! But…it gets worst.) When I try to correct the problem in the table, I keep getting an “Invalid Argument” error or “The search key was not found in any record.” In the hyperlink field–where I should see the number 202 formatted as hyperlink–I see “#Error.” When I try to switch to Design view, I get the same “…search key…” error. Oh, by the way, the primary key field for this one record has “1.1E+09,” where it used to have an AutoNumber like all the other records!! Access won’t let me delete the offending record, it won’t let me add or delete any other records, nor will it let me repair the damage!! I see weeks of work going up in flames!! I’m looking for a paper bag to blow into…. Help!!!

Reply | Quote

First thing, make a backup copy of the db. In the worst case, you will still have a corrupt db.

Now that you have made your copy, try a Compact and Repair (Tools|Database Utilities|Compact and Repair Database…)

If that doesn’t work, head on over to http://support.microsoft.com/default.aspx?…B;EN-US;q273956%5B/url%5D and get the Jet Compact Utility. THat will USUSALLY fix the corruption.

If THAT doesn’t work, import everything into a new MDB, except the corrupt table. Once everything is in the new DB, then try and get the corrupt table in. It may work, it may not, or it may bring the corruption with it. It’s hard to tell.

Good Luck.

Reply | Quote

Thanks, Bryan! I’ll follow your advice and let you know how it worked.

Reply | Quote

Here’s what has happened. I followed your suggestions in order. First, I made a backup copy of the db. Then I tried to compact and repair–didn’t remove the corruption. Next I tried the Jet Compact Utility–encountered “error while compacting” (or words to that effect). So I created a new MDB and imported into it everything except the corrupt table. Then I imported the corrupt table, and at first it appeared I might be in the clear. The offending record was still there with “1.1E+09” in the primary field, but I was able to select and delete the record! That’s good, but…. When I tried to add a new field (to rebuild the old record), Access put “1.1E+09” as the next record in the primary field!! I don’t get it. What do those letters and numbers mean?

Note: Just now I tried again. This time Access put the numbers 1072693251 into the primary key field!!! The next line jumps up by one (…3252) Somebody help!!

Give it to me straight, doctor…am I going to have to rebuild this table from scratch?

Reply | Quote

A couple of things. Try rebuilding the structure of the offending table in the new db then import from the old db and/or export from old db to an ASCII text file then import into new. If there is a way you could exclude the offending record from the export that would be desireable, i.e. use a query as the source for the export.

Reply | Quote

Delete the record with the ugly autonumber and compact the db. It should reset the autonumber to 1 larger than the last record.

Reply | Quote

Whew! It worked! Thanks, guys!

One valuable lesson out of all this nightmare: Keep a good backup copy at all times!

Reply | Quote

[indent]

---

One valuable lesson out of all this nightmare: Keep a good backup copy at all times!

---

[/indent]

At least it wasn’t a horrendously expensive lesson. Only 1 record and what a couple of hours?

Glad it all worked out for you.

Reply | Quote

Hansv wrote:
[indent]

---

    For i = 0 To lstDocuments.ListCount - 1
        If lstDocuments.Selected(i) Then
            wrdApp.PrintOut FileName:=strPath & lstDocuments.ItemData(i), Copies:=Me.txtCopies
        End If
    Next i

---

[/indent]
Then Lucas wrote:
[indent]

---

Here are the differences:

Document number and name as they appear in lstDocuments: [first field] 202 [second field] A Class Divided
Same document (handout) as it appears in the the actual directory: 202 – A Class Divided.doc

---

[/indent]

IF the documents are all named “# – Course Title.doc” then you could do something like:

    For i = 0 To lstDocuments.ListCount - 1
        If lstDocuments.Selected(i) Then
            strFileName = lstDocument.column(0,i) & " - " & _
                lstDocument.column(1,i) & ".doc"
            wrdApp.PrintOut FileName:=strPath & strFile, Copies:=Me.txtCopies
        End If
    Next i

The changes I made are in red. Don’t forget to change the column numbers to reflect your actual columns.

Reply | Quote

Thank you, Bryan. I invite your attention to my panic message above, in hopes that you will know how to fix for this “little problem.”

But getting back to your reply: If I understand your code, I would not need to add another field to the table, eh? I hasten to add that we have not been entirely consistent in naming our documents. Most (but not all) appear thusly: “202 – A Class Divided.doc.” Note the spaces before and after the hyphen! Will a file name like this lead to trouble? Should I consider reconstructing the file names?

Also, when you refer to Column 0, Column 1 and so forth, you mean the columns as they appear in the list box on the form, right? Thanks very much for your contribution!

Reply | Quote

[indent]

---

But getting back to your reply: If I understand your code, I would not need to add another field to the table, eh? I hasten to add that we have not been entirely consistent in naming our documents. Most (but not all) appear thusly: “202 – A Class Divided.doc.” Note the spaces before and after the hyphen! Will a file name like this lead to trouble? Should I consider reconstructing the file names?

---

[/indent]
Correct, you would NOT need another column. However, if the naming “standards” aren’t adheered to 100%, then it won’t work for that particular document. Which, if you trap for errors, will give you a chance to fix the misnamed files or courses. The spaces shouldn’t cause any more trouble in code than they do in Wondows.
[indent]

---

Also, when you refer to Column 0, Column 1 and so forth, you mean the columns as they appear in the list box on the form, right? Thanks very much for your contribution!

---

[/indent]
Correct.

And you are welcome.

Reply | Quote