Print off query results?

[Ajz1971]

Hi There,
I’m not sure if I’m in the right section here? But what I’m looking for is a way to do the following…


I have a query that gives me a list of Linked PDF Files (C:/Policies/Policy1.PDF…. C:/Policies/Policy2.PDF etc…) what I would like to do is be able to print off each document using VBA code that I can attach to the on click of a button…Is this possible?



Thanks in advance for any feedback

 

[XelaIrodavlas]

I recently had a go at something very similar to this, I quickly found it became very convoluted so I hope you're good with VBA!

First let's assume your form is called MyForm, and you're using a combo box (MyComboBox) based on the query you mentioned to select individual PDF's related to the current record (you could print for each record in the combo box if preferred, but I wont go into that right now)

You would then have your command button with an on click event. For me, solving this meant opening the PDF using internet explorer (as most company machines should have it installed) and printing through that.

My on click code:

 Dim prt As Printer
'Find, Open and Print the PDF
Dim vLink As String
Dim MyBrowser As SHDocVw.InternetExplorer
Set MyBrowser = New SHDocVw.InternetExplorer
 If Len(Forms!MyForm!MyComboBox & vbNullString) <> 0 Then         'Check a selection has been made.
    vLink = HyperlinkPart(Forms!MyForm!MyComboBox, acAddress)
    'To display the file:
    'MyBrowser.Visible = True
    'MyBrowser.Navigate vLink
    'To print it:
    ExecuteFile vLink, PrintFile
Else
    MsgBox "Error finding PDF: " & Forms!MyForm!MyComboBox
End If

You will also need to copy the ExecuteFile Function in a separate Macro as preferred.

Function ExecuteFile(fileName As String, action As actionType)
Dim strDefaultPrinter As String
Dim objPrinter As Object
Dim sAction As String
Dim vPrinter As String
 ' get current default printer.
strDefaultPrinter = Application.Printer.DeviceName
vPrinter = strDefaultPrinter 'You could change this if you want to use a specific printer. I use a separate combo box populated on open, but have simplified for this example.
  
 Select Case action
Case 0 ' openfile
  sAction = "Open"
Case 1 ' printfile
  sAction = "PrintTo"
  Set objPrinter = CreateObject("WScript.Network")
  ' switch to printer of your choice:
  objPrinter.SetDefaultPrinter (vPrinter)
End Select
 'Print it:
 ShellExecute 0, sAction, fileName, """" & vPrinter & """", "", SW_SHOWNORMAL               
  ' Reset defaults
objPrinter.SetDefaultPrinter (strDefaultPrinter)
 End Function

I made a lot of tweaks to the code for this (thanks originally to MS and the Access World users) but I think this still includes all the essential components, sorry if I missed anything out! Good luck!

 

[Ajz1971]

Hi Alex...big thanks for the prompt reply


The code you gave me opens the pdf in the webrowser viewer you stated which is great. Im not sure on how I go about executing the second part of the code though?



…does the code given print off all the pdf documents in the query?
I think I should have made it more clear in my post.


All I want is the user to be able to click the button and print off all the pdf documents (stored as file paths) in the query in one go (if that’s possible?)



So in my basic understanding it goes like… user presses button, this loads the query (Policy Query…. there’s no need to view or select the pdf’s) and prints off all pdf files that are listed in the (policy path links) column of the query.


Thanks again for your help

 

[XelaIrodavlas]

Ah sorry I think I misunderstood slightly.

That code works off the basis that it's one PDF but you could add a loop for each record in the query.

Not entirely sure how you'd go about that at this stage but let me look into it

 

[Ajz1971]

Thanks again Alex... Any help will be gratefully appreciated

 

[XelaIrodavlas]

Ok just thinking out loud, but I think something like this will do it.

Private Sub [COLOR=red]Command0[/COLOR]_Click()

 Dim objPrinter As Object
Dim sAction As String
Dim vPrinter As String
Dim vLink As String
Dim MyBrowser As SHDocVw.InternetExplorer
Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset

  Set MyBrowser = New SHDocVw.InternetExplorer
Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("[COLOR=red]NameOfYourQuery[/COLOR]", dbOpenDynaset)
 Do While Not rsQuery.EOF
    vLink = HyperlinkPart(rsQuery![COLOR=red]LinkedField[/COLOR], acAddress)
    vPrinter = Application.Printer.DeviceName
    sAction = "PrintTo"
    Set objPrinter = CreateObject("WScript.Network")
    objPrinter.SetDefaultPrinter (vPrinter)
    ShellExecute 0, sAction, fileName, """" & vPrinter & """", "", SW_SHOWNORMAL
rsQuery.MoveNext
Loop
End Sub

You should be able to run that just off the on click but I haven't had a chance to properly test it... will keep looking though

 

[Ajz1971]

Thanks Alex... I'm out of the office tomoz so wont be able to give it a go until thurs

Do i need to change anything to the names of my files (query etc..), and where will this code be run, I know its on the on click of the button ...I'm guessing it's the button on the form with the combobox? ...sorry if i sound a bit dull lol! ..in my defence, I'm a newbie giving it a go and whats (top 2 wpars)?

thanks again!!

 

[XelaIrodavlas]

Hiya,

I've tweaked the code above to show where you need to change names for your database.

It might be easier if you could attach a stripped back version of your database. Nothing sensitive just the basics so I can see exactly what we're dealing with

Command0 is your button - but that should appear automatically when you find the onclick event (properties > events > find the ... next to On Click)

NameOfYourQuery is the name as saved in objects explorer, I assume you haven't written the SQL into code but let me know if you have coz we will need to rearrange slightly.

LinkField is the name of the column containing the PDF address in your query.

Oh and the 'top 2' thing was just the name of the query I drummed up as part of the example.. you don't need it.

I will test more tomorrow, I have people nagging at me for other things atm

 

[Ajz1971]

Good morning Alex

At the moment I am unable to attatch anything as I have not had more then ten posts ...getting there though lol!

I have done as you have instructed and the code runs well until it hits 'Shellexecute 0' ...it gets a compile error at that point (shellexecute compile error sub or function not defined)?

I bet your expertise is inconstant demand, so just reply whenever you can ...By the way! your a saint for taking the time out to help with this