Printing a record from report?

Hi,

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. Does anyone knows how to
do this? I have tried with report based on query, but I get error message.
I was trying to put criteria in query like [Forms]![Name of form]![Name of
field], but doesn't work.

Thanks in advance.

re: Printing a record from report?

One way to do this is by setting up a report based on a query that looks at
the form's current record and returns only that record for your report. If
you have an autonumber field in your primary table, then this gets easier,
because you can use that autonumber field as the search field in the report.
If you don't have this type of field, don't worry. It will still work if
you use the primary key field (No duplicates).

For this report, you only need to create the query for the primary table.
You can use Subreports for the child tables. This will ensure that you only
get the records for the corresponding primary table record.

This can be created as follows:

1. Open the Query editor and give it a name. Preferably "QryCurrRecord" or
something to that effect
2. Add all the fields you want in the report including the primary key.
3. In the Primary Key "Criteria" field, enter the form and field name as
follows:

=Forms!<FormName>!<fieldName>
NOTE: The form and field name most be preceeded by =Forms! or else it won't
work.

When the report opens, the query will look at the value in the primary key
field of the form and search the database's primary table for a match. When
it finds it, the report opens and the date selected for inclusion in the
report will be displayed. The subreports will then use the primary key of
the main table to populate the corresponding information in their fields.

Bob

John <john75@hotmail.hr> wrote in message
news:bu4d9o$rgb$1@ls219.htnet.hr...[color=blue]
> Hi,
>
> 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[/color]
print[color=blue]
> only data from form, not whole records from tables. Does anyone knows how[/color]
to[color=blue]
> do this? I have tried with report based on query, but I get error message.
> I was trying to put criteria in query like [Forms]![Name of form]![Name of
> field], but doesn't work.
>
> Thanks in advance.
>
>[/color]

re: Printing a record from report?

"John" <john75@hotmail.hr> wrote in message news:<bu4d9o$rgb$1@ls219.htnet.hr>...[color=blue]
> Hi,
>
> 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. Does anyone knows how to
> do this? I have tried with report based on query, but I get error message.
> I was trying to put criteria in query like [Forms]![Name of form]![Name of
> field], but doesn't work.
>
> Thanks in advance.[/color]

ONE way. Base the report on a query joining the 3 tables. Pass the
filter to the report in the open event.
DoCmd.OpenReport "MyReport",,[RecordID]=Forms!MyForm!MyField

re: Printing a record from report?

Thanks Pieter but I couldn't make it work.
I have made Print command button on form with event
DoCmd.OpenReport "MyReport",,[RecordID]=Forms!MyForm!MyField
If I put as RecordID field from main form, I get 3 records back on report.
So, I tried to enter filter with subform, but I get the error message in VB
code "access can't find the form". I enter valid name of subform (which is
on form), but access can't find it, I don't know why.
I am newbee in this, so if you know solution please help.

Marco

"Pieter Linden" <pietlinden@hotmail.com> wrote in message
news:bf31e41b.0401142012.7740888b@posting.google.c om...[color=blue]
> "John" <john75@hotmail.hr> wrote in message[/color]
news:<bu4d9o$rgb$1@ls219.htnet.hr>...[color=blue][color=green]
> > Hi,
> >
> > 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[/color][/color]
print[color=blue][color=green]
> > only data from form, not whole records from tables. Does anyone knows[/color][/color]
how to[color=blue][color=green]
> > do this? I have tried with report based on query, but I get error[/color][/color]
message.[color=blue][color=green]
> > I was trying to put criteria in query like [Forms]![Name of form]![Name[/color][/color]
of[color=blue][color=green]
> > field], but doesn't work.
> >
> > Thanks in advance.[/color]
>
> ONE way. Base the report on a query joining the 3 tables. Pass the
> filter to the report in the open event.
> DoCmd.OpenReport "MyReport",,[RecordID]=Forms!MyForm!MyField[/color]

re: Printing a record from report?

Thanks Robert, but doesn't working, I got several records in report. I have
made query based on primary table as you wrote. I have put criteria in
query, but when I add 2 subreports on report based on query with criteria
(each subreport is from other tables), I got several records back.

Marco

"Robert Gray" <graymatter@erols.com> wrote in message
news:4005e2f5$0$6739$61fed72c@news.rcn.com...[color=blue]
> One way to do this is by setting up a report based on a query that looks[/color]
at[color=blue]
> the form's current record and returns only that record for your report.[/color]
If[color=blue]
> you have an autonumber field in your primary table, then this gets easier,
> because you can use that autonumber field as the search field in the[/color]
report.[color=blue]
> If you don't have this type of field, don't worry. It will still work if
> you use the primary key field (No duplicates).
>
> For this report, you only need to create the query for the primary table.
> You can use Subreports for the child tables. This will ensure that you[/color]
only[color=blue]
> get the records for the corresponding primary table record.
>
> This can be created as follows:
>
> 1. Open the Query editor and give it a name. Preferably "QryCurrRecord"[/color]
or[color=blue]
> something to that effect
> 2. Add all the fields you want in the report including the primary key.
> 3. In the Primary Key "Criteria" field, enter the form and field name as
> follows:
>
> =Forms!<FormName>!<fieldName>
> NOTE: The form and field name most be preceeded by =Forms! or else it[/color]
won't[color=blue]
> work.
>
> When the report opens, the query will look at the value in the primary key
> field of the form and search the database's primary table for a match.[/color]
When[color=blue]
> it finds it, the report opens and the date selected for inclusion in the
> report will be displayed. The subreports will then use the primary key of
> the main table to populate the corresponding information in their fields.
>
> Bob
>
> John <john75@hotmail.hr> wrote in message
> news:bu4d9o$rgb$1@ls219.htnet.hr...[color=green]
> > Hi,
> >
> > 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[/color]
> print[color=green]
> > only data from form, not whole records from tables. Does anyone knows[/color][/color]
how[color=blue]
> to[color=green]
> > do this? I have tried with report based on query, but I get error[/color][/color]
message.[color=blue][color=green]
> > I was trying to put criteria in query like [Forms]![Name of form]![Name[/color][/color]
of[color=blue][color=green]
> > field], but doesn't work.
> >
> > Thanks in advance.
> >
> >[/color]
>
>[/color]

re: Printing a record from report?

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]![NameofPrimaryKeyFieldinForm] (this is
for form). 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
[color=blue][color=green][color=darkred]
> > > Hi,
> > >
> > > I have simple database based on 3 tables. Relationship is 'one to[/color][/color][/color]
many'[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
subform[color=blue][color=green][color=darkred]
> > > based on relationships.
> > > I want to make report to print data which is shown on form. I want to[/color][/color]
> print[color=green][color=darkred]
> > > only data from form, not whole records from tables. Does anyone knows[/color][/color]
> how to[color=green][color=darkred]
> > > do this? I have tried with report based on query, but I get error[/color][/color]
> message.[color=green][color=darkred]
> > > I was trying to put criteria in query like [Forms]![Name of[/color][/color][/color]
form]![Name[color=blue]
> of[color=green][color=darkred]
> > > field], but doesn't work.
> > >
> > > Thanks in advance.[/color]
> >
> > ONE way. Base the report on a query joining the 3 tables. Pass the
> > filter to the report in the open event.
> > DoCmd.OpenReport "MyReport",,[RecordID]=Forms!MyForm!MyField[/color]
>
>[/color]

re: Printing a record from report?

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]![NameofPrimaryKeyFieldinForm] (this is
for form). 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