Page size

[hoofit]

One of my forms has several pages the size of which I would like to manipulate. Seems that I'm able to set the size and position of the form but not the pages themselves. I can capture the on load event of the form but not the page. I tried an on click event of the page without success. Any thoughts?

Thank you,

hoof

 

[dhookom]

I don't understand the question. Are you actually printing a form? Is there a reason why you aren't using a report?

Duane
Hook'D on Access
MS Access MVP

 

[hoofit]

Sorry, let me re-word. I used a tab control on one of my forms. The tab control as you know provides more real estate. The result is several 'pages' of more space. It is these pages that I would like to manipulate in terms if size.

hoof

 

[dhookom]

Every "page" of a tab control is the same size. You can use code to change the size of the tab control but I believe it can't be any smaller than the contained controls would allow.

Duane
Hook'D on Access
MS Access MVP

 

[hoofit]

Yes I see. Is there a workaround? For example, one of the pages is occupied by nearly all form, say 8 x 8. Another page is occupied by half that, the rest is just blank page. Can I do anything to give the illusion the the page is smaller? Maybe a cleverly colored box sized and located just right?

Thank you

hoof

 

[MajP]

Any chance you could post an image of what you have and what you would want? I believe you could fake this. Here is the issue. Even in code, you can only shrink the page so that all the controls are still visible, even if the controls are on the other page. So the workaround is on the change event shrink the controls or move them up, then "shrink/expand" the page by adjusting the height. You could do this with a few lines of code to always make the page "fit" the height of the controls.

 

[hoofit]

Even in code, you can only shrink the page so that all the controls are still visible". That's what I'm looking for. Let's say I have 4 pages (OK, I have 4 pages....just kidding). In code then I want to manipulate the size of the page to fit the controls that are on it so there's not a bunch of dead space. Thus, not all pages will be the same size.

hoof

 

[hoofit]

Seems pointless to have pages sizeable, but then they are'nt..........

 

[MichaelRed]

Just to be curious, what is the rationale for wanting to 'grow / shrink' the form pages? (other than the cosmetics?)



MichaelRed

 

[MajP]

I am not so sure about the utility either, but this demonstrates some real good Access coding and a good academic exercise.

The following code changes any tab control into a "fitted tab control". I did this in a custom class so it can be reused anywhere. It is a little involved. You have to move everything out of the way on the pages that are not active so that you can resize the current page. Then you have to resize/reposition the controls on the active page. You have to determine the size you want. Also have to determine which controls are on the tab control and which are on the active page.

1)Create a class module
2)Rename the class module to exactly this: "FittedTab". If you use another name it will not work. If you put it in a standard module it will not work.
3)Drop the following into the class module

Option Compare Database
Option Explicit

Private WithEvents mTabCtl As Access.tabControl
Private WithEvents mFrm As Access.Form
Private mControlsOnTab As Collection
Private mPageTop As Long
Private mPgHeight As Long
Private mPgWidth As Long

Public Sub InitFittedTab(theTabControl As Access.tabControl)
  Set mTabCtl = theTabControl
  Set mFrm = theTabControl.Parent
  mPageTop = theTabControl.Pages(0).Top
  mTabCtl.OnChange = "[Event Procedure]"
  mFrm.OnCurrent = "[Event Procedure]"
  LoadControlsOnTab
  SaveControlPositions
End Sub

Private Sub SaveControlPositions()
  Dim ctl As Access.Control
  For Each ctl In mControlsOnTab
   ctl.Tag = ctl.Left & ";" & ctl.Width & ";" & ctl.Top & ";" & ctl.Height
   'Debug.Print ctl.Name & " " & ctl.Tag
  Next ctl
End Sub

Private Sub ShrinkAll()
   Dim ctl As Access.Control
  For Each ctl In mControlsOnTab
    With ctl
      .Height = 0
      .Top = mPageTop
    End With
  Next ctl
End Sub

Private Function CtlOnPage(ByVal ctl As Access.Control) As Boolean
  Dim pg As Access.Page
    Do Until ctl Is mFrm Or ctl.Parent Is mFrm
      For Each pg In mTabCtl.Pages
        If ctl.Parent Is pg Then
          CtlOnPage = True
          Exit Do
        End If
      Next pg
      Set ctl = ctl.Parent
    Loop
End Function
Private Function CtlOnActivePage(ByVal ctl As Access.Control) As Boolean
   CtlOnActivePage = (ctl.Parent Is mTabCtl.Pages(mTabCtl.Value))
End Function
Private Sub mTabCtl_Change()
  SetPgHeight
  ShrinkAll
  ResizeActivePageControls
  FitPage
End Sub
Private Sub SetPgHeight()
  Dim ctl As Access.Control
  Dim tempTop As Long
  For Each ctl In mControlsOnTab
    If CtlOnActivePage(ctl) Then
      If ctl.Top > tempTop Then
        mPgHeight = (ctl.Top - mPageTop) + ctl.Height
      End If
      tempTop = ctl.Top
    End If
  Next ctl
  'debug.print mPgHeight
End Sub
Private Sub LoadControlsOnTab()
  Dim ctl As Access.Control
  Set mControlsOnTab = New Collection
  For Each ctl In mFrm.Controls
    If CtlOnPage(ctl) Then
       'Debug.Print "adding " & ctl.Name
      mControlsOnTab.Add ctl
    End If
  Next ctl
End Sub
Private Sub ResizeActivePageControls()
  Dim ctl As Access.Control
  For Each ctl In mControlsOnTab
    If CtlOnActivePage(ctl) Then
      With ctl
        .Height = Val(Split(ctl.Tag, ";")(3))
        .Top = Val(Split(ctl.Tag, ";")(2))
      End With
    End If
  Next ctl
End Sub
Private Sub FitPage()
  mTabCtl.Height = mPgHeight
End Sub

Once you have done your copy and paste you can turn any tab control into a fitted tab control with two lines of code. You will need code like this on the form, where "tbCtlOne" is the name of the tab control.

Option Compare Database
Option Explicit
Dim fT As FittedTab
Private Sub Form_Load()
  Set fT = New FittedTab
  fT.InitFittedTab Me.tbCtlOne
End Sub

The class is written to be expanded. I could easily write code to fit the width. I currently only fit the height. I could also allow you to specify the size of the the fitted page. Currently I do a "best fit".
Someone may be able to provide a simpler approach. Sometimes I see a solution and do not see the simpler path. However, I think this demonstrates some pretty good coding techniques.

 

[hoofit]

MajP,
Your example still has my head spinning - way out of my league though impressive. I'm going to spend some time on living with 'non-sizeable pages' by remodeling some of the forms. I'm not going to post an image and yes, this was cosmetics only. I agree, pretty good coding techniques!

Dhookum, I found as you note "Every "page" of a tab control is the same size. You can use code to change the size of the tab control but I believe it can't be any smaller than the contained controls would allow." that this is true, but it can't be smaller than the largest BODY of contained controls....interesting

hoof

 

[MajP]

As I said the Class may be a little complicated to understand, but using it is very simple. That is the concept of object oriented programming. So give it a try. Like I said, it just requires to copy and paste the code into a module and then write two lines of code.

http://www.4shared.com/document/zNLNkjIl/FittedTab_MajP.html

Here is a demo. I included the vertical and horizontal sizing.

 

[dhookom]

I was just reading parts of Access Solutions - Tips, Tricks, and Secrets from Microsoft Access MVPs by Meyer, Steele, and others which has a tip on creating your own tab-like interface.

Duane
Hook'D on Access
MS Access MVP

 

[hoofit]

Duane,
no habla ingles. No ershtansie hoyman so dee-site ess no-help.

hoof

 

[hoofit]

MajP, I'll give it a shot

hoof

 

[MajP]

As Duane is suggesting there are lots of ways to fake your own tab control which can allow you to simulate different things a tab control can not really do. If you google you probably can find examples

Anyways here is an update. I made it overly complicated because I forgot that each page has a controls collection. This helps remove a couple of methods and make it a little more understandable.

Option Compare Database
Option Explicit
' FittedTab Class
' This class fits the pages of a tab control either Height or Height and width
' 2011
' Developer Maj P
'
' Methods:
'   InitFitted - Used as a pseudo constructor to initialize the class by passing the tab control.

Private WithEvents mTabCtl As Access.tabControl
Private WithEvents mFrm As Access.Form
Private mControlsOnTab As Collection
Private mPageTop As Long
Private mPgHeight As Long
Private mPgWidth As Long
Private mFitWidth As Boolean

Public Sub InitFittedTab(TheTabControl As Access.tabControl, Optional FitWidth As Boolean = False)
  Set mTabCtl = TheTabControl
  Set mFrm = TheTabControl.Parent
  mPageTop = TheTabControl.Pages(0).Top
  mTabCtl.OnChange = "[Event Procedure]"
  mFrm.OnCurrent = "[Event Procedure]"
  mFitWidth = FitWidth
  LoadControlsOnTab
  SaveControlPositions
  'SetPgHeight
  'SetPgWidth
  'ShrinkAll
  'ResizeActivePageControls
  'FitPage
End Sub

Private Sub SaveControlPositions()
  Dim ctl As Access.Control
  For Each ctl In mControlsOnTab
   ctl.Tag = ctl.Left & ";" & ctl.Width & ";" & ctl.Top & ";" & ctl.Height
   'Debug.Print ctl.Name & " " & ctl.Tag
  Next ctl
End Sub

Public Sub ShrinkAll()
   Dim ctl As Access.Control
  For Each ctl In mControlsOnTab
    With ctl
      .Height = 0
      .Top = mPageTop
      .Width = 0
    End With
  Next ctl
End Sub

Private Function CtlOnActivePage(ByVal ctl As Access.Control) As Boolean
   CtlOnActivePage = (ctl.Parent Is mTabCtl.Pages(mTabCtl.Value))
End Function

Private Sub mTabCtl_Change()
  SetPgHeight
  SetPgWidth
  ShrinkAll
  ResizeActivePageControls
  FitPage
End Sub

Public Sub SetPgHeight()
  Dim ctl As Access.Control
  Dim tempTop As Long
  For Each ctl In mControlsOnTab
    If CtlOnActivePage(ctl) Then
      If ctl.Top > tempTop Then
        mPgHeight = (ctl.Top - mPageTop) + ctl.Height
      End If
      tempTop = ctl.Top
    End If
  Next ctl
  'debug.print mPgHeight
End Sub

Public Sub SetPgWidth()
  Dim ctl As Access.Control
  Dim tempWidth As Long
  For Each ctl In mControlsOnTab
    If CtlOnActivePage(ctl) Then
      If ctl.Left + ctl.Width > tempWidth Then
        mPgWidth = ((ctl.Left - mTabCtl.Left) + ctl.Width)
      End If
      tempWidth = ctl.LeftPadding + ctl.Width
    End If
  Next ctl
End Sub

Private Sub LoadControlsOnTab()
  Dim ctl As Access.Control
  Dim pg As Access.Page
  Set mControlsOnTab = New Collection
  For Each pg In mTabCtl.Pages
    For Each ctl In pg.Controls
      mControlsOnTab.Add ctl
     Next ctl
  Next pg
End Sub

Private Sub ResizeActivePageControls()
  Dim ctl As Access.Control
  For Each ctl In mTabCtl.Pages(mTabCtl.Value).Controls
      With ctl
        .Left = Val(Split(ctl.Tag, ";")(0))
        .Width = Val(Split(ctl.Tag, ";")(1))
        .Top = Val(Split(ctl.Tag, ";")(2))
        .Height = Val(Split(ctl.Tag, ";")(3))
      End With
  Next ctl
End Sub

Private Sub FitPage()
  mTabCtl.Height = mPgHeight
  If mFitWidth Then
    mTabCtl.Width = mPgWidth
  End If
End Sub

 

[hoofit]

MajP,

Where is the code placed. I see no on load event.

hoof

Option Compare Database
Option Explicit
Dim fT As FittedTab
Private Sub Form_Load()
Set fT = New FittedTab
fT.InitFittedTab Me.tbCtlOne
End Sub

 

[MajP]

That is the code that goes in the form? The other code goes in the class module. All forms have an "on load" property which should say "[Event Procedure]".

 

[hoofit]

This may take me awhile, I downloaded your demo by Access won't open it due to security concerns. Anyway, the following throws an error in the 2nd line "compile error, a module is not a valid type". Also, I copied code to a module, not sure if I did it correctly, you may need to walk me thru.

hoof


Private Sub Form_Load()

Dim fT As fittedTab

Set fT = New fittedTab
fT.InitFittedTab Me.tbCtlOne

 

[hoofit]

I already had some code for the on load event of the main form. Here's how it looks


Option Compare Database
Option Explicit

Private Sub Form_Current()
Forms!frm_MainUnit!UnitID = Me.UnitID 'assign a value to mainUnitID based on units (unitID)
End Sub

Private Sub Form_Load()
Dim fT As fittedTab
Set fT = New fittedTab
fT.InitFittedTab Me.tbCtlOne

'**************************************************************
'On load, set the size and position of the form
'**************************************************************
DoCmd.MoveSize 1500, 200, 7700, 8900
End Sub