Find control data source size

[Guest]

I have a generic pop up bulk data entry form. Basically, where there is a
field on a form that potentially will have several lines of text, you right
click and up pops a bigger window to enter text - sort of like F2 in design
view. While most of the fields are memo data types, some are text. When
entering data into the bulk data entry screen, users sometimes enter too much
text to store in a 255 character text field. When they save and close the
bulk entry screen, and the program copies the text into the underlying form,
they get an error message that the pasted information is too big.

What I would like to do is to identify the length of the Control Source for
a particular form control. I already know the control name which is stored
when the form is open. If I know the size, I can work out a way check the
number of characters being entered and provide a warning if the text is too
long. At a minimum, I can display the limit on the number of characters.

 

[Allen Browne]

You can derive the FieldSize from the Field in the RecordsetClone of the
form.

For example:
Me.RecordsetClone.Fields!F2.Size

If you only know the control name is txtF2 (which may not be the field
name):
Me.RecordsetClone.Fields(Me.txtF2.ControlSource).Size

 

[Guest]

Hi again Allen. I am almost there. Set up the following line:
intFieldLength =
Forms(Screen.ActiveForm.Name).Controls(strText).ControlSource.Size
The form name is correct. strText is the correct control name. I am still
not getting a result. Any suggestions?

 

[Allen Browne]

Try something like this:

With Screen.ActiveForm
Debug.Print .RecordsetClone.Fields(.ActiveControl.ControlSource).Size
End With

Of course that assumes there is an active form, and that its active control
has a Control Source (e.g. command buttons don't.)

 

[Marshall Barton]

Set up the following line:
intFieldLength =
Forms(Screen.ActiveForm.Name).Controls(strText).ControlSource.Size
The form name is correct. strText is the correct control name.

When you use a control name as a collection index, it must
be in quotes. Use either:

Controls("strText")
or
Controls!strText

 

[Guest]

Thanks to Allen (once again) and Marshall for your help. It all got too hard
to add a 'nice to have' feature. After fiddling for some hours, it was sort
of working but then I spent another hour or two trying to get it to work for
a subform as well. Found a useful article at
http://support.microsoft.com/kb/139040 on identifying a subform but cannot
justify the time to complete it. I may come back to it some time in the
future.