Khalid-
Add a text box to the page header of the main report and set its Control Source to:
=[Forms]![frmRunCrosstab]![cboYear]
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 25, 2015, at 5:08 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
I've deleted both sub report and report. Then re-named their copies to original report names.
I kept the original settings of sub report and report. Only changed/reduced the widths of other controls:
ClientCIN
ClientName
Total Of CartonNo
Total Weight
I am not getting any error up to now.
Now if you could see to get Report Year on Report Header, the Year which is selected from the form "frmRunCrosstab"
Once again thanks for being in touch giving your kind advise.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
Sounds like you've run into a bug in Access 2003. You could try fixing the Column Size in the subreport to see if it still occurs. If it is still breaking, create a new database and import all the objects from the old one.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 25, 2015, at 2:31 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John,When i run the form "frmRunCrossTab" and clicking on the command button "cmdRunXTabReport" to open the report, i get Microsoft Access Office message "Microsoft Access Office has Stopped working and below that the typical lines-> Check online for a solution and close the program-> Close the programIn details following are the lines:Problem event name: APPCRASHApplication Name: MSACCESS.EXEApplication Version: 11.0.5614.0Application Timestamp: 3f3c8e3cFault Module Name: MSACCESS.EXEFault Module Version: 11.0.5614.0Fault Module Timestamp: 3f3c8e3cException Code: c0000005Exception Offset: 0002e657OS Version: 6.1.7601.2.1.0.256.48Locale ID: 2057Additional information about the programLCID 1033Brand: Office11Crashskulcid 1033I dont know i had to give this information or not, but this is all. I have not re-named any form, query and report name.Regards,Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :Khalid-Please explain what you mean by "crashed". What was the error message? Was there an error in VBA?If you change the width of the text boxes in the subreport, then you have to also change the Column Size in Page Setup / Columns to match your new width.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)On Feb 25, 2015, at 7:09 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John and Duane,After sending confirmation that the report is OK, i am once again back with you.John, i showed the report to my boss and it was appreciated and approved and asking for few more things on the report.1- Year of report to be shown on Report Header.2- Total Weight of Cartons to be shown next to Count of Cartons.3- Sum of Total Of CartonNo and Sum of Total Weight on the report Footer.I did not tried the Report Year on the Report Header yet. Now the query is:TRANSFORM Count(CollectionVoucher.CartonNo) AS CountOfCartonNoSELECT tblConsignmentAlias.Level, Clients.ClientName, CollectionVoucher.ClientCIN, Count(CollectionVoucher.CartonNo) AS [Total Of CartonNo], Sum(CollectionVoucher.WeightOfCarton) AS [Total Weight]FROM Clients INNER JOIN (CollectionVoucher INNER JOIN tblConsignmentAlias ON CollectionVoucher.ConsignmentNo = tblConsignmentAlias.ConsignmentNo) ON Clients.ClientCIN = CollectionVoucher.ClientCINGROUP BY tblConsignmentAlias.Level, Clients.ClientName, CollectionVoucher.ClientCINORDER BY CollectionVoucher.ClientCIN, tblConsignmentAlias.ColumnAliasPIVOT tblConsignmentAlias.ColumnAlias In ("A","B","C","D","E","F","G","H");John, please also see that did i changed Module1 as you mentioned:Option Compare DatabaseOption ExplicitFunction UpdateConsignmentAlias(pbytNumColumns As Byte) As Long'============================================================' Purpose:' Copyright: 2015' Company: NA' Phone:' E-Mail:' Programmer: Duane Hookom' Called From:' Date: 1/22/00 modified 2/18/2015' Parameters:'============================================================On Error GoTo UpdateConsignmentAlias_ErrDim strErrMsg As String 'For Error HandlingDim strSQL As StringDim intAlias As IntegerDim bytLevel As ByteDim lngEmpID As LongDim bytMaxColumns As ByteDim db As DatabaseDim rs As RecordsetstrSQL = "Delete * from tblConsignmentAlias"DoCmd.SetWarnings FalseDoCmd.RunSQL strSQLstrSQL = "INSERT INTO tblConsignmentAlias (ConsignmentNo) " & _"SELECT DISTINCT ConsignmentNo " & _"FROM CollectionVoucher " & _"WHERE LEFT([ConsignmentNo], 4) = '" & [Forms]![frmRunCrosstab]![cboYear] & "' " & _"ORDER BY ConsignmentNo"DoCmd.RunSQL strSQLDoCmd.SetWarnings TruebytMaxColumns = pbytNumColumnsSet db = CurrentDbSet rs = db.OpenRecordset("tblConsignmentAlias") 'table used to redefine/alias the column headingsWith rsIf Not (.EOF And .BOF) Then.MoveFirstDo While Not .EOF'lngEmpID = !employeeIDbytLevel = 0intAlias = 65 'ascii value of 'A'Do While True '!employeeID = lngEmpID.Edit!Level = bytLevel!ColumnAlias = Chr(intAlias) 'assign alias A - whatever.UpdateintAlias = intAlias + 1If intAlias = 65 + bytMaxColumns ThenbytLevel = bytLevel + 1intAlias = 65End If.MoveNextIf .EOF ThenExit DoEnd IfLoopLoopEnd IfEnd WithUpdateConsignmentAlias_Exit:On Error Resume Nextrs.CloseSet rs = NothingSet db = NothingExit FunctionUpdateConsignmentAlias_Err:Select Case ErrCase ElseUpdateConsignmentAlias = Err.NumberResume UpdateConsignmentAlias_ExitEnd SelectEnd Function=============After doing the necessary formatting and running/checking the report 2-3 times, results were OK but it DID CRASHED, I made the copy before making changes, and did tried 3 times again and report CRASHED.i don't know why it is happening again and again.One thing which i did is that in report "srptConsignmentNumberHeadings" i reduced the width of Text box ConsignmentNo to 1.905cm. And so in the report "rptConsignmentCrosstab" reduced the width of Text boxes A to H to 1.905cm, and on the Level Header of this report i clicked and dragged from the left to right "srptConsignmentNumberHeadings" to my required length. Is this the mistake i'm doing?Only this report "rptConsignmentCrosstab" is crashed all other forms/reports/queries are OK.Please check what is going on wrong. And also see to display Report Year on the Report Header.Regards,Khalid
---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :John,Thank you very much. Every thing is perfect and OK now.I also added first Control on the report ClientCIN before ClientName.I once again thank both of you (Duane & you) without help & guideness of both of you i could have not build this report.Regards,Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :Khalid-To get rid of the year anomaly, you would have to change the query and some of the code. Don't use the Consignment Number table at all. Select the records based on the first four digits of then ConsignmentNo.The revised query looks like this:TRANSFORM Count(CollectionVoucher.CartonNo) AS CountOfCartonNoSELECT tblConsignmentAlias.Level, Clients.ClientName, CollectionVoucher.ClientCIN, Count(CollectionVoucher.CartonNo) AS [Total Of CartonNo]FROM Clients INNER JOIN (CollectionVoucher INNER JOIN tblConsignmentAlias ON CollectionVoucher.ConsignmentNo = tblConsignmentAlias.ConsignmentNo) ON Clients.ClientCIN = CollectionVoucher.ClientCINGROUP BY tblConsignmentAlias.Level, Clients.ClientName, CollectionVoucher.ClientCINORDER BY CollectionVoucher.ClientCIN, tblConsignmentAlias.ColumnAliasPIVOT tblConsignmentAlias.ColumnAlias In ("A","B","C","D","E","F","G","H");In Module1, change the SQL in UpdateConsignmentAlias to:strSQL = "Delete * from tblConsignmentAlias"DoCmd.SetWarnings FalseDoCmd.RunSQL strSQLstrSQL = "INSERT INTO tblConsignmentAlias (ConsignmentNo) " & _"SELECT DISTINCT ConsignmentNo " & _"FROM CollectionVoucher " & _"WHERE LEFT([ConsignmentNo], 4) = '" & [Forms]![frmRunCrosstab]![cboYear] & "' " & _"ORDER BY ConsignmentNo"I was able to add a Sort on ClientCIN in the report (Use Sorting and Grouping) to change the output sequence.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)On Feb 23, 2015, at 12:01 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:Duane,Thanks once again for your Report. I have done some changes in the design as per my needs, like making Report Header, in Report Footer putting Totals of Cartons under each ConsignmentNo. Changing some Captions on the "frmRunCrosstab".Although the report is perfect and i'm going to use it. You might have noticed in my data that in table "Consignment Number" for ConsignmentNo 2014-B-08 ExportDocs date is 15/01/15 that is why this ConsignmentNo is not displaying in Year 2014, but it appears in Year 2015. This situation might occur in future like ConsignmentNo is say 2015-A-10 and ExportDocs date is suppose 20/02/16.For this i was thinking that should i add a new field like "ConsignmentYear" in table "Consignment Number" and while adding a new ConsignmentNo fill that field with Left 4 digits of ConsignmentNo, and then we just change in our all coding "ExportDocs" with "ConsignmentYear" so that we may get in our report all Consignments in their respective year.Is my approach right? or i am on the wrong? Please guide.In the report "ClientName" is displaying in Descending Order and in query "qryKhalid_DH" ClientCIN has Sort Order Ascending. I tried to add the Control "ClientCIN in report but it does not shows me ClientCIN in Ascending Order and ClientName in Descending Order. My requirement is to display ClientCIN in Ascending Order with their respective ClientName. I am unable to get it resolve.Could you please be helpful again.Thanks in advance.Regards,Khalid
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :Khalid and John,
I just uploaded a crosstab report demo using the same data. The report design allows for a predefined number of columns based only on the horizontal width of the page. If the actual crosstab would generate additional columns, they are automatically "wrapped" below the first set of columns. For instance if the columns were the month January - December and you only had width for 6 columns January - June would display first in a group and July - December would display later. There are realistically no limits to the number of column headings that could be generated with this solution.
There is much less code and probably more efficient with larger data sets.
It took me a while to review my demo and re-purpose it with Khalid's data. Let me know if you have any questions.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 18 Feb 2015 16:59:34 +0100
Subject: Re: [MS_AccessPros] Report based on Crosstab query
Khalid-All you need are the rptKhalidMT report and the ztblKhalidReport table. You can rename the report and change the report header. If you want to rename the table, you will have to fix the code behind the report that references the table.' Clear out the "working" tabledb.Execute "DELETE * FROM ztblKhalidReport", dbFailOnErrorand' Open the output recordsetSet rstO = db.OpenRecordset("ztblKhalidReport", dbOpenDynaset, dbAppendOnly)And you will have to change the Record Source of the report.You should copy those two objects into your front end. Do not modify your existing backend.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)On Feb 18, 2015, at 4:26 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (46) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar