Link to home
Start Free TrialLog in
Avatar of Jon Jaques
Jon JaquesFlag for United States of America

asked on

Simulate Control Anchoring with VBA in Access 2003?

Hello,

I have a common problem in a database I work on, whereby there is a tab control on a form with datasheet view subforms on one or more of the tabs. When the form is stretched, the tab control and the subforms should all be stretched along with it in order to fill in the entire screen.

My overall goal is essentially to reproduce the new Anchoring behavior in A2k7 with VBA so that it works for A2k3.
My problem is that the following code seems to differ from computer to computer and has some sort of accumulating error which causes the tab control to either take over the screen or have too much margin (and drive me nuts!)

Private Sub Form_Resize()
fAnchorDownLeft Me.Form, Me.frmAccountTransactions_Subform.Name ' subform datasheet
fAnchorDownLeft Me.Form, Me.Student_Trip_Entry.Name ' subform datasheet
fAnchorDownLeft Me.Form, Me.TabCtl17.Name ' tab control container
End Sub

Public Sub fAnchorDownLeft(ByRef frm As Form, strCtrl As String, Optional blTabControl As Boolean = False)
frm.Controls(strCtrl).Width = (frm.InsideWidth) - frm.Controls(strCtrl).left
frm.Controls(strCtrl).Height = (frm.InsideHeight) - frm.Controls(strCtrl).Top
End Sub

The idea is that the named controls should grow or shrink according to the change size of insidewidth and height, but for some reason this just doesn't work out right. I've tried using various means of accounting for margins and such but all have been unsuccessful or least no more reliable.

Does anybody have a suggestion as to what's wrong? I thought this would be a no-brainer, but for some reason it's giving me more problems than I expected.

Thanks in advance,

--Jon
Avatar of puppydogbuddy
puppydogbuddy

You may be over complicating the solution.  I think the attached code snippet from the tips page of www.aadconsulting.com   will resize all subform controls within a resized form.  Be sure and replace the object names used for illustrative purposes with their actual names in your application
You can use the InsideHeight and InsideWidth properties of an Access form to dynamically resize controls at run-time. A form's Resize event as well as firing when a user resizes a form, also fires when a form is loaded.
 
For example, this code will resize a sub-form within a resized form:
 
Private Sub Form_Resize()
On Error GoTo ResizeError
 
    'Turn off screen redraw  
    Application.Echo False
        Me!subfrmTest.Height = Me.InsideHeight -30      
        Me!subfrmTest.Width = Me.InsideWidth - 30
    'Turn screen redraw back on
    Application.Echo False
 
Exit Sub
ResizeError:
 
    ' NB: Turn screen redraw back on if an error occurs!
    On Error GoTo 0
    Exit Sub
       
End Sub

Open in new window

Avatar of Jon Jaques

ASKER

Hmmm, interesting usage of turning on and off screen redraws, hadn't thought of that, but I do know that at the least you must have a little bit more logic to account for the current top and left of the control so that it doesn't just fill all available space, as the above would do.

I checked out some of the databases that they have available on aadonsulting, but so far, all of them have skirted around the resize issue entirely by using popups and locked forms.

One last point is that I do NOT want to just scale everything larger: I want to get more information on screen, not just make everything bigger to fill the screen.
Well, if you need code that resizes differently based on the screen resolution, try the code that comes with the free screen resizer at the following link.

http://www.jamiessoftware.tk/resizeform/rf_jump.html


No, actually, I'm specifically saying that I do NOT want to resize based on screen resolution... My users with low screen resolution use it because they're half-blind, not because they can't afford bigger screens.

I just want an element on a form to stretch larger and smaller with the resizing of the form, so that I can display more data on everybody else's computer under the higher resolutions that they can use.

This method would be applicable to text boxes, subforms, whatever, but would have to be coded for each "stretchy" element, sort of like the new "Anchoring" feature available in Access 2007. (I would just upgrade now, if I could, but it's just not an option at the moment.)
OK, here are two sources for you:

This link has a copy of Ken Getz's original pre-2007 resizing code from the Developers Handbook:
http://bytes.com/forum/thread211349.html


A "how to" resize fonts and other control properties.....a tutorial and sample code  (scroll down past the advertising on page 1).
http://www.brainbell.com/tutors/Visual_Basic/Size.htm
Both near misses that talk about scaling fonts and such. I don't want to change any fonts, just automatically adjust the height and width of certain controls when the form resizes.
The tutorial link I gave you uses fonts as its main example, but refers to other properties (left, top, etc).  Further, it gives you a feel for why your code does not work.......the coding structure you need to use.....you need to loop the controls on the form.

Dim ctl as Control

For Each ctl In Me.Controls                                          'or For Each ctl in Screen.ActiveForm.Controls
    With ctl
        If .ControlType = acTabCtl Then
            'adjust control properties
        ElseIf  .ControlType = acTextBox Then
      ' adjust size
        End If
    End With
Next ctl
Yeah, they do come close, but ultimately, they're trying to do a generally okay job on all controls of a form, rather than doing a perfect job on a select few controls.
????? If you look at the code I gave you,actions are based on the type of control encountered in the loop................e.g. tab controls  textboxes.  You can choose to take action on some control types, and no action on others.
For Each ctl In Me.Controls          
    With ctl
        If .ControlType = acTabCtl Then
            'adjust control properties
        ElseIf  .ControlType = acTextBox Then
            ' adjust control properties
        Else
              'do nothing                        '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        End If
    End With
Next ctl
Yeah, I've been over this code time and time again, and it just does not do anything better than the original snippet of code I provided in my first post, and does not deal with the sizing complexities that I'm running into with access.

The real problem, I think, is that when there is a form footer, the InsideHeight property does not correlate to the height of the Details section unless the details section is manually re-sized and that causes the re-size event to be fired again, and then everything seems to go wrong.

The only thing I can think of to do is to somehow measure the before and after sizes of insideheight and then use the x and y differences to make the desired elements larger or smaller according to those differences, but that seems to go wrong too with too much complexity.

Hmmm, another idea though... A function which accepts as inputs, a control, and the top, left, bottom, right "margins" to the form space...

I could call it like this:

[code]
' Stretch a big textbox to fill the screen when resized.
AnchorControl(me.text1.name, _ ' pass the name of the control
    me.text1.top, _                     ' leave the top fixed where it is
    me.text1.left, _                     ' leave the left fixed where it is
    me.detail.height- 300, _       ' set the bottom to the bottom of the detail area - 300 for margin
    me.insidewidth - 10)          ' set the right to have a very small margin, almost right up against the edge


' Now, underneath the big text box, float another basic textbox:
AnchorControl(me.text2.name, _
    me.detail.height - 200, _     ' Set a top 200 from the bottom of the form
    me.insidewidth - (me.text2.width + varHChange),_
    0,_                                        ' Tell the code that we don't want to change
    0)                                          ' height or width.
[/code]

My brain hurts, so I'm going to have to think about this some more before I can attempt to write the actual function.

I am working on a demo db, though, that should help to figure this out.

--J
Hang in there.  I think this link has the info you need.

                         https://www.experts-exchange.com/questions/21633633/VB6-How-does-ScaleWidth-ScaleHeight-and-ScaleMode-affect-my-form.html
ASKER CERTIFIED SOLUTION
Avatar of Jon Jaques
Jon Jaques
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I know you posted this a while ago but I just found it and think you saved me hours of work.
I still use Access 2003 for development as I think the later versions are rubbish - a fine example of taking a good product and having a committee ruin it!

Thank you very much jjaques719.