Solved

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

Posted on 2007-04-08
36
747 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:thenelson
  • 16
  • 8
  • 8
  • +2
36 Comments
 
LVL 75
ID: 18873849
Is this command button code on a main form or the subform?

mx

0
 
LVL 75
ID: 18873850
Never mind ... I see ....

0
 
LVL 75
ID: 18873853
Is it always the same control that you need to get the value of on the subform?

mx
0
 
LVL 75
ID: 18873858
Sorry ... guess my eyes are buggy. I have RE-READ the question now ...

ok ... let see ...

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18873863
Global Var set on lost focus...would require a FE/BE setup.
0
 
LVL 75
ID: 18873874
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
 
LVL 75
ID: 18873882
"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
 
LVL 75
ID: 18873884
... 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
 
LVL 75
ID: 18873898
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
 
LVL 75
ID: 18873900
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
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 166 total points
ID: 18873911
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
 
LVL 75
ID: 18873917
Gittin' kinda late in HOU isn't it jw ?

mx
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 18873927
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 18873931
sorry for typos...
0
 
LVL 75
ID: 18873932

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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18873998
Hey Nelson,
How come we don't see you here answering questions anymore?
J (Vicodin King)
0
 
LVL 39

Author Comment

by:thenelson
ID: 18874143
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
 
LVL 75
ID: 18874169
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75
ID: 18874171
jw:  "(Vicodin King)"

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

mx
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18874184
>>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
 
LVL 39

Author Comment

by:thenelson
ID: 18874225
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
 
LVL 3

Accepted Solution

by:
PHaddock earned 167 total points
ID: 18874240
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18874261
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
 
LVL 39

Author Comment

by:thenelson
ID: 18874265
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18874266
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
 
LVL 39

Author Comment

by:thenelson
ID: 18874276
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
 
LVL 3

Expert Comment

by:PHaddock
ID: 18874282
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18874297
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
 
LVL 39

Author Comment

by:thenelson
ID: 18874323
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18874347
Good....Z-Time. Good to see you around Nelson. Learned a lot from you in this past year.
J
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 167 total points
ID: 18874421
"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
 
LVL 39

Author Comment

by:thenelson
ID: 18874469
DatabaseMX,

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

Nelson
0
 
LVL 75
ID: 18874500
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
 
LVL 39

Author Comment

by:thenelson
ID: 18874514
Yep, it actually works!

I'll split it between Jeff & you.

Thanks to all.
0
 
LVL 39

Author Comment

by:thenelson
ID: 18874525
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
 
LVL 75
ID: 18875807
ok ... no problem.

Glad we solved it ...

mx
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now