Print query (1 Viewer)

[kidrobot]

Whats the code to print a query from VBA or even in a Macro? Can't find the solution on google. I put this in the VBA section but no one seemed to know.

 

[Rabbie]

This forum doesn't like people who double post.

 

[KenHigg]

Just curious - Why do you not want to put the data in a report to print?

 

[raskew]

Obviously, a report would normally be the way to go.

Still, it's an interesting question. It can be done, in a roundabout way, by creating a desktop shortcut to the query, then right-clicking on the shortcut and selecting Print. Haven't yet found a more direct way to accomplish it, nor found a way to programatically create, or simulate, a desktop shortcut.

Bob

 

[kidrobot]

This forum doesn't like people who double post.

sorry no one replied for like 4 hours to my previous post so i figured no one knew.. I found it more awkward that i couldn't find the answer on google.

 

[kidrobot]

Just curious - Why do you not want to put the data in a report to print?

lol... my manager told me his 2003 database does this and it isn't working in 2007. i thought this would be simple, i guess i was wrong.

 

[kidrobot]

Obviously, a report would normally be the way to go.

Still, it's an interesting question. It can be done, in a roundabout way, by creating a desktop shortcut to the query, then right-clicking on the shortcut and selecting Print. Haven't yet found a more direct way to accomplish it, nor found a way to programatically create, or simulate, a desktop shortcut.

Bob

weird that you cant directly print a query from a macro or vba... thanks for you help bob.

 

[raskew]

OK - back to the drawing board. If you have a saved query (in both A97 and A2003) you can go the database window, right-click on the query and select either Print or Print Preview (to just see it as it would be printed).

Nonetheless, still would be interesting to know a method whereby, if you'd created an on-the-fly query, probably never to be used again, you could print the output just for reference, or whatever.

I could see a use for this in the procedure I posted recently (Search on Multiple Parameters) that allows the user to specify any table/query, specify the applicable field, and then specify any number of parameters that may be contained in the field. It produces/displays a querydef corresponding to the input data. It'd be nice have the capability to click a button and print the resulting output without further ado.

Bob

 

[raskew]

Alright! Figured out a method.

1) In preceding code, have populated strSQL, e.g.:

strSQL = "Select [customers].* From [customers] WHERE inStr([companyname],'grocer')>0 OR inStr([companyname],'market')>0 OR inStr([companyname],'store')>0;"

2) The next command creates the querydef:

'Create QueryDef
Set qd = CurrentDb.CreateQueryDef("qryTemp", strSQL)

3) Then this opens the query in Print Preview mode. User may then view the results and, if desired, right-click on the displayed output and select Print.

'Open / view new query
docmd.OpenQuery qd.name, acViewPreview, acReadOnly

4) ...and finally,

'Delete QueryDef since this is just an example
CurrentDb.QueryDefs.Delete qd.name

*************************************************

All done programatically.

Bob

 

[kidrobot]

Thanks for the help. Is there a way to make the query automatically view landscaped?

 

[raskew]

Is there a way to make the query automatically view landscaped?

With your stated requirements, you're getting closer and closer to a formatted report.

re my previous post:

3) Then this opens the query in Print Preview mode. User may then view the results and, if desired, right-click on the displayed output and select Print.

'Open / view new query
docmd.OpenQuery qd.name, acViewPreview, acReadOnly

...the user may then right-click on the displayed output and select Page Setup, which then provides the option to select Portrait (the default) or Landscape.

As far as having it occur automatically, think you just may have exceeded the boundaries. If someone has a programatic solution, I'd love to see it.

Best Wishes - Bob

 

[KenHigg]

Bob, While that's a creative solution I'm still thinking by the time you do all that you could have a report - Or have I missed something?

 

[raskew]

Ken -

Referring back to http://www.access-programmers.co.uk/forums/showthread.php?t=154686, which I've used as my example in this thread...

This is a wham-bam operation. Select 'any' table/query, 'any' field in the specified table/query, and then pump in a bunch of possible parameters. Your output occurs! It takes longer to explain than to execute.

If, after reviewing the output I, for one, may not want to screw-around with a report. Just want to print the output for future reference. The solution I provided does that, with virtually no delay (i.e. <= 2 mouse-clicks). Problem being, it doesn't have a whole lot of 'automatic' options, e.g. print in Landscape. Added: Remember, you don't necessarily know the data/type (or the length) of the field you've entered (text, date, memo, etc) But, if you can live with 2 mouse-clicks, could you live with 4 (which is what it'd take to turn your print-out from portrait to landscape). If not, that's what reports are for.

I'm the first to agree that the a formatted report is the way to go, but for an on-the-fly solution, think this probably works as well, if not better.

Best Wishes - Bob

 

[KenHigg]

Thanks Bob. I'll have to tinker with that when I get a few minutes.