• 0

Print macro in MS acces 2003


Question

Hi,

I'm trying to repeat a print task from an invoice from msacces to multiple tray's. Please look in this tread for more info.

Any tips on how the code should like like? I think installing the printer 4 times and setting the tray's different and then print the invoice 4 times to the different printers should be the easiest option.

Zwinky

Link to comment
Share on other sites

Recommended Posts

  • 0

Ok, So i tried it in word and it was no problem at al ... I recorded a macro and it works but how can i do this in acces ? where can i record a macro in acces ?

Ths code from word look like:

ActivePrinter = "HP 4250 tray 1"

Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _

wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _

ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _

False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _

PrintZoomPaperHeight:=0

ActivePrinter = "HP 4250 TRAY2"

Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _

wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _

ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _

False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _

PrintZoomPaperHeight:=0

ActivePrinter = "HP 4250 TRAY3"

Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _

wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _

ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _

False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _

PrintZoomPaperHeight:=0

tia !

Link to comment
Share on other sites

  • 0

Hi, me again :)

I found the code from the print button in acces. Any thoughts on how I can insert the above code in this ?

This is the code:

Private Sub PrintFaktuur_Click()

On Error GoTo Err_PrintFaktuur_Click

Dim stDocName As String

stDocName = "Faktuur"

DoCmd.OpenReport stDocName, acPreview

Exit_PrintFaktuur_Click:

Exit Sub

Err_PrintFaktuur_Click:

MsgBox Err.Description

Resume Exit_PrintFaktuur_Click

End Sub

Zwinky

Link to comment
Share on other sites

  • 0

Hi,

So it looks like you have installed the printer 3 times right? With the names: HP 4250 tray 1, HP 4250 tray 2 and HP 4250 tray 3. If so then this 'should' work. But my VBA coding is very limited.

'Form name
Dim strFormName as String
strFormName = "Form Name" 'Put your form name here

'Open the report
DoCmd.OpenReport strFormName, acViewNormal

'Set the default printer to the first printer
Set Application.Printer = "HP 4250 tray 1"
'Print the report
DoCmd.PrintOut

'Set the default printer to the second printer
Set Application.Printer = "HP 4250 tray 2"
'Print the report
DoCmd.PrintOut

'Set the default printer to the third printer
Set Application.Printer = "HP 4250 tray 3"
'Print the report
DoCmd.PrintOut

'Close the report
DoCmd.Close acReport, strFormName, acSaveNo  'report name again!

Place the code in a button were ever you want to print the report from.

Now if any VBA program could check this over that would be great. But if not give it a go. I haven't tested it at all so there may be some problems. In-fact if it works first time I will be amazed. Lol

Good luck.

Link to comment
Share on other sites

  • 0
Hi,

So it looks like you have installed the printer 3 times right? With the names: HP 4250 tray 1, HP 4250 tray 2 and HP 4250 tray 3. If so then this 'should' work. But my VBA coding is very limited.

'Form name
Dim strFormName as String
strFormName = "Form Name" 'Put your form name here

'Open the report
DoCmd.OpenReport strFormName, acViewNormal

'Set the default printer to the first printer
Set Application.Printer = "HP 4250 tray 1"
'Print the report
DoCmd.PrintOut

'Set the default printer to the second printer
Set Application.Printer = "HP 4250 tray 2"
'Print the report
DoCmd.PrintOut

'Set the default printer to the third printer
Set Application.Printer = "HP 4250 tray 3"
'Print the report
DoCmd.PrintOut

'Close the report
DoCmd.Close acReport, strFormName, acSaveNo  'report name again!

Place the code in a button were ever you want to print the report from.

Now if any VBA program could check this over that would be great. But if not give it a go. I haven't tested it at all so there may be some problems. In-fact if it works first time I will be amazed. Lol

Good luck.

Hi,

thnxs for looking at it. I tried it to insert it by creating a button right clicking it and press and build event. When I try it I get an error "--------------------------- Microsoft Visual Basic --------------------------- Compile error: Object required ---------------------------OK Help ---------------------------

Any thoughts ?

Link to comment
Share on other sites

  • 0
Hi,

thnxs for looking at it. I tried it to insert it by creating a button right clicking it and press and build event. When I try it I get an error "--------------------------- Microsoft Visual Basic --------------------------- Compile error: Object required ---------------------------OK Help ---------------------------

Any thoughts ?

Does it highlight any line of code? You did replace the form name with the name of the form you are trying to print? Otherwise I don't really know.

As for were you put it, I 'think' that is right. I am used to 2007 now. I create a button, cancel the wizard that opens and go to 'Properties>Event>Click>Code Builder' (Which should open the VBA editor) and put the code in there.

If i get time sometime I will load up a VM and give this a try.

Link to comment
Share on other sites

  • 0
Does it highlight any line of code? You did replace the form name with the name of the form you are trying to print? Otherwise I don't really know.

As for were you put it, I 'think' that is right. I am used to 2007 now. I create a button, cancel the wizard that opens and go to 'Properties>Event>Click>Code Builder' (Which should open the VBA editor) and put the code in there.

If i get time sometime I will load up a VM and give this a try.

Hi,

I've made a screenshot. Could you take a looksie pls ?

Zwinky

post-292090-1262186773_thumb.jpg

post-292090-1262186792_thumb.jpg

Link to comment
Share on other sites

  • 0

Ok here is the revised code:

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 1")
'Print the report
DoCmd.PrintOut

'Set the default printer to the second printer
Set Application.Printer = Application.Printers("HP 4250 tray 2")
'Print the report
DoCmd.PrintOut

'Set the default printer to the third printer
Set Application.Printer = Application.Printers("HP 4250 tray 3")
'Print the report
DoCmd.PrintOut

'Close the report
DoCmd.Close acReport, strFormName, acSaveNo  'report name again!

Just make sure that the printers are listed in the Print menu with those names, and it 'SHOULD' print. Good luck!

Link to comment
Share on other sites

  • 0
'Close the report

DoCmd.Close acReport, strFormName, acSaveNo 'report name again![/code]

Just make sure that the printers are listed in the Print menu with those names, and it 'SHOULD' print. Good luck!

Hi,

thnx ! It prints now. It only ignores the tray selection in the printer drivers. It prints to the bypass tray. The printers print fine on the correct page when printing a windows testpage. Any thoughs ?

I also did not use the above code. When I did this It printed but got an error that an object was required.

Thnx alot for helping !

Link to comment
Share on other sites

  • 0

I have a feeling its not changing the printer. Try putting Tray 2 or 3 first in the list. I bet you it prints on HP 4250 tray 1. If you go to print manually which printer does it select. Is it changing the printer at all?

I will try it on mine.

Link to comment
Share on other sites

  • 0
I have a feeling its not changing the printer. Try putting Tray 2 or 3 first in the list. I bet you it prints on HP 4250 tray 1. If you go to print manually which printer does it select. Is it changing the printer at all?

I will try it on mine.

thats what I thought but I can see the printjobs enter in the correct que. and it ignores the tray selection on HP 4250 tray 1 also. When I print the document manually it prints from the correct tray.

Link to comment
Share on other sites

  • 0

Ok, after a few sheets of paper and 'Low Ink' I have redone the code and it seems to be working! Woot.

It looks like the reports automatically print when opened, well mine did.

Here is the new redone code:

'Set report name
Dim strReportName As String
strReportName = "rptEnquiry" 'Put your report name here

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 1")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 2")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 3")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

The PrintOut is still in there if it doesn't print when opened, just remove the '.

You need to use the 'report name' bit at top to set the report name. (I called it form before, oops)

Try it and see, and good luck with that. Its a nice bit of code, very useful.

Link to comment
Share on other sites

  • 0
Ok, after a few sheets of paper and 'Low Ink' I have redone the code and it seems to be working! Woot.

It looks like the reports automatically print when opened, well mine did.

Here is the new redone code:

'Set report name
Dim strReportName As String
strReportName = "rptEnquiry" 'Put your report name here

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 1")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 2")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 3")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

The PrintOut is still in there if it doesn't print when opened, just remove the '.

You need to use the 'report name' bit at top to set the report name. (I called it form before, oops)

Try it and see, and good luck with that. Its a nice bit of code, very useful.

Still no go :( ... It prints fine but ignore's the tray selection in the driver.

Link to comment
Share on other sites

  • 0

So its printing to the different printers fine, but it won't print on the correct tray. If you print manually it prints on the correct tray. Could you take a screen-shot of 3 different printers, and the tray settings you have set to print on the correct tray. Thanks

Link to comment
Share on other sites

  • 0
So its printing to the different printers fine, but it won't print on the correct tray. If you print manually it prints on the correct tray. Could you take a screen-shot of 3 different printers, and the tray settings you have set to print on the correct tray. Thanks

Yep, its printing to the correct printers but ignores the tray settings. Even better ... I disabled all the other tray's in the drivers but it even ignores these settings to. Really at my withs end now ...

The screens like you asked are in attachment. Could this be driver related ? Can I set the tray in the code ? Maybe this is a solution ?

post-292090-1262205128.jpg

post-292090-1262205139_thumb.jpg

post-292090-1262205148_thumb.jpg

post-292090-1262205170_thumb.jpg

Link to comment
Share on other sites

  • 0

I personal don't change it there. Try going to 'Printing Shortcuts' and creating a new shortcut. Call it Tray 1 or something. Then set the paper source. You then apply the shortcut. See if that works.

Link to comment
Share on other sites

  • 0
I personal don't change it there. Try going to 'Printing Shortcuts' and creating a new shortcut. Call it Tray 1 or something. Then set the paper source. You then apply the shortcut. See if that works.

tried it and still no go. Keep the same. It ignores the tray settings

Link to comment
Share on other sites

  • 0
Link to comment
Share on other sites

  • 0

But isn't there a way to tell per printer with tray to pick ?

Link to comment
Share on other sites

  • 0

Ok looking at toddmcdermid blog, or more importantly the comments shows that this bit of code 'seems' to change the tray:

Set Application.Printer = Application.Printers("My Big Color Laser")
DoCmd.OpenReport "rptFieldReportnew", acViewPreview, , , acHidden
With Application.Reports("rptFieldReportnew").Printer
.ColorMode = acPRCMMonochrome
.Orientation = acPRORLandscape
.Duplex = acPRDPHorizontal
.PaperSize = acPRPS11x17
.PaperBin = acPRBNLower <--- This is the important bit.
End With
DoCmd.OpenReport "rptFieldReportnew", acNormal

But what I am thinking is if the printer configuration is saved in the report, why not have 3 reports? So you copy your report 3 times and set the printer settings different in each one? I know its cheating but it should work. If you really would like to use 1 report I will have to look in to using this code above. Let me know.

Thanks

Link to comment
Share on other sites

  • 0
Ok looking at toddmcdermid blog, or more importantly the comments shows that this bit of code 'seems' to change the tray:

Set Application.Printer = Application.Printers("My Big Color Laser")
DoCmd.OpenReport "rptFieldReportnew", acViewPreview, , , acHidden
With Application.Reports("rptFieldReportnew").Printer
.ColorMode = acPRCMMonochrome
.Orientation = acPRORLandscape
.Duplex = acPRDPHorizontal
.PaperSize = acPRPS11x17
.PaperBin = acPRBNLower <--- This is the important bit.
End With
DoCmd.OpenReport "rptFieldReportnew", acNormal

But what I am thinking is if the printer configuration is saved in the report, why not have 3 reports? So you copy your report 3 times and set the printer settings different in each one? I know its cheating but it should work. If you really would like to use 1 report I will have to look in to using this code above. Let me know.

Thanks

It doesn't mather if there are 3 reports to be honest. So if that's easiest then that's the way to go :) How can I do that ?

The code I using now is:

Private Sub PrintFaktuur_Click()
 On Error GoTo Err_PrintFaktuur_Click

	Dim stDocName As String

	stDocName = "Faktuur"
	DoCmd.OpenReport stDocName, acPreview

'Set report name
Dim strReportName As String
strReportName = "Faktuur" 'Put your report name here

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 1")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 2")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 3")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

Exit_PrintFaktuur_Click:
	Exit Sub

Err_PrintFaktuur_Click:
	MsgBox Err.Description
	Resume Exit_PrintFaktuur_Click

End Sub

thnx alot in advance for everybody's help !

Link to comment
Share on other sites

  • 0

Wooohooo :) It works for the invoice. I've used the code below. It now prints on tray 1,2,3 without any problems. Strangly thow I've inserted the same code for the "stockorders" and this time it wants to print to the bypass tray. Really annoying because basically it's the same code ...

The code for the invoices that works:

Private Sub PrintFaktuur_Click()
 On Error GoTo Err_PrintFaktuur_Click

	Dim stDocName As String

	stDocName = "Faktuur"
	DoCmd.OpenReport stDocName, acPreview

'Set report name
Dim strReportName As String
strReportName = "Faktuur" 'Put your report name here

Set Application.Printer = Application.Printers("HP 4250 tray 1")
DoCmd.OpenReport "Faktuur", acViewPreview, , , acHidden
With Application.Reports("Faktuur").Printer
.ColorMode = acPRCMMonochrome
.PaperBin = acPRBNLower
End With
DoCmd.OpenReport "Faktuur", acNormal

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 2")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 3")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

Exit_PrintFaktuur_Click:
	Exit Sub

Err_PrintFaktuur_Click:
	MsgBox Err.Description

The code for the stockorders that don't work:

Option Compare Database

Private Sub Form_Current()
	DoCmd.ApplyFilter , "Levering = True"
End Sub


Private Sub Form_Open(Cancel As Integer)
	DoCmd.ApplyFilter , "(Levering = True) and (Magazijnbon = False)"
End Sub

Private Sub PrintMagazijnbon_Click()
On Error GoTo Err_PrintMagazijnbon_Click

	Dim stDocName As String

	stDocName = "Magazijnbon"
	DoCmd.OpenReport stDocName, acPreview

'Set report name
Dim strReportName As String
strReportName = "Magazijnbon" 'Put your report name here

Set Application.Printer = Application.Printers("HP 4250 tray 1")
DoCmd.OpenReport "Magazijnbon", acViewPreview, , , acHidden
With Application.Reports("Magazijnbon").Printer
.ColorMode = acPRCMMonochrome
.PaperBin = acPRBNLower
End With
DoCmd.OpenReport "Magazijnbon", acNormal

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 2")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

'Set the default printer to the first printer
Set Application.Printer = Application.Printers("HP 4250 tray 3")
'Open the report
DoCmd.OpenReport strReportName, acViewNormal
'DoCmd.PrintOut - Only needed if you report doesn't print when opened, remove the '
DoCmd.Close acReport, strReportName, acSaveNo

Exit_PrintMagazijnbon_Click:
	Exit Sub

Err_PrintMagazijnbon_Click:
	MsgBox Err.Description
	Resume Exit_PrintMagazijnbon_Click

End Sub

Am I overlooking something ?

Link to comment
Share on other sites

  • 0

I really can't see anything there that wouldn't make it work. You could try checking the print settings on that report to make sure they match the invoice?

Link to comment
Share on other sites

  • 0

Well ... I tried to insert the working code into a back-up of the file. (not working on the actual production file offcourse) and it just won't work anymore. I've went over both files side by side (multi monitor setup) and compared them (code and page setup) and they are identical but just won't work.

I am willing to upload the files to you so maybe you can take a look both they are in dutch so I don't know if you can read them

thnx alot in advance for helping out ! This is something for a friend of mine and he is in some serious debt when this is working ;)

Link to comment
Share on other sites

  • 0

Yer you could upload them if you want, or email me. I will take a look, dutch might be a problem but code is code so that's fine :)

Have you tried putting the 'Magazijnbon' in to the first code? Then put the 'Faktuur' into the 2nd bit of code. That way we test both bits of code work and know its the report that is the problem.

And help is fine, I got a lot when I was building databases (Still not finished my main one, been working on that for over a year now). I always like to help!

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.