Report Design Theory and Practice

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

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?

Nick67

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.

regards,Franz

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

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?

Nick67

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
http://ourworld.compuserve.com/homepages/attac-cg/ACGFree.htm

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

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

https://www.experts-exchange.com/questions/20947313/Return-Height-of-a-CanGrow-Textbox-at-Runtime-Access-2000.html (google cached version, original deleted)

the experts gave up

Others have gone to www.lebans.com 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)
    Else
    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?

Nick67

Oh, sure, sure!
After I burned out half my gray matter building my report I find this

http://support.microsoft.com/?kbid=291533
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
Conti:
  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)

Cheers!

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
   
LinkedDb_Error:
    Err.Clear
    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
        Err.Clear
        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
        .MoveNext
        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
            .MoveNext
            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!

Cheers!

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?

Nick67

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!

Cheere:)

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.

Nick67

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!

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

Nick67