Re: [MS_AccessPros] Re: Report based on Crosstab query

 

Khalid-

I assume _be has all your data in it.  Is PCTL.mdb a front-end using linked tables to PCTL_be??  If so, you should not mess with your existing back end.  Rather, you should import these objects into PCTL.mdb:

ztblKhalidReport

qryKhalid

qryKhalid_DH

frmRunCrosstab

frmRunCrosstabJV

rptConsignmentCrosstab

rptKhalidMT

srptConsignmentNumberHeadings

Module1

Yes, you should be able to add totals for all the columns.

Note that I didn't do a good job lining up the Fldxx text boxes under their labels in rptKhalidMT, but that should be easy to fix.

In Access 2003, choose Tools / Options / General and clear all the boxes under Name AutoCorrect.

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 

http://www.viescas.com/ 

(Paris, France)

On Feb 26, 2015, at 1:24 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Thank you very much!

I have checked both reports. Few more questions before proceeding further.

1- Should i delete my existing PCTL_be ?

2- Rename your PCTL_be_JV to PCTL_be and put in the folder PCTL for path ? (During this period i've made few new entries in my db, which i have saved exporting those tables in Excel and shall re-enter them.)

3- What to do with my existing PCTL.mdb ?

4- I will be using Form "frmRunCrosstabJV" and its report, and i am sure i would be able to modify Report Header and Report Footer adding text boxes for Sum under Fld01 to Fld10 ?

For future where can i see and check whether "Track Name AutoCorrect and Perform Name AutoCorrect turned" are turned off.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

The sample file was badly corrupted as a result of leaving Track Name AutoCorrect and Perform Name AutoCorrect turned on.  I rebuilt the database and fixed both my and Duane's samples per your latest requirements.

Watch for the upload notification.

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 

http://www.viescas.com/ 

(Paris, France)

On Feb 26, 2015, at 9:28 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

This has become hectic for me, again when i put a text box on the report header and set its Control Source to:

=[Forms]![frmRunCrosstab]![cboYear]

 

On opening the report same thing happened i run into a bug in Access 2003, which i mentioned earlier as CRASHED.

I am now thinking to delete this report and all its relevant code tables/query and form. I want to have a go with your report "rptKhalidMT".

Now you may please check that it was not showing ConsignmentNo 2014-B-08 for ExportDocs 15/01/15, which you rectified in Duane's query.

I'm not going to re-name any thing from your db PCTL_be_John. Also i am sure in your report i will not face any problems changing the widths of columns & rows. You will please also tell the Control Source for the text box on Report Header to get Year of report.

While reading open event of your report, does the following line of code means that input box is generated automatically with this code, as i can not see any form in your PCTL_be_John? 

' Prompt for the year filter

    strYear = InputBox("Enter year (4 digits) to filter:", "2012")

Hope you would take the pain again to resolve my issue, as i have already told my boss and shown him earlier version of Duane's report, in which he said to add some controls.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

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 

http://www.viescas.com/ 

(Paris, France)

On Feb 25, 2015, at 5:08 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

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 

http://www.viescas.com/ 

(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 program

In details following are the lines:

Problem event name: APPCRASH

Application Name: MSACCESS.EXE

Application Version: 11.0.5614.0

Application Timestamp: 3f3c8e3c

Fault Module Name: MSACCESS.EXE

Fault Module Version: 11.0.5614.0

Fault Module Timestamp: 3f3c8e3c

Exception Code: c0000005

Exception Offset: 0002e657

OS Version: 6.1.7601.2.1.0.256.48

Locale ID: 2057

Additional information about the program

LCID 1033

Brand: Office11Crash

skulcid 1033

I 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, 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 

http://www.viescas.com/ 

(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 CountOfCartonNo

SELECT 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.ClientCIN

GROUP BY tblConsignmentAlias.Level, Clients.ClientName, CollectionVoucher.ClientCIN

ORDER BY CollectionVoucher.ClientCIN, tblConsignmentAlias.ColumnAlias

PIVOT 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 Database

Option Explicit

Function 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_Err

   Dim strErrMsg As String 'For Error Handling

   Dim strSQL As String

   Dim intAlias As Integer

   Dim bytLevel As Byte

   Dim lngEmpID As Long

   Dim bytMaxColumns As Byte

   

   Dim db As Database

   Dim rs As Recordset

   

   

    strSQL = "Delete * from tblConsignmentAlias"

   DoCmd.SetWarnings False

   DoCmd.RunSQL strSQL

   strSQL = "INSERT INTO tblConsignmentAlias (ConsignmentNo) " & _

        "SELECT DISTINCT ConsignmentNo " & _

        "FROM CollectionVoucher " & _

        "WHERE LEFT([ConsignmentNo], 4) = '" & [Forms]![frmRunCrosstab]![cboYear] & "' " & _

        "ORDER BY ConsignmentNo"

   DoCmd.RunSQL strSQL

   

   DoCmd.SetWarnings True

   

   bytMaxColumns = pbytNumColumns

   

   Set db = CurrentDb

   Set rs = db.OpenRecordset("tblConsignmentAlias") 'table used to redefine/alias the column headings

   With rs

      If Not (.EOF And .BOF) Then

         .MoveFirst

         Do While Not .EOF

            'lngEmpID = !employeeID

            bytLevel = 0

            intAlias = 65   'ascii value of 'A'

            Do While True   '!employeeID = lngEmpID

                 .Edit

                  !Level = bytLevel

                  !ColumnAlias = Chr(intAlias) 'assign alias A - whatever

               .Update

               intAlias = intAlias + 1

               If intAlias = 65 + bytMaxColumns Then

                  bytLevel = bytLevel + 1

                  intAlias = 65

               End If

               .MoveNext

               If .EOF Then

                  Exit Do

               End If

            Loop

         Loop

      End If

   End With

UpdateConsignmentAlias_Exit:

   On Error Resume Next

   rs.Close

   Set rs = Nothing

   Set db = Nothing

   Exit Function

UpdateConsignmentAlias_Err:

   Select Case Err

      Case Else

         UpdateConsignmentAlias = Err.Number

         Resume UpdateConsignmentAlias_Exit

   End Select

   

End 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 CountOfCartonNo

SELECT 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.ClientCIN

GROUP BY tblConsignmentAlias.Level, Clients.ClientName, CollectionVoucher.ClientCIN

ORDER BY CollectionVoucher.ClientCIN, tblConsignmentAlias.ColumnAlias

PIVOT 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 False

   DoCmd.RunSQL strSQL

   strSQL = "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, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQ

(Message over 64 KB, truncated)

__._,_.___

---

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 (50)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___