Solved

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

Posted on 2008-06-24
12
2,701 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
  • 7
  • 4
12 Comments
 
LVL 57
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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 57
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 57

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 57
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now