Detect which subform is active

In Access 2003, I have a form Main that is a holder for several subforms, including Action and Task.  (They aren't linked to the MAIN form, it's just a shell/holder.  I have these subforms on fixed positions on the Main form instead of floating for various reasons that are *currently* more important that the feature I'm asking about here).

I would like to be able to set a global string variable TaskOrAction to either "Task" or "Action" when that respective subform is activated.

I need to find an event that I can use to detect that the subform is activated, or, more specifically, if ANY control on that subform is used, so I can set the value of TaskOrAction (which I use later to inform a query of whether to look for a key value in ...Task or Action).

I tried a few things that didn't work:
1)  According to MS, "Activate" does not work for subforms,

2)  According to this,
GotFocus does not work unless you use the record selector (though I did not see that happen either, when I tried GotFocus

3)  Enter only works for specific controls, and I need to set the variable if any control on the subform is used, or if the subform record selector is keyed.

I built a little test bed, attached.

Any suggestions on this would be appreciated.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Have you looked at the subform controls "Enter" and "Exit" events.  You could declare a variable in the Main form and use the subforms Enter and Exit events to set the value of that variable.

Depending on where you want this code to fire, say in a public module, you could use something like:


If the focus is on one of the controls on the main form, this would print out "Main", but if the focus is one of the textboxes in a subform, it would give the name of the subform.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this:

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
A slightly better version ...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"1)  According to MS, "Activate" does not work for subforms, "
That is correct.

We are basically using


keeping in mind that a subform IS a Control ... a container for an actual Form - to be used as a subform.

codequestAuthor Commented:
Works slick.  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.