Re: Openreport to print only one page with multipage sub-report?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Ynot (Ynot_AZ_at_hotmail.com)
Date: 09/01/04 

Date: Wed, 1 Sep 2004 10:11:07 -0700

Marsh,

I got it. Yahooo

I tried adding DISTINCT and that didn;t work. I had to add "TicketNumber"
which is the autonumber primary key of the JobDates table to the ORDER BY
clause. That solved the problem since it became a tie-breaker when several
dates were the same.

It works like a charm now. Thanks for all your help. Without it I might
have thrown my hands up days ago.

Tony

SELECT TOP 18 JobDates.DateStart, DateDescTable.DateDesc,
JobDates.JobNumber, JobDates.DateTaskEntered, JobDates.TicketNumber,
DateDescTable.DateSF

FROM JobDates LEFT JOIN DateDescTable ON JobDates.DateType =
DateDescTable.DateKey

WHERE (((JobDates.JobNumber)=[reports]![jobcardprintN].[Jobnumber]))

ORDER BY JobDates.DateStart DESC , JobDates.TicketNumber;

"Marshall Barton" <marshbarton@wowway.com> wrote in message
news:hpp7j017jp0k0i79hn0artnbdaf848a58g@4ax.com...

> Ynot wrote:
>
>>Sorry to be confusing. The source of the main report is a query named
>>"JobsAll" that pulls together 3 tables - Job Card, Customer, and Job Start
>>information. The sub-report uses a query "JobDatesSubRQ" that pulls
>>together a date table with all dates of all items associated with a job
>>and
>>a date description table that is a standard description for each date code
>>in the date table.
>>
>>I am using the sub-report control to match on "JobNumber" in the date
>>table
>>so that only dates associated with a single job are on the sub-report for
>>that job.
>>
>>I am invoking the printing using a "docmd.openreport" with a
>> "wherecondition" Jobnumber = "Me.Jobnumber" off of an edit job card form.
>>This allows me to print a job card for the job that has just been changed
>>or
>>entered.
>
> Let's try this:
>
> Leave the main report's query as you originally had it.
>
> Remove the subreport control's Link Master/Child properties.
>
> Set the subreport's Record Source to this SQL statement:
>
> SELECT TOP 18 *
> FROM JobDatesSubRQ
> WHERE JobNumber = Forms!editjobcardform.Jobnumber
> ORDER BY [Job Start] DESC
>
> --
> Marsh
> MVP [MS Access]