Setting report to print one record only

[gnarpeggio]

Hello,

I have quick question about a task regarding reports. I've placed a "Print Invoice" button on my form that will print the report or invoice of each record.

I'm simply trying to set the DB to print only the form's current record when the button is clicked. When clicked, the DB is performing the default setting and printing a page for each record!!!

I'm sure that no VBA is required; I think I'm just passing by the appropriate option somewhere...

Any help on this is appreciated.

 

[boblarson]

What code or macro do you have for the click event of that button?

You would need to use something to limit it by:

DoCmd.OpenReport "myReportNameHere", acViewNormal, , "[InvoiceID] = " & Forms!YourFormName.YourControlWithInvoiceIDInIt.

 

[gnarpeggio]

The OnClick event for the button is set to it's default action when clicked (created with the command button wizard).

The names of my controls are as follows:

Invoice Number field - [Inv#] (This is the Primary Key, Autonumber data type)
Form Name - [Invoices]
Button Name - [PrintInv]
Report Name - [CapitolInv]

So, the code would be:

DoCmd.OpenReport "CapitolInv", acViewNormal, , "[Inv#] = " & Forms!Invoices.YourControlWithInvoiceIDInIt

I'm still puzzled on the YourControlWithInvoiceIDInIt string. Would it look like

[PrintInv]With[Inv#]InIt?

 

[boblarson]

YourControlWithInvoiceIDInIt

is simply saying to reference the text box, combo box, whatever which is displaying the Invoice number. So if your text box on the form is txtInvoice you would use

Forms!Invoices.txtInvoice

 

[gnarpeggio]

Bob, you are my hero haha! Works like a charm!

Thanks again

 

[boblarson]

 

[gnarpeggio]

Adding onto this thread...

I recently just changed my PC at my office and I'm trying to use the same button in Access. For some reason, a Save As dialog box is now appearing prompting me to save as an *.xps format. If I click cancel, the run-time error tells me that the OpenReport action was canceled.

I never altered my code from the example you provided. Is there a reason this dialog is now being forced?

Again, here's my code to limit the printed report:

Docmd.OpenReport "InHouse", acViewNormal, , "[JobNo] = " & Forms!InHouseForm.JobNo

Thanks!

 

[boblarson]

It would appear that you don't have the default printer set and so the default that is set it the XPS printer. Go to your printers and set the right one to be your default.

 

[gnarpeggio]

That's exactly what I did. I noticed that when the Now Printing dialog box was being displayed. Thanks for responding so quickly!

 

[eicarril]

Hey Guys,

I was hoping someone could tell me what I am doing wrong. I copied the code from the previous post. I click on the button and it does nothing.

Form name: DataEntry
Report: RateConfirmation
Key: Load Number

Private Sub cmdPrint_Click()

DoCmd.OpenReport "RateConfirmation", acViewNormal, , "[LoadNumber] = " & Forms!DataEntry.LoadNumber

End Sub

I appreciate any help anyone can provide.

Thanks,
Eddi

 

[boblarson]

If the code is on the form DataEntry, which it seems it is, then you would use

DoCmd.OpenReport "RateConfirmation", acViewPreview , , "[LoadNumber] = " & Me![Load Number]

It looks like you have a space in the name but in the code you took it out. Also, if the code is on the same form you use ME instead of the full form reference. And one more thing, I used the bang just in case you didn't have a control on the form named Load Number with the space.

And last - when you say it does nothing, does it not even open the report? And I would use acViewPreview instead as acViewNormal just prints it immediately without viewing.

 

[eicarril]

I have made all the changes that you recommended. I made a mistake when I typed in LoadNumber I removed all spaces to make sure no errors were made. I created a button with the wizard and it still shoots out multiple pages. When I create a button without the wizard and select event procedure. I click on the button and it does nothing no error message.You are correct the button is on form DataEntry and LoadNumber is the Primary Key in DataEntry. Its been a long time since I've programmed in Access.

Thanks,
Eddi

 

[boblarson]

Are you using Access 2007 or 2010? If so, you need to enable code to run. See here for how to do it:

2007

2010

 

[eicarril]

You have to be kidding me. I have been working on this all day. Thanks so much for the help on this. I had a feeling it had something to do with 2007. Again Thanks so much for your help.

Eddi Carrillo

 

[eicarril]

Hello Bob,

Not sure if you are still looking at this thread. But if you are I would like to see if you can help me with one more problem I am having. I am trying to save the current record on a report to PDF. Is there a way to automate this process. I currently have this code but am trying to figure out a way to save to pdf and use the key as the file name.

Thanks,
Eddi Carrillo


Private Sub CmdSendtoFile_Click()

Dim strDocName As String
Dim strWhere As String
strDocName = "Rate Confirmation"
strWhere = "[Load]=" & Me!Load
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub