Sizing a form in Access

Topic

New Reply

Hi,

I am more than a bit confused about how to properly size a form.
It seems that it is a combination of trial and error by moving the right side and the bottom of the design grid.

But there is also some connection with the size of the window in which the design grid is located. It has the nasty habit of overlapping the design grid by at least the width of the scrollbars in order for the form to come out right, when in use.

The form property Width has a value of 2.375″, there is no Height property. The Documenter prints the Width as 3420 (pixels?). It does have a WindowHeight and WindowWidth, but as far as I can determine, nowhere to set these properties.

Finally, on screen the form is actually 3.5″W x 4″H. How that relates to 2.375″W totally escapes me.

I looked in Options to determine if I can set the size property(ies) to anything other than inches. But I can’t see it. In Tools|Options|Forms/Reports there are two check boxes for Fully Enclosed and Partially Enclosed. What do they do? (my head spins much faster!)

Your help would be much appreciated!

Reply | Quote

Replies

With Access 2000 there is a form property to make it automatically fit the record. Is that any use to you?

If you turn that off then I think Access will use the size that you have the form when it is last saved (don’t have it maximised when you save it).

Reply | Quote

Hi Andy,

I guess you’re referring to the AutoResize property. I got Auto Resize set to No and Auto Centre set to Yes. The Help doesn’t seem to address what I want to no (although it admits that Access will clip the bottom of the form – see attached screenshots of sample/test form)

I need to size the ‘design’ form as indicated in the right pane (the scrollbars obscure it partly), to make it come out as shown in the middle pane. To adjust the form seems to be a rather imperfect science.

Reply | Quote

Sorry, I meant to say: …what I want to know…

Reply | Quote

Ken, in Access 97, if I open the form, resize it while it is open to the size I want, and then save it while it is still open, it keeps that size when it opens again. This is the only way I have been able to get forms to stay the size I want.

Reply | Quote

Hi LonnieB,

Yes, I tried that as well, but that doesn’t work when you set the Border Style to anything other than Sizable. I use the Thin value most of the time for dialog type forms, which doesn’t let you resize the form when it’s open.

Reply | Quote

I use the DoCmd.MoveSize command on the On Open event or something like that to size the form. Then DoCmd.Restore on close. Check out the Help on MoveSize for more info.

Reply | Quote

Hi Gary,

I will investigate that a bit further how I can use that to my advantage. The Help says about MoveSize:

Note Each measurement is in inches or centimeters, depending on the units set for Measurement System on the Number tab of the Regional Settings Properties dialog box in Windows Control Panel.

Actually, neither inches or centimeters has any meaning to me on my 17″ monitor with 800 x 600 screen settings.

Reply | Quote

See the MoveSize method of the DoCmd object in VBA. The measurements are in Twips. The help says there are 567 twips in a centimeter. However, I have found it to be a trial and error to get it set like you want it, depending on screen size and resolution.

Reply | Quote

This is a very confusing operation in Access, since you don’t actually have a size for the form that you can simply set. Unlike MS forms, Access forms appear to be composite critters, so you have to deal with their individual components separately and do a little math. There is some very heavy code for this in the Access 2000 Developer’s Handbook Vol 1, and similar code in the previous versions of that tome. But it is possible to do it yourself as well.

Here’s some slightly tweaked code based on the Access 97 Developer’s Handbood version. The comments are mine:

Sub glrAutoResize(ByVal frm As Form, Optional SetHeight, Optional SetWidth)
  ' added 8/15/98
  ' modified 9/25/98 by Charlotte Foust
  Dim dblWidth As Double
  Dim dblHeight As Double
  
  If IsMissing(SetWidth) Then
  '---Get the new width by calculating
    ' the difference between the window width
    ' and the InsideWidth and adding it to the InsideWidth
    dblWidth = frm.InsideWidth + (frm.WindowWidth - frm.InsideWidth)
  Else
    dblWidth = SetWidth
  End If
  If IsMissing(SetHeight) Then
  '---Get the new height by calculating the
    ' difference between the window height and
    ' the InsideHeight and adding it to the InsideHeight
    dblHeight = frm.InsideHeight + (frm.WindowHeight - frm.InsideHeight)
  Else
    dblHeight = SetHeight
  End If
'---Set the new size
  DoCmd.MoveSize , , dblWidth, dblHeight
End Sub

That part is pretty straight forward. The tricky part is figuring out what to pass to the routine in the first place. That’s where the math comes in. I typically set constants in the form module and set them to the sizes I want to always see for that form. Here is an example:

Const cHEADER_HT As Double = 0.8854
Const cDETAIL_HT As Double = 3.3229 + 0.25
Const cFOOTER_HT As Double = 0.25
Const cSHOW_ROWS As Integer = 1
Const cFRM_HEIGHT As Double = _
 (cHEADER_HT + (cDETAIL_HT * cSHOW_ROWS) + cFOOTER_HT) * 1440
Const cFRM_WIDTH As Double = 5.6042 * 1440

The measurements are in inches and multiplying them by 1440 converts them to twips. Then I call my standard routine and pass it the form as an object so it can retrieve some of the information itself and then apply the sizing to that form. Here’s a call where all I’m concerned about is the form height:

glrAutoResize(frm:=Me, SetHeight:=cFRM_HEIGHT)

Reply | Quote

Hi Charlotte,

Thank you for replying to my post. So, I wasn’t all that silly after all, it IS a confusing mess in Access.

Thank you for the code. I am going to study this a lot more closely.

The info was right under my nose.
I actually own the Access 2000 Developer’s handbook, Vol I, Desktop Edit. Boy, do I know it. I am dragging all 1613 pages back and forth from home and work each day. Get my exercise each day that way. Currently, I am studying and practicing Class Modules, very interesting…But I totally missed the section on manipulating forms when I went through the Table of Contents to pick my subjects of interest.

As a matter of fact, the authors are also using their FormInfo class module for form manipulation, which should fit right in with my area of interest right now.

Thanks again for pointing me in the right direction and for doing all the volunteer work in this lounge!

Reply | Quote

Glad to help, but don’t give me too much credit. The other moderators and a whole legion of Access users and developers post their answers and solutions as well. Sometimes I let them do all the hard work and then I waltz in with a quick answer and get the thanks!

Reply | Quote