 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / July 2005Changing Recordsource of a report using Code
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. > Hi, > [quoted text clipped - 15 lines] > Thanks in advance, > Anthony. Have the report change it's own recordsource in it's open event. 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 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 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 & ";" > Anthony, > [quoted text clipped - 10 lines] > HTH, > Nikos 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. > Anthony, > [quoted text clipped - 44 lines] > HTH, > Nikos 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 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. Anthony, Not sure I understand the difference between the two queries. Can you post their SQL? Nikos 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 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, > [quoted text clipped - 176 lines] >> >>Nikos 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 :-) 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. > 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? [quoted text clipped - 20 lines] > HTH, > Nikos > 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. 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 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. >> Problem is Yes I do need all those fields... > Tough luck! As long as it doesn't slow down your query... [quoted text clipped - 7 lines] > Good Luck, > Nikos > 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. [quoted text clipped - 4 lines] > The 2 Query option with changing the recordsource generates the Report > within a second. 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 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. > :-) > [quoted text clipped - 32 lines] >>HTH, >>Nikos I know a good Irish Pub & an even better Taverna that we could use to work through the query (and some pints). > 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? [quoted text clipped - 21 lines] > HTH, > Nikos I doubt that any single Irish pub or taverna will be prepared to provide enough booze for this query! Have a nice weekend, Nikos 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. > Anthony, > [quoted text clipped - 45 lines] > HTH, > Nikos Place the code in the onOpen event of the Report. > Hi, > [quoted text clipped - 15 lines] > Thanks in advance, > Anthony. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.