Printing Help! Is printing more than one record every once in a while...

[balderman]

Someone in this group helped me with the code in a button to print a report and print only one record...it works most of the time but all of a sudden now, when the print dialog comes up that says "Printing 1 page...." It will start counting up and says it is going to print more than one page and we have to cancel it, close out the database and open it back up to get it to print only one record. It is doing this even though in the print preview there is only one record showing and it is filtered to show only one record. Does anyone know what is causing this and how to fix it???

[Minty]

What is the code and how is it called?

[balderman]

What is the code and how is it called?

Private Sub GenerateWorkOrder_Click()
Dim sWhere As String
Dim iMyID As Long

'tblWorkOrder Subform

iMyID = Me.[tblWorkOrder Subform]![Work Order No]
sWhere = "[Work Order No] = " & iMyID

DoCmd.OpenReport "rptWorkOrder", acViewPreview, , sWhere

End Sub

Private Sub TabCtl52_Click()

End Sub

What do you mean, how is it called? It is a button on a form that is clicked to bring up the report in print preview and then they click the print button from there.

[Minty]

Okay - that is nice and simple.
I assume that there is always a valid value in your subform work order field ?

The report can't be opened anywhere else by any chance can it ?
If it was already opened, unfiltered then the code would not reopen or refresh it.

[balderman]

No, the button is the only way we run the report and me and one other person has used it so far and we do not both run the database at the same time. It was working perfectly and then once I added some customer data into the database and we started using it, it would start doing that. And it doesn't happen every time, just every once in a while.

And yes there is always a valid work order number because it's an autonumber and numbers the work order every time we create a new one

[Minty]

That sounds very odd. It may have something to do with the ID being on a sub form.

Add a the code in red below and next time it plays up open the vba code window (Press Alt+F11) ,
then look in the immediate window (Press Ctrl+G) to see what it thinks sWhere was at the time.

Private Sub GenerateWorkOrder_Click()
Dim sWhere As String
Dim iMyID As Long

'tblWorkOrder Subform

iMyID = Me.[tblWorkOrder Subform]![Work Order No]
sWhere = "[Work Order No] = " & iMyID

Debug.Print sWhere
DoCmd.OpenReport "rptWorkOrder", acViewPreview, , sWhere

End Sub

[balderman]

ok, thank you!

[balderman]

Ok I did what you said above and this is what I got....To me that means that it is going to only one record like it is supposed to, so why is it that the print pop-up box will saying printing page 1...2..3..4..5..and so on and keep going unless I hit cancel?

[aytee111]

That might just be the way the printer is set up, maybe it only prints one page after all that! Use something like PdfCreator and see how many pages print.

You can also try filtering the report using the record source instead. In the query for the record source, put the [Work Order No] criteria to =Forms!frmCustomer![tblWorkOrder subfrm]![Work Order No].

(PS Spaces in field names is very bad!)

[balderman]

You can also try filtering the report using the record source instead. In the query for the record source, put the [Work Order No] criteria to =Forms!frmCustomer![tblWorkOrder subfrm]![Work Order No].

If I do this, will it effect the coding behind the button?

[aytee111]

Yes - you will open the report without a filter.

[balderman]

OK, I did this and it is still happening....must be a printer issue then...I am gonna do some googling on that. Thanks for the help! It's just a PITA to have to close out of it all, open it back up, search for the customer and try to reprint after every time we do a work order....hopefully I can find a solution

[aytee111]

Can you print directly from the form, or do you have to go thru preview first? Worth a try.

[balderman]

Well that is how the button is set up, to go to print preview, and I didn't do it someone did it for me so IDK how to change it and I am afraid to mess with it and mess the whole thing up

[aytee111]

Change the command to
DoCmd.OpenReport "..",acPrint (or something like that, VBA will provide the correct syntax)
That will print directly to the printer without first going thru print preview.