Replies

Hi Pat,

NB. I would like an explanation of when the PrintCount =1,2, etc, and under what conditions that happens

A section’s printcount apparently changes when Access executes the Print event for that section. If it prints the section more than once (when a section spans more than one page) it increments the PrintCount property to 2.

So, any accumulating should be done when PrintCount is 1, otherwise you get duplicated values.

(see Access Dev Handbook – page 686)

Hi Charlotte,

See also my reponses to Pat and Mark. (Sorry for being lengthy, but how can one explain complex issues in two words?)

GroupFooter1 is the footer for a single railcar record and several recovered parts records.

You asked: Are lngCarCount and lngZeroLightWeightCount module level variables?

Yes.

Public dblLightWeightTonsGT As Double
Public dblRecovTonsGT As Double
Public dblScrapTonsGT As Double
Public lngCarCount As Long
Public lngZeroLightWeightCount As Long

They are initialized with this event (which I think and hope occurs only once):

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
dblLightWeightGT = 0
dblRecovTonsGT = 0
dblScrapTonsGT = 0
lngCarCount = 0
lngZeroLightWeightCount = 0
End Sub

Hi Mark,

See my response to Pat.
Are your incorrect totals appearing when you print the report, or just when you preview it? Both.

You said: ..to try to do all your math in your query,…
That is what I tried. The data for the report is retrieved by a named query. Below the GT row, we also want a row showing the Average weight, average recovery weight and average scrap weight for a bunch of unique cars.

What is was doing is to provide the count of every part record. I want a single count for a single car, hence a single count for each car group total. That’s why I used the lngCarCount accumulator.

We are populating the db with data some years old and not all pieces are in yet. There are still some cars that don’t have a lightweight (the weight of an empty railcar) in the record. By including these zero LW records, the average per car (on the last page) would get skewed, so I decided to also count the cars with zero LW and substract them from the total car count for purposes of calculating the overall average per car.

But maybe, just maybe, there is a much better way of doing this. But my Access books show me in great detail how to place controls on the report (which I already know how to do) but there is not a single mention on how to make correct totals from complex queries.

Any help is greatly appreciated!

Hi Pat,

You asked: Why are you manually totalling for a Grand Total, why not use =Sum in the grand total variables instead?

I guess the truth had to come out some day! This is my very first report! I guess it shows .So far, I only have been using queries for my own purposes (to sanitize the data), but now that it is clean, requests for reports come in. I am a success (and the guy who ask for this report is weird) Just kiddin’.

To answer your question: Originally, to get started I used the report wizard and selected summary report.
Guess what. The totals were wrong. So, I went on a quest for correct totals and I discovered report events.

The report looks like this:
Car # KENM123456 (6 recs) Totals: (A) 40 Tons ( 5 Tons © 35 Tons

(A) is the column header for the total weight of the railcar (80,000 lbs/2000), ( the weight of the six parts that were recovered off the railcar (10000 lbs/2000) and © is the remainder (scrap steel) (A-.

The above line is a group total for a single car and there are 18 or 19 such group totals on each page – no details showing – although the # of recs shows the # of hidden detail lines (recovered parts).

In the underlying query, Access matches the weight of the car (80000 lbs) with each detail line. The car record and the part records connect on fldCarID. The result was that the =SUM of the tons displayed was ((6 x 80000)/2000) ie 240 tons. Definitely wrong.
So, instead I used the =Max([fldLightWeight])/2000, which selects the weight only once – ie 40 tons.
Each group is now correct, but the GrandTotal is still wrong because it too was based on the =SUM function. So, I hit on the idea to not use the =SUM in the GrandTotal, but to accumulate the values of the group totals while each was being printed (event).

I used Public vars, because each time the event finishes, the local vars will go out of scope.

Actually, it works perfectly, except for the fact that it doesn’t accumulate past Page 1. Perhaps it does, but the results of Page 1 are the ones showing on the last page (7).

Interestingly, when I run the report for a shorter period of time (a week instead of a month), the data only requires 1.5 pages, and the GT is just fine.

You said: NB. I would like an explanation of when the PrintCount =1,2, etc, and under what conditions that happens.

Under investigation!

Hi cat,

Just taking a stab.
Have you considered table relationships and joins?

Not entirely sure why my jpg didn’t attach, but I think hans already provided you with an example.

Hi Bob,

Spinner is a control with two buttons, an Up button and a Down button.

Click on the Spinner on the Forms toolbar, then click on the Excel sheet and drag it to the size you want.
While the ‘handles’ are still showing, right-click on it and select Format Control. This allows you to set the upper and lower limits of the counter, the value by which each click of a button increases or decreases the return value.

And, you can select the cell where the result (return value) will be dumped.

One way you could use it is to select a month (1-12) and then use the return value as a parameter for a cell formula. See attached file.

Hi Bob,

Tell me what you have in mind for the spinner.

Hi Cecil,

First, determine if Quickbooks has an import procedure (probably under File menu). If affirmative, determine what format the import file must be. Maybe CSV or another type of ASCII file. Different file layouts may be required for different types of import, eg customer records, journal entries, etc.

Once you know what the import file must look like, you can design what data you must capture in Excel (using an Excel form perhaps). When you have the data on a sheet, set the data range and (using a VBA macro), step through the rows in the range and write the data to the text file in the required format and data sequence.

This is the method I use to import data into our accounting app (not QB) from external systems, like Access, Excel etc. I use CSV, DBF and Fixed Width etc.

Hi Khushnood,

Thanks for reply. I probably wasn’t entirely clear on what I am having a problem with.

I would like to be able to trap the Pivot_Change event (if such an event exists) when the user makes a different selection from a Page field(cell).

As I have it now, the code is being executed when the Worksheet_Calculate event occurs. That means it also occurs when something other than a Pivot_Change event occurs.

I came up with a solution that works, but requires a few extra clicks. The solution consists of a message box that asks whether the pivot has changed. Only when I click the Yes button will it execute the code that removes the formulas from the old location and places them in the new location. It works.

Private Sub Worksheet_Calculate()
……Dim myLastCell As String
……Dim myResponse As Integer

……myResponse = MsgBox(“Was pivot changed?”, vbYesNo, “Information required”)
……If myResponse = vbYes Then
…………Application.Calculation = xlManual
…………Remove_Formulas ‘remove formulas from old location
…………Insert_Formulas ‘insert formulas at new location

…………GotoLastCell
…………myLastCell = ActiveCell.Address(ReferenceStyle:=xlA1)
…………ActiveSheet.PageSetup.PrintArea = “$A$1:” & myLastCell
…………Application.StatusBar = “Lastcell: ” & myLastCell
…………Application.Calculation = xlCalculationAutomatic
……End If
End Sub

RE: i’m assuming that the formula rows are stored for copy-paste somewhere else, or you can re-create them at run-time.

I just re-create them at runtime – inside a loop. Had not considered the copy/paste solution. See, I learn something different each time.

Thanks Howard! I appreciate it.

Hi jscher,

re: One of the recent nasty viruses used a flaw in IE (MIME header vulnerability) to automatically download/open an EML file, which caused all sorts of havoc…..

Yup, PE NIMDA.A-O caused all the havoc. 285 corrupt files. Luckily, antivirus.com had a fix tool.

Back on track! Phew…

Hi Howard,

I know why I have to install the updates. (Sometimes I hesitate a bit before taking uncle Bill’s advise – he doesn’t come over to my house to clean up the mess!).

My question was: Is it safe to do?

Maybe I should re-phrase the question: Has anybody done the update with Win XP installed and suffered adverse consequences?

(Please don’t tell me I am the first to try – )

Hi unka

I didn’t find anything that specifically addressed my concerns.

Maybe something will show up soon. I’ve got time..
(I’ve done already a long time without it )

Hi Wassim,

Thanks for reply!

Re: virus software. Not sure whether they have any. I have alerted our IS people.

There was one additional file in the XLstart folder: MScreate.dir. I have 112 instances of this file on my own pc and they are all zero byte files. So I told her to delete it.

We found no Personal.xls on C: or D: and the Windows|Unhide option was greyed out.

I am also suspecting a network problem. Maybe a dll is missing an we may need to re-install. Or perhaps
she lacks certain rights on D:.

I’ve asked the IS people to become involved now.

Thanks!!