Solved

Dynamically add controls to report w/ VBA in Open Event?

Posted on 2008-06-24
12
2,750 Views
Last Modified: 2013-11-28
Is it possible to dyanamically add controls to a report w/ VBA in its open event?
Do not want persistent controls, just want to add them on the fly as needed on each report run (they do not get saved).  I know I can do the .Visible setting thing... that is not what I am looking for.

I cannot find any good references on this topic.
0
Comment
Question by:PumpMeister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 58
ID: 21854467

  Rather then add controls on the fly, unless you are creating the report object each time as well, you are better off to create the controls you need before hand, then hide/unhide them as needed.

  The reason for this is that Access has an internal counter used for controls over the life of the object (form or report).  Once that counter hits the max (768 if I remember right), that's it; you can't create any more controls (even if you delete some).

  A compact/repair does not reset the counter.  Your only option at this point is to recreate the object.

  And to answer your question directly, before calling the report, you need to open it in design mode and add the controls.  I don't believe you can flip it into design mode after the report has already started executing.

JimD.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21860471
PumpMeister,

Can you give us a brief explanation of why you need this functionality?

I mean, even if this was a good idea you still might have a tough time dynamically positioning all the controls on the report, and loading all the controlsources.

(Not to mention controlling page breaks, CanGrow/Shrink, Margins, ...ect)

JeffCoachman
0
 

Author Comment

by:PumpMeister
ID: 21861822
The hide/unhide thing is not a very good approach if what you are hiding/unhiding in the report has code behind it that runs that should only run if the control is visible.  While you certainly control the visibility, if you have a lot of objects that have VBA code behind them, the VBA code can still run.  For example, the open events in hidden subreport objects will still fire and the corresponding event handlers still run.  To the user, it sometimes looks like you have a very slooooooooow report.

I guess I could set a semaphore somewhere (or .TAG property) so that the VBA code does not run, but that will just add more stuff to check and code to run.

This is already *very* complex.  The comments about all the positioning, page breaks, etc...  I know--I am already doing all that stuff with the hide/unhide approach.  The hide/unhide approach is not as easy or elegant in this instance as you may think.  Events still fire on the hidden subreports... so it takes just as long to generate a report with a bunch of subreports hidden as it does with them all visible.

A "dirty" code snippet is attached.  Sorry it is not more "beautified"... it works fine but I still consider it a work in progress.  The "log" variable lines woul d normally be commented out.  I would prefer to either do a delete of a control instead of setting the .Visible property or have the loops insert the needed subreports at the appropriate locations.  If I do a delete, then the open events should not fire, I would expect.  Sounds like the control counter constraint in Access may prevent the approach of inserting the subs on the fly from being feasible.

So, how about the delete on the fly approach then?  I would still have messy complex code (repositioning), but at least open event would not fire.  Would not want a permanent delete, though.

Steve
Option Compare Database
Option Explicit
 
Dim Drawings As String, AdHocFilter As String
Const ReportSettings = "Drawing Checker Report Settings", ChecksListbox = "Select Checks"
 
Private Sub Report_Open(Cancel As Integer)
    Dim ctl As Control, ctl2 As Control, pgCtl As Control, Started As Boolean, MaxTop As Long, log As String, log2 As String
    Dim bottom As Long
    
    'On Error GoTo Err_Report_Open   
      
    If Not FormIsLoaded(ReportSettings) Then
        DoCmd.OpenForm ReportSettings
        Cancel = True
    Else
        
        'We have to fetch the settings from the settings form and take
        'appropriate action before we close the form and run the report...
        
        ReportFilter = Nz(Me.OpenArgs, "")  'This is a global variable declared in Form and Report Management module...
        Drawings = Nz(Forms(ReportSettings)![Drawing List], "ALL")
        AdHocFilter = Nz(Forms(ReportSettings)![AdHocFilter], "NONE")
        
        log = ""
        
        For Each ctl In Me.Controls
            
            With ctl
                
                If Len(.Tag) Then    'Only examine controls that are tagged in the report...
                    If Not IsListSelection(.Tag, ReportSettings, ChecksListbox, 1) Then
                        log = log & "HIDING: " & ctl.Name & " FOR " & ctl.Tag & " @ .Top=" & .Top & vbCrLf
                        .Visible = False
#If 1 Then
                        'Move controls below up to fill space...
                        If .ControlType = acSubform Then  'Examine height of Subreports to adjust upward...
                            log2 = ""
                            bottom = .Top + .Height
                            For Each ctl2 In Me.Controls
                                'Must have a value for .Tag and it must be different than what we are hiding
                                '     to be eligible for moving the control up to remove empty space above.
                                '     Pagebreaks are a special case...
                                If (Len(ctl2.Tag) > 0) And (ctl2.Tag <> .Tag) And _
                                    ((ctl2.ControlType = acPageBreak And (ctl2.Top > bottom)) Or _
                                        (ctl2.Top >= bottom)) Then
                                            ctl2.Top = ctl2.Top - .Height
                                            log2 = InsertSorted("MOVING: (" & Format(ctl2.Top + .Height, "0000") & ")" & ctl2.Name & " FOR " & ctl2.Tag & " FROM " & ctl2.Top + .Height & " TO " & ctl2.Top, log2)
                                End If
                            Next ctl2
                            log = log & log2
                        End If
#End If
                    End If
                End If
                
            End With
            
        Next
 
#If 0 Then
        DoCmd.OpenForm "Message Viewer", acNormal, , , acFormReadOnly, acDialog, _
                "log:" & vbCrLf & vbCrLf & log
#End If
 
        'Now examine the controls to determine if there are any unneeded page breaks below all the visible controls...
        Started = False
        MaxTop = -1    'Dummy initialization value...
        
        For Each ctl In Me.Controls
            With ctl
                If .Visible Then  'Only work with controls not already hidden...
                    Select Case .ControlType
                        Case acPageBreak  'Find bottom-most page break...
                            If Started Then
                                If pgCtl.Top < .Top Then Set pgCtl = ctl
                            Else
                                Set pgCtl = ctl
                                Started = True
                            End If
                        Case Else         'Find the bottom-most extent of other controls
                            If MaxTop < (.Top + .Height) Then MaxTop = (.Top + .Height)
                    End Select
                End If
            End With
        Next ctl
        If Started Then If pgCtl.Top >= MaxTop Then pgCtl.Visible = False  'Hide unneeded page break
        
        DoCmd.Close acForm, ReportSettings
        
    End If
    
Exit_Report_Open:
    Exit Sub
 
Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
    
End Sub
 
 
Private Sub Report_Close()
    ReportFilter = ""
End Sub
 
Public Function GetDrawingList() As String
    GetDrawingList = sjReplace(sjReplace(Drawings, Chr(34), ""), ",", vbNewLine)
End Function
 
Public Function GetAdHocFilter() As String
    GetAdHocFilter = AdHocFilter
End Function
 
Public Function InsertSorted(newitem As String, List As String, Optional Delimiter As String = vbCrLf) As String
    Dim ctr1 As Integer, ctr2 As Integer, found As Boolean
    
    If Len(List) = 0 Then
        InsertSorted = newitem & Delimiter
    Else
        ctr1 = 1
        found = False
        Do
            ctr2 = InStr(ctr1, List & Delimiter, Delimiter)
            If newitem < Mid(List, ctr1, ctr2 - 1) Then
                InsertSorted = Left(List, ctr1 - 1) & newitem & Delimiter & Mid(List, ctr1)
                found = True
            Else
                ctr1 = ctr2 + Len(Delimiter)
            End If
        Loop Until found Or ctr1 >= Len(List & Delimiter)
        If Not found Then _
            InsertSorted = List & newitem & Delimiter
    End If
            
End Function
 
Public Function GotData(rpt As String) As Boolean
    GotData = Reports(rpt).HasData
End Function

Open in new window

Drawing-Checker.pdf
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:PumpMeister
ID: 21861863
"Sounds like the control counter constraint in Access may prevent the approach of inserting the subs on the fly from being feasible."

By "subs", I meant *subreports*... sorry for any confusion.

Steve
0
 

Author Comment

by:PumpMeister
ID: 21863068
I think could get around the control counter constraint, if I make my report "spawn" a copy of itself that adds the controls.  The copy is just deleted.  This way the controls counter is never incremented on the main report.  Unfortunately, I think this just adds unnecessary complexity to something already complex.

This "spawn a copy" approach is the easy way I have found to get around many of Access's Reporting limitations.   You can sort of approximate re-entrant code with it--you can effectively have a report "re-open" itself (actually the copy) with an Openargs parameter.  This greatly simplifies building a report with a table of contents section, for instance.  (The spawned copy actually builds the TOC, then reopens the main report with different OpenArgs "switch", which closes and deletes the copy.)

Steve
0
 

Author Comment

by:PumpMeister
ID: 21863097
Ok, another thought...

The real issue here is that it looks like the subreports take time to generate the recordsets even when they are hidden.  That looks like it is the real slow-down issue.

But what if I have each subreport detect (in the Open event handler) whether it is visible or not in the main report, and set the RecordSource property of the subreport to "" (empty string) if it is determined that the subreport control is hidden in the parent report?

Wouldn't this prevent the recordset generation in the subreports if the relevant subreport is hidden?

See the attached code... which would be in a subreports code module.  The ImVisible() function determines a subreport's visibility in a parent report.

This would not reduce the complexity of this monster report, but maybe it would speed it up???

Steve
Private Sub Report_Open(Cancel As Integer)
    Static AlreadySet As Boolean
    Dim MyFilter As String
    
    If Not AlreadySet Then   'ReportFilter is a global variable declared in the Form and Report Management Module
        
        If ImVisible() Then
            If Len(ReportFilter) Then _
                Me.RecordSource = ChangeSQLCriteria(Me.RecordSource, ReportFilter, "And")
        Else
            Me.RecordSource = ""
        End If
        
        AlreadySet = True
    
    End If
 
End Sub
 
Private Function ImVisible() As Boolean
    Dim ctl As Control, rpt As String
    
    ImVisible = True
    rpt = ParentRpt()  'This is an empty string if there is no Parent report...  (Subreport ran stand-alone)
    
    If Len(rpt) Then
        For Each ctl In Reports(rpt).Controls
            If ctl.ControlType = acSubform Then                'SourceObject property only valid for some controls...
                If ctl.SourceObject.Name = Me.Name Then   'Matches "Me"???
                    ImVisible = ctl.Visible
                    Exit Function
                End If
            End If
        Next
    End If
    
End Function
 
Public Function ParentRpt() As String
    ParentRpt = GetParentRpt(Me, Me.Name)
End Function

Open in new window

0
 
LVL 58
ID: 21863660
<<I think could get around the control counter constraint, if I make my report "spawn" a copy of itself that adds the controls. >>

  Yes, you would.  As I mentioned, if your creating the report object each time, then you can add 768 controls in one shot.  But that leads to another problem; database bloat.  You'd need to either turn on compact on close or create a temp DB (which you'd kill after your done) to contain the temp report.

<<But what if I have each subreport detect (in the Open event handler) whether it is visible or not in the main report, and set the RecordSource property of the subreport to "" (empty string) if it is determined that the subreport control is hidden in the parent report? >>

 If I remember correctly, the Open and Load events for a subreport occur before the main reports OnOpen.  Typically, one can do:

  Me!SubReportControl.Visible = Me!SubReportControl.Report.HasData

  To hide/unide an empty subreport.  So you'd need the indication for the subreports being visible or not external to the report (not handled in the main reports OnOpen event).

  I would do a quick test to make sure that is the case before moving forward.

JimD.
0
 

Author Comment

by:PumpMeister
ID: 21863814
Jim,

Not worried about DB bloat... as I mentioned, we already use my "spawn a report copy" for my technique of builidng report TOC's automatically.  We already compact the DB regularly.  So... back to my original question then... how to add the subreport controls on the fly in the open event (in the copy)?

You are assuming something simpler than I am attempting and missing the point with your ".Visible/.HasData" comments.    The key thing to realize here is that, in effect, the user selects which subreport sections are "included" or not in the report... this is very different than the subreports not having records.  (.HasData is handled in a different manner.)

Unfortunately, there is a lot more complexity going on with this report than you may think:  report fields and settings pulled from a form and database properties, "on-the-fly" hidden/displayed subreports with "on-the-fly" reformatting requirements, "fake page headers" in subreports, "on-the-fly" recordsource modifications in subreports to accommodate user-specified "ad-hoc" filters, consistent page numbering through all the subreports, etc., etc.

I would like less complexity, but not at the expense of functionality.  
Getting the report faster is a higher priority than making it simpler.

Steve
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 21864014
Steve,

<<You are assuming something simpler than I am attempting and missing the point with your ".Visible/.HasData" comments.    The key thing to realize here is that, in effect, the user selects which subreport sections are "included" or not in the report... this is very different than the subreports not having records.  (.HasData is handled in a different manner.)>>

  Yes, I understood that.  The inclusion of the subreports is not data driven.  I made the comment about .hasdata just to show that yes indeed, the subform is populated before the main event reports fire.  That being the case, deciding which subreports to appear cannot be done in the main's OnOpen event as all the subreports OnOpen and Onload events have already fired.

<<Not worried about DB bloat... as I mentioned, we already use my "spawn a report copy" for my technique of builidng report TOC's automatically.  We already compact the DB regularly.  So... back to my original question then... how to add the subreport controls on the fly in the open event (in the copy)?>>

  Seems like a long way around to do a TOC.  I would have used the two pass mode.

  As for your original question, you need to flip the report into design view:

Dim strRptName as string

strRptName = "MyReport"
DoCmd.OpenReport strRptName, acDesign

  and then use CreateReportControl() to create the controls you need.  This cannot be done within the report.  It must be done when the report is closed.  You cannot modify a reports design while it is executing.

JimD.
0
 

Author Comment

by:PumpMeister
ID: 21864181
"That being the case, deciding which subreports to appear cannot be done in the main's OnOpen event as all the subreports OnOpen and Onload events have already fired."

That's funny, because I already do this and it works fine, just slow.  It may be that the subs events are firing 1st, but it just doesn't matter.  I have actually walked through all the events in order on this report about 6 mos ago when I built it, but I can't remember the exact sequence of things now... I just remember that it all works.  

"Seems like a long way around to do a TOC.  I would have used the two pass mode."  I am doing two passes, just in an automated no-brainer fashion that the user does not have to think about.

So... Ok then, CreateReportControl() and DeleteReportControl() (my opinion) are the functions I need to look at in the Report's design mode.  Cool.  

Thanks for your help, Jim.

Steve

0
 

Author Closing Comment

by:PumpMeister
ID: 31470077
Thanks!  You got me on the right track.  I can take it from here.

0
 
LVL 58
ID: 21864843
<<
"That being the case, deciding which subreports to appear cannot be done in the main's OnOpen event as all the subreports OnOpen and Onload events have already fired."

That's funny, because I already do this and it works fine, just slow.  It may be that the subs events are firing 1st, but it just doesn't matter.  I have actually walked through all the events in order on this report about 6 mos ago when I built it, but I can't remember the exact sequence of things now... I just remember that it all works.
 >>

  I'm consfused.   Your said you wanted to avoid the recordsources loading for the subreports.  You can't do that from the OnOpen event because it fires after the subforms have already loaded.  You certainly can hide and unhide controls, which is what I originally suggested, but that doesn't help with your slowness on subreports as you pointed out.

  If you want to manipulate the the recordsource for the subforms, you need to do it outside of the report before the report starts executing because the hiding/unhiding of the subreports is based on user selection and not if they have data or not.  If it was, this would be a simple matter and the OnOpen event for the main report would suit your purposes.

<<"Seems like a long way around to do a TOC.  I would have used the two pass mode."  I am doing two passes, just in an automated no-brainer fashion that the user does not have to think about. >>

  No, two pass mode in the report engine.  By referencing the pages property, the report engine will make two passes through a report.  One pass to format everything and come up with the total number pages, then a second pass to actually preview or print the report.

  On the first pass, you can do all sorts of things such as build an index or table of contents, do page 'x' of 'y' over a group, etc.

<<So... Ok then, CreateReportControl() and DeleteReportControl() (my opinion) are the functions I need to look at in the Report's design mode.  Cool.  >>

  Yes, but unless your creating the report object each time, you will bump into the control limit.  DeleteReportControl() deletes the control, but it does not decrement the internal counter.

  Last, with my PE (Page Editor) hat on, I notice that your are new to EE (Welcome BTW).  In regards to the "B" grade, you might want to review this when you get a chance:

http://www.experts-exchange.com/help.jsp#hi97

  I'd also add that in the future, if you feel something hasn't been explained or is lacking, you should bounce back with additional comments and give the Experts a chance to respond, before deciding on a final grade.   Also keep in mind that on something like this, which there is no right or wrong answer for and possibly multiple ways to achieve it, can take quite a few go arounds exploring different techniques to handle a specific problem.  It can be difficult at times as well for Experts to fully understand a problem with only a few comments.

Have a good day!
JimD







0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question