Passing record from form to report?

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have simple database based on 3 tables. Relationship is 'one to many'
between table 1 and 2, also between 2 and 3 table'one to many'.
I have made form where I enter data in all 3 tables. Form has 2 subform
based on relationships.
I want to make report to print data which is shown on form. I want to print
only data from form, not whole records from tables.
I was trying with event on command button "Print", which is on main
DoCmd.OpenReport "MyReport", acviewPreview,[RecordID]=Forms!MyForm!MyField,
but I don't know how to make it work, because on form are 2 subforms, so I
have to specify what record is on main form and what are on subforms.
Query also doesn't work.
Does anyone knows how to do this?

Your help is greatly appreciated.
 
You can create a Report/Subreport just like you can make a Form/Subform.
Then, all you need to set is the RecordID for the record on the main report
and the subreports will do the rest.
 
I can't make it work. I have made report based on query with criteria in
primary key field (Forms!NameofForm!NameofField. This query works fine.
But, when I make 2 subreport (as there is main form and 2 subforms) and put
it on report, I got several records back.
Subreport 1 are based on query with table 1, without criteria. Subreport 2
are based on query with table 2, without criteria.
I dont know what am I doing wrong. Please help!

Thanks, John

Roger Carlson said:
You can create a Report/Subreport just like you can make a Form/Subform.
Then, all you need to set is the RecordID for the record on the main report
and the subreports will do the rest.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

John said:
I have simple database based on 3 tables. Relationship is 'one to many'
between table 1 and 2, also between 2 and 3 table'one to many'.
I have made form where I enter data in all 3 tables. Form has 2 subform
based on relationships.
I want to make report to print data which is shown on form. I want to print
only data from form, not whole records from tables.
I was trying with event on command button "Print", which is on main
DoCmd.OpenReport "MyReport", acviewPreview,[RecordID]=Forms!MyForm!MyField,
but I don't know how to make it work, because on form are 2 subforms, so I
have to specify what record is on main form and what are on subforms.
Query also doesn't work.
Does anyone knows how to do this?

Your help is greatly appreciated.
Click to expand...
Click to expand...
 
I think I have found where is mistake. In report design report is not linked
with subreports (options link child fields and link master field).
I will try again.

Thanks, John.

John said:
I can't make it work. I have made report based on query with criteria in
primary key field (Forms!NameofForm!NameofField. This query works fine.
But, when I make 2 subreport (as there is main form and 2 subforms) and put
it on report, I got several records back.
Subreport 1 are based on query with table 1, without criteria. Subreport 2
are based on query with table 2, without criteria.
I dont know what am I doing wrong. Please help!

Thanks, John

Roger Carlson said:
You can create a Report/Subreport just like you can make a Form/Subform.
Then, all you need to set is the RecordID for the record on the main report
and the subreports will do the rest.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

John said:
I have simple database based on 3 tables. Relationship is 'one to many'
between table 1 and 2, also between 2 and 3 table'one to many'.
I have made form where I enter data in all 3 tables. Form has 2 subform
based on relationships.
I want to make report to print data which is shown on form. I want to print
only data from form, not whole records from tables.
I was trying with event on command button "Print", which is on main
DoCmd.OpenReport "MyReport", acviewPreview,[RecordID]=Forms!MyForm!MyField,
but I don't know how to make it work, because on form are 2 subforms,
Click to expand...
Click to expand...
so
I
Click to expand...
Click to expand...
 
I have found solution as someone ask also this question. I have made query
based on table in each form (1 form and 2 subforms). You have to put
criteria in query: [Form]![NameofForm]![NameofPrimaryKeyFieldinForm] (this
is for form)
Criteria for query based on table in subform is:
[Form]![NameofForm]![NameofSubform]![NameofPrimaryKeyFieldinSubform] (this
is for subform). So there are 3 query, each with criteria.
Then I made 3 reports based on each query. Then I just put 2 reports for
subforms in main report. Main report is for form, other 2 reports are for
subform. That's it, it works.

Thanks for your answers, John
 
Back
Top