ControlSource Properties

[mbentley]

I'm trying to query the properties of the control source of a field in a form. Specifically I want to interrogate the Data Type and the Field Size. I've gotten as far through the object model as:

Me.ActiveControl.ControlSource

But I cannot seem to srting together the right syntax to get it to work. Anyone had any experience with this?

 

[Mile-O]

You can't get that from a ControlSource - you'll need to use DAO or ADO to get in about the TableDefs Object model.

 

[The_Doc_Man]

Well, there is hope - but not necessarily a lot of hope.

Here is the theory behind how you would do this...

Behind every bound form is a recordset. For which you can get to the recordsetclone (HINT: LOOK UP "CLONE" or "RECORDSETCLONE" IN THE HELP FILES).

If you have a recordset, that recordset has a collection called "Fields" available to you. (HINT: LOOK UP "FIELDS COLLECTION" IN THE HELP FILES)

So if you loop through each field in the recordsetclone's Fields collection, you will be able to compare its name to the name in the controlsource of the control in question. (HINT: USE A FOR EACH LOOP, LOOK UP "FOR EACH" IN THE HELP FILES.)

Now, here is the catch. If the control is computed or unbound, you will not find it. But if you find a matching name, then that field's properties including .TYPE and .SIZE are available to you. (HINT: LOOK UP FIELD PROPERTIES IN THE HELP FILES TO GET THE RIGHT PROPERTY SPELLINGS.)

Do NOT try to just plop the name from the controlsource into the fields collection directly, unless you have a very good error handler that can identify "object not found in collection" errors.

 

[mbentley]

Looks like more trouble than it's worth. Think I'll just manually set a variable to the field size for each control that calls the code.

Thanks guys.