Printing report for current record 2

[mikeg8]

Hello everyone, this is probably another mind-numbingly simple question, but here goes...

I have an open form on screen displaying data from a single table and wish to print that data as a report rather than print the form. I guess I need to use a query to retrieve the record currently displayed on the form and then print the report based on that query. So far so good? OK, how do I get the query to return the currently displayed record? Presumably using the 'criteria' field thingy - but what to put in it?

Please keep any reply simple...because I am!

Mike

 

[hymn]

You could try

In the unique field on the form (lets say its called "home" )on a button

Private Sub Command113_Click()
On Error GoTo Err_Command113_Click

Dim stDocName As String

stDocName = "YourReportName"
DoCmd.OpenReport stDocName, acPreview , , "TableName.home= " & home

Exit_Command113_Click:
Exit Sub

Err_Command113_Click:
MsgBox Err.Description
Resume Exit_Command113_Click

End Sub

OR

in the reports query in the unique field("home&quotcriteria
type [forms]![YourFormName]![home]


Hope this helps
Hymn

 

[mikeg8]

Hymn

Thanks a lot. I'll try this as soon as I can.

Ta Da

Mike

 

[mikeg8]

Hymn

Have tried your suggestion, it worked, but it wasn't quite what I was after. Either I didn't phrase the question properly or I failed to understand your reply!

What I wanted was the report for the current form to display without asking the user to input the required unique field. I have tried a few things including:

[fieldname] = Forms![formname]![controlname on form] as suggested in VBA help for OpenReport Action but got multiple records in the report and yes, the unique field actually is unique! In my application, the unique field is called 'SpecimenID'.

 

[hymn]

OK Is your report source a Query?
if so in the query criteria of the field "SpecimenID" type
[forms]![YourFormName]![SpecimenID]

Thats for the query

To do it straight from the form try on the button
DoCmd.OpenReport YourReportName, acPreview , , "Me!SpecimenID= " & SpecimenID


Hope this helps
Hymn

 

[mikeg8]

Hymn

No, still not quite there. I now don't get various compile and run errors...but I do get a 17 page report listing every record in the table!

Perhaps I haven't given you all the information you require...

Form is called frm-SpecimenView
Unique field is called SpecimenID and it is numeric, long int type
Report is called rpt-SingleSpecimen and is based on table tbl-Specimen, not a qry (though I have tried a qry, with fairly disastrous results!)

Required action: User clicks 'Print' button on form and up pops a preview of the form's data using rpt-SingleSpecimen as the report format.

I tried the qry version of your reply, but it popped up a dialog box asking me for the SpecimenID.

Plus another question, what is 'Me!'?

Mike

 

[baugie]

I think there was a problem with the second solution that mikeg8 wrote. You can call the report from the form and simply add the where clause as he showed, however you need to reference the table field name in the where clause.

DoCmd.OpenReport "SingleSpecimen", acPreview , , "SpeciminID = " & me!SpeciminID

The where clause is "[Table unique Field] =" & [Form Field containing ID]. I assummed that the key field in the table is called [SpeciminID], and the control on the form is also called [SpeciminID]

I just added this to a program today so I know it works.

Good Luck

 

[mikeg8]

Thanks Baugie, it works a treat!

Mike