Pulling only one record into a report

[jessicamosby]

All,



I have done this before, but just cannot remember how I did it ...so I hope someone with way more knowledge knows this off the top of his/her smart head!

I have a form and a subform created. From these two, I have created a report that populates with the information. However, I cannot remember the code, nor where to put it in order for the report to only pull whatever record is current in the form. Currently, the report will create a page for each record stored in the table.

I hope this makes sense and any help would be greatly, greatly, greatly appreciated (and I promise to write it down this time)!

Thanks so much!!!!

(Access 2007)

[ranman256]

docmd.openreport "rptMyrpt" ,acViewPreview,,"[recID]=" & IDnum

[ItsMe]

You can apply the filter to the report when you open it.

Something like this

Dim strReport as string
dim strWhere as string
strReport = "MyReportName"
strWhere = "[FieldPK] = " & Me!FieldPK
DoCmd.OpenReport "strReport", acViewPreview, , strWhere

[jessicamosby]

Do I put this in the code on the form or the report? I am thinking the form, but just wanted to double check....thanks so much!!!

[jessicamosby]

You can apply the filter to the report when you open it.

Something like this

Dim strReport as string
dim strWhere as string
strReport = "MyReportName"
strWhere = "[FieldPK] = " & Me!FieldPK
DoCmd.OpenReport "strReport", acViewPreview, , strWhere

Where would I put this? And thank you!!!

[ItsMe]

You could add a Command Button to your form and create a click event. Use the Command Button's property sheet. Under the Event tab, click the ellipses(...) next the field named "On Click". Select the option, Code Builder.

You will need to adjust the Report name and field names to match your application's needs.

strReport = "MyReportName"
strWhere = "[FieldPK] = " & Me!FieldPK

[jessicamosby]

You could add a Command Button to your form and create a click event. Use the Command Button's property sheet. Under the Event tab, click the ellipses(...) next the field named "On Click". Select the option, Code Builder.

You will need to adjust the Report name and field names to match your application's needs.

strReport = "MyReportName"
strWhere = "[FieldPK] = " & Me!FieldPK

I'm sorry. One last question. I understand changing the report name, but when you say adjust the field name, I am not sure which field to put. Would it be the one that links the main form and subform together?

[ItsMe]

It may be. There should be something, some value, that will determine how you filter the report. This is typically a field that is a Key value. Typically it would be the Primary Key or Foreign Key field.

This concept is similar to the concept of linking Main and Child links for subforms.

[jessicamosby]

Thank you so very much for your help!!! I really appreciate it!

[ItsMe]

Not a problem and welcome to the forum. We are all happy to help.