Changing Recordsource of a report using Code

A

Anthony

Hi,

I have a Report & 2 queries, I want to change the recordsource of the report
depending on which query I want to use, by means of 2 Buttons. ie: push
button one, record source would be query1, and the report would display.

My current code is:

Reports![rptPacking_DeliverySheet].RecordSource =
"qryPackingSheets-OktoStart"

As the report is not yet open I get the error:
"The report name 'rptPacking_DeliverySheet' you entered is misspelled or
refers to a report that isn't open or doesn't exist"

Is it possible to change the recordsource of an unopened report?

Thanks in advance,
Anthony.
 
B

Baz

Anthony said:
Hi,

I have a Report & 2 queries, I want to change the recordsource of the report
depending on which query I want to use, by means of 2 Buttons. ie: push
button one, record source would be query1, and the report would display.

My current code is:

Reports![rptPacking_DeliverySheet].RecordSource =
"qryPackingSheets-OktoStart"

As the report is not yet open I get the error:
"The report name 'rptPacking_DeliverySheet' you entered is misspelled or
refers to a report that isn't open or doesn't exist"

Is it possible to change the recordsource of an unopened report?

Thanks in advance,
Anthony.
Click to expand...

Have the report change it's own recordsource in it's open event.
 
N

Nikos Yannacopoulos

Anthony,

The right "place" to assign / change the report's recordsource is the
report's own Open event. So, the trick is in letting the report know,
somehow, which query to use.

If you are using Access 2003 (and possibly 2002? not sure) you can use
the OpenReport's OpenArgs argument to pass that accross to the report;
so, the code behind each of the two command buttons would be something like:

DoCmd.OpenReport "rptPacking_DeliverySheet", acViewPreview, , , , _
"qryPackingSheets-OktoStart"

and the code in the report's On Open event:

Me.Recordsource = OpenArgs

If your file format is Access 2000, and you plan to share it with A2K
users, don't use this method, it won't work for A2K users (OpenArgs was
not available for reports in A2K and earlier).

So, if you have / need to use A2K or earlier, you need another approach,
in fact one I like better in terms of your form design:

* use one command button for both cases, to simply open the report,
omitting the OpenArgs argument:
DoCmd.OpenReport "rptPacking_DeliverySheet", acViewPreview

* add an option group to your form for the user to select the query; use
the OG wizard to add two options with user-friendly description (don't
mind the actual query names), returning values 1 and 2 respectively;

* in your report's Open event, put this code:

Dim vQuery As String
Select Case Forms!YourFormName!OptionGroupName
Case 1
vQuery = "qryPackingSheets-OktoStart"
Case 2
vQuery = "some other query"
Case Else
MsgBox "No query selected!", vbCritical, "Can't Open Report"
Cancel = True
End Select
Me.Recordsource = vQuery

HTH,
Nikos
 
N

Nikos Yannacopoulos

Anthony,

I forgot to mention the first thing I should have, really... do you
really need the two different queries? If the only difference between
the two is the criteria, then you only need one query! Two ways to do that:
1. make the query "read: the criteria from the form, based on user
choices (option groups, combo boxes, listboxes), so effectively the
report will as well, or
2. remove the criteria from the query altogether, and use the user
choices on the form to create a filter string for the report. passed in
the OpenReport's WhereCondition argument.

HTH,
Nikos
 
A

Anthony

Hi Nikos,

Thankyou for you prompt reply.

I am yet to try this but I am sure it will work. (I am using Access 2003)

Many thanks,
Anthony.
 
N

Nikos Yannacopoulos

The OpenArgs method will work with A2K, but I still don't like the two
command buttons for the same purpose, and still question the need for
two queries :)

Post back if you need more help.

Nikos
 
A

Anthony

Hi,

Thanks that solution worked great.

I am new to a lot of this VBA and am learning as I go.

The 2nd query(qrypackingSheets-OktoStart) contains the 1st
Query(qryPackingSheets) + a second Query (qryOkToStart)which only displays
the clients that fit the criteria to "start".
The 2 queries within "qrypackingSheets-OktoStart" have the common CustomerID
field.
(hope this makes sense)
I need to be able to print "All" packing slips as well as only those "Ok to
Start".

You would no doubt have an easier way to do this and if you have the time to
tell me I would be greatful. But no problem if not.

Many thanks for your help,
Anthony.
 
N

Nikos Yannacopoulos

Anthony,

Not sure I understand the difference between the two queries. Can you
post their SQL?

Nikos
 
A

Anthony

Hi,

This is probably a mess but here goes...

This is the second query: (contains the first query 'qryPackingSheets' +
'qryOkToSTART'.

SELECT qryPackingSheets.CancelledDate, qryPackingSheets.ContractDate,
qryPackingSheets.CustomerID, qryPackingSheets.BRBusinessName,
qryPackingSheets.BRAddress, qryPackingSheets.BRSuburb,
qryPackingSheets.PhArea, qryPackingSheets.BRPhone, qryPackingSheets.BREMAIL,
qryPackingSheets.BRWEB, qryPackingSheets.BROther, qryPackingSheets.Category,
qryPackingSheets.Balance1, qryPackingSheets.ApproxCustomerspw,
qryPackingSheets.Staff, qryPackingSheets.ContactFirstName,
qryPackingSheets.ContactLastName, qryPackingSheets.EmployeeSurname,
qryPackingSheets.EmployeeFirstName, qryPackingSheets.ContractNumber,
qryPackingSheets.[Ref#], qryPackingSheets.AverageSale,
qryPackingSheets.DeliveryAddress, qryPackingSheets.[LettersToDear:],
qryPackingSheets.PostalCode, qryPackingSheets.TermDate,
qryPackingSheets.EnteredDate, qryPackingSheets.ServiceSheets,
qryPackingSheets.Expr1028, qryPackingSheets.TrainingType,
qryPackingSheets.ApplicableTransactions, qryPackingSheets.ChqBook,
qryPackingSheets.ATforChqBook, qryPackingSheets.MerchantPack,
qryPackingSheets.ServoPack, qryPackingSheets.RollID,
qryPackingSheets.RollType, qryPackingSheets.RollWidth,
qryPackingSheets.RollDiam, qryPackingSheets.RollCore,
qryPackingSheets.RollsPW, qryPackingSheets.SampleEnclosed,
qryPackingSheets.RollEnclosed, qryPackingSheets.ThermalCoated,
qryPackingSheets.PrintOrient, qryPackingSheets.RollNotes,
qryPackingSheets.RegisterRequired, qryPackingSheets.StampRequired,
qryPackingSheets.PrintonReceipt, qryPackingSheets.LocationZone,
qryPackingSheets.PrintZone, qryPackingSheets.ServiceStation,
qryPackingSheets.Flag_Holder, qryPackingSheets.RewardLevelCard,
qryPackingSheets.PerspexCardHolder, qryPackingSheets.Brochures,
qryPackingSheets.PerspexBrochureHolder, qryPackingSheets.Posters,
qryPackingSheets.TermsCond, qryPackingSheets.StickerSheets,
qryPackingSheets.ExtraMerch, qryPackingSheets.[Total Roll Usage],
qryPackingSheets.Region, qryPackingSheets.NoOfWeeks,
qryPackingSheets.NoofCHQBooks, qryPackingSheets.Banner,
qryPackingSheets.Banner_NoOf, qryPackingSheets.RollShortName,
qryPackingSheets.RollNumber, qryPackingSheets.PromStartMonth,
qryPackingSheets.FaxNumber, qryPackingSheets.PPAmount,
qryPackingSheets.InvalidStamp, qryPackingSheets.AccountinDefault,
qryPackingSheets.AIDNotes, qryPackingSheets.LabelExtra,
qryPackingSheets.BrochureCode, qryPackingSheets.RewardLevel,
qryPackingSheets.RewardLocation, qryPackingSheets.RegionName,
qryPackingSheets.DeliveryAddressShort,
qryPackingSheets.DeliveryAddressShort2, qryPackingSheets.PromStartDate,
qryPackingSheets.MerchSticker, qryPackingSheets.SpecialInstructions,
qryPackingSheets.Total, qryPackingSheets.SumofPaymentAmount,
qryPackingSheets.ImmediateStart
FROM qryOkToSTART INNER JOIN qryPackingSheets ON qryOkToSTART.CustomerID =
qryPackingSheets.CustomerID;


This is the First Query:

SELECT TblCustomer.CancelledDate, TblCustomer.ContractDate,
TblCustomer.CustomerID, tblBrochure.BRBusinessName, tblBrochure.BRAddress,
tblBrochure.BRSuburb,
IIf([State]="NSW","02",IIf([State]="QLD","07",IIf([State] In
("VIC","TAS"),"03",IIf([State] In ("SA","WA","NT"),"08")))) AS PhArea,
tblBrochure.BRPhone, tblBrochure.BREMAIL, tblBrochure.BRWEB,
tblBrochure.BROther, [Category List].Category, IIf([Balance]<10,0,[Balance])
AS Balance1, zqryPrintersReportALL.ApproxCustomerspw,
zqryPrintersReportALL.Staff, TblCustomer.ContactFirstName,
TblCustomer.ContactLastName, tblEmployees.EmployeeSurname,
tblEmployees.EmployeeFirstName, TblCustomer.ContractNumber,
[tblCustomer].[LocationZone] & "-" & [tblCustomer].[CustomerID] & "-" &
[TblCustomer].[ContractNumber] AS [Ref#], TblCustomer.AverageSale,
qryDeliveryAddress.DeliveryAddress, qryDeliveryAddress.[LettersToDear:],
qryDeliveryAddress.PostalCode, tblEmployees.TermDate,
TblCustomer.EnteredDate, "Printed all Country Before 30/3/03" AS
ServiceSheets, TblCustomer.SpecialInstructions, TblCustomer.TrainingType,
TblCustomer.ApplicableTransactions, TblCustomer.ChqBook,
TblCustomer.ATforChqBook, TblCustomer.MerchantPack, TblCustomer.ServoPack,
qryRollDetails.RollID, qryRollDetails.RollType, qryRollDetails.RollWidth,
qryRollDetails.RollDiam, qryRollDetails.RollCore, qryRollDetails.RollsPW,
qryRollDetails.SampleEnclosed, qryRollDetails.RollEnclosed,
qryRollDetails.ThermalCoated, qryRollDetails.PrintOrient,
qryRollDetails.Notes AS RollNotes, TblCustomer.RegisterRequired,
qryRollDetails.StampRequired, qryRollDetails.PrintonReceipt,
TblCustomer.LocationZone, tblZones.ZoneName AS PrintZone,
TblCustomer.ServiceStation, TblCustomer.Flag_Holder,
TblCustomer.RewardLevelCard, TblCustomer.PerspexCardHolder,
TblCustomer.Brochures, TblCustomer.PerspexBrochureHolder,
TblCustomer.Posters, TblCustomer.TermsCond, TblCustomer.StickerSheets,
TblCustomer.ExtraMerch, [qryRollDetails].[rollsPW]*[NoOfWeeks] AS [Total
Roll Usage], tblZones.Region,
tblZones.NoOfWeeks, -Int(-([ATforChqBook]*[NoOfWeeks]/[NoOfChqsInEachBook]))
AS NoofCHQBooks, TblCustomer.Banner, TblCustomer.Banner_NoOf,
qryRollDetails.RollShortName, qryRollDetails.RollNumber,
TblCustomer.PromStartMonth, qryClientFaxNumber.PhoneNumber AS FaxNumber,
TblCustomer.PPAmount, TblCustomer.InvalidStamp,
TblCustomer.AccountinDefault, TblCustomer.AIDNotes, TblCustomer.LabelExtra,
tblZones.BrochureCode, qryRollDetails.RewardLevel,
qryRollDetails.RewardLocation, tblZones.RegionName,
qryDeliveryAddress.DeliveryAddressShort,
qryDeliveryAddress.DeliveryAddressShort2, TblCustomer.PromStartDate,
IIf([QryRollDetails].[RewardLevel]>=50,IIf([ChqBook]=-1,"Merchandise
Stickers May be required"," ")," ") AS MerchSticker,
TblCustomer.SpecialInstructions, [zqryMerc+2Months].Total,
zqryPaymentpaidTotal.SumofPaymentAmount, TblCustomer.ImmediateStart, "
IIf([qryOkToStart].[CustomerID]=[TblCustomer].[CustomerID],'YES','')" AS
OkToStart1
FROM SetUp, ((qryClientFaxNumber INNER JOIN (((((((tblEmployees INNER JOIN
((TblCustomer INNER JOIN tblBrochure ON TblCustomer.CustomerID =
tblBrochure.CustomerID) INNER JOIN ([Category List] INNER JOIN
tblCustomerCategories ON [Category List].CategoryID =
tblCustomerCategories.CategoryID) ON TblCustomer.CustomerID =
tblCustomerCategories.CustomerID) ON tblEmployees.EmployeeID =
TblCustomer.EmployeeID) INNER JOIN zqryPrintersReportALL ON
TblCustomer.CustomerID = zqryPrintersReportALL.CustomerID) INNER JOIN
qryBalanceDue ON TblCustomer.CustomerID = qryBalanceDue.CustomerID) INNER
JOIN qryDeliveryAddress ON TblCustomer.CustomerID =
qryDeliveryAddress.[Customer ID]) INNER JOIN tblPrintZones ON
TblCustomer.CustomerID = tblPrintZones.CustomerID) INNER JOIN tblZones ON
tblPrintZones.ZoneName = tblZones.ZoneName) INNER JOIN qryRollDetails ON
TblCustomer.CustomerID = qryRollDetails.CustomerID) ON
qryClientFaxNumber.CustomerID = TblCustomer.CustomerID) INNER JOIN
[zqryMerc+2Months] ON TblCustomer.CustomerID =
[zqryMerc+2Months].CustomerID) INNER JOIN zqryPaymentpaidTotal ON
TblCustomer.CustomerID = zqryPaymentpaidTotal.CustomerID
GROUP BY TblCustomer.CancelledDate, TblCustomer.ContractDate,
TblCustomer.CustomerID, tblBrochure.BRBusinessName, tblBrochure.BRAddress,
tblBrochure.BRSuburb,
IIf([State]="NSW","02",IIf([State]="QLD","07",IIf([State] In
("VIC","TAS"),"03",IIf([State] In ("SA","WA","NT"),"08")))),
tblBrochure.BRPhone, tblBrochure.BREMAIL, tblBrochure.BRWEB,
tblBrochure.BROther, [Category List].Category,
IIf([Balance]<10,0,[Balance]), zqryPrintersReportALL.ApproxCustomerspw,
zqryPrintersReportALL.Staff, TblCustomer.ContactFirstName,
TblCustomer.ContactLastName, tblEmployees.EmployeeSurname,
tblEmployees.EmployeeFirstName, TblCustomer.ContractNumber,
[tblCustomer].[LocationZone] & "-" & [tblCustomer].[CustomerID] & "-" &
[TblCustomer].[ContractNumber], TblCustomer.AverageSale,
qryDeliveryAddress.DeliveryAddress, qryDeliveryAddress.[LettersToDear:],
qryDeliveryAddress.PostalCode, tblEmployees.TermDate,
TblCustomer.EnteredDate, "Printed all Country Before 30/3/03",
TblCustomer.SpecialInstructions, TblCustomer.TrainingType,
TblCustomer.ApplicableTransactions, TblCustomer.ChqBook,
TblCustomer.ATforChqBook, TblCustomer.MerchantPack, TblCustomer.ServoPack,
qryRollDetails.RollID, qryRollDetails.RollType, qryRollDetails.RollWidth,
qryRollDetails.RollDiam, qryRollDetails.RollCore, qryRollDetails.RollsPW,
qryRollDetails.SampleEnclosed, qryRollDetails.RollEnclosed,
qryRollDetails.ThermalCoated, qryRollDetails.PrintOrient,
qryRollDetails.Notes, TblCustomer.RegisterRequired,
qryRollDetails.StampRequired, qryRollDetails.PrintonReceipt,
TblCustomer.LocationZone, tblZones.ZoneName, TblCustomer.ServiceStation,
TblCustomer.Flag_Holder, TblCustomer.RewardLevelCard,
TblCustomer.PerspexCardHolder, TblCustomer.Brochures,
TblCustomer.PerspexBrochureHolder, TblCustomer.Posters,
TblCustomer.TermsCond, TblCustomer.StickerSheets, TblCustomer.ExtraMerch,
[qryRollDetails].[rollsPW]*[NoOfWeeks], tblZones.Region,
tblZones.NoOfWeeks, -Int(-([ATforChqBook]*[NoOfWeeks]/[NoOfChqsInEachBook])),
TblCustomer.Banner, TblCustomer.Banner_NoOf, qryRollDetails.RollShortName,
qryRollDetails.RollNumber, TblCustomer.PromStartMonth,
qryClientFaxNumber.PhoneNumber, TblCustomer.PPAmount,
TblCustomer.InvalidStamp, TblCustomer.AccountinDefault,
TblCustomer.AIDNotes, TblCustomer.LabelExtra, tblZones.BrochureCode,
qryRollDetails.RewardLevel, qryRollDetails.RewardLocation,
tblZones.RegionName, qryDeliveryAddress.DeliveryAddressShort,
qryDeliveryAddress.DeliveryAddressShort2, TblCustomer.PromStartDate,
IIf([QryRollDetails].[RewardLevel]>=50,IIf([ChqBook]=-1,"Merchandise
Stickers May be required"," ")," "), TblCustomer.SpecialInstructions,
[zqryMerc+2Months].Total, zqryPaymentpaidTotal.SumofPaymentAmount,
TblCustomer.ImmediateStart, "
IIf([qryOkToStart].[CustomerID]=[TblCustomer].[CustomerID],'YES','')"
HAVING (((TblCustomer.CancelledDate) Is Null Or (TblCustomer.CancelledDate)
Is Null))
ORDER BY TblCustomer.CustomerID;


Thanks Again,
Anthony
 
D

David C. Holley

I would test thought that OpenArgs have been provided as in

If IsNull(OpenArgs) = False

this will allow the report to be open as-is if no OpenArgs are provided.
 
D

David C. Holley

Or pass the WHERE statement of the query to the report to have it
applied as in...

OnOpen

strRS = "SELECT * FROM tblTransports "

If isNull(Me.OpenArgs) = False then
strRS = strRS & Me.OpenArgs
End fi

strRS = strRS & ";"
 
D

David C. Holley

Does the REPORT utilize EVERY field listed? If not you should trim the
query down to just the fields that are absolutely needed. In essence, do
some liposuction.
Hi,

This is probably a mess but here goes...

This is the second query: (contains the first query 'qryPackingSheets' +
'qryOkToSTART'.

SELECT qryPackingSheets.CancelledDate, qryPackingSheets.ContractDate,
qryPackingSheets.CustomerID, qryPackingSheets.BRBusinessName,
qryPackingSheets.BRAddress, qryPackingSheets.BRSuburb,
qryPackingSheets.PhArea, qryPackingSheets.BRPhone, qryPackingSheets.BREMAIL,
qryPackingSheets.BRWEB, qryPackingSheets.BROther, qryPackingSheets.Category,
qryPackingSheets.Balance1, qryPackingSheets.ApproxCustomerspw,
qryPackingSheets.Staff, qryPackingSheets.ContactFirstName,
qryPackingSheets.ContactLastName, qryPackingSheets.EmployeeSurname,
qryPackingSheets.EmployeeFirstName, qryPackingSheets.ContractNumber,
qryPackingSheets.[Ref#], qryPackingSheets.AverageSale,
qryPackingSheets.DeliveryAddress, qryPackingSheets.[LettersToDear:],
qryPackingSheets.PostalCode, qryPackingSheets.TermDate,
qryPackingSheets.EnteredDate, qryPackingSheets.ServiceSheets,
qryPackingSheets.Expr1028, qryPackingSheets.TrainingType,
qryPackingSheets.ApplicableTransactions, qryPackingSheets.ChqBook,
qryPackingSheets.ATforChqBook, qryPackingSheets.MerchantPack,
qryPackingSheets.ServoPack, qryPackingSheets.RollID,
qryPackingSheets.RollType, qryPackingSheets.RollWidth,
qryPackingSheets.RollDiam, qryPackingSheets.RollCore,
qryPackingSheets.RollsPW, qryPackingSheets.SampleEnclosed,
qryPackingSheets.RollEnclosed, qryPackingSheets.ThermalCoated,
qryPackingSheets.PrintOrient, qryPackingSheets.RollNotes,
qryPackingSheets.RegisterRequired, qryPackingSheets.StampRequired,
qryPackingSheets.PrintonReceipt, qryPackingSheets.LocationZone,
qryPackingSheets.PrintZone, qryPackingSheets.ServiceStation,
qryPackingSheets.Flag_Holder, qryPackingSheets.RewardLevelCard,
qryPackingSheets.PerspexCardHolder, qryPackingSheets.Brochures,
qryPackingSheets.PerspexBrochureHolder, qryPackingSheets.Posters,
qryPackingSheets.TermsCond, qryPackingSheets.StickerSheets,
qryPackingSheets.ExtraMerch, qryPackingSheets.[Total Roll Usage],
qryPackingSheets.Region, qryPackingSheets.NoOfWeeks,
qryPackingSheets.NoofCHQBooks, qryPackingSheets.Banner,
qryPackingSheets.Banner_NoOf, qryPackingSheets.RollShortName,
qryPackingSheets.RollNumber, qryPackingSheets.PromStartMonth,
qryPackingSheets.FaxNumber, qryPackingSheets.PPAmount,
qryPackingSheets.InvalidStamp, qryPackingSheets.AccountinDefault,
qryPackingSheets.AIDNotes, qryPackingSheets.LabelExtra,
qryPackingSheets.BrochureCode, qryPackingSheets.RewardLevel,
qryPackingSheets.RewardLocation, qryPackingSheets.RegionName,
qryPackingSheets.DeliveryAddressShort,
qryPackingSheets.DeliveryAddressShort2, qryPackingSheets.PromStartDate,
qryPackingSheets.MerchSticker, qryPackingSheets.SpecialInstructions,
qryPackingSheets.Total, qryPackingSheets.SumofPaymentAmount,
qryPackingSheets.ImmediateStart
FROM qryOkToSTART INNER JOIN qryPackingSheets ON qryOkToSTART.CustomerID =
qryPackingSheets.CustomerID;


This is the First Query:

SELECT TblCustomer.CancelledDate, TblCustomer.ContractDate,
TblCustomer.CustomerID, tblBrochure.BRBusinessName, tblBrochure.BRAddress,
tblBrochure.BRSuburb,
IIf([State]="NSW","02",IIf([State]="QLD","07",IIf([State] In
("VIC","TAS"),"03",IIf([State] In ("SA","WA","NT"),"08")))) AS PhArea,
tblBrochure.BRPhone, tblBrochure.BREMAIL, tblBrochure.BRWEB,
tblBrochure.BROther, [Category List].Category, IIf([Balance]<10,0,[Balance])
AS Balance1, zqryPrintersReportALL.ApproxCustomerspw,
zqryPrintersReportALL.Staff, TblCustomer.ContactFirstName,
TblCustomer.ContactLastName, tblEmployees.EmployeeSurname,
tblEmployees.EmployeeFirstName, TblCustomer.ContractNumber,
[tblCustomer].[LocationZone] & "-" & [tblCustomer].[CustomerID] & "-" &
[TblCustomer].[ContractNumber] AS [Ref#], TblCustomer.AverageSale,
qryDeliveryAddress.DeliveryAddress, qryDeliveryAddress.[LettersToDear:],
qryDeliveryAddress.PostalCode, tblEmployees.TermDate,
TblCustomer.EnteredDate, "Printed all Country Before 30/3/03" AS
ServiceSheets, TblCustomer.SpecialInstructions, TblCustomer.TrainingType,
TblCustomer.ApplicableTransactions, TblCustomer.ChqBook,
TblCustomer.ATforChqBook, TblCustomer.MerchantPack, TblCustomer.ServoPack,
qryRollDetails.RollID, qryRollDetails.RollType, qryRollDetails.RollWidth,
qryRollDetails.RollDiam, qryRollDetails.RollCore, qryRollDetails.RollsPW,
qryRollDetails.SampleEnclosed, qryRollDetails.RollEnclosed,
qryRollDetails.ThermalCoated, qryRollDetails.PrintOrient,
qryRollDetails.Notes AS RollNotes, TblCustomer.RegisterRequired,
qryRollDetails.StampRequired, qryRollDetails.PrintonReceipt,
TblCustomer.LocationZone, tblZones.ZoneName AS PrintZone,
TblCustomer.ServiceStation, TblCustomer.Flag_Holder,
TblCustomer.RewardLevelCard, TblCustomer.PerspexCardHolder,
TblCustomer.Brochures, TblCustomer.PerspexBrochureHolder,
TblCustomer.Posters, TblCustomer.TermsCond, TblCustomer.StickerSheets,
TblCustomer.ExtraMerch, [qryRollDetails].[rollsPW]*[NoOfWeeks] AS [Total
Roll Usage], tblZones.Region,
tblZones.NoOfWeeks, -Int(-([ATforChqBook]*[NoOfWeeks]/[NoOfChqsInEachBook]))
AS NoofCHQBooks, TblCustomer.Banner, TblCustomer.Banner_NoOf,
qryRollDetails.RollShortName, qryRollDetails.RollNumber,
TblCustomer.PromStartMonth, qryClientFaxNumber.PhoneNumber AS FaxNumber,
TblCustomer.PPAmount, TblCustomer.InvalidStamp,
TblCustomer.AccountinDefault, TblCustomer.AIDNotes, TblCustomer.LabelExtra,
tblZones.BrochureCode, qryRollDetails.RewardLevel,
qryRollDetails.RewardLocation, tblZones.RegionName,
qryDeliveryAddress.DeliveryAddressShort,
qryDeliveryAddress.DeliveryAddressShort2, TblCustomer.PromStartDate,
IIf([QryRollDetails].[RewardLevel]>=50,IIf([ChqBook]=-1,"Merchandise
Stickers May be required"," ")," ") AS MerchSticker,
TblCustomer.SpecialInstructions, [zqryMerc+2Months].Total,
zqryPaymentpaidTotal.SumofPaymentAmount, TblCustomer.ImmediateStart, "
IIf([qryOkToStart].[CustomerID]=[TblCustomer].[CustomerID],'YES','')" AS
OkToStart1
FROM SetUp, ((qryClientFaxNumber INNER JOIN (((((((tblEmployees INNER JOIN
((TblCustomer INNER JOIN tblBrochure ON TblCustomer.CustomerID =
tblBrochure.CustomerID) INNER JOIN ([Category List] INNER JOIN
tblCustomerCategories ON [Category List].CategoryID =
tblCustomerCategories.CategoryID) ON TblCustomer.CustomerID =
tblCustomerCategories.CustomerID) ON tblEmployees.EmployeeID =
TblCustomer.EmployeeID) INNER JOIN zqryPrintersReportALL ON
TblCustomer.CustomerID = zqryPrintersReportALL.CustomerID) INNER JOIN
qryBalanceDue ON TblCustomer.CustomerID = qryBalanceDue.CustomerID) INNER
JOIN qryDeliveryAddress ON TblCustomer.CustomerID =
qryDeliveryAddress.[Customer ID]) INNER JOIN tblPrintZones ON
TblCustomer.CustomerID = tblPrintZones.CustomerID) INNER JOIN tblZones ON
tblPrintZones.ZoneName = tblZones.ZoneName) INNER JOIN qryRollDetails ON
TblCustomer.CustomerID = qryRollDetails.CustomerID) ON
qryClientFaxNumber.CustomerID = TblCustomer.CustomerID) INNER JOIN
[zqryMerc+2Months] ON TblCustomer.CustomerID =
[zqryMerc+2Months].CustomerID) INNER JOIN zqryPaymentpaidTotal ON
TblCustomer.CustomerID = zqryPaymentpaidTotal.CustomerID
GROUP BY TblCustomer.CancelledDate, TblCustomer.ContractDate,
TblCustomer.CustomerID, tblBrochure.BRBusinessName, tblBrochure.BRAddress,
tblBrochure.BRSuburb,
IIf([State]="NSW","02",IIf([State]="QLD","07",IIf([State] In
("VIC","TAS"),"03",IIf([State] In ("SA","WA","NT"),"08")))),
tblBrochure.BRPhone, tblBrochure.BREMAIL, tblBrochure.BRWEB,
tblBrochure.BROther, [Category List].Category,
IIf([Balance]<10,0,[Balance]), zqryPrintersReportALL.ApproxCustomerspw,
zqryPrintersReportALL.Staff, TblCustomer.ContactFirstName,
TblCustomer.ContactLastName, tblEmployees.EmployeeSurname,
tblEmployees.EmployeeFirstName, TblCustomer.ContractNumber,
[tblCustomer].[LocationZone] & "-" & [tblCustomer].[CustomerID] & "-" &
[TblCustomer].[ContractNumber], TblCustomer.AverageSale,
qryDeliveryAddress.DeliveryAddress, qryDeliveryAddress.[LettersToDear:],
qryDeliveryAddress.PostalCode, tblEmployees.TermDate,
TblCustomer.EnteredDate, "Printed all Country Before 30/3/03",
TblCustomer.SpecialInstructions, TblCustomer.TrainingType,
TblCustomer.ApplicableTransactions, TblCustomer.ChqBook,
TblCustomer.ATforChqBook, TblCustomer.MerchantPack, TblCustomer.ServoPack,
qryRollDetails.RollID, qryRollDetails.RollType, qryRollDetails.RollWidth,
qryRollDetails.RollDiam, qryRollDetails.RollCore, qryRollDetails.RollsPW,
qryRollDetails.SampleEnclosed, qryRollDetails.RollEnclosed,
qryRollDetails.ThermalCoated, qryRollDetails.PrintOrient,
qryRollDetails.Notes, TblCustomer.RegisterRequired,
qryRollDetails.StampRequired, qryRollDetails.PrintonReceipt,
TblCustomer.LocationZone, tblZones.ZoneName, TblCustomer.ServiceStation,
TblCustomer.Flag_Holder, TblCustomer.RewardLevelCard,
TblCustomer.PerspexCardHolder, TblCustomer.Brochures,
TblCustomer.PerspexBrochureHolder, TblCustomer.Posters,
TblCustomer.TermsCond, TblCustomer.StickerSheets, TblCustomer.ExtraMerch,
[qryRollDetails].[rollsPW]*[NoOfWeeks], tblZones.Region,
tblZones.NoOfWeeks, -Int(-([ATforChqBook]*[NoOfWeeks]/[NoOfChqsInEachBook])),
TblCustomer.Banner, TblCustomer.Banner_NoOf, qryRollDetails.RollShortName,
qryRollDetails.RollNumber, TblCustomer.PromStartMonth,
qryClientFaxNumber.PhoneNumber, TblCustomer.PPAmount,
TblCustomer.InvalidStamp, TblCustomer.AccountinDefault,
TblCustomer.AIDNotes, TblCustomer.LabelExtra, tblZones.BrochureCode,
qryRollDetails.RewardLevel, qryRollDetails.RewardLocation,
tblZones.RegionName, qryDeliveryAddress.DeliveryAddressShort,
qryDeliveryAddress.DeliveryAddressShort2, TblCustomer.PromStartDate,
IIf([QryRollDetails].[RewardLevel]>=50,IIf([ChqBook]=-1,"Merchandise
Stickers May be required"," ")," "), TblCustomer.SpecialInstructions,
[zqryMerc+2Months].Total, zqryPaymentpaidTotal.SumofPaymentAmount,
TblCustomer.ImmediateStart, "
IIf([qryOkToStart].[CustomerID]=[TblCustomer].[CustomerID],'YES','')"
HAVING (((TblCustomer.CancelledDate) Is Null Or (TblCustomer.CancelledDate)
Is Null))
ORDER BY TblCustomer.CustomerID;


Thanks Again,
Anthony


Anthony,

Not sure I understand the difference between the two queries. Can you post
their SQL?

Nikos
Click to expand...
Click to expand...
 
N

Nikos Yannacopoulos

Hey, I'd need the whole weekend to go through qryPackingSheets! David C.
Holley has got a point, do you really need all those fields?

Anyway, the good news is, if I'm reading this right, that the second
query is only filtering the first one for records with CustomerID
existing in qryOkToSTART, right? In other words, the second query could
well be:

SELECT * FROM qryPackingSheets
WHERE CustomerID In (SELECT CustomerID FROM qryOkToSTART)

This makes things a whole lot easier! In essense, it means that you want
to run your report with or without a filter. So, you only need the first
query, an option group on your form with options All and OK to start
(returning values 1 and 2 respectively), and this code in the On Open
event of the report:

Me.FilterOn = False
If Forms!YourFormName!YourOptionGroupName = 2 Then
Me.Filter = "CustomerID In (SELECT CustomerID FROM qryOkToSTART)"
Me.FilterOn = True
End If

HTH,
Nikos
 
A

Anthony

:)

Problem is Yes I do need all those fields...

But yes you do have it right.

I will give that a try, I had tried to use only one query but couldn't get
it to work.

Thank you for your help/persistance.
Anthony.
 
N

Nikos Yannacopoulos

Problem is Yes I do need all those fields...
Tough luck! As long as it doesn't slow down your query...

I will give that a try, I had tried to use only one query but couldn't get
it to work.
Click to expand...
Do post back to let me know how it went, or if you need some more help.

I'll be off for the weekend in 90 minutes, but will check back on Monday.

Good Luck,
Nikos
 
A

Anthony

The query isn't too bad generates the report within 1 sec,
but this single query option was extremely slow, took a couple of minutes.

At this point qryPackingSheets has about 1600 records, qryOktoStart has
none.

The 2 Query option with changing the recordsource generates the Report
within a second.

I have no idea why this happens.

Have a good weekend, and thankyou.
Anthony.
 
D

David C. Holley

I know a good Irish Pub & an even better Taverna that we could use to
work through the query (and some pints).
 
D

David C. Holley

So all of the fields are displayed in the report? If they represent at
least 85% of the fields in the underlying tables, I would use the
generic field selector * in the statement.
 
N

Nikos Yannacopoulos

The query isn't too bad generates the report within 1 sec,
but this single query option was extremely slow, took a couple of minutes.

At this point qryPackingSheets has about 1600 records, qryOktoStart has
none.

The 2 Query option with changing the recordsource generates the Report
within a second.
Click to expand...
I suppose subqueries can be slower than joins, and each one's delaying
effect is pobably multiplied, if you know what I mean. You might be
better off sticking with the two queries, but it doesn't hurt to know
another technique that might be useful somewhere else, does it?

Good luck,

Nikos
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calling report/setting Recordsource 6
Dynamically changing a report's recordsource property 6
Add a logo to the report header with code 2
SubReport RecordSource 3
Running Report via VBA 3
Change RecordSource for Report; Subreports 2
setting a reports recource programatically 6
Total Pages of Report 3

Top