Labels on Left Side of Report - KB Article

[John D]

MS has a Knowledge Base article describing how to "modify a report so that
when you print a multicolumn report, the labels for each row of information
in the columns are printed only along the left margin of the report."

(This is, of course, exactly what financial statement models in spreadsheets
usually look like - yet Access appears not to provide an "easy" report model
for this very fundamental type of report - but that's another story.)

http://support.microsoft.com/default.aspx?scid=kb;en-us;210044&Product=acc

The article presents a method to accomplish this, which includes seting the
OnFormat property of the Detail section to:

Dim i As Integer If Me.Left <Me.Width Then Me.NextRecord = False For i = 1
To 11 Me("txt" & i).Visible = False Me("lbl" & i).Visible = True Next i Else
For i = 1 To 11 Me("txt" & i).Visible = True Me("lbl" & i).Visible = False
Next i End If

My application has 22 fields, not 11. But I followed the step by step
instructions of this KB article exactly, except I changed the two references
to the number of "txt" and "lbl" fields to "1 to 22". Everything is set up
exactly as in this KB article except for this one difference. (I used the
Code Builder to enter and edit the Event Procedure above.)

But when I try to view the report I get an error message saying "Expected:
Identifier".

Can anyone see either what I'm doing wrong or what is wrong with the
KB-suggested Event Procedure?

Thanks

John D

 

[Klatuu]

The code is expecting text boxes named like
txt1 txt2 txt3 txt4 txt5 ... txt11
and
lbl1 lbl2 lbl3 lbl4 lbl5 ... lbl11

If that is not what your naming is, then you need to align either the code
to the names or the names to to code.

As to your comment regarding Excel. You are obviously an Accountant.
Accountants live and die by Excel. Excel is a spreadsheet, Access is a
relational database. A database is, by nature, a more restrictive
environment. That is because in a relational database environment, one of
the objectives is data integrety. Data integrety is something you do not get
in Excel. Spreadsheet data can never be trusted to be correct because there
are no validations of the data. You never know how the data got entered and
you can't be sure the formulas being used are accurate or have not been
changed.

The Access report writer is strong enough to do whatever you need from a
financial reporting perspective. It is just a matter of taking the time to
learn how to do it.

 

[John D]

The code is expecting text boxes named like
txt1 txt2 txt3 txt4 txt5 ... txt11
and
lbl1 lbl2 lbl3 lbl4 lbl5 ... lbl11

If that is not what your naming is, then you need to align either the code
to the names or the names to to code.

Klatuu - I double checked, and indeed the names I have for the text boxes
and labels are exactly what is specified in the code. As I said, I followed
the steps in the article exactly except I adapted it for 22 fields. Can you
see any other reason I'd get this error message?

As regards my maybe too "flip" comment about Access and this model of a
report, well - I'm not an accountant but I have done economic and business
analysis and planning for quite a long time. The world is full of information
organized as "time series" that are accomodated in databases - Census Bureau,
Bureau of Economic Analysis - the list goes on and on. The KB article states
"Microsoft Access does not have a report option that enables the printing of
labels only along the left margin of a report." Just seems to me that,
because so much information is organized as "time series", it would be
helpful if Access had such an option. Not everyone would use it perhaps, but
a lot of the world would I'd wager.

I am eager to learn how to use Access to produce such a report.

Thanks

John D

 

[Klatuu]

Nothing pops out as obvious. I read the article, and it all seems very
straight forward. Perhaps if you could post back the code as you have it in
your report, it could be helpfuls. Also, exactly where do you get the error?
Can you pin it down to a specific line?

 

[John D]

Klatuu

Well, I kept banging away at it and eventually came up with what is probably
an obvious solution. I put labels for row "titles" or "names" on the left of
the Detail section. I then created a multi-column sub-report with its fields
aligned with the row titles. At this point it works "pretty well" - a few
kinks I'm working out, and the big issue of how to have another column at the
end that sums all the columns in the sub-report. But - I'll keep whacking
away.

I was able later to get the KB article's method to work - but I'd already
gotten the above method to work as well, and so stuck with it.

Thanks - these Discussion Groups are amazing at how quickly you get help -
from all over the place.

John D