Use a string to reference a control?

[mbuelsing]

I've got a complicated Access form with numerous subforms. The client
would like to require data input on some fields being displayed on the
form before the record(s) is saved. The fields he chooses could be on
the main form or a subform. I have figured out a procedure that will
record the control reference to the text box he chooses as being a
required field and I store that reference as a string in a table. Like
this

Fieldname=ControlRef
Field value = "Forms!frmEvent.tbFirstName"

The question is, how to assign a control object to that control the
string points to so I can get the control's value and check for nulls
and empties.

I have tried:

strReference = "Forms!frmEvent.tbFirstName"

Dim ctl as control
set ctl = strReference

But the string is not translated into the object. There is an error
message.

How do I use a string variable to set the object?

Thanks in advance!

 

[Klatuu]

Me.Controls(strCtlName)

 

[Douglas J. Steele]

If all you want is the value of that's contained in the control, try using
the Eval function:

Eval(strReference)

To set a reference to it, though, I think you'll need to parse the string.
The Split function will help:

Dim ctl as control
Dim strReference As String
Dim varReference As Variant

strReference = "Forms!frmEvent!tbFirstName"
varReference = Split(strReference, "!")
set ctl = Forms(varReference(1)).Controls(varReference(2))


Obviously the code will have to be a little more sophisticated if you've got
to deal with subforms as well.

 

[mbuelsing]

Doug, I think you have answered the question best for my purpose. I
guess I was hoping for something more simple.

I had hoped there was something that would just accept the entire
reference string as it is since it is already built and correct.

Since I have a number of sub forms, I will have to write a procedure to
parse my reference strings into their components and then use the
components as you suggest. Sometimes the field I need to evaluate will
be on the main form and sometimes on the subform, That will make the
procedure more convoluted. It will have to test for that and react
accordingly.

Something like Forms("frmEvent").controls(strControlReference) would be
better, except I could not get it to refer to controls on the subforms

Thanks

 

[Douglas J. Steele]

For a subform, you'd need:

Forms("FormName").Controls("SubformContainerName").Form.Controls("ControlName")

Depending on how the subform was added to the parent form, the name of the
subform container control may be different than the name of the form that's
being used as a subform. You need the name of the control on the parent
form, not the name of the form being used as a subform if they're different.

 

[mbuelsing]

Thanks Doug, that helps. I'll use it. - Mark