Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Using VBA to print a report and Peripheral documents as one report

    Ok guys - I am here once in a while and am a pretty good developer - but I have got brain freeze on how to do the following

    I have a record say "A"

    When i click a button i see that report in print preview. Now here are some of the things i went to do.

    There are several document locations stored in a table that are supporting documents for this record.

    the field in tblDocs is called Location and the data is stored as :

    c:\myfolder\test.docx
    c:\myfolder\Test.pdf
    c:\myfolder\test.xlsx

    Ultimately when i bring the report up in preview mode I would like to see the 3 docs behind the report as if in page order - if i cant i want to print the report and the 3 docs follow that report to the printer.

    I am very good at vba - but cant figure out how to bring the docs in preview mode with the report and have drained the brain so much i cant focus on printing them out with the report - any help would be great.
    Dale Houston, TX

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    well, this is a bit old school, but assuming the computer you are running this on has all the necessary file associations setup, you can use the shell command to invoke the file (as if you "double-clicked" the file in explorer). For most file types, the default action is for the owning application to open to document.

    Lot's of good google fodder on this. Basic syntax is
    Code:
    Shell("notepad C:\file.txt")
    you can also be a little fancier and use some atrocious (but very useful) code to derive the parent application from the registry, invoke the application as a com object and open the file (or do anything else with it) through automation. That's a lot of coding and debugging, however. I'd go for the first option. For code on this, search the "Archive" - several of us have posted a lot of code to this and other "seemingly basic" functions.

    I suppose a third option is to dynamically build the report with your target access report as a sub report and then add the appropriate active x controls corresponding to each other related document. This is also a bit of coding, but probably the most elegant.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    resolved

    Well i agree with the active x control on report - but theree are multiple users and not each has the dll's or active x controls registered to their machines so i took two approaches

    If the user wants to review one doc at a time I load a form that places the document name in a hyperlink control. There are 50 hidden controls like this with each one only being visible if there is a docname to set as it's value associated with my master record (job card number)

    On Error Resume Next
    x = 1
    Set rst = db.OpenRecordset("select filename from tbldrawings " _
    & "where imgid = " & "'" & Me.JobCardNumber & "'", dbOpenDynaset)
    If Not rst.EOF Then
    rst.MoveFirst
    Do Until rst.EOF
    Me("text" & x).Visible = True
    Me("text" & x).Value = rst!Filename
    rst.MoveNext
    x = x + 1
    Loop
    End If

    Then i wrote a function that when any of these controls with file names were clicked I launched the application and doc via a hyperlink

    so then they could review and print as needed.

    Dim ctl As String, strlocation As String
    ctl = Screen.ActiveControl.Name
    strlocation = Nz(DLookup("[drawing_location]", "tbldrawings", "imgid= " & "'" & Me.JobCardNumber & "'" & "and filename = " & "'" & Me(ctl).Value & "'"), "NA")
    If strlocation <> "NA" Then
    Me(ctl).Value = "#" & strlocation & "#"
    GoTo line25
    Else
    strlocation = Nz(DLookup("[imgpath1]", "tblpdf_images", "imgid= " & Me.jobcardid & " and imgName = " & "'" & Me(ctl).Value & "'"), 0)
    If strlocation <> "0" Then
    Me(ctl).Value = "#" & strlocation & "#"
    Text223 = "#" & strlocation & "#"
    GoTo line25
    End If
    End If
    line25: Exit Function

    That took care of a "thumbnail" approach to pick and choose which docs but to print all the docs whether thay were excel, word, pdf, jpg I did use the shell command but i used the full robust API

    Private Declare Function apiShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
    As Long


    Option Compare Database
    Option Explicit



    and the code to print all docs regardless of extension (since the fully "equipped" shell api knows the associatiion of each file and therefore i do not have to supply it:

    Set rst = db.OpenRecordset("select Drawing_Location, filename from tbldrawings " _
    & "where imgid = " & "'" & Me.Job_Card_Number & "'", dbOpenDynaset)

    If Not rst.EOF Then
    rst.MoveFirst
    Do Until rst.EOF
    strSource = rst!Drawing_Location
    strext = right(rst!Filename, 4)
    fs.CopyFile rst!Drawing_Location, "c:\mytest" & strext, True
    lngreturn = apiShellExecute(hWndAccessApp, "print", _
    "c:\mytest" & strext, vbNullString, vbNullString, 0)
    rst.MoveNext
    Loop
    End If

    and that prints any doc regardless of extension or association.
    Dale Houston, TX

Posting Permissions

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