Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Print from list (2000/9.0.3821 SR1)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print from list (2000/9.0.3821 SR1)

    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. <LI>In any module, select Tools/References... and set a reference to the Microsoft Word 9.0 Object Library.
      <LI>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 Cocs. 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 = "Cocs"
      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

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print from list (2000/9.0.3821 SR1)

    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!

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print from list (2000/9.0.3821 SR1)

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

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print from list (2000/9.0.3821 SR1)

    Hansv wrote:
    <hr><pre> 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
    </pre>

    <hr>
    Then Lucas wrote:
    <hr>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
    <hr>

    IF the documents are all named "# - Course Title.doc" then you could do something like:

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


    The changes I made are in red. Don't forget to change the column numbers to reflect your actual columns.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print from list (2000/9.0.3821 SR1)

    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!

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print from list--TROUBLE!! (2000/9.0.3821 SR1)

    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!!!

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print from list--TROUBLE!! (2000/9.0.3821 SR1)

    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 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.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print from list (2000/9.0.3821 SR1)

    <hr>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?<hr>
    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.
    <hr>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!<hr>
    Correct.

    And you are welcome.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print from list--TROUBLE!! (2000/9.0.3821 SR1)

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

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print from list--TROUBLE!! (2000/9.0.3821 SR1)

    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?

  12. #12
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print from list--TROUBLE!! (2000/9.0.3821 SR1)

    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.

  13. #13
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print from list--TROUBLE!! (2000/9.0.3821 SR1)

    Delete the record with the ugly autonumber and compact the db. It should reset the autonumber to 1 larger than the last record.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print from list--TROUBLE!! (2000/9.0.3821 SR1)

    Whew! It worked! Thanks, guys! <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

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

  15. #15
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print from list--TROUBLE!! (2000/9.0.3821 SR1)

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

    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.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •