Print a filtered report 1

[MAPetry]

How do I filter a report to only print current record?
I have a form that I want to print info for the current record on screen. I know to add a command buttom but need direction on code on how to limit report to the current record.
Thanks,
Mary Ann

 

[Mich2too]

The easiest way I have found to do this is by writing a quick macro. Choose Macros from the Objects bar. Then choose New Macro from the database tool bar. When in the Macro Window, choose View, then choose Macros Names. Now you are ready to create your macro. On the first Macro Name line, enter a short but sweet name for your macro. Suggest using... PrintRecord...as your macro name. Press the tab key to move to the Action field and choose Print Out from the pulldown list or simply start to type the words into the field and Access will fill it in for you. Press tab key to move to the Comment field. This field lets you create a note to yourself what that macro does. Suggest....Prints current record of [your form's name]. Then press the F6 key to move to the arguments section. Set the Print Range to SELECTION. Ignore the Page From and Page To lines. Set your Print Quality, Copies, and Collate Copies as you need. If you only need one print out of the record each time you press the button, leave these lines alone as well. Now close the macros window and save as prompted. Now go to your report in design view and add your Command button. I do not use the wizard when creating command buttons so I can't give you the step by steps for using the wizard. I put a command button where I want it then right click on it. Choose properties. Click on Event Tab. Go to On Click line and use the down arrow button to choose your macro name from the list. It should be the only one if you do not have any other macros. If you have other macros, you will be looking for a macro name Main Macros.PrintRecord. You can also simply type the complete name in, but for accuracy I recommend using the pulldown list to choose the proper macro. Click on Format tab. Go to Caption line and type in Print Record. This will put the words Print Record on the face of your command button. Go take your form for a test drive by changing back to View. Don't forget to save your work.

 

[JerryDennison]

In deference to Mich's response. Please avoid using macros. You can very easily modify the code behind your button to open your report with the current record on your form.

If you haven't already, use the button wizard to create a button to open your report. Open the button's properties and click on the '...'s next to the On Click property. You will see something like the following:

Private Sub cmdDailyDepositReport_Click()
On Error GoTo Err_cmdDailyDepositReport_Click

Dim stDocName As String

stDocName = "rptEmployerDepositsPostedByDate"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdDailyDepositReport_Click:
Exit Sub

Err_cmdDailyDepositReport_Click:
MsgBox Err.Description
Resume Exit_cmdDailyDepositReport_Click

End Sub

Change the DoCmd line to something like the following:

DoCmd.OpenReport stDocName, acPreview, , "[ID] = " & Chr(39) & Me.ID & Chr(39)

Substitute your actual primary key for the placeholders I've given.

 

[Mich2too]

Jerry,
Thanks for a different option. I can see that learning more about VBA is in my future. Guess being a new user to Tek-Tips I probably should have kept my mouth shut for awhile. More input on why to stay away from macros please?? People I have learned from are diehard macro users. Time to learn more.

 

[MAPetry]

Thank both of you very much for your input. Being unfamiliar with macros I went the VB route and it works like a champ. Thanks..

 

[OverDrive]

Jerry, I am kind of new to the VB world of things, what did you mean by "Substitute your actual primary key for the placeholders I've given". I have tried everything I know substituting my Priamry key (which happens to be "ID&quot in the VB description you gave.

My report name is "Account Inquiry" and my form name is "Frontend", and my Primary Key I use is "ID". How with that information would I properly write the VB code to make it print correctly the current form in my report?

Thanks for the help . . .
OverDrive

 

[JerryDennison]

Based on what you've given me you need the following(it doesn't matter what the name of the form is, just make sure the underlying recordset of the form includes the [ID] field):

DoCmd.OpenReport "Account Inquiry", acPreview, , "[ID] = " & Me.ID

NOTE: if the ID field is numeric then use what is listed above, if it's text use the following:

DoCmd.OpenReport "Account Inquire", acPreview, , "[ID] = " & Chr(39) & Me.ID & Chr(39)

 

[OverDrive]

This is exactly what I have? I get the print preview, but it does not print it out for me? Am I going to have to do the print myself in print preview? If so, that is ok . . . but it would be nice if I could get it to print as well?

Thanks for the help . . .
OD~

Private Sub btn_openreport_Click()
On Error GoTo Err_btn_openreport_Click

Dim stDocName As String

stDocName = "Account Inquiry"
DoCmd.OpenReport "Account Inquiry", acPreview, , "[ID] = " & Me.ID


Exit_btn_openreport_Click:
Exit Sub

Err_btn_openreport_Click:
MsgBox Err.Description
Resume Exit_btn_openreport_Click

End Sub

 

[JerryDennison]

Change the acPreview to acNormal, it will then immediately print.

 

[OverDrive]

Man, you are the greatest!

Thank you so much for making my pain and suffering through that easy. I have been straining on that issue for about a week now, and you reflected it to me in 5 seconds. Thank you for your quick response and galant effort, I nominated you a few times for tipmaster of the week, I hope you get the Harley.

Again, thank you for your time, you helped me more than you know!

OD~

 

[JerryDennison]

Well, I'm honored. Thank you. Glad to be of service.

 

[JerryDennison]

To Mich,

Sorry I didn't answer your question sooner. As for not using macros. The biggest reason is any errors generated by macros are not trappable. Some of these errors can cause the database to close unexpectedly. There are times when the use of macros is unavoidable, fortunately these a few and far between.