More than 10 Group Headers

[xcrx]

I am trying to reduce the size of a report I have by hiding any empty columns or columns with a "None" value. After searching around for a while I found this http://www.utteraccess.com/forum/Shrink-empty-textboxs-re-t1955986.html&gopid=2045156#entry2045156. I thought it was going to work great for me and if would have but my report has 30 lines that I want to Hide/Show based on the value. Access will only let me make 10 group headers so this method won't really work for me unless I group my controls into sets of 3 which I don't really want to do.

Does anybody have any other suggestions on how to achieve the same effect or a way to create more than 10 group headers?

Thanks
Ryan

 

[MarkK]

A trick here is to use both + and & as concatenation operators, and use the fact that when you use +, nulls will propagate and remove your vbCrLf's. So what the hell does that mean?
Consider...

  dim tmp as string
  dim rst as dao.recordset

  set rst = currentdb.openrecordset("SomeTable")
  with rst
    do while not .eof 
      tmp = tmp & _
        Field1 + vbcrlf & _
        Field2 + vbcrlf & _
        Field3 + vbcrlf & vbcrlf
      .movenext
    loop
    .close
  end with

See how at each iteration of the loop we add (+) Field1 to vbcrlf (CarriageReturnLineFeed) so that if Field1 is null, the addition propagates that null and nulls the vbCrLf. The we concatenate using ampersand (&), which doesn't propagate the null, for the next field.
The result of this approach is what you described. Only the items with data will appear!
Cheers,

 

[xcrx]

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
  Dim tmp As String
  Dim rst As dao.Recordset

  Set rst = CurrentDb.OpenRecordset("Report_qry")
  With rst
    Do While Not .EOF
      tmp = tmp & _
        Option_1 + vbCrLf & _
        Option_2 + vbCrLf & _
        Option_3 + vbCrLf & vbCrLf
      .MoveNext
    Loop
    .Close
  End With
  MsgBox tmp

Here is what I set up. I put a msgbox at the end so I could see what the code was doing. It doesn't seem to move forward. It just displays the first 3 Options I have in the msgbox down off the screen. Also I don't quite understand how I can implement this into my report. Would I have 1 unbound Text Box and have this populate it?