print just 1 record from the subform

[Carol Shu]

Hi Rick & everyone there,

I have a main form link with a sub form, sub form in a data sheet view, how
do I set up to just print out 1 single record from sub from. ex: main form
contains customers info (names, address, vehicle info), sub form contains
service records (date of service, parts, labor.cost...) set up as 1 to many
relationships.

anyway I know how to print out just 1 record by using primary key, but not
sure how to print a record from sub from, could you please help and many
thanks.

 

[Albert D. Kallal]

In this case what I do is build a query that is the join of the main table
and the child table. I then build a report that includes all the fields from
both tables that I want to display (so in this report is going to be no sub
reports at all).

What you do then is place a button on your form to launch the report and
restricted to the one particular route that represents both the main form
and the sub form.

You do this with teh follwing code:


dim strwhere as string


strwhere = "CustomerID = " & me!customerID & _
" and ServiceRecordID = " &
me.MySubForm.Form!ServiceReocrdID

me.Refresh

docmd.Openreport "name of reports",acViewPreview",,strwhere


So the above "where" clause will actually restrict the rows of the query we
built to the one particular row that represents the main form and the one
detail record. In the above I assume the primary key of the main form record
is customer ID, and I assume that the primary key value of the sub form is
service record ID. You'll have to modify the above to represent that correct
primary key in both of the cases.

Keep in mind notice how I'm not referencing nor using the foreign key value
of the child table in the above, but the ONLY primary key of both tables.