Easy Way to move Control from one report section to another? 1

[lameid]

Long story short I need to move controls from one report section to another programmatically to make room for new stuff in a report to avoid hitting the 21" section height limit.

Is there an easy way to do this without creating the controls in the new section, copying the properties and deleting the original controls?

Thanks for any insight.

I'm off to figure out other pieces of the puzzle before circling back.

 

[LaurieHamlin]

Maybe I'm missing something. Can't you copy the control and paste it?

 

[lameid]

No. I have to use Code in my situation... Unless there is a code way to copy and paste a control?

 

[dhookom]

Place duplicate controls in the sections and then hide or resize them.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

No I am creating the design of the report.

 

[dhookom]

lameid,
Can you provide some context to your question? If "No I am creating the design of the report" was meant for me then my question would be why not create the controls in the section of the report you are creating?


Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Long story short I need to move controls from one report section to another programmatically to make room for new stuff in a report to avoid hitting the 21" section height limit.

I am inserting new controls for reasons that can't be avoided and need to make room as described above.

 

[dhookom]

Delete them from one section and add them to the other section. You might also be able to set their "parent" property but I'm not sure if this would refer to the report or the section.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Access does not gracefully handle adding and deleting controls, you can run into some serious issues. At some point this code will fail. There is a hard limit to the amount of controls that can be added to a form over its lifetime (700 something including deleted controls). You are always better off having hidden controls in the other section, that you can use. Just have more than you will ever need. I make them 0x0 and invisible and stack them in the corner of the form/report.
The move method does not allow movement between sections, and I no of no other means to move a control
You can use createcontrol and deletecontrol method of the application object which allows you to create in any section, but I would really look at showing and hiding controls. Much more efficient.

 

[lameid]

700 control creations over the lifetime of a form is an upper limit. I would assume the same must be true for reports. I do not expect that to cause a problem. I not am moving things back and forth between sections, more of a fix it once kind of situation so I expect it to be fine.

 

[MajP]

I forgot it was a report, so I double checked and it is the same:

Number of controls and sections you can add over the lifetime of the form or report: 754

So if you deleted and readded 10 controls every time you opened the report after 70 days it would fail.

If it is a fix it once kind of thing then I would do it in this order
1. Tag the controls that could possibly move
2. Loop the controls collection
3. If the control is tagged create a new control (createcontrol) with the same name and some added suffix. Example it was "txtBxUserID" make new control "txtBxUserID_1"
4. Loop the tagged controls property collection and assign the new control the same values of the tagged control (basically creating a clone). You will need some error checking because some properties are read only. Or you can pick which properties you want.
5. Delete the tagged control (deletecontrol)

If the control has event procedures you would have to use vba extensibilty to write a new event procedure.

 

[lameid]

That's very similar to my plan except step 1 is more like a few hundred lines of code, I was just hoping to reduce step 3-5 could be accomplished simpler. I accept no as the answer. It is just a lot of code compared to changing the top property (that much works already). I am hating the 21" section height limit.

I'll probably get back to this next week now. Off to fix new higher priority issues today.

 

[MajP]

I accept no as the answer

Not sure if it is no, but I cannot think of one. Usually if it can be done thrugh a menu choice it can be done using the docmd.runcommand method. Since you can cut and paste I tried the accmdcopy and accmdpaste. They do not seem available in design mode. Also I could not figure how to selecet a control to copy and paste.

 

[lameid]

Setfocus usually controls the selection behavior... I am betting if you played with it you tried that?

 

[dhookom]

Lameid,
You can double or triple the detail section height by creating a group header and footer on a unique field like the primary key. This will ensure there is a header and footer for every record in your report's record source.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Duane, good add to the thread. I knew that but didn't mention it as part of my solution as I am really just hung up on the single issue of moving the controls from the existing section to the section I create in code in an easier way. It just seems like there should be an easier way to move controls. It looks like selection in design view may be the key.

 

[lameid]

Oops... Section height limit seems to be 22 inches instead of 21... (Access 2010, not sure of other versions as this is my first time on this issue)

My Report has a header and footer already. Adding another footer adds it with a section index of 10. I'm guessing they would increment by 2 as I add them (I can only add footers or I'd have to move all the controls around to use a header and I'm not going to rewrite the whole module).

I've got a logic issue to fix beyond the scope of this thread but otherwise it seems to be working. Also this code is based on a 21 inch section limit (21 inches by 1440 inches per twip) instead of the actual 22 inches.

Basically I had to calculate the ending top value for reasons outside the scope of this thread so I populated one array with control names and had a running changes to destination top values in another (a_strAllControlNames; a_lngNewTop). This gives me an array of final positions to separate out into appropriate sections doing some arithmetic. All other variables should be relatively obvious. Noteworthy is CopyProperties started out to copy form control properties to report control properties so the error handler may be more robust than needed here.

                   'Put controls in appropriate places
                    For lngI = 0 To UBound(a_lngNewTop)
                        Set ctlRptCtrl = rpt.Controls(a_strAllControlNames(lngI))
                        lngTestBottom = a_lngNewTop(lngI) + ctlRptCtrl.Height
                        
                        If lngTestBottom Mod (1440 * 21) = 0 Then
                            lngJ = lngTestBottom \ (1440 * 21)
                        Else
                            lngJ = lngTestBottom \ (1440 * 21) + 1
                        End If
                        
                        If lngJ = 1 Then
                            ctlRptCtrl.Top = a_lngNewTop(lngI)
                        Else
                            'ctlRptCtrl.Top = a_lngNewTop(lngI)
                            'The Section for the CreateReportControl seems like it should be right 
                            'but it was not tested beyond one additional section as of this writing 
                            '(It does seem odd that sections add after those defined by AcSection constants)
                            Set ctlCntrl = _
                                CreateReportControl(rpt.Name, _
                                ctlRptCtrl.Properties("ControlType"), _
                                (lngJ - 1) * 2 + 8, "", "", _
                                ctlRptCtrl.Properties("left"), _
                                a_lngNewTop(lngI) - ((lngJ - 1) * 1440 * 21), _
                                ctlRptCtrl.Properties("width"), _
                                ctlRptCtrl.Properties("height")) 'Top shifts from _
                                report minumum up and down by adding to the _
                                minimum; lngTopOffset: 1440 twips is 1 inch
                            strTmp = ctlRptCtrl.Name
                            CopyProperties ctlRptCtrl, ctlCntrl, _
                                Nz(strAppendFields, "")
                            Application.DeleteReportControl rpt.Name, strTmp
                            ctlCntrl.Name = strTmp
                        End If
                        
                    Next lngI

Private Sub CopyProperties(ByRef ctlCntrl As Control, ByRef ctlTargetCntrl As Control, ByVal strAppendFields As String)
    'Copies properties from a source control to a target control skipping properties that produce an error
    'In general does not copy control source unless it is a field in the comma separated list strAppendFields
    'The optional Controlsource copy is to support displaying values form the new_obs table for Type 2 and similar forms
    
    Dim lngMmm As Long
    Dim lngI As Long
    Dim a_strAppendFields() As String
    
    On Error GoTo CopyProperties_Err

    a_strAppendFields() = Split(Replace(strAppendFields, " ", ""), ",")
    
    For lngMmm = 0 To ctlCntrl.Properties.Count - 1
        Select Case ctlCntrl.Properties(lngMmm).Name
            Case "Height", "Top", "Left", "Width", "Name", "ControlType", "HorizontalAnchor", "Tag", "Picture", "PictureData"
                'Properties to skip, some set on create, some are read only, (tag intentionally left blank for other processing)
            Case "ControlSource"
                If strAppendFields <> "" Then
                    For lngI = 0 To UBound(a_strAppendFields())
                        If ctlCntrl.Properties(lngMmm).Value = a_strAppendFields(lngI) Then
                            ctlTargetCntrl.Properties(ctlCntrl.Properties(lngMmm).Name) = ctlCntrl.Properties(ctlCntrl.Properties(lngMmm).Name)
                            Exit For
                        End If
                    Next lngI
                End If
            Case Else
                ctlTargetCntrl.Properties(ctlCntrl.Properties(lngMmm).Name) = ctlCntrl.Properties(ctlCntrl.Properties(lngMmm).Name)  'set all control properties on report to match the form
        End Select
    Next lngMmm
    
    Exit Sub
CopyProperties_Err:
    
    Select Case Err
            
        Case 2101 'The setting you entered isn't valid for this property.
            Resume Next
        Case 2113 'The value you entered isn't valid for this field.
            Resume Next
        Case 2135 'This property is read-only and can't be set.
            Resume Next
        Case 2184 'The value you used for the TabIndex property isn't valid. The correct values are from 0 through 1.
            Resume Next
        Case 2455 'You entered an expression that has an invalid reference to the property XXXXX.
            Resume Next
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description
            'Resume
    End Select
End Sub