Control datatype?

[SusanV]

Hi all,

Is it possible to loop through the controls collection of a form and
determine whether the datatypes of the various textbox controls are (date,
integer, or text)? I've got *most* of what I need to do worked out, but I'd
like to only check the value of the controls which contain a date, not those
which contain other information. I've found a workaround by first checking
the control name, but would like to write a function which is flexible to
all forms, rather than needing to write nearly identical code on multiple
forms containing different controls.

TIA,

SusanV

 

[Allen Browne]

You can use IsDate() to determine whether the value in a control is a date.

That's not really reliable though: it misses date fields that are Null, and
yields a false positive for non-date fields that happen to contain a value
that could be understood as a date.

Instead, use the ControlSource of the control to determine the field it is
bound to. You can then use the Type of the Field in the RecordsetClone of
the form to identify whether the field is dbDate or not.

In practice, there's a fair bit of work to get this going reliably. Controls
such as lines and command buttons don't have a ControlSource. Some controls
are unbound, and some may be bound to expressions (starts with =), or the
control might be bound to a calculated query field if the form's
RecordSource is a query.

This article has an example of how to loop through the form's controls,
identify the ControlSource, and refer to that Field in the form's
RecordsetClone:
Carry data over to new record
at:
http://allenbrowne.com/ser-24.html

From there, you just need:
If rs(strControlSource).Type = dbDate Then

 

[SusanV]

Thanks much Allen,

I've already got the code limiting the manipulation of the controls to only
textboxes similar to the following "play" code:

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
Debug.Print ctl.Name
End If

Next ctl

So I'm only going to be working with textboxes in this case. The function
I'm trying to write will have the control type passed to it in calling args,
so that I can verify what's been done whether the validation is Boolean,
numeric, what-have-you. (If that makes any sense).

I may never actually use it - but it's the best way I know to learn stuff:
"Can this be done? Sure why not? Well how would I go about it? Oh - this
isn't doing what I want - but I see what it CAN do!" Reading books puts me
to sleep and I can't remember syntax unless I've fought with it a few times.
<grin>

Thanks much for the link - something to chew on a bit!

;-)

SusanV