Printing out multiple files from a form

  • Thread starter Thread starter Brian Beck
  • Start date Start date
B

Brian Beck

I have a table, tDistrictSub, in Access 2003 that contains various pieces of
information. The relevant fields in this table for this problem are:

DistrictIncidentNum - the pk (always a 9-digit number, then a dash, followed
by another 9 digit number)
DistrictIncidentID - the 9-digit number in front of the dash from above

I also have a table, tAttachments, that has the following fields:

AttachmentID - the pk (autogenerated)
DistrictIncidentNum - linked to tDistrictSub
Attachment - contains the filename of an attachment.

I've created a form, fDistrictSub, that lists information for any given
DistrictIncidentNum. What I want to do, is add a command button to this
form that will allow me to print out all the attachments for the record
currently showing in fDistrictSub.

I created a query, qAttachments, to return all the values for
tAttachments.Attachment where the value of fDistSub.DistrictIncidentNum is
equal to tAttachments.DistrictIncidentNum. I've also placed a subform,
fAttachments, in my fDistrictSub to show me attachments there are for the
currently selected record.

My problem comes in trying to write some VBA code for the command button's
onClick event. I know how to get the files to print out, but I don't know
how I can get the various attachment filenames moved into variables so that
I can then grab the extension from the end of the name and determine what
program needs to be used to open and print out the attachment. Plus, since
there can be any number of attachments for a given DistrictIncidentNum, I
need some way of determining how many attachments I need to print.

Any ideas?

-Brian Beck
 
Brian Beck said:
I have a table, tDistrictSub, in Access 2003 that contains various pieces
of information. The relevant fields in this table for this problem are:

DistrictIncidentNum - the pk (always a 9-digit number, then a dash,
followed by another 9 digit number)
DistrictIncidentID - the 9-digit number in front of the dash from above

I also have a table, tAttachments, that has the following fields:

AttachmentID - the pk (autogenerated)
DistrictIncidentNum - linked to tDistrictSub
Attachment - contains the filename of an attachment.

I've created a form, fDistrictSub, that lists information for any given
DistrictIncidentNum. What I want to do, is add a command button to this
form that will allow me to print out all the attachments for the record
currently showing in fDistrictSub.

I created a query, qAttachments, to return all the values for
tAttachments.Attachment where the value of fDistSub.DistrictIncidentNum is
equal to tAttachments.DistrictIncidentNum. I've also placed a subform,
fAttachments, in my fDistrictSub to show me attachments there are for the
currently selected record.

My problem comes in trying to write some VBA code for the command button's
onClick event. I know how to get the files to print out, but I don't know
how I can get the various attachment filenames moved into variables so
that I can then grab the extension from the end of the name and determine
what program needs to be used to open and print out the attachment. Plus,
since there can be any number of attachments for a given
DistrictIncidentNum, I need some way of determining how many attachments I
need to print.

Any ideas?

-Brian Beck
Click to expand...

Populate a recordset from the query, and you can use the Count property to
determine how many records there are. Loop through the recordset and use the
Right function to determine the file's extension:

ext = Right(rs![filename], 3)

However, you don't necessarily need to know what program is used to open
each file. You can use Application.FollowHyperlink with the full file name,
and Windows will use the standard file associations to open it with the
correct program.

An unrelated note: since your field DistrictIncidentNum already contains
DistrictIncidentID, you don't need to save the ID part as a separate field
in the table. You can always extract it any time you want from
DistrictIncidentNum using the Left function:

DistrictIncidentID = Left(DistrictIncidentNum, 9)

Carl Rapson
 
I don't seem to be getting anywhere with this. Can someone take a look at
the code below and tell me where I'm going wrong?

Private Sub cmdPrintAttach_Click()
Dim Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim sSQL As String

Set Cnn = CurrentProject.Connection
Set Rst = New ADODB.Recordset
sSQL1 = "SELECT tAttachments.Attachment" & vbCrLf & _
"FROM tAttachments" & vbCrLf & _
"WHERE tAttachments.DistrictIncidentNum =" &
[Forms]![fDistrictSub]![DistrictIncidentNum]

Rst.Open sSQL, Cnn

Do While Not Rst.EOF
Debug.Print Rst(0)
Rst.MoveNext
Loop

Set Rst = Nothing
Set Cnn = Nothing

End Sub

-Brian Beck
 
While you're inserting unnecessary Carriage Return/Line Feeds into your
query, I believe you still need blanks between the parts of the query.

sSQL1 = "SELECT tAttachments.Attachment " & _
"FROM tAttachments " & _
"WHERE tAttachments.DistrictIncidentNum =" & _
[Forms]![fDistrictSub]![DistrictIncidentNum]

This assumes that DistrictIncidentNum is a numeric field, not a text field.
If it's text, that last line should be

Chr$(34) & [Forms]![fDistrictSub]![DistrictIncidentNum] & Chr$(34)

If that doesn't solve your problem, how about explaining what the problem
is? "I don't seem to be getting anywhere with this" doesn't give us much to
go by. Are you getting an error? If so, what's the error? If you're not
getting an error, what's the symptom of the problem?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Brian Beck said:
I don't seem to be getting anywhere with this. Can someone take a look at
the code below and tell me where I'm going wrong?

Private Sub cmdPrintAttach_Click()
Dim Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim sSQL As String

Set Cnn = CurrentProject.Connection
Set Rst = New ADODB.Recordset
sSQL1 = "SELECT tAttachments.Attachment" & vbCrLf & _
"FROM tAttachments" & vbCrLf & _
"WHERE tAttachments.DistrictIncidentNum =" &
[Forms]![fDistrictSub]![DistrictIncidentNum]

Rst.Open sSQL, Cnn

Do While Not Rst.EOF
Debug.Print Rst(0)
Rst.MoveNext
Loop

Set Rst = Nothing
Set Cnn = Nothing

End Sub

-Brian Beck
Carl Rapson said:
Populate a recordset from the query, and you can use the Count property
to determine how many records there are. Loop through the recordset and
use the Right function to determine the file's extension:

ext = Right(rs![filename], 3)

However, you don't necessarily need to know what program is used to open
each file. You can use Application.FollowHyperlink with the full file
name, and Windows will use the standard file associations to open it with
the correct program.

An unrelated note: since your field DistrictIncidentNum already contains
DistrictIncidentID, you don't need to save the ID part as a separate
field in the table. You can always extract it any time you want from
DistrictIncidentNum using the Left function:

DistrictIncidentID = Left(DistrictIncidentNum, 9)

Carl Rapson
Click to expand...
Click to expand...
 
The problem is what I had mentioned in my first post...trying to grab the
attachment filenames and placing them into a temp recordset. Your note
about DistrictIncidentNum needing quotes if it is a text field is exactly
what was causing me problems. The completed Subroutine is below:

Private Sub cmdPrintAttach_Click()
Dim Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim sSQL As String
Dim strCurrentAttachment As String
Dim strPath As String
Dim strName As String
Dim strIncidentID As String
Dim strExtension As String
Dim appWord As Word.Application
Dim docPrint As Word.Document

Set Cnn = CurrentProject.Connection
Set Rst = New ADODB.Recordset
strIncidentID = Me.DistrictIncidentID
strPath = "S:\Security\Email_Archives\Incident_Report_Attachments\" & _
strIncidentID & "-"

sSQL = "SELECT tAttachments.Attachment" & vbCrLf & _
"FROM tAttachments" & vbCrLf & _
"WHERE tAttachments.DistrictIncidentNum =" & _
Chr$(34) & [Forms]![fDistrictSub]![DistrictIncidentNum] &
Chr$(34)

Rst.Open sSQL, Cnn

Do While Not Rst.EOF
strCurrentAttachment = Rst(0)
strName = strPath & strCurrentAttachment
strExtension = Right(strCurrentAttachment, 3)
Select Case strExtension
Case "pdf"
Shell ("C:\Program Files\Adobe\Acrobat
7.0\Reader\AcroRd32.exe /t " & strName)

Case "doc"
Set appWord = New Word.Application

With appWord
Set docPrint = .Documents.Open(strName)
With docPrint
.PrintOut
.Close 0
End With
.Quit
End With

Case "txt"
Set appWord = New Word.Application

With appWord
Set docPrint = .Documents.Open(strName)
With docPrint
.PrintOut
.Close 0
End With
.Quit
End With

Case "tif"
Shell ("C:\Program Files\Common Files\Microsoft
Shared\MODI\11.0\mspview.exe " & strName)

Case "jpg"
Shell ("C:\Program Files\Microsoft Office\OFFICE11\OIS.EXE "
& strName)

Case "htm"
Application.FollowHyperlink (strName)

End Select

Rst.MoveNext
Loop

Set Rst = Nothing
Set Cnn = Nothing
Set docPrint = Nothing
Set appWord = Nothing

End Sub

Thanks for the help!
 
Back
Top