nested subform reference

[Jeff]

I'm trying to refer to a control on a nested subform within Access 2003
but get a ""Type Mismatch" error from the following code:

Dim ctl As Control
Dim intYear As Integer
Dim datDate As Date

datDate = Date
intYear = DatePart("yyyy", datDate)

Set ctl = Forms![Main form]![Subform1].Form![Subform2].Form & "!" &
intYear



The last line gives me the error. The name of the control I am
referring to is a year (i.e. 2006). So the last line should set the ctl
to:

Forms![Main form]![Subform1].Form![Subform2].Form!2006

I know this is a simple syntax problem but I can't seem to get around
it. Any help is much appreciated. Thanks.

 

[Douglas J. Steele]

You can't use a variable to refer to a control like that.

Try:

Set ctl = Forms![Main
form]![Subform1].Form![Subform2].Form.Controls(CStr(intYear))

I would question your design, though, if you have a field named 2006. It's
generally considered to be a bad idea to store data in a field name.

 

[Jeff]

Man that was a quick response! Thanks it worked.

Yeah I know the field names are lousy, I inherited this database from
someone else. My guess is that he did it this way to avoid having to
use yet another nested subform to store all of the mutiple years (2002,
2003,2006, etc). I'll overhaul it at some point. Thanks for all of your
help.