By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,501 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,501 IT Pros & Developers. It's quick & easy.

Referencing problem

P: n/a
cmd
I have code in the OnExit event of a control on a subform. The code
works properly in this instance. If, however, I put the same code in
the OnExit event of a control on a Tab Control of a main form, the code
errors out at the 2nd line. The error number is 13 and the description
is "Type mismatch". Both controls are memo fields. I suspect that
"Screen.ActiveControl.Parent" is not referencing the form as intended.

Thanks for any help,
Mark
'*********************
Dim frm As Form
Set frm = Screen.ActiveControl.Parent
If frm.Dirty = True Then
frm.Dirty = False
End If

'(rest of the code)
'*********************

Jul 25 '06 #1
Share this Question
Share on Google+
21 Replies


P: n/a
The Parent of a control is not necessarily the form.
It could be the tab control (your example), or another control (e.g. for an
attached label), or an option group (for a button in the group), or another
form (for a subform control), and so on.

Perhaps you could use use Form.ActiveControl, or pass a reference to the
control if you are calling a generic procedure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<cm*@mountain.netwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...
>I have code in the OnExit event of a control on a subform. The code
works properly in this instance. If, however, I put the same code in
the OnExit event of a control on a Tab Control of a main form, the code
errors out at the 2nd line. The error number is 13 and the description
is "Type mismatch". Both controls are memo fields. I suspect that
"Screen.ActiveControl.Parent" is not referencing the form as intended.

Thanks for any help,
Mark
'*********************
Dim frm As Form
Set frm = Screen.ActiveControl.Parent
If frm.Dirty = True Then
frm.Dirty = False
End If

'(rest of the code)
'*********************

Jul 26 '06 #2

P: n/a
cmd
Thanks Allen, but I'm lost.

I checked my copy of the Access97 Developer's Handbook, 3rd ed. and
found several cautions against using Screen.ActiveControl and
Screen.ActiveForm. The suggestion was to use Me properties instead. But
"Me" isn't applicable in a global function -- right?

In this case, I don't think there's a problem using
Screen.ActiveControl, but there is trying to reference the
ActiveControl's form by using Screen.ActiveControl.Parent or
Screen.ActiveForm.

"Parent" is this case appears to be the TabControl or Page of the
TabControl on which the ActiveControl exists -- rather than the subform
itself.

I wasn't sure how to use your suggestion (Form.ActiveControl) -- I
tried using:

If Form.ActiveControl.Dirty = True Then
Form.ActiveControl.Dirty = False
End If

.... but I get an "object doesn't support this property or method"
error.

Thanks,
Mark
Allen Browne wrote:
The Parent of a control is not necessarily the form.
It could be the tab control (your example), or another control (e.g. for an
attached label), or an option group (for a button in the group), or another
form (for a subform control), and so on.

Perhaps you could use use Form.ActiveControl, or pass a reference to the
control if you are calling a generic procedure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<cm*@mountain.netwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...
I have code in the OnExit event of a control on a subform. The code
works properly in this instance. If, however, I put the same code in
the OnExit event of a control on a Tab Control of a main form, the code
errors out at the 2nd line. The error number is 13 and the description
is "Type mismatch". Both controls are memo fields. I suspect that
"Screen.ActiveControl.Parent" is not referencing the form as intended.

Thanks for any help,
Mark
'*********************
Dim frm As Form
Set frm = Screen.ActiveControl.Parent
If frm.Dirty = True Then
frm.Dirty = False
End If

'(rest of the code)
'*********************
Jul 26 '06 #3

P: n/a
<cm*@mountain.netwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
"Me" isn't applicable in a global function -- right?
PMFJI ... it is available if you pass the form object to the function.

Call libSomeFunction(Me)

libSomeFunction(frm As Form)

Regards,
Keith.
www.keithwilby.com
Jul 26 '06 #4

P: n/a
"cm*@mountain.net" <cm*@mountain.netwrote in message
<11**********************@m79g2000cwm.googlegroups .com>:
"Parent" is this case appears to be the TabControl or Page of the
TabControl on which the ActiveControl exists -- rather than the
subform itself.
You'd probably need some errorhandling, but try out this idea

Dim frm As Form
Dim ctl As Object

Set ctl = Screen.ActiveControl
Do While Not TypeOf ctl Is Form
Set ctl = ctl.Parent
Loop

If TypeOf ctl Is Form Then
Set frm = ctl
Else
MsgBox "ouch"
End If

--
Roy-Vidar
Jul 26 '06 #5

P: n/a
cmd
Thanks RoyVidar,

.... Debug.Print Screen.ActiveControl gives me the value of the control,
rather than the control name or type -- is "Set ctl =
Screen.ActiveControl" still what I want?

Thanks,
Mark

RoyVidar wrote:
"cm*@mountain.net" <cm*@mountain.netwrote in message
<11**********************@m79g2000cwm.googlegroups .com>:
"Parent" is this case appears to be the TabControl or Page of the
TabControl on which the ActiveControl exists -- rather than the
subform itself.

You'd probably need some errorhandling, but try out this idea

Dim frm As Form
Dim ctl As Object

Set ctl = Screen.ActiveControl
Do While Not TypeOf ctl Is Form
Set ctl = ctl.Parent
Loop

If TypeOf ctl Is Form Then
Set frm = ctl
Else
MsgBox "ouch"
End If

--
Roy-Vidar
Jul 26 '06 #6

P: n/a
cmd

Help! This is getting pretty frustrating and it seems that it shouldn't
be that much of a problem!

The spellcheck function (in a global module) which I have been using
works fine for controls on a mainform/subform layout. The function is
placed in the relevant controls' OnExit event. Spellcheck occurs as the
user tabs out of each field. Warnings are turned off during the event
to suppress the "Spellcheck is complete" message. The code is as
follows:

Dim frm As Form
Set frm = Screen.ActiveControl.Parent
If frm.Dirty Then
frm.Dirty = False
End If

On Error Resume Next

Dim L As Integer
L = Nz(Len(Screen.ActiveControl), 0)
L = Len(Nz(Screen.ActiveControl, ""))
If L 0 Then
With Screen.ActiveControl
.SetFocus
.SelStart = 0
.SelLength = L
End With

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSpelling
DoCmd.SetWarnings True
End If

This global function, however, does not work as is on a
mainform/subform/tabcontrol layout. The procedure does not recognize
"Screen.ActiveControl.Parent" as the subform on which the
control/tabcontrol is placed -- hence, it errors at the line: "Set frm
= Screen.ActiveControl.Parent".

I can live without using this global function for layouts where I have
a subform with a tabcontrol on the subform. Therefore, I thought I
would try using the code as a local procedure and placed the code in
the OnExit event for a specific control on the tabcontrol of the
subform. I changed "Set frm = Screen.ActiveControl.Parent" to: "Set frm
= Me.Form" and apparently this works for this portion of the code.
Spellcheck works as it's supposed to, but the focus will not leave the
control. An error-handler reports the error number as "0" and there is
no error description.

I added a statement at the end which returns the focus to the
tabcontrol:
"Screen.ActiveControl.Parent.SetFocus" and this resolves the problem of
getting stuck in the control. However, it also prevents the user from
simply tabbing from one field to the next.

I've tried other variations of spellchecking routines placed as a local
procedure for each control, but the focus also stays locked on the
current control -- e.g.:

(I changed "PreviousControl" to "ActiveControl" since I wanted it to
run as an OnExit event, rather than from a command button.)

'************ Code Example 1 Start ****************
' Adaptation by Terry Wickenden of code
' from Microsoft Knowledge Base

Private Sub cmdSpell_Click()
Dim ctlSpell As Control

Set ctlSpell = Screen.PreviousControl
If TypeOf ctlSpell Is TextBox Then
If IsNull(Len(ctlSpell)) Or Len(ctlSpell) = 0 Then
MsgBox "There is nothing to spell check."
ctlSpell.SetFocus
Exit Sub
End If
With ctlSpell
.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)
End With
DoCmd.RunCommand acCmdSpelling
Else
MsgBox "Spell check is not available for this item."
End If
ctlSpell.SetFocus
End Sub
' ****************** Code Example 1 End ********************

Jul 26 '06 #7

P: n/a
cm*@mountain.net wrote in
news:11*********************@h48g2000cwc.googlegro ups.com:
... Debug.Print Screen.ActiveControl gives me the value of the
control, rather than the control name or type -- is "Set ctl =
Screen.ActiveControl" still what I want?
Since you'll be calling the external function from a context where
Me is meaningful, pass the control reference to your function. Then
test its parent until you get to its parent form:

If TypeOf ctl.parent = Form
ctl.parent.Requery
Else
ctl.parent.parent.Requery
End If

That's air code, so you'll have to change it appropriately to work
in all cases.

This method completely avoids the Screen.Activecontrol issues, which
I have never trusted.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 26 '06 #8

P: n/a
cmd
Thanks David for the response
.... but, "requery"?
.... I must be way over my head here because I have no idea what I would
be requerying.
Mark

David W. Fenton wrote:
cm*@mountain.net wrote in
news:11*********************@h48g2000cwc.googlegro ups.com:
... Debug.Print Screen.ActiveControl gives me the value of the
control, rather than the control name or type -- is "Set ctl =
Screen.ActiveControl" still what I want?

Since you'll be calling the external function from a context where
Me is meaningful, pass the control reference to your function. Then
test its parent until you get to its parent form:

If TypeOf ctl.parent = Form
ctl.parent.Requery
Else
ctl.parent.parent.Requery
End If

That's air code, so you'll have to change it appropriately to work
in all cases.

This method completely avoids the Screen.Activecontrol issues, which
I have never trusted.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 26 '06 #9

P: n/a
cm*@mountain.net wrote in
news:11**********************@m79g2000cwm.googlegr oups.com:
... but, "requery"?
... I must be way over my head here because I have no idea what I
would be requerying.
I thought the context was requerying the parent form.

I just saw your other post about spell checking. I don't quite
comprehend why you'd want to spell check something in this fashion,
nor do I know if it's possible do to this with a subform.

However, I do know that you should be able to test what type of item
the .Parent is and then conditionally execute different code. The
parent of a control on a subform is the subform control, and you'd
want to find the name of that, and then use it to get a form
reference.

If Me.Parent is subform control and it is named "MySubForm", you'd
refer to it as:

Me.Parent.Parent("MySubForm").Form

If the parent is a tab, you'd use:

Me.Parent.Parent

to return a reference to the form the tab is embeded in.

If the tab is on a subform, you'd use:

Me.Parent.Parent.Parent("MySubForm").Form

Make sense?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 26 '06 #10

P: n/a
cmd

David,
>
I just saw your other post about spell checking. I don't quite
comprehend why you'd want to spell check something in this fashion,
nor do I know if it's possible do to this with a subform.
-- The purpose is to automatically catch spelling errors as the user
moves out of a textbox or memo field. The staff in our office have said
they like this feature, as they often forget to click on a spellcheck
command button or to hit F7.
-- The code I posted initially (and whose creator I've forgotten) has
worked fine on a mainform/subform/textbox layout, but it is not working
on a mainform/subform/tabcontrol/textbox layout.
If Me.Parent is subform control
-- I don't think it is (see below)
and it is named "MySubForm", you'd
refer to it as:

Me.Parent.Parent("MySubForm").Form

If the parent is a tab, you'd use:

Me.Parent.Parent

to return a reference to the form the tab is embeded in.

If the tab is on a subform, you'd use:

Me.Parent.Parent.Parent("MySubForm").Form

Make sense?
-- Sorry, no; I think my brain just short-circuited. With a
mainform/subform/tabcontrol/textbox layout, I seem to get the correct
results if I enter the following in the OnExit event for the textbox:

Dim frm As Form
Set frm = Me.Form
If frm.Dirty Then
frm.Dirty = False
End If

-- the above saves the record on the subform (the tabcontrol is located
on this subform and the textbox is located on the tabcontrol).

-- the following occurs when executed from the OnExit event of the
textbox:

(Debug.Print)

Me.Name = MySubformName
-- I was expecting the name of the textbox from which the event is
being fired, but I guess the textbox is actually two levels down from
"Me" -- Me/TabControl/Textbox

Me.Form.Name = MySubformName

Me.Parent.Name = MyMainformName

Me.Parent.Form.Name = MyMainformName

Screen.ActiveControl.Name = MyTextBoxName

Screen.ActiveControl.Parent.Name = Page 1

Screen.ActiveControl.Parent.Parent.Name = TabCtl163

If I add "Screen.ActiveControl.Parent.SetFocus" to the end of the code
(after acCmdspell), then on exit from the textbox and Spellcheck the
cursor moves to an unbound textbox I placed on the tabcontrol (first in
tab ordering) and the focus is not stuck on the textbox from which the
code is run. Without this, however, there is evidently some loop going
on that keeps the focus on the textbox.

So, if the record is correctly being saved first as specified in the
code, and Screen.ActiveControl is correctly specifiying the textbox in
question, and Spellcheck is correctly checking that textbox, then
what's causing the loop or keeping the focus on that textbox -- why
can't you tab out of, or otherwise escape from that textbox?
Mark

Jul 27 '06 #11

P: n/a
cm*@mountain.net wrote in
news:11*********************@h48g2000cwc.googlegro ups.com:

[]
>If Me.Parent is subform control

-- I don't think it is (see below)
Well, I wasn't describing your situation. I was outlining what
generic code should be doing.

What you need to return is the form reference that can be used to
set focus to the appropriate form so that the Runcommand will be
applied to the right control.
>and it is named "MySubForm", you'd
>refer to it as:

Me.Parent.Parent("MySubForm").Form

If the parent is a tab, you'd use:

Me.Parent.Parent

to return a reference to the form the tab is embeded in.

If the tab is on a subform, you'd use:

Me.Parent.Parent.Parent("MySubForm").Form

Make sense?

-- Sorry, no; I think my brain just short-circuited. With a
mainform/subform/tabcontrol/textbox layout, I seem to get the
correct results if I enter the following in the OnExit event for
the textbox:

Dim frm As Form
Set frm = Me.Form
If frm.Dirty Then
frm.Dirty = False
End If
Me.Dirty works because it's running in the original context, i.e.,
in the code behind the subform.

You can't use the Me operator or the form reference returned by Me
in that context because you're running code outside the form's code
module (the Me actually refers to the code module first, and only
secondarily the object it is attached to), and using it to set focus
to a form that is embedded in a tab in a form control inside another
form. The subform doesn't exist as itself, only as a child object
within the parent form.

So, you need a form reference that fully specifies the context in
which the subform is loaded.
-- the above saves the record on the subform (the tabcontrol is
located on this subform and the textbox is located on the
tabcontrol).

-- the following occurs when executed from the OnExit event of the
textbox:

(Debug.Print)

Me.Name = MySubformName
-- I was expecting the name of the textbox from which the event
is
being fired, but I guess the textbox is actually two levels down
from "Me" -- Me/TabControl/Textbox

Me.Form.Name = MySubformName

Me.Parent.Name = MyMainformName

Me.Parent.Form.Name = MyMainformName

Screen.ActiveControl.Name = MyTextBoxName

Screen.ActiveControl.Parent.Name = Page 1

Screen.ActiveControl.Parent.Parent.Name = TabCtl163
Don't use the Screen object. Pass a control reference to the code
that you call from the events of the control you want to spellcheck.

Better yet, pass the form reference. That way you don't have to make
your spellcheck code smart -- you put all the smarts in the context
in which it is called. It would look something like this if your
control is on a tab control on a subform:

SpellCheck(Me.Parent.Parent.Parent(Me.Parent.Paren t.Name).Form)

Me.Parent.Parent.Name will return th ename of the subform control
that the subform is embedded in.

Me.Parent.Parent.Parent will return a form reference to the parent
form.

Or, you could do it as:

Dim frm As Form

Set frm = Me.Parent.Parent.Parent

SpellCheck(frm(Me.Parent.Parent.Name).Form)

Of course, if the subform is used in only one parent form, you don't
need to do all of this. Just specify the forms by name:

SpellCheck(Forms!ParentForm!Subform.Form)

You only need to use all the Parent.Parent garbage if you need the
code to run in multiple contexts. A subform that is only ever used
in one parent form can have a hardwired form reference.
If I add "Screen.ActiveControl.Parent.SetFocus" to the end of the
code (after acCmdspell), then on exit from the textbox and
Spellcheck the cursor moves to an unbound textbox I placed on the
tabcontrol (first in tab ordering) and the focus is not stuck on
the textbox from which the code is run. Without this, however,
there is evidently some loop going on that keeps the focus on the
textbox.
Activecontrol does not return the correct reference for controls
embedded in a tab, so it's not usable.

Indeed, I would hardly ever recommend using it.
So, if the record is correctly being saved first as specified in
the code, and Screen.ActiveControl is correctly specifiying the
textbox in question, and Spellcheck is correctly checking that
textbox, then what's causing the loop or keeping the focus on that
textbox -- why can't you tab out of, or otherwise escape from that
textbox?
Well, that I don't know. I wouldn't expect the code to run properly
at all.

What event are you running it in?

I would recommend the OnExit event, as you can't use AfterUpdate or
BeforeUpdate.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 27 '06 #12

P: n/a
cmd
Sure appreciate the response, David
I'll take some time to try to fully understand your syntax for
referencing the controls.
As mentioned, this code IS being run from the OnExit event.

Re: Activecontrol does not return the correct reference for controls
embedded in a tab, so it's not usable.
-- in this case it seems to reference the controls correctly, but as I
acknowledged in another post, Access97 Developers' Handbook also does
not recommend using Screen.ActiveControl and I'm certainly willing to
try other ways of referencing.

Re: SpellCheck(Forms!ParentForm!Subform.Form)
-- I'll try this, at least in the way the control is referenced, but
I'm quessing that it'll need to be in the format of other spellchecking
routines that I've seen: e.g,

Dim ctlSpell As Control
Set ctlSpell = Screen.ActiveControl
If TypeOf ctlSpell Is TextBox Then
If IsNull(Len(ctlSpell)) Or Len(ctlSpell) = 0 Then
MsgBox "There is nothing to spell check."
ctlSpell.SetFocus
Exit Sub
End If
With ctlSpell
.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)
End With
DoCmd.RunCommand acCmdSpelling
Else
MsgBox "Spell check is not available for this item."
End If
ctlSpell.SetFocus

Thanks again David,
Mark
David W. Fenton wrote:
cm*@mountain.net wrote in
news:11*********************@h48g2000cwc.googlegro ups.com:

[]
If Me.Parent is subform control
-- I don't think it is (see below)

Well, I wasn't describing your situation. I was outlining what
generic code should be doing.

What you need to return is the form reference that can be used to
set focus to the appropriate form so that the Runcommand will be
applied to the right control.
and it is named "MySubForm", you'd
refer to it as:

Me.Parent.Parent("MySubForm").Form

If the parent is a tab, you'd use:

Me.Parent.Parent

to return a reference to the form the tab is embeded in.

If the tab is on a subform, you'd use:

Me.Parent.Parent.Parent("MySubForm").Form

Make sense?
-- Sorry, no; I think my brain just short-circuited. With a
mainform/subform/tabcontrol/textbox layout, I seem to get the
correct results if I enter the following in the OnExit event for
the textbox:

Dim frm As Form
Set frm = Me.Form
If frm.Dirty Then
frm.Dirty = False
End If

Me.Dirty works because it's running in the original context, i.e.,
in the code behind the subform.

You can't use the Me operator or the form reference returned by Me
in that context because you're running code outside the form's code
module (the Me actually refers to the code module first, and only
secondarily the object it is attached to), and using it to set focus
to a form that is embedded in a tab in a form control inside another
form. The subform doesn't exist as itself, only as a child object
within the parent form.

So, you need a form reference that fully specifies the context in
which the subform is loaded.
-- the above saves the record on the subform (the tabcontrol is
located on this subform and the textbox is located on the
tabcontrol).

-- the following occurs when executed from the OnExit event of the
textbox:

(Debug.Print)

Me.Name = MySubformName
-- I was expecting the name of the textbox from which the event
is
being fired, but I guess the textbox is actually two levels down
from "Me" -- Me/TabControl/Textbox

Me.Form.Name = MySubformName

Me.Parent.Name = MyMainformName

Me.Parent.Form.Name = MyMainformName

Screen.ActiveControl.Name = MyTextBoxName

Screen.ActiveControl.Parent.Name = Page 1

Screen.ActiveControl.Parent.Parent.Name = TabCtl163

Don't use the Screen object. Pass a control reference to the code
that you call from the events of the control you want to spellcheck.

Better yet, pass the form reference. That way you don't have to make
your spellcheck code smart -- you put all the smarts in the context
in which it is called. It would look something like this if your
control is on a tab control on a subform:

SpellCheck(Me.Parent.Parent.Parent(Me.Parent.Paren t.Name).Form)

Me.Parent.Parent.Name will return th ename of the subform control
that the subform is embedded in.

Me.Parent.Parent.Parent will return a form reference to the parent
form.

Or, you could do it as:

Dim frm As Form

Set frm = Me.Parent.Parent.Parent

SpellCheck(frm(Me.Parent.Parent.Name).Form)

Of course, if the subform is used in only one parent form, you don't
need to do all of this. Just specify the forms by name:

SpellCheck(Forms!ParentForm!Subform.Form)

You only need to use all the Parent.Parent garbage if you need the
code to run in multiple contexts. A subform that is only ever used
in one parent form can have a hardwired form reference.
If I add "Screen.ActiveControl.Parent.SetFocus" to the end of the
code (after acCmdspell), then on exit from the textbox and
Spellcheck the cursor moves to an unbound textbox I placed on the
tabcontrol (first in tab ordering) and the focus is not stuck on
the textbox from which the code is run. Without this, however,
there is evidently some loop going on that keeps the focus on the
textbox.

Activecontrol does not return the correct reference for controls
embedded in a tab, so it's not usable.

Indeed, I would hardly ever recommend using it.
So, if the record is correctly being saved first as specified in
the code, and Screen.ActiveControl is correctly specifiying the
textbox in question, and Spellcheck is correctly checking that
textbox, then what's causing the loop or keeping the focus on that
textbox -- why can't you tab out of, or otherwise escape from that
textbox?

Well, that I don't know. I wouldn't expect the code to run properly
at all.

What event are you running it in?

I would recommend the OnExit event, as you can't use AfterUpdate or
BeforeUpdate.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 27 '06 #13

P: n/a
cm*@mountain.net wrote in
news:11**********************@i42g2000cwa.googlegr oups.com:
-- I'll try this, at least in the way the control is referenced,
but I'm quessing that it'll need to be in the format of other
spellchecking routines that I've seen
I believe that you can't set focus directly to a control until the
form it is on has the focus.

So, you'd just build your spellchecking routine to take two
arguments, the parent form and the control.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 28 '06 #14

P: n/a
cmd
David,
I had to adjust some of the reference syntax you suggested:
"Set frm = Me.Parent.Parent.Parent ", for example, took me so far up
the hierarchy that Me.Parent.Parent.Parent.Name gave me the name of a
file lying on my desk. (Actually, just error 2452).

But, I learned that "Me" is going to refer to the active form (I
think), or the form immediately above a textbox control. For example,
Me.Name when executed from an event for a textbox control will give the
name of the form that the textbox is located on. Me.Parent.Name when
used for that same textbox will give the name of the next higher-up
form. (I had always thought that "Me" referred to the actual control
that had the focus -- i.e., where the cursor is.)

Regarding the use of ActiveControl, I can find no way to refer to the
active textbox control when using a generic procedure without using
ActiveControl -- e.g., Me.ActiveControl -- that is, unless I want to
refer to the textbox control by its actual name -- and then the
procedure or function loses its generality. Also, regarding the use of
ActiveControl on tabcontrols, I did not run into any problem -- this
consistently provided the correct results when I used it in different
textboxes on a tabcontrol (e.g., Me.ActiveControl.Name, or
Me.ActiveControl = "Hello").

I discovered that the couple of Spellcheck routines I've tried all work
fine when run from the OnExit event of a textbox control and at any
level of nested subforms. They plain do not work, however, for a
textbox located on a tabcontrol, regardless of the level of that
tabcontrol. Spellcheck is generated and will correctly adjust spelling
errors, but the focus remains locked on that textbox control -- unless
you click on the form itself; then you get a run-time error. Maybe it's
something internal to the Spellcheck routine and which is not
accessible to VBA programming?

I've begun to think that I need to redesign my layout to exclude the
use of a tabcontrol -- that is, if I still want to generate Spellcheck
when the user tabs out of a field. Perhaps, a mainform with buttons
across the form header (page1, etc.) that open and close subforms, with
each subform containing a portion of the fields from the relevant
table. (The mainform is based on a parent table; the subforms are based
on a child table that has a 1:1 relationship with the parent table).

If you have any other suggestions regarding how to make spellcheck
routines work on tabcontrols, please let me know. And, thank you for
your previous suggestions.

Mark

David W. Fenton wrote:
cm*@mountain.net wrote in
news:11**********************@i42g2000cwa.googlegr oups.com:
-- I'll try this, at least in the way the control is referenced,
but I'm quessing that it'll need to be in the format of other
spellchecking routines that I've seen

I believe that you can't set focus directly to a control until the
form it is on has the focus.

So, you'd just build your spellchecking routine to take two
arguments, the parent form and the control.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 28 '06 #15

P: n/a
cm*@mountain.net wrote in
news:11**********************@75g2000cwc.googlegro ups.com:
I had to adjust some of the reference syntax you suggested:
"Set frm = Me.Parent.Parent.Parent ", for example, took me so far
up the hierarchy that Me.Parent.Parent.Parent.Name gave me the
name of a file lying on my desk. (Actually, just error 2452).
Of course -- I was thinking ActiveControl.Parent. Perhaps.

Or a subform in a tab control.
But, I learned that "Me" is going to refer to the active form (I
think), or the form immediately above a textbox control. . . .
In a certain sense, Me actually refers to the *code module* that the
code is in.
. . . For example,
Me.Name when executed from an event for a textbox control will
give the name of the form that the textbox is located on.
Me.Parent.Name when used for that same textbox will give the name
of the next higher-up form. (I had always thought that "Me"
referred to the actual control that had the focus -- i.e., where
the cursor is.)
If the subform is embedded in a tab control, it will *not* give you
the parent form, but the tab control.
Regarding the use of ActiveControl, I can find no way to refer to
the active textbox control when using a generic procedure without
using ActiveControl -- e.g., Me.ActiveControl -- that is, unless I
want to refer to the textbox control by its actual name -- and
then the procedure or function loses its generality. Also,
regarding the use of ActiveControl on tabcontrols, I did not run
into any problem -- this consistently provided the correct results
when I used it in different textboxes on a tabcontrol (e.g.,
Me.ActiveControl.Name, or Me.ActiveControl = "Hello").
I think you've missed the whole point of my previous post.

Make your generic code something like this:

Public Sub SpellCheck(frm As Form, ctl As Control)

Then in the calling context, you can know exactly how to return a
proper form reference, as well as using the control reference to
figure out the name.

If you passed:

SpellCheck Me.Parent!MySubform.form, Me!MyTextBox

you could then use that in the SpellCheck routine to do:

frm(ctl.Name).SetFocus

Of course, that will only work when the form already has the focus
itself.
I discovered that the couple of Spellcheck routines I've tried all
work fine when run from the OnExit event of a textbox control and
at any level of nested subforms. They plain do not work, however,
for a textbox located on a tabcontrol, regardless of the level of
that tabcontrol. . . .
It could be made to work if you'd work out the TypeOf of
Screen.ActiveControl.Parent.
. . . Spellcheck is generated and will correctly adjust spelling
errors, but the focus remains locked on that textbox control --
unless you click on the form itself; then you get a run-time
error. Maybe it's something internal to the Spellcheck routine and
which is not accessible to VBA programming?
I don't know. I'd never contemplate using ActiveControl for this
kind of thing. Indeed, I think I'd never contemplate doing this kind
of thing. If somebody wants to spellcheck they can hit F7.

One of the problems with your approach would be an endless loop,
seems to me. If in the OnExit event you evoke the SpellCheck
routine, that subroutine is going to set focus back to the control
that was just left. How do you break out of that loop?
I've begun to think that I need to redesign my layout to exclude
the use of a tabcontrol -- that is, if I still want to generate
Spellcheck when the user tabs out of a field. . . .
Have you considered using the GotFocus event of the next control to
launch a spellcheck of the control before it?

Or, maintaining a history of which controls are visited?

Another option might be to use the AfterUpdate event and a flag that
keeps track of whether or not the spelling has been checked. That
would eliminate an endless loop and allow you to skip the step of
resetting the focus. All you'd need, I'd think, is something like
this:

Static bolSpellingHasBeenChecked As Boolean

If Not bolSpellingHasBeenChecked Then
DoCmd.RunCommand acCmdSpelling
bolSpellingHasBeenChecked = True
End If

Now, I'm not entirely certain that will work -- I'm not sure the
logic is correct. Also, a Static variable may not be the best
approach -- a module-level variable may be better. But you'd have to
set it to False in the OnEnter event of the control.

This eliminates any need for setting focus or anything like that,
since it will be running while the control still has the focus. The
flag variable takes care of getting you out of an endless loop.

You might also want to use an AutoKeys macro to map F7 to run the
spell check and then set the variable to true. That way, if the user
manually invokes the spell checker with F7, it won't run twice. That
doesn't prohibit the user from invoking the spell checker from the
toolbar, but you could also edit the standard toolbar button to run
code that sets the variable, or simply disable the spellcheck button
in fields that are automatically spellchecked.
. . . Perhaps, a mainform with buttons
across the form header (page1, etc.) that open and close subforms,
with each subform containing a portion of the fields from the
relevant table. (The mainform is based on a parent table; the
subforms are based on a child table that has a 1:1 relationship
with the parent table).

If you have any other suggestions regarding how to make spellcheck
routines work on tabcontrols, please let me know. And, thank you
for your previous suggestions.
I think the best answer is to get the code to execute when the focus
has not left the control you're checking. The above should be a
start.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 28 '06 #16

P: n/a
cmd
Thanks David,
I sure appreciate the effort you put into the response. It'll take me a
while to assimilate all this, but I'll certainly give your suggestions
a try.
Mark

David W. Fenton wrote:
cm*@mountain.net wrote in
news:11**********************@75g2000cwc.googlegro ups.com:
I had to adjust some of the reference syntax you suggested:
"Set frm = Me.Parent.Parent.Parent ", for example, took me so far
up the hierarchy that Me.Parent.Parent.Parent.Name gave me the
name of a file lying on my desk. (Actually, just error 2452).

Of course -- I was thinking ActiveControl.Parent. Perhaps.

Or a subform in a tab control.
But, I learned that "Me" is going to refer to the active form (I
think), or the form immediately above a textbox control. . . .

In a certain sense, Me actually refers to the *code module* that the
code is in.
. . . For example,
Me.Name when executed from an event for a textbox control will
give the name of the form that the textbox is located on.
Me.Parent.Name when used for that same textbox will give the name
of the next higher-up form. (I had always thought that "Me"
referred to the actual control that had the focus -- i.e., where
the cursor is.)

If the subform is embedded in a tab control, it will *not* give you
the parent form, but the tab control.
Regarding the use of ActiveControl, I can find no way to refer to
the active textbox control when using a generic procedure without
using ActiveControl -- e.g., Me.ActiveControl -- that is, unless I
want to refer to the textbox control by its actual name -- and
then the procedure or function loses its generality. Also,
regarding the use of ActiveControl on tabcontrols, I did not run
into any problem -- this consistently provided the correct results
when I used it in different textboxes on a tabcontrol (e.g.,
Me.ActiveControl.Name, or Me.ActiveControl = "Hello").

I think you've missed the whole point of my previous post.

Make your generic code something like this:

Public Sub SpellCheck(frm As Form, ctl As Control)

Then in the calling context, you can know exactly how to return a
proper form reference, as well as using the control reference to
figure out the name.

If you passed:

SpellCheck Me.Parent!MySubform.form, Me!MyTextBox

you could then use that in the SpellCheck routine to do:

frm(ctl.Name).SetFocus

Of course, that will only work when the form already has the focus
itself.
I discovered that the couple of Spellcheck routines I've tried all
work fine when run from the OnExit event of a textbox control and
at any level of nested subforms. They plain do not work, however,
for a textbox located on a tabcontrol, regardless of the level of
that tabcontrol. . . .

It could be made to work if you'd work out the TypeOf of
Screen.ActiveControl.Parent.
. . . Spellcheck is generated and will correctly adjust spelling
errors, but the focus remains locked on that textbox control --
unless you click on the form itself; then you get a run-time
error. Maybe it's something internal to the Spellcheck routine and
which is not accessible to VBA programming?

I don't know. I'd never contemplate using ActiveControl for this
kind of thing. Indeed, I think I'd never contemplate doing this kind
of thing. If somebody wants to spellcheck they can hit F7.

One of the problems with your approach would be an endless loop,
seems to me. If in the OnExit event you evoke the SpellCheck
routine, that subroutine is going to set focus back to the control
that was just left. How do you break out of that loop?
I've begun to think that I need to redesign my layout to exclude
the use of a tabcontrol -- that is, if I still want to generate
Spellcheck when the user tabs out of a field. . . .

Have you considered using the GotFocus event of the next control to
launch a spellcheck of the control before it?

Or, maintaining a history of which controls are visited?

Another option might be to use the AfterUpdate event and a flag that
keeps track of whether or not the spelling has been checked. That
would eliminate an endless loop and allow you to skip the step of
resetting the focus. All you'd need, I'd think, is something like
this:

Static bolSpellingHasBeenChecked As Boolean

If Not bolSpellingHasBeenChecked Then
DoCmd.RunCommand acCmdSpelling
bolSpellingHasBeenChecked = True
End If

Now, I'm not entirely certain that will work -- I'm not sure the
logic is correct. Also, a Static variable may not be the best
approach -- a module-level variable may be better. But you'd have to
set it to False in the OnEnter event of the control.

This eliminates any need for setting focus or anything like that,
since it will be running while the control still has the focus. The
flag variable takes care of getting you out of an endless loop.

You might also want to use an AutoKeys macro to map F7 to run the
spell check and then set the variable to true. That way, if the user
manually invokes the spell checker with F7, it won't run twice. That
doesn't prohibit the user from invoking the spell checker from the
toolbar, but you could also edit the standard toolbar button to run
code that sets the variable, or simply disable the spellcheck button
in fields that are automatically spellchecked.
. . . Perhaps, a mainform with buttons
across the form header (page1, etc.) that open and close subforms,
with each subform containing a portion of the fields from the
relevant table. (The mainform is based on a parent table; the
subforms are based on a child table that has a 1:1 relationship
with the parent table).

If you have any other suggestions regarding how to make spellcheck
routines work on tabcontrols, please let me know. And, thank you
for your previous suggestions.

I think the best answer is to get the code to execute when the focus
has not left the control you're checking. The above should be a
start.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 28 '06 #17

P: n/a
cmd
David,
Just a follow-up ...
(my previous post)
>Me.Name when executed from an event for a textbox control will
give the name of the form that the textbox is located on.
Me.Parent.Name when used for that same textbox will give the name
of the next higher-up form. (I had always thought that "Me"
referred to the actual control that had the focus -- i.e., where
the cursor is.)
(your response)
"If the subform is embedded in a tab control, it will *not* give you
the parent form, but the tab control."

-- This did not happen when I tried it.
In the case of MyMainForm/MyTabcontrol/MySubform/MyTextbox, Me.Name in
one of the events for the textbox produces "MySubform", Me.Parent.Name
produces "MyMainForm". On the other hand, Me.ActiveControl.Name when
used in that textbox produces "Page1", Me.ActiveControl.Parent.Name
produces "MyTabcontrol".

Mark

Aug 2 '06 #18

P: n/a
cm*@mountain.net wrote in
news:11**********************@m73g2000cwd.googlegr oups.com:
David,
Just a follow-up ...
(my previous post)
>>Me.Name when executed from an event for a textbox control will
give the name of the form that the textbox is located on.
Me.Parent.Name when used for that same textbox will give the
name of the next higher-up form. (I had always thought that "Me"
referred to the actual control that had the focus -- i.e., where
the cursor is.)

(your response)
"If the subform is embedded in a tab control, it will *not* give
you the parent form, but the tab control."

-- This did not happen when I tried it.
In the case of MyMainForm/MyTabcontrol/MySubform/MyTextbox,
Me.Name in one of the events for the textbox produces "MySubform",
Me.Parent.Name produces "MyMainForm". . . .
OK, that makes it easier, except that it actually doesn't, as you
need the name of the subform control in order to be able to set
focus to it.
. . . On the other hand, Me.ActiveControl.Name when
used in that textbox produces "Page1",
Me.ActiveControl.Parent.Name produces "MyTabcontrol".
I always, at every point, advised *against* using
Screen.ActiveControl.

And the advice that I gave about using a flag and the AfterUpdate
event gets rid of the problem entirely, since you run the spellcheck
before leaving the textbox control.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 2 '06 #19

P: n/a
cmd

David,
-- Not to beat a dead horse, but in a *generic* proc (i.e., without
knowing the actual name of the control) how would you refer to the
current control *without* using Screen.ActiveControl, or
Me.ActiveControl?

Yes, practically speaking, a flag does the job; but, I was trying to
puzzle this out just by using references involving "Me".

cm*@mountain.net wrote in
news:11**********************@m73g2000cwd.googlegr oups.com:
David,
Just a follow-up ...
(my previous post)
>Me.Name when executed from an event for a textbox control will
give the name of the form that the textbox is located on.
Me.Parent.Name when used for that same textbox will give the
name of the next higher-up form. (I had always thought that "Me"
referred to the actual control that had the focus -- i.e., where
the cursor is.)
(your response)
"If the subform is embedded in a tab control, it will *not* give
you the parent form, but the tab control."

-- This did not happen when I tried it.
In the case of MyMainForm/MyTabcontrol/MySubform/MyTextbox,
Me.Name in one of the events for the textbox produces "MySubform",
Me.Parent.Name produces "MyMainForm". . . .

OK, that makes it easier, except that it actually doesn't, as you
need the name of the subform control in order to be able to set
focus to it.
. . . On the other hand, Me.ActiveControl.Name when
used in that textbox produces "Page1",
Me.ActiveControl.Parent.Name produces "MyTabcontrol".

I always, at every point, advised *against* using
Screen.ActiveControl.

And the advice that I gave about using a flag and the AfterUpdate
event gets rid of the problem entirely, since you run the spellcheck
before leaving the textbox control.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 2 '06 #20

P: n/a
cm*@mountain.net wrote in
news:11**********************@m73g2000cwd.googlegr oups.com:
-- Not to beat a dead horse, but in a *generic* proc (i.e.,
without knowing the actual name of the control) how would you
refer to the current control *without* using
Screen.ActiveControl, or Me.ActiveControl?
I don't think you can do it reliably.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 2 '06 #21

P: n/a
<cm*@mountain.netwrote
-- Not to beat a dead horse, but in a
*generic* proc (i.e., without knowing
the actual name of the control) how
would you refer to the current control
*without* using Screen.ActiveControl,
or Me.ActiveControl?
ActiveControl _is_ the built-in variable that identifies the "active
control" for use in "generic" situations. If you are calling the generic
code from the Event property of a given control, you could pass the name of
the Control with the function call (but that seems a lot of work to me, just
to avoid ActiveControl, which has never given me any trouble). Have you had
difficulties using ActiveControl?
Yes, practically speaking, a flag does
the job; but, I was trying to puzzle this
out just by using references involving
"Me".
"Me" identifies the current object (Form or Report), only in the current
object's code module. It does not identify the Control, and, AFAIK, can't
be "extended" or altered to refer to the Control.

Larry Linson
Microsoft Access MVP
Aug 3 '06 #22

This discussion thread is closed

Replies have been disabled for this discussion.