view report of records returned by search 2

[buee04]

Hi,

I'm using Access 2000 to develop my first db and I'm finally moving on to the report part. I have a search form that calls a query and that returns the records in the spreadsheet format. Is there a way to put like a button on the spreadsheet for each record so that i can click on the record and its report button and see the report for that record?

Or maybe use a subform to hold the results and then put a reports button on the main form? But, I don't know how to do any of that.

PLEASE, any help is appreciated!

 

[logicalman]

Firstly you will need to design the results form to open in Continuous forms format. Format your form, if possible, to have all the displayed fields on the same line, or, at least, the fewer number of lines as possible. Remember to set the Vertical Scroll bar to on. On this form include a button called 'cmdReport'.
Design a Report to display the data for one record in any layout you like.
For the recordsource of this report create a query that is the same as that for your results form.
Place code behind the On Click event of cmdReport to supply the Report with it's recordset, being the same as the form itself but include a WHERE statement for the unique record ID currently displayed.
Save both report and form.

The below example uses 8 fields, ID, FNME, LNME, ADDR1, ADDR2, ZIP, CITY, STTE. The ID column is the Primary Key (poss an autonumber).

The form will display just the first and Last name together with the cmdReport button, the Report will display the rest of the details.

The recordsource for the form will be something like :
'SELECT ID, FNME, LNME, FROM tblClients'

The recordsource for the Repoprt will be (initially) :
'SELECT * FROM tblClients'.

The Report is called rptClients.

The code behind the On Click event of cmdReport will be:

Dim strSQLRep as String
strSQLRep = ""
strSQLRep = strSQLRep & " SELECT * FROM tblClients "
strSQLRep = strSQLRep & " WHERE [ID]=" & Me.ID
DoCmd.OpenReport "rptClients", acViewPreview
Reports!rptClients.RecordSource = strSQLRep


This will open the report and supply it it's recordsource based on the button clicked on the particular record selected.

HTH

Tony
Indiana

 

[buee04]

Hey Tony, thanks so much for responding. But I'm having trouble understanding it all. This is my first db and I have no experience with vb scripting.

I created a form that displays the results, frmSearchResults, which displays 7 fields: ContractNumber, Job Name, Type of Building, Area, Controls, Floor, and Installed Date, out of 30 or so fields in tblJobs.

I kept the recordsource of frmSearchResults to be qrySearching. But the recordsource of my report, rptTest, is SELECT * FROM tblJobs;

And on the On Click event of cmdReport, I pasted your code but changed tblClients to tblJobs, and ID to ContractNumber which is my primary key.

But when I try to see the report, I get this in a message box: You can't set the Record Source property after printing has started.

Please help!!

 

[WildHare]

Buee, if I understand you correctly, you have a continuous mode form with a query as the recordsource. So far, so good. Now you'd like to place a button (on each row of the Cont. Form, I assume), to print a more complete REPORT for THAT particular record/row on the form, right?

This is very simple to do. Create a query that produces all the fields that you want on your report. Design your report to use this query, and test them both to make sure they look/act the way you want.

Finally, in the key field on your query, refer back to the RECORD on the Cont. Form that you're pointing to when you click the command button.

In the criteria line, you'll need a reference back to the form name and key-field name, something like this:


=Forms!MyContinuousForm!MyKeyField


Make your BUTTON run the report. When you open the report, it will be specifically filtered to the one record (e.g., row on the continuous form) that is the 'current' record.

I have done several of these forms in the past, I can probably whip up quick demo if you really need it.

Jim
How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[buee04]

GREAT! I got it. That was rather easy Jim. Thank you very much!