By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Printing hyperlinked Word doc automatically with an Access report.

P: n/a
Hello, hope someone can help. I have a request to print word .doc check
lists with selected records in Access for surgery procedure cards. I
have hyperlinked the word doc's to a field in each record, but do not
know how to write the code to open the word doc and print it. I assume
this code should be put in the list box print button. As you can see, I
have two different reports that go out; one (or more) to Surgery, and
one (or more) to SPD (Sterile Processing).
Thanks for any help given.
David

Private Sub cmdPrtRpt_Click()
On Error GoTo Err_cmdPrtRpt_Click

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![List0]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You Must Select at least One Procedure in
the" & _
" List Box!", vbExclamation, "No Selection Made:")
Exit Sub
End If

Set DB = CurrentDb()
Set Q = DB.QueryDefs("qrySelProcCard")

Q.SQL = "SELECT [qryProcCard].* FROM qryProcCard Where
qryProcCard.LstBxAutoNbr In (" & Criteria & ");"
Q.Close

Dim stDocName As String

If cboCopies = 1 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
ElseIf cboCopies = 2 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal

ElseIf cboCopies = 3 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 4 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 5 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 6 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 7 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 8 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
End If

DoCmd.Close acForm, "frmSelProcCard", acSaveNo

DoCmd.OpenForm "frmSelProcCard", acNormal, "", "", acEdit, acNormal

Exit_cmdPrtRpt_Click:
Exit Sub

Err_cmdPrtRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrtRpt_Click
End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
David
I have a better solution to your code. Repeating the same command all the
time can cause problems when debugging errors. Try this

Dim intI as Integer
For intI = 1 To cboCopies
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
Next

7 lines of code instead of over 200

Here's even another solution:

Dim intI As Integer
intI = cboCopies
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acPreview
DoCmd.PrintOut , , , , intI
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acPreview
DoCmd.PrintOut , , , , intI

This uses the PrintOut method but I've opened your reports in preview mode
to prevent another copy from being printed.

With your main question to print a word doc, some one else might be able to
help you here.

Jeff
"David" <da****@mail.heartland-health.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hello, hope someone can help. I have a request to print word .doc check
lists with selected records in Access for surgery procedure cards. I
have hyperlinked the word doc's to a field in each record, but do not
know how to write the code to open the word doc and print it. I assume
this code should be put in the list box print button. As you can see, I
have two different reports that go out; one (or more) to Surgery, and
one (or more) to SPD (Sterile Processing).
Thanks for any help given.
David

Private Sub cmdPrtRpt_Click()
On Error GoTo Err_cmdPrtRpt_Click

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![List0]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You Must Select at least One Procedure in
the" & _
" List Box!", vbExclamation, "No Selection Made:")
Exit Sub
End If

Set DB = CurrentDb()
Set Q = DB.QueryDefs("qrySelProcCard")

Q.SQL = "SELECT [qryProcCard].* FROM qryProcCard Where
qryProcCard.LstBxAutoNbr In (" & Criteria & ");"
Q.Close

Dim stDocName As String

If cboCopies = 1 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
ElseIf cboCopies = 2 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal

ElseIf cboCopies = 3 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 4 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 5 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 6 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 7 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
ElseIf cboCopies = 8 Then
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardPrnt"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
stDocName = ""
stDocName = "rptProcCardSpdOnlyPrtToSpd"
DoCmd.OpenReport stDocName, acNormal
End If

DoCmd.Close acForm, "frmSelProcCard", acSaveNo

DoCmd.OpenForm "frmSelProcCard", acNormal, "", "", acEdit, acNormal

Exit_cmdPrtRpt_Click:
Exit Sub

Err_cmdPrtRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrtRpt_Click
End Sub

Nov 13 '05 #2

P: n/a
print word files from within Access. First, I would be inclined NOT to
store the names as hyperlinks, but as regular text. *Format* them as
hyperlinks if you want, though...

Second... the printing problem...
Automate Word. big deal.

Something like
Public Sub PrintWordDoc(ByVal strDoc As String)
Dim appWord As Word.Application
Dim docWord As Word.Document

If fIsAppRunning("Word") Then
appWord.Activate
Else
Set appWord = New Word.Application
End If

appWord.Documents.Open (strDoc)
appWord.Documents(strDoc).PrintOut

End Sub

Nov 13 '05 #3

P: n/a
Thanks Jeff, I will streamline that code.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.