Report Design Theory and Practice

I need to create a report based on a table that has a primary key, 5 native fields and 12 foreign keys.  I need more granualar control over how the report behaves than HideDuplicates and IsVisible will give me.  The primary key need not be printed visibly but the other sixteen fields must occupy a single line on the report.  I need the report to display textboxes because the CanGrow property will need to be used.  Combo boxes for the foreign keys are therefore ruled out.  Textboxes drawn from a query of the table in question at present return the (integer) foreign key value, not the required text values.

Moreover, depending on the value of the field, the value of fields earlier in the row, and the previous value of the field, the report must display the value, display "-", or be Visible=False.  As you can see, I will need VERY granular control over the reports controls.  I have little experience and even less luck manipulating objects like

Dim ctl as control
dim rs as recordset
set rs=OpenRecordset(some sql statement)

for each somedamnthing in rs
check it against criteria
decide what was needed
get the value and set it equal to ctl.value

populate the report
next do it again dammit


Nothing I have googled has really pointed out how Access reports operate in theory, and how to take things a step farther.  Like on continuous forms, report controls are multiple instances of a single object whose values change with each row in the recordset.  What I tried originally was to put controls from the underlying report query on the form, set them to Visible=false and then add unbound textboxes whose data source
= IIF("hiddenquerycontrol".value=somecondition,outcome1,outcome2)
That quickly got ugly with zillions of nested IIf statements to test multiple conditions.  Next I tried creating custom functions to set the textbox's value.  PROBLEM: I don't get multiple instances of the control. I only get the last value in the recordset in a single textbox.

There is an open question worth 500 points regarding that specific attempt to solve this at

Full points here will go to those who help create an answer that will aid other poor buggers like me who need very fine granular control of reports.  Emphasis on theory and design are appreciated

LVL 26
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

i suppose you use some query as recordsource

manipulating the controls on reportlevel is cumbesome, so why not do it in the recordsourcequery:

build your line with the 16 extenal keys as one field:

SELECT myTable.myNativeFields, (SELECT myExtTable1.myExtTextField1 FROM myExtTable1 ((WHERE myExtTable1.Key= myTable.ForeignKey1))) As FF1, (SELECT myExtTable2.myExtTextField1 FROM myExtTable2 ((WHERE myExtTable1.Key= myTable.ForeignKey2))) As FF2, .........................................., Switch(Len(FF1)>0,"FiledTextFF1: " & FF1 & " - ","") & Switch( Len(FF2)>0,"FiledTextFF2: " & FF2 & " - ","") & Switch(...) & ......................................) AS myLine
FROM myTable INNER JOIN (................your foreign table joins.......................);

so what is in this:
the Subselects supply the neede Textvalues from the foreign tables and allocate friendly names to them (FF1,FF2,...)
the Switch statements  handle the conditional output and if necessary formating
ths Switches are connected to a single field : myLine

Regards; Franz
Nick67Author Commented:
I get the gist of your proposal, but with seventeen fields and three+ conditions per field that would be on bear of an ugly SQL statement to write and debug.  My report will only draw <100 records but the performance of a big SQL statement like you have proposed might be a little suspect.  Performance tips I have browsed on the web reccommend that you save recordsources for objects as queries AND avoid multi-table queries when possible.

What I have learned since posting this question is this:

the value of bound controls on a report CANNOT be manipulated
the value of unbound controls on a report is subject to manipulation
the OnFormat event of the section header (in this case the Details Header) is the place to put code to manipulate the unbound controls. (ie private functions that compare at bound controls run-time values to criteria and then set the unbound controls value accordingly can be built and then called in the OnFormat event)
the use of sorting and grouping to add a header with a height greater than zero (minimum height of 0.007") will allow for more granular control of the HideDuplicates property.

Anybody want to add to things that novice report-makers should know about when trying to create complex reports?

Nick you are right, we are talking about a little SQL monster, but it is in fact less complex than the VBA on the form
just start with two or three tables, look how it is going - then you simply cpoy ther rest along and change the names one by one and test after adding the next
so you end up with an ugly SQL, but it is working within reasoable time, especially when you are a report novice.
of corse one could do it more elegant in VBA, but to talk you throught this in batch reply mode will take much more time, so i encourage you to try the sql. dont let the theoretical tasks bother you. what counts is results and used time.


PS: there is reason, why very few fellow experts join in on this q

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Author Commented:
I guess it is splitting hairs.  What I have wound up doing, and testing to great satisfaction, is writing a function for each unbound textbox on the report to set its value based on the value of a hidden, bound contol on the field.  In cases where the bound field is a foreign key, I have written reasonably small SQL statements powering an
rs = db.OpenRecordset (SQL)
 to retrieve the desired text field.  I then can put either the foriegn key integer, or the retrieved text through a Select Case structure to test some of the conditions I need to handle.  There was therefore no need to screw around with the HideDuplicates or IsVisible properties in code.

The OnFormat event for the details section calls each of the functions to set the unbound textbox values.  You CANNOT screw around with the values of bound controls, period.  What I now AM able to do is test conditions of the values of the unbound textboxes through another Select Case structure and set properties AND values accordingly. This gives me the granular control over the report output that I wanted.

What I had hoped to spark with this question was input into best practices and theory in making reports that go well beyond what the Report Wizard can provide.  I had little or no luck googling anything substantive on the topic.  At the very least, this question will come up near the top of any google searching for "Access HideDuplicates isVisible OnFormat"

 I would have dearly loved to know that building a report with visible unbound textboxes referencing hidden bound text/combo boxes would be one way to accomplish what I needed.  The documentation available on the web on this topic is scant.  I would like some input from more experienced people saying "Nice job, good idea" or "hey dumbass, there is a far easier way to accomplish that"

Regarding your PS, why do you think no one has piped up?

Nick, i see your point

but is is timeconsuming to discuss complex reporting issues without working on a specific database. access reporting is unhandy, but there is a lot of tricks to deal with that.
so if your intention is to get deeper understanding on advanced issues, i recommend some special sites like

i recommend not to go into complex structures, you willi spend lots of time there (especially on fine granular control), i do complex report by breaking them up into subreports, trying to get as much as possible to the sql-side.
furtheron i avoid using th built in grouping and pagebreak properties, use the "manual" pagebreaks, which you can set visible/invisible in connection with variables.

regarding your "hiddencontrol" question, you can also have white print on white - less coding

Regards, Franz
Nick67Author Commented:
Other fun things that I have learned the hard way.

Lets say you have a bunch of textboxes on a report that have CanGrow set to true and you have their borders set to visible so you get a nice datasheet look to the report.  Then something grows.  And now you have a bunch of unwanted white space where the rest of textboxes did not need to grow. You want to set all the textbox heights to the tallest of the CanGrow (didgrow) boxes. Now what?  Well, with (google cached version, original deleted)

the experts gave up

Others have gone to and gotten code to programmatically draw the "cell" boundaries at run-time with line objects

One post at the utteraccess discussion forum pointed at a solution which I used too!

I wrote a couple of functions that get called in the Details_OnFormat event

Private Sub BoxHeight()

Dim Myheight(x) As Single
dim x as integer 'number of controls you have in Details section
Dim i As Integer

Dim currentVal As Single

'the first part figures the width in twips your whole line will take in current font and font size: nice for variable width font!
'the second part is the controls fixed width.  You can do a .TextHeight to figure out how high a single line of text will be
'this ratio tells you how many lines you'll have.  Multiply it by your text height and you'll have a max box height.

    With Me
Myheight(0) = Nz(Me.TextWidth(Nz(Me.txtFirstTextBox, "")), 0) / Nz(Me.txtFirstTextBox.Width, 1)
... however many you may have--i had 18
Myheight(x) = Nz(Me.TextWidth(Nz(Me.txtLastTextBox, "")), 0) / Nz(Me.txtLastTextBox.Width, 1)
    end with

    ' Cycle through each value from the row to find the largest.

   For i = 0 To UBound(Myheight)
      If Myheight(i) > currentVal Then
         currentVal = Myheight(i)
      End If
   Next i

   ' Return the maximum value found.
   maxheight = currentVal
end function

Now my font and size gave a minimum height of 180 twips.  My boxes are  two + rows high to start with (420 twips)
Next function!

Private Sub FixBoxHeight()

   'set all textboxes to max height
   If maxheight > 2 Then
'adding the half makes sure that things get rounded up when the text is going to grow
        maxheight = Round((Round(maxheight + 0.5) * 210), 4)
    maxheight = 420
    End If
       With Me
        .txtFirstTextBox.Height = maxheight
... and so on
      end with

end function

The final thing is to call the functions in the Detail_OnFormat event AND after calling them...

call boxheight()
call fixBoxHeight
'put in this line
Me.Section(acDetail).Height = 420 'the height of my original boxes

The section height reset is necessary--why, I'm not sure, but it is needful and works

This is something that report rookies may dearly want to do--and is also poorly documented.

Any comments, anyone?


Nick67Author Commented:
Oh, sure, sure!
After I burned out half my gray matter building my report I find this
How to use Visual Basic to fill text boxes on a report in Microsoft Access

Why, oh why, couldn't i have found this sooner?

This is EXACTLY how I did it, after much debugging!
Hi Nick

looked at the above link.
very elegant method, but it looks like it will scale all lines of the report to the max. height needed. this will may lead to lots of empty space.
i tried another method using the TextHeight and TextWidth Properties
this is a trick matter, but i found something, which you may improve to a working solution:
it gives fairly results for Arial 8pt, the algoritm needs some finetuning

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Dim ctl As Control
  Dim maxH, testH, testW, testWW, testHH As Long
  Dim x
  testH = 0
  maxH = 0
  Me.ScaleMode = 1
  Me.FontName = "Arial"
  Me.FontSize = 8
  'calculate something like the max. Height in this record
  For Each ctl In Me.Controls
    If IsNull(ctl.Value) Then GoTo Conti
    testW = ctl.Width * 0.9
    testWW = TextWidth(ctl.Value)
    testHH = TextHeight(ctl.Value) * 1.3
    testH = testHH * Abs(Int(-testWW / testW))
    If testH > maxH Then maxH = testH
  Next ctl
  'setting back the detailsection height from last record
  Me.Detail.Height = 0
  ' apply the max height to all fields of the record
  For Each ctl In Me.Controls
    ctl.Height = maxH
  Next ctl
  maxH = 0
End Sub

Regards, Franz
The above makes me think of two rather complex things I did with reports.

1) vertical centering...

Yes, I needed to center various pieces of information vertically within each row. I toyed a little with the .TextWidth and .TextHeight methods, but they didn't give me enough control (probably due to my inability to manage additional margins arount these values... I finally  found an elegant solution:
Let the section format, but cancel the printing (at that time the *true* height of each control was known, but Access no longer allows to change the .Top of the controls). Store the values and print again!
On the second pass of formatting, set the .Top values in order to center the controls... No luck, Access prevents that.

So? I used *two* sections. The first being a group on the key field, it duplicated the detail section with all relevant fields (same size, same font, etc.). The I could use the information during the (canceled) printing of the first section to set the proper values in the second's format event...

I ended up with a class module, that successfully managed half a dozen reports, set up with *dual detail sections"...

2) drawing flowchart boxes.

In the same context, it was thought it would be nice if each number of a procedure phase could be printed on a symbol representing the type of operation (input, decision, etc...). I played for a while with a linked table containing the drawings, but I ended up drawing them during the pint event! Just before printing, you can still draw lines, circles and arcs, with which I rebuilt each symbol...

Either way, the drawing of an Excel-like grid, where each cell can make the entire row grow, it one of the things most often asked by Access users. I often created custom Excel export features (with formatting) just to answer that need...

I know this is not helping, but I just wanted to show that we all have done crazy things with reports and other things when we wanted to work around some limitations...

But I did find something useful for you... (in the next comment)

Here it goes. I couldn't do any complex reporting without something like this... In fact, it is often the very first module I write when working over an exiting database :)
[I kept only some relevant examples and added a few comments.]

' This module implements fast lookup functions and calculations between rows
' of the same table.
Option Compare Database
Option Explicit

' I need the following function for a FE/BE application. As I want to open
' table-type dynasets, they must be opened in the BE database. As I will
' it very often, it is stored as a static variable.
' If I need to close this instance (e.g. before compacting the BE or when
' the user relinks the tables), I call: LinkedDb False
' For tables in the same database, replace LinkedDb with CurrentDb, or
' use a similar mechanism to keep one open instance as ThisDb, inspired by
' the following...
Function LinkedDb(Optional pfOpen As Boolean = True) As Database
' Returns the database containing the linked tables
    Static sdbLinked As Database
    Dim strPath As String
On Error GoTo LinkedDb_Error
    If Not pfOpen Then
        Table ""   ' close all tables
        If Not sdbLinked Is Nothing Then sdbLinked.Close
        Set sdbLinked = Nothing
    ElseIf sdbLinked Is Nothing Then
        strPath = Mid$(CurrentDb.TableDefs("tblFunds").Connect, 11)
        Set sdbLinked = OpenDatabase(strPath)
    End If
    Set LinkedDb = sdbLinked
    Exit Function
    Set sdbLinked = Nothing
    Exit Function
End Function

' In the next function, I use a collection to store all open tables.
' This is essential as I will use them up to several thousand times during
' a single query or during the formatting of a report...
' To close all tables, use: Table ""
Function Table(pstrName As String) As Recordset
' Returns a table-type recordset for the pased table name.
' Index is set to the PrimaryKey
    Static scolRecordsets As New Collection

    Dim recTable As Recordset

    If pstrName = "" Then
        ' should use .Close on all tables, but it works without :)
        Set scolRecordsets = New Collection
        Exit Function
    End If

On Error Resume Next

    Set recTable = scolRecordsets(pstrName)
    If Err Then
        Set recTable = LinkedDb.OpenRecordset(pstrName, dbOpenTable)
        scolRecordsets.Add recTable, pstrName
    End If
    ' reset to primary key if the table has been used before
    ' note that all indexes start with "ndx"...
    If recTable.Index <> "ndxPrimaryKey" Then recTable.Index = "ndxPrimaryKey"
    Set Table = recTable

End Function

' Now the fun part: how do we use that?
' Example 1: a lookup function. Simply return the currency of a fund.
Function FundCurr(pvarFund)

    FundCurr = Null
    With Table("tblFunds")
        .Seek "=", pvarFund
        If Not .NoMatch Then FundCurr = !strCurrNAV
    End With
End Function

' Example 2: equivalent to a DMax() (or rather Last()) function
' Returns the last date for which there is data:
Function FundLastDate(pstrFundCode As String)

    FundLastDate = Null
    With Table("tblFundsDaily")
        .Seek "<=", pstrFundCode, #12/31/9999#
        If Not .NoMatch Then _
            If !kstrFundCode = pstrFundCode Then _
                FundLastDate = !klngDate
    End With

End Function

' Example 3: beyond DLookup. This uses a record and the next record
' In this case, we want to show movements of shares, by comparing the
' number of shares at a given date with that at the next available date.
Function NewShares(plngInvestID As Long, plngDate As Long)

    Dim dblSharesNow As Double
    NewShares = Null
    With Table("tblShareholdersDaily")
        .Seek "=", plngInvestID, plngDate
        If .NoMatch Then Exit Function
        dblSharesNow = !dblShares
        If Not .EOF Then
            If !klngInvestID = plngInvestID Then
                If !dblShares <> dblSharesNow Then
                    NewShares = !dblShares - dblSharesNow
                End If
            End If
        End If
    End With

End Function

' Example 4: beyond Find_Duplicates. This will return a flag if the
' shareholder number is used in more than one investment (which must
' therefore be identified with the secondary field ProductCode).
' This also shows that .Seek can be used on any custom index!
Function DuplicateShNb(pvarShareholderNb) As Boolean

    With Table("tblShareholders")
        .Index = "ndxSecondaryKey"
        .Seek ">=", pvarShareholderNb, Null
        If Not .NoMatch Then
            If Not .EOF Then _
                DuplicateShNb = (!kstrShareholderNb = pvarShareholderNb)
        End If
    End With

End Function

If you liked the link to the microsoft page, you will LOVE this!

Good Luck
I can assure you that the above functions work as fast as any combo box, almost as fast as a query, and they are much more versatile, of course!

Nick67Author Commented:
The report I am printing is a workorder.  It is VERY important that the client only be given a copy of it once.  These reports replace triplicate paper forms.  I see that others have flailed around trying to figure out how to record and count actual "sent to the printer" events.  I have yet to see anything substantive on the order of report events and how to capture an honest-to-goodness printing event.
The only suugestion I have seen is to hide the default menu and control bars and FORCE printing to occur from a form control so that you can capture  the click event and record IT as a proxy.  That is not a satisfactory result for me because I have reasons for wanting to leave the default menus intact.
Can anyone comment on the theory of report events and how to capture a count of committed printer events?

I think this new topic, "capturing the print event" is best resolved outside of Access.

In ISO 9000(etc.) terminology, you have a procedure called "Produce client's workorder". In it, the last phase is titled "Print workorder and send to client". This last phase has one "outgoing document" called "Client's Workorder" with a *control* (e.g. Employee initials in a small-font reference field). You should simply add a second "outgoing document" like: "Workorders database", with control "Employee initials and date stamp". The description of the phase states: when the document is printed, the phase responsible puts it in an envelope and places it in the "Out" bin, then - and not before - he/she add his/her initials in the form...

Well, you get the idea. You might find a way to capture the "send to printer" event, but not the successful printing, nor the sending of the workorder to the client.

If this is buisiness critical to you, create a procedure and create a control in the procedure, even if you are not ISO9000!

Nick67Author Commented:
We are ISO 9001:2000!  And what I need to create is an automated control of the process.  ie. PROVE that someone hasn't produced the workorder twice and sent it.  Being able to capture a print event goes along way toward doings so.  You are right that many things can happen to hardcopy before it is mailed--but, if before you can print a SECOND copy, you have to log a reason, and confirm the cause of the problem that requires you to print a second copy (ie. printer jam ect ect) that goes a very long way indeed towards the almighty PROOF that ISO auditors want to see! I am presently working with Access 2003 (which I think is v. 7.0--and Office 11.0!)  You would think that by now MS would have tied the print subsystem in so that a bi-directional printer's signal to the print spooler that it successfully printed a document would be something that could be easily accessed via VBA!!
God knows that the Almighty Windward traders db resembles an accounting program enough that someone must have thought that printing confirmation was something important.  I suppose that it could be done through Windows API programming--but that's another steep learning curve I'D rather not scale.

I played a little with reports and report events, but I didn't find any way to capture the "print to printer" event as opposed to "preview"...

I guess you can do something like this:

1) Create a mechanism in a table to store the printing of the workorders (lngOrderID, datPrinted, strEmplInitials, ysnFailed, strReason). You will need a function that returns True is there is a non-failed printing in the table for a given OrderID, one to add a new printing, and another to let the employee mark the last successful printing as failure... Function headers could thus be:
    Function WorkOrderIsPrinted( plngOrderID As Long ) As Boolean
    Sub WorkOrderPrintedBy( plngOrderID As Long, pstrEmplInitials As String )
    Sub WorkOrderPrintFailed( plngOrderID As Long, pstrReason As String )

2) In the report, add a background picture that shows "DUPLICATA" accross every page.

3) On some control form, have a normal button to preview (and print if the users wishes) the report, which will always show "DUPLICATA"

4) On the same form, have a second button: [Print and Send Work Order]. This will check whether it has already been successfully printed, let the user "cancel" the previous printing if he/she provides a reason, set a global variable (say "gfRemoveDuplicata") to true, send the report to the default printer, store the printing date and initials, and display a message like: "please send the order immediately to the client".

5) In the report, use the Report_Open to check the global variable gfRemoveDuplicata and clear the background image if true.

This is just a general idea... The employee can only print the "final" order (i.e. without the background image) by using your button. If he/she opens the report in preview and prints, or if he/she prints it any other way, the background image will be visible. This allows archiving, validation by a manager for complex orders, etc.

This should satisfy your inspectors, as each printing of the order will leave a trace. You can add more fields if you like, for example the initals of the employee who requested a new printing, the date of that event, etc.
Another nice thing would be to have "DRAFT" accross the page for orders that have never been printed and "DUPLICATA" for those that have. This can also be managed in the Report_Open event.

I hope this will lead to a satisfying ISO procedure, without being rude to the employees :)
I can create a sample database for you if you like...

Good luck!
Nick67Author Commented:
What exactly does that mean?  Will this discussion remain searchable by Google.  The whole point of the exercise was to create some documentation for the uses of IsVisible and HideDuplicates. (which turn up very little when used a keywords).  If it takes the awarding of points to ensure that this question remains posted, that will be done.

Please reply

PAQed with points refunded (500)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.