How might I change the width of a field on an Access form at runtime?

Question

I'm working to devise a custom layout system for my MS-Access forms. So far so good - I've had the idea! But now comes the tricky bit: resizing individual fields such that their width matches in an attractive manner.

Sad to say, I'm a bit stuck here - surely there must be some function, property or method call that will allow me to specify an exact width for a given field... But if so, I've been unable to find it.

Can someone here provide me with the information I so desperately need? Or better yet, suggest an appropriate Google query or reference site that could provide me with an answer to my query?

Answer 1

In code, at runtime, this is what you do:

Me!MyControl.Width = 1.5 * 1440

The 1.5 is 1.5" and the 1440 is something called TWIPs, which are relative measurement units that are sized according to screen resolution and your Windows base font size.

Now, where do you do this?

If you have different records in the same table that need different layouts, you'd do it in the OnCurrent event. That way, as you navigate from one record to another, the OnCurrent event would check whatever data controls which widths you use and set the widths. If there are only two layouts, an If/Then/Else is best. If there are more then two, then a SELECT CASE is better.

Also, if you are sizing and aligning colums, it would be helpful to assign module-level constants, something like this (this is pulled from an app that I first created in 1997 that has 15 different record types and 3 basic layouts with 8 or 9 minor variations):

Const Height = 0.1583 * 1440
Const row1Top = 1.0208 * 1440 ' top of first row
Const row2Top = row1Top + 0.2083 * 1440
Const row3Top = row2Top + 0.2083 * 1440
Const row4Top = row3Top + 0.2083 * 1440
Const row5Top = row4Top + 0.2083 * 1440
Const row6Top = row5Top + 0.2083 * 1440
Const row7Top = row6Top + 0.2083 * 1440
Const Logic0Top = 1.6833 * 1440
Const logic1Top = Logic0Top + 0.1667 * 1440
Const logic2Top = logic1Top + 0.1667 * 1440
Const logic3Top = logic2Top + 0.1667 * 1440
Const logic4Top = logic3Top + 0.1667 * 1440
Const lblLogic0Top = Logic0Top - 0.0208 * 1440
Const lblLogic1Top = lblLogic0Top + 0.1667 * 1440
Const lblLogic2Top = lblLogic1Top + 0.1667 * 1440
Const lblLogic3Top = lblLogic2Top + 0.1667 * 1440
Const lblLogic4Top = lblLogic3Top + 0.1667 * 1440
Const DateWidth = 0.7833 * 1440
Const lWidth = 2.0167 * 1440
Const rWidth = 2.7771 * 1440
Const lMemoWidth = 4.8771 * 1440
Const commtWidth = (2.745 * 1440)
Const lCommt = 3.775 * 1440
Const CommtHeight = 1.5 * 1440
Const memoHeightOffset = 0.94 '0.8354
Const bksCommtHeight = ((memoHeightOffset + 0.5) * 1440) + 250
Const ConditionWidth = 4.2417 * 1440 - 15
Const memo3Tall = ((memoHeightOffset + 1.4146) * 1440) + 160
Const memo4Tall = ((memoHeightOffset + 1.2063) * 1440) + 160
Const memo5Tall = ((memoHeightOffset + 0.9979) * 1440) + 160
Const memo6Tall = ((memoHeightOffset + 0.7896) * 1440) + 160
Const memoShort = (1.5208 * 1440) + 160 ' Height of memo
Const lblOffset = -0.3291 ' adjusts memo label top for new layout
Const lblMemoTop3 = ((lblOffset + 1.9167) * 1440) - 480 - 100
Const lblMemoTop4 = ((lblOffset + 2.125) * 1440) - 480 - 55
Const lblMemoTop5 = ((lblOffset + 2.3333) * 1440) - 480 - 100
Const lblMemoTop6 = ((lblOffset + 2.5417) * 1440) - 480 - 100
Const lblMemoTop7 = ((lblOffset + 2.75) * 1440) - 480 - 100
Const lblBib0 = (2.5833 * 1440) - 480
Const Bib1 = 2.4674 * 1440
Const Bib2 = 2.6257 * 1440
Const Bib3 = 2.784 * 1440
Const Bib4 = 2.9424 * 1440

Very complicated because the form is very complicated (the OnCurrent event got so big that I had to split it into two different subroutines -- I can't remember the exact maximum size of a subroutine, but it might be 64K. Yes, that's how bad it was, and yes, I regret implenting things in that fashion. If I had it to do over, I'd use a tab control with invisible tabs and allocate one tab per record type. But I didn't think of that way back then.

If you don't need to move or resize things vertically you might be able to get by with a handful of constants:

Col1Left
Col2LeftA
Col2LeftB
Col1WidthA
Col1WidthB
Col2WidthA
Col2WidthB

That would allow you to have two columns of controls with two different horizontal positions for the second column and two different widths for each column. I would recommend very transparent names for these constants so that your code is more-or-less self-explanatory.

Answer 2

All the properties of any given control can be referenced in VBA code by using me.CtlName.PropertyName or Forms!CtlLName.PropertyName. In your case that would be me.CtlName.Width.

Note that the sizes of properties are in twips. And there are 1440 twips per inch. If you are using Metric I have no idea how many twips in cm.

That said I size my forms to match 1024x768 unless a client requestes otherwise.

Answer 3

how to change the fields width [question]

Using SQL DDL:

ALTER TABLE MyTable ALTER MyField VARCHAR(20);

This will not affect the column's other properties e.g. it would remain NOT NULL if originally created that way and the default value (if any) would be the same.

P.S. I'm answering the OP's question, not the controversial edited.

Answer 4

Why do you want the field widths to change at runtime? Behavior like this falls outside the typical Windows UI conventions, and will probably through your users for a loop when things start changing size after the form has been displayed. Just give the fields a wide margin and leave the sizes alone after the form has been displayed.

For a problem like this, just think gloves.