"screen.PreviousControl" when previous control is in a subform

I need to get the value of the control in a subform that had the focus before a command button in the parent form was clicked. It would be anyone of eight combo boxes in the subform.

When I put
Screen.PreviousControl.Name
in the command button code, I get the name of the command button.

When I put
Me.ICD_subform.SetFocus
Screen.ActiveControl.Name
n the command button code, I get Run Time Error 2474:
"The expression you entered requires the control to be in the active window."

What is the correct syntax to accomplish this?
LVL 39
thenelsonAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Is this command button code on a main form or the subform?

mx

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Never mind ... I see ....

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Is it always the same control that you need to get the value of on the subform?

mx
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry ... guess my eyes are buggy. I have RE-READ the question now ...

ok ... let see ...

0
jefftwilleyCommented:
Global Var set on lost focus...would require a FE/BE setup.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I can see at least one way:

Put a hidden control on the parent.
Have each control on the subform set it's value in the hidden control
Then ... reference your hidden control's value with the command button code.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"When I put Screen.PreviousControl.Name in the command button code, I get the name of the command button."

I emulated that, but I got the Name of the subform control.

mx

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
... Which tells me that the Screen.PreviousControl will not drill down to the control tier on the subform, only the subform control itself.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So.

On the main form:

Add txtGetMySubformValue

On the subform:

Private Sub cboYourCombo1_LostFocus()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo1
End Sub

Private Sub cboYourCombo2_LostFocus()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo1
End Sub

Private Sub cboYourCombo3_LostFocus()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo1
End Sub

and so on ....

Now reference txtGetMySubformValue  in your command button code

mx


0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry ... typo's :

Private Sub cboYourCombo1_LostFocus()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo1
End Sub

Private Sub cboYourCombo2_LostFocus()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo2   >> #2
End Sub

Private Sub cboYourCombo3_LostFocus()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo3  >> #3
End Sub
0
jefftwilleyCommented:
Nelson,
The Subform Control itself would have been the PreviousControl.

I played around too with after focus to set a global MyControl variable.

then with the command button


Private Sub Command18_Click()
MsgBox Screen.PreviousControl.Name & "." & MyControl
End Sub

Looks like subFormName.Text1
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Gittin' kinda late in HOU isn't it jw ?

mx
0
Mike EghtebasDatabase and Application DeveloperCommented:
thenelson,

You probably are interested in the value of controls like text box, listbox, etc. not all (and some like command boxes which has no value).

As jefftwilley also mentioned, have a buplic variable (variant type possibly) and in the Lost Focus of the controls you which which to track their values add:

MyVar = Me!ControlName

Now, when you click on your cmmand button in question, MyVar would deliver what you expect.

Mike
0
Mike EghtebasDatabase and Application DeveloperCommented:
sorry for typos...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

Note:  " It would be anyone of eight combo boxes in the subform."

Really, no need for a global variable. A hidden text box on the main for will do it.

mx
0
jefftwilleyCommented:
Hey Nelson,
How come we don't see you here answering questions anymore?
J (Vicodin King)
0
thenelsonAuthor Commented:
DatabaseMX,

Great thought but cboYourCombo1 does not fire the Lost focus or exit event when I click the command button

Private Sub cboYourCombo1_LostFocus()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo1
End Sub

jefftwilley:

Screen.PreviousControl.Name & "." & MyControl
would work if I knew which control in the subform had the focus.

How come we don't see you here answering questions anymore?
I got burnt out, Jeff. I'll probably join back in sometime after I figure out this new Experts Exchange look.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Nelson ... not sure what you mean?

My assumption is that ... you are in the subform, moving around, selecting values in the combo's, etc. Then, you go to the main form ... and click the command button ... and you want the value of the combo you were just last in ?  No ?  Maybe I'm still not clear on just what you need.  

Also ... lets bag the Lost Focus and use AfterUpdate instead ...

Change these:

Private Sub cboYourCombo1_LostFocus()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo1
End Sub

To:

Private Sub cboYourCombo1_AfterUpdate()
    Me.Parent.txtGetMySubformValue=Me.cboYourCombo1
End Sub

I'm leaving for LA from San Diego right now (20:15 pst) ... I will be back online later tonight.

mx

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
jw:  "(Vicodin King)"

LOLOL .... You and ' Dr House' !!!!

mx
0
jefftwilleyCommented:
>>Screen.PreviousControl.Name & "." & MyControl
would work if I knew which control in the subform had the focus.

This, on your subform control's lost focus event

Private Sub Text_LostFocus()
MyControl = Screen.ActiveControl.Name ' MyControl is a Global Variable
End Sub

my thought was to set the Global Var in the lost_focus event for each of your 8 controls.

This we already know pulls the subform control name..concatenated with the Global MyControl

Private Sub Command18_Click()
MsgBox Screen.PreviousControl.Name & "." & MyControl
End Sub
0
thenelsonAuthor Commented:
DatabaseMX:

My assumption is that ... you are in the subform, moving around, selecting values in the combo's, etc. Then, you go to the main form ... and click the command button ... and you want the value of the combo you were just last in ?  

Yes, that's it.

Unfortunately leaving a subform to a parent form does not fire the subform's control (or the subform's) Lost focus or exit events. The subform container exit event does fire but this is too late because Screen.PreviousControl then points back to the subform container.

I can't use the control's update event because I am not changing the control's contents.
0
PHaddockCommented:
Is it the control's value you want to read?  If it's not changing, I'm not sure why you want to know which control last had focus.  If you do then I'd hold the control name in a global variable as someone else suggested, but set it in the On Got Focus event.

However, if you're not changing the control's contents, and it is the value you want from the control, why don't you read the control value explicitly since there are only 8 of them.
0

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
jefftwilleyCommented:
Method # 1287

Private Sub Command18_Click()
Dim sfm As String
sfm = Screen.PreviousControl.Name
MsgBox Me(sfm)(MyControl).Value   ' MyControl as Global String variable
End Sub
0
thenelsonAuthor Commented:
PHaddock,

"I'm not sure why you want to know which control last had focus."
I want the user to select one of the eight Combo boxes then click on the button to copy its value to a table (using an insert query).

"why don't you read the control value explicitly"
Which control?-that's the rub.

"set it in the On Got Focus event"
The got focus event if the value doesn't change and the update event if it does. Might work. I'll try it.
0
jefftwilleyCommented:
method #1287A

Me(Screen.PreviousControl.Name)(MyControl).Value   ' MyControl as Global String variable set on the Lost_Focus event of your combo boxes subform controls

0
thenelsonAuthor Commented:
jefftwilley,

"Me(sfm)(MyControl).Value   ' MyControl as Global String variable"
Where does the text string in (MyControl) come from?

BTW: I don't need to use Screen.PreviousControl.Name to get the name of the subform. I know the name of the subform - It's ICDsubform - the name of the control is what I don't know.
0
PHaddockCommented:
That makes sense.   Are the combo boxes defaulting to something meaningful?  If they're just for new input you could put a default value into the dropdown list such as "not yet selected".  That would force the user to choose a value and thus have the after update fire.
0
jefftwilleyCommented:
Private Sub MyCombo_LostFocus()
MyControl = Screen.ActiveControl.Name ' MyControl is a Global Variable
End Sub

I'm not sure if this is the best way to do what you need...I mean, in truth you can actually store the Value from the control itself when you lose focus or After_Update...Less fuss that way. Then just call on the variable directly to get your value.

Or as Mx eluded to earlier, hide the value in a text box on your form...so you don't have to go the Global Variable route.

>>I want the user to select one of the eight Combo boxes then click on the button to copy its value to a table (using an insert query).

Is it just one of the 8? or just the LAST one of the 8. Are you harvesting the values from all to create a where-type statement? Got code for that.
J

0
thenelsonAuthor Commented:
I got it:

I added a text box to the subform. I don't want the use's to see it but it's Visible property has to be true so:
Name! ReturnFromParent
Top: 0"
Left: 0"
Height: 0.0007"
Width: 0.0007
BackStyle: Transparent
Special Effect: Flat
BorderStyle: Transparent

Private Sub CommandButton1_Click()
Me.ICD_subform.SetFocus
Me.ICD_subform!ReturnFromParent.SetFocus   'This fires the combo box lost focus event
Debug.Print Transfer
End Sub

Private Sub MyCombobox1_LostFocus()
Transfer = "MyCombobox1"  'Transfer is a global variant I use for passing all sorts of data.
End Sub
0
jefftwilleyCommented:
Good....Z-Time. Good to see you around Nelson. Learned a lot from you in this past year.
J
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Unfortunately leaving a subform to a parent form does not fire the subform's control (or the subform's) Lost focus or exit events."

***** Method # 1288: 'Got Focus' *******************

OK ... I see what you are doing ***** >>>>

On the Main form:

Add txtGetMyCombo

On the subform:

Private Sub cboYourCombo1_GotFocus()
    Me.Parent.txtGetMyCombo=Me.cboYourCombo1
End Sub

Private Sub cboYourCombo2_GotFocus()
    Me.Parent.txtGetMyCombo=Me.cboYourCombo3
End Sub

Private Sub cboYourCombo3_GotFocus()
    Me.Parent.txtGetMyCombo=Me.cboYourCombo3
End Sub

and so on ....

Now reference txtGetMyCombo in your command button code.

So, whenever to click into or enter into a combo box on the subform. the current value is immediately written to the hidden form on the main form. NOW ... if you might be changing the combo box value after you have entered, the add the same code to the AfterUpdate also.  Even if you make a change ... and the next thing you do is click into the Main form, the Combo AU has to trigger ... I don't see how it cannot.  So, you've done a CYA for both situations.

mx
0
thenelsonAuthor Commented:
DatabaseMX,

I like yours better than mine. It's simpler, cleaner.

Nelson
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, allrighty then ...

I think it came to me on the 2hr drive up from San Diego to LA.

Where are you located ?

Oh ... but ... does it actually work ?

And actually ... the AU and GotFocus could each call a common function (in code behind your subform) right from the property sheet, that way ... you can select all eight cbo's at once ... paste in the function call on each event ... basically two pastes ... one for AU and on for GotFocus.  The function would grab the value of the Screen.ActiveControl ... and send it to the hidden text box on the main form.  That's even cleaner yet.

btw ... if ... this is the solution ... I  have no problem splitting the points with Jeff ... in fact, preferred.

ok ... time to zzzz  ... catch you in the am

mx
0
thenelsonAuthor Commented:
Yep, it actually works!

I'll split it between Jeff & you.

Thanks to all.
0
thenelsonAuthor Commented:
Actually, PHaddock was the first to suggest using the got focus event. I'll split it three ways.

BTW: Don't need to bother with the after update event it I capture the name of the control instead of its value and look up the value after the button is clicked.

Night All.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... no problem.

Glad we solved it ...

mx
0
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
Visual Basic Classic

From novice to tech pro — start learning today.