Get subform and control name that has focus? (1 Viewer)

[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.