Make subform control reference with a string

[dcdimon]

Greetings,



Once again I find myself tantalizingly close to an answer but unable to get all the way there without help. Or at least without someone saying "it can't be done".

I'm trying to reference a control on a subform so I can auto-fill it based on a control on the main form. I am well versed in how to do this when typing the specific pointers, however, what I'm trying to do is build the pointer through strings. The controls on my main form and subform are similarly named, with the last three characters being different.

I have a controls on the main form titled ControliTxt (where i is a number 1-10). The subform controls are titled ControliOOR (again i is a number 1 - 10) The subform controls are used to indicate if the data in the main form is within the data range for that control and the numbers on the main form control coincide with the subform control number (Control1Txt goes with Control1OOR).

I should also add that my form controls, both main and sub, are dynamic. Meaning that what they measure, the ranges, and the control source changes as different fields are picked via another form. The control names don't change, but what they're measuring does. Which is why I'm looking for coding solution that can pull names and point to controls based on the current control and not hard wiring that information.

I'm trying to create a module that looks at the active control's name (Control1Txt) and use that to work out the subform control's name, something like this:

Forms!DynamicDataTest!Compliancesubform.form.Control1OOR

I can build this as a string by cropping the last three characters of the Screen.ActiveControl.Name property and inserting the OOR ending for the subform using this:

Ctl = "forms!dynamicdataformcopy!compliancesubform.form." & Left(Screen.ActiveControl.Name, Len(Screen.ActiveControl.Name) - 3) & "OOR"

My problem comes that I don't know how to use this string to insert a value into this control. I've also tried building the pointer using the components of the string (see below), but as you may expect, the VBA editor doesn't know what to do with all the separate parts.

forms!dynamicdataformcopy!compliancesubform.form. & Left(Screen.ActiveControl.Name, Len(Screen.ActiveControl.Name) - 3) & "OOR"

I'm thinking that I'm missing the proper use of quotes and the '&' symbol in putting this together. And/or that I'm trying to use the string in the incorrect way. Or that I'm barking up the wrong tree.

Any help would be appreciated.

DD

[June7]

I have programmatically constructed control name like:

Forms(Me.Name).Controls("tbxPost" & i).Value

So maybe in your case:

Forms!dynamicdataformcopy!compliancesubform.Form.C ontrols(Left(Screen.ActiveControl.Name, Len(Screen.ActiveControl.Name) - 3) & "OOR").Value

[dcdimon]

Woohoo!!!!!! It works!!!

Thank you June7, I knew there was a way to do this, but I couldn't think of how to put it together. I would have never stumbled upon the parenthesis usage on my own. I have spent hours today searching for how to do this - I should have just posted my question right away and I'd be farther along on my project.

Thank you again. You are the best!

DD