Get subform and control name that has focus?

[CedarTree]

Hello - Have a main form with several subforms (on tabs within a Tab control). Whenever someone clicks on a subform (or using a Timer is fine too), I'd like to know which form was most recently focused on, and which field was focused on (the subforms are Datasheets). I'd love to do it universally so I don't have to put code within each subform, if possible. Suggestions? Thanks!

 

[MajP]

I do not understand and what is the purpose?

 

[CedarTree]

Purpose is to do a look up from a table that has "form", "field", "valid values" to choose from.

 

[MajP]

See solution. I do not get what you will do with it, but it shows the active control from the subforms.

 

[CedarTree]

Thanks - will check it out!

 

[MajP]

As I said, this is just a guess of what you were asking. Not sure what you then do with that information.

 

[CedarTree]

Hi - what triggers the SetControl function? I put a breakline there and it never runs for me in my database. I created a global variable and created the subform function SetControl to set global variable to ActiveControl, but it never triggers. Thanks!

What you gave me is perfect - if I can get it to work in my database.

 

[MajP]

In the subforms I select all the controls in design view. Then on the OnEnter event instead of select [Event Procedure] or [Embedded Macro] I type in the name of the function.
=FunctionName()

Now a single function handles all the events for every control. It would be a pain to have to do individual event procedures, but you could do it. I describe here the three ways to handle events.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

 

[CedarTree]

Nice.

 

[CedarTree]

That was perfect. Thanks again.
Now, when I know the form and the field, I have a mapped table that tells the user - here are the translations of various codes in those fields.

 

[MajP]

Now it makes some sense to me. At first I thought this was a silly idea, but I can definitely see some utility to this. This might give you some ideas. From the control I can get the field name, and the recordsource, So like you I can build a query based off of that field name. Your query would obviously be different.

Public Sub ShowControl()
  Dim Domain As String
  Dim FieldName As String
  Dim RS As DAO.Recordset
  Dim strSql As String

  FieldName = mCurrentControl.ControlSource
  Domain = mCurrentControl.Parent.RecordSource
  strSql = "Select Distinct [" & FieldName & "] from ( " & Domain & ")"
  Me.txtControl = "Form: " & mCurrentControl.Parent.Name & "       Control: " & mCurrentControl.Name

  Set RS = CurrentDb.OpenRecordset(strSql)
  Set Me.lstDetails.Recordset = RS

End Sub

In this example I show the discrete values for any control you selected. But this could be your list show code descriptions. Or this could be simpler if you already have the code lists as individual queries for each field. Then you can just assign that query to your listbox based on the field and form name.

 

[CedarTree]

Right - in my case I load a table with possible values and the user can then see a "Legend" of possible values. Normally I use contextual drop-downs to show the "legend" but this scenario was different.

 

[Bubba002]

Hey MajP, I'm using your 'ID Active Control' example to get the Control Names from just one subform to my main form. I copied your main form and subform code exactly onto my main form and subform code respectively, but it's not working. It compiles OK but, ...

I get the following error when I open my main form, and when I move from field to field in my subform:
Microsoft Access can’t find the field ‘Microsoft Access can’t fine the field ‘|1’ referred to in your expression1 referred to in your expression.
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

Not sure how to get past this issue. Any suggestions?

 

[June7]

Best to start a new thread instead of hi-jacking an old one. New threads get more attention. Include link to old thread if you think it could be helpful.

Possibly you will have to provide your db so we can determine where the sample and your implementation diverge.

 

[MajP]

Can you add a debug.print and postback the results from the immediate window says? If you have a sample DB that would be easier.

My guess would be that strSql does not resolve nicely based on the forms "Domain"

Public Sub ShowControl()
Dim Domain As String
Dim FieldName As String
Dim RS As DAO.Recordset
Dim strSql As String

FieldName = mCurrentControl.ControlSource
Domain = mCurrentControl.Parent.RecordSource
strSql = "Select Distinct [" & FieldName & "] from ( " & Domain & ")"

Debug.Print strSql

Me.txtControl = "Form: " & mCurrentControl.Parent.Name & " Control: " & mCurrentControl.Name

Set RS = CurrentDb.OpenRecordset(strSql)
Set Me.lstDetails.Recordset = RS

End Sub

 

[Bubba002]

Can you add a debug.print and postback the results from the immediate window says? If you have a sample DB that would be easier.

My guess would be that strSql does not resolve nicely based on the forms "Domain"

Yes, I have a test database I’m using. It's a split databse. 'Test-01 Instr Summary_be' is the back end (~15MB), and 'Test-01 Instr Summary_fe1' (~30MB) is the front end. I hope they can attach here.

My main form is frmIS-1c. My subform (subformIS-1a) is in tab ‘All’. New ‘txtControl’ unbound control is at far right bottom of main form. Once I’m able to capture the control names, my plan is to use the ‘txtControl’ value as a sort of variable in a Conditional Formatting expression.

Right now my Conditional Formatting expressions are all hard-coded. While it works, I’d like to make my forms universal to make it easier to apply to other applications.

 

[Bubba002]

Looks like the files are too large to attach. Is there another way I can get these over to you?

 

[CJ_London]

Compact/repair then zip

 

[Bubba002]

Gee, why didn't I think of that? Here is the zip file (~5MB).

 

[MajP]

Module level and class variables have to appear at the top of the module normally like this.

Option Explicit
Option Database

Public someModuleLevelVariable as Datatype
Private someOtherVariable as datatype

Also to debug your code, force a compile and it would have told you this
Debug-->Compile in the vba editor.

Whenever you get this error

The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].

Force a compile. This error comes up a lot when there is some bad code anywhere in the application, and often has nothing to do with the problem. Basically the code is hanging.