How can I center Access 2007 switchboard opened to full screen?

[JAGster]

I am creating a database in Access 2007 for multiple users, some with very limited experience with access. I am using a customized switchboard to make it more user-friendly.

I have the switchboard opening to full screen (covers all of the access window) and it is centered on my laptop but when I open the database on a different computer, different monitor size and resolution, it is not centered.

Is there anyway to have the switchboard centered, no matter the monitor size/resolution when opening to full screen?

[ADezii]

You can always make sure that a Form is Centered regardless of a User's Screen Resolution, but the Code is a little complex, and I do not believe that the Form can be Maximized. If you wish to see how this can be done, just let me know.

[NeoPa]

Have a look at the AutoCentre property of the form.

[JAGster]

ADezil, I appreciate the offer on the code but I feel it necessary to have the switchboard opened to full screen for security and to avoid overwhelming users unfamiliar with Access.

NeoPa, The AutoCentre property is set to yes, but the form is not centered.

Thanks for the ideas. Any others will be greatly appreciated.

Regards, JAGster

[ADezii]

@JAGster: Kindly explain...

I feel it necessary to have the switchboard opened to full screen for security and to avoid overwhelming users unfamiliar with Access.

[JAGster]

ADezii,
I am concerned that inexperienced Access users will be confused/intimidated by the Access menus on the ribbon. I am also concerned about securing the database from tampering, inadvertent or otherwise.

I read a little bit of Rabbit's post on Securing a Database in Access and will look into the methods descrbed there for securing the database.

I am very interested in learning how to center forms, regardless of screen resolution. Thanks, JAGster

[ADezii]

How, exactly, are you Opening the switchboard to Full Screen?

[JAGster]

I got this from Jim Doherty's reply to an old post

min max buttons none
Borderstyle none
popup yes
navigation buttons no
Recordselectors no
scrollbars neither

In the on load event entered the code - DoCmd.Maximize

[JAGster]

The form is triggered to open from the Autoexec macro.

[NeoPa]

So, if it's opening in Full Screen mode, how is it centred or not centred? Surely that is something that's relevant only in restored mode?

[ADezii]

@NeoPa:
Post #11 says it all from what I can see. It is not a matter of the Form itself being 'Centered' since this makes little sense in its 'Maximized' State. It appears to be a problem of centering the 'Controls' within this Form for various Screen Resolutions. A Command Button on a Maximized Form that is Centered on the Screen, will no longer be once the Resolution is changed. Technically the Controls on the Form need to be Scaled as well as Repositioned which can be done, but will be a little complex.

@JAGster: Is this correct?

[NeoPa]

ADezii:
Post #11 says it all from what I can see.

Post #11 is the one you just posted ADezii :-S

I couldn't find any post that even seemed to indicate that, but if that's true, we have a much clearer question to answer. We'd need details of these controls of course and how they fit on the form generally. With that the situation is certainly handlable. I'm sure either of us could help with that.

[ADezii]

@NeoPa: Make that Post #8! (LOL)

[JAGster]

ADezii, You are correct, I misstated what I really needed to know how to do. Scaling and repositioning the controls for any resolution is what I need to do.

The controls are command buttons that are opening sub-switchboards. The sub-switchboard forms have command buttons to return to the main switchboard, close and exit the database, import files to populate tables, open split-forms with subforms, etc. I've attached a copy of the database with sensitive data removed.

I am in the process of changing the look and feel of the sub-switchboard forms and the other forms to be more like the main switchboard. My end goal is to either have all forms open full-screen with the controls scaled and repositioned or to have just the main switchboard and sub-switchboards open full screen with the controls scaled and repositioned and the other forms open centered on screen no matter the resolution (if they can open in front of the sub-switchboard form that is open full screen). I want the access window always hidden no matter what form(s) are open.

[ADezii]

Unfortunately, I can only use Access 2002 at this time. Kindly Convert a Copy of the DB (Forms only) to Access 2002, so that I can have a really good look at the Forms and Controls contained within.

[NeoPa]

I just found out that maximised form windows keep the same values for height and width as when in restored view. I'm just seeing if I can find out how to determine the actual values for the form window. I know it will involve a Windows API call, I just don't know which yet ;-)

[NeoPa]

It seems like you will need some code in your Form_Open() event I would suggest. It needs to determine the size of the form and its sections. The form object (Switchboard) has a Width property but only the sections have Height properties. This is worth remembering even though we won't be using these values due to their being unfit for purpose (See post #16). They remind us of the structure though. If you have multiple sections you will need to handle this in your logic. How you move things around within that, and what logic you use, is up to you (and we can't really comment as you haven't shared that with us), but you will almost certainly want to cycle through your controls to process each one. Here is a stub of code you can work with (I assume just a Detail section) :

Option Compare Database
Option Explicit
 
Private Declare Function GetWindowRect Lib "user32" _
                             (ByVal hWnd As Long, recWnd As Rect) As Long
Private Type Rect
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type
 
Private Sub Form_Open()
    Dim lngWidth As Long, lngHeight As Long
    Dim recForm As Rect
    Dim ctl As Control
 
    Call GetWindowRect(Me.hWnd, recForm)
    With recForm
        lngWidth = .Right - .Left
        lngHeight = .Bottom - .Top
    End With
    For Each ctl In Me.Detail.Controls
        '...  Your logic
    Next ctl
End Sub

[JAGster]

NeoPa, All functioning controls (the Quarterly Audit button has no logic/function currently assigned to it) have an On Click event that calls a macro. All macros were included in the attached macro. Except for the macro triggered by the Exit "button" all other macros close the main switchboard and open another form. The macro for the Exit button saves and closes the database.

ADezii, I have attached a 2002 version of the sample database.

[Rabbit]

Use a subform as a container and then center the subform in the form's on load event.

[JAGster]

Rabbit, What settings/code is needed for the subform to be centered on the form no matter the screen resolution?

As a brief recap of what I'm trying to accomplish - I want to open a switchboard to full screen size, not maximized in the Access window, but full screen so none of the access toolbars, buttons, etc are visible. And have the controls on the switchboard centered no matter what computer/monitor or monitor resolution on which the database is running.

[ADezii]

@JAGster:

1. To perfect Scaling, you must know specific information regarding the Screen Resolution that is active at the time you created the Form and its Controls. This info is used to create specific Ratios for Scaling all Objects. The info needed is:
   1. Horizontal Screen Resolution in Pixels
   2. Vertical Screen Resolution in Pixels
   3. Logical Dots/Inch X
   4. Logical Dots/Inch Y
2. Download the Attachment that I have provided for you and Open frmScreenInfo. All the information that you need for proper Scaling is generated within. Copy the Call to the SetDesignCoords() Method generated at the bottom of the Screen, it will look something like this:
   

   Call frmResize.SetDesignCoords(1152, 830, 96, 96)

3. Paste this Method Call and overwrite the comparable Line in the Load() Event of frmCentered (Line #9).
   

   Private Sub Form_Open(Cancel As Integer)
   'Instantiate the Class to handle all resizing.
   Set mfr = New FormResize
    
   'Tell the new Object what form you want it to work with.
   Set mfr.Form = Me
    
   'For 1152 x 864 Screen Resolution at the Kimmel Center
   Call mfr.SetDesignCoords(1152, 830, 96, 96)
    
   'Set certain Properties
   With mfr
     .ScaleForm = False
     .ScaleColumns = True
     .ScaleFonts = True
     .ScaleControls = scYes
   End With
   End Sub

4. Open Demo.mdb in any Screen Resolution. You should find that the Form covers the entire Screen and Scales all Controls/Fonts/Columns accordingly in any Screen Resolution, making it truly independent.
5. If this actually works, I'll tell you what to do to incorporate this into your Database.

[JAGster]

ADezii,

After pasting the resolution information from the frmScreenInfo Call frmResize.SetDesignCoords(1280, 772, 96, 96)

I got compile error "variable not defined" and showing frmResize highlighted, when I tried to open the frmCentered.

[ADezii]

Try Call mfr.SetDesignCoords(1280, 772, 96, 96) first,

then

Paste mfr.SetDesignCoords(1280, 772, 96, 96) over the corresponding Line in the Open() Event of frmCentered in the Demo Database, not yours. Test the Demo at various Resolutions. If this works, then we can migrate to your DB. If you are still having trouble, I can modify the Demo at this end, then Upload the Revision to you for testing.

[JAGster]

It appears to be working. One questions regarding scaling. One monitor I am using is set at resolution 1280x772 (the display settings say 1280x800). The other one 1024x768. When I opened frmCenter on the second one the layout looks exactly the same as on the first one, but the picture of the globe is taller and narrower. Is this because of the scaling from the first resolution to the other?

[ADezii]

Is this because of the scaling from the first resolution to the other?

I would image so.

Don't forget that mfr.SetDesignCoords(1280, 772, 96, 96) must correspond to the Screen Data where the Form was created.

Test at various Resolutions, and try substituting the reported resolution instead of the actual ==> mfr.SetDesignCoords(1280, 800, 96, 96)

[JAGster]

I've tested on a number of different resolutions and it does work on all of them. So, I'm now ready migrate it to my database.

[ADezii]

1. The vast majority of the Code is encapsulated within 3 Class Modules, Import them into your DB from the Demo. The Class Modules are: ControlResize, FormResize, and SectionResize.
2. In the Declarations Section of your Form's Code Module, place the following Declaration which will Declare a variable to represent a New Instance of the Class.
   

   Private mfr As FormResize

3. In the Open() Event of the Form, Copy-N-paste the Code in the Demo's Form Open() Event to your Form:
   

   Private Sub Form_Open(Cancel As Integer)
   'Instantiate the Class to handle all resizing.
   Set mfr = New FormResize
    
   'Tell the new Object what form you want it to work with.
   Set mfr.Form = Me
    
   'For JAGster's Screen, Form Developement, Resolution 
   Call mfr.SetDesignCoords(1280, 772, 96, 96)
    
   'Set certain Properties
   With mfr
     .ScaleForm = False
     .ScaleColumns = True
     .ScaleFonts = True
     .ScaleControls = scYes
   End With
   End Sub

[JAGster]

It works!

ADezii, Thanks for all the help! Very much appreciated.

[ADezii]

Glad it all worked out for you.