Current Event

[Guest]

I have a parent/master form with several subforms (under tabs). When I
change a check box in one subform, I want the controls in another subform to
show or hide according to program code that looks something like this:

If Forms![HSPCA]![PetInfo].Form!PetType = "Dog" Then
Me.HWT.Visible = True
Me.CatFIVFeleukCombo.Visible = False
End If

Some of you who assisted me before (see 2/8 entries) advised me to put this
type of code in the Current event of the form with the controls that I want
to show/hide. I did this and it works...once. The Current event--as I
understand it--works when you first open a subform or when you have gone from
one record to another but, after that, if you change a checkbox in one
subform and then go to another subform the Current event does not trigger.
I've read and reread the online guidance (including 'Order of events for
database objects') and it seems to state that one can trigger the Current
event with a requery or refresh statement. I tried the refresh (Me.refresh)
and it didn't seem to do anything when located either in the Current event or
when placed in the OnFocus of a form control. The requery (Me.Requery)
'does' work but there's about a 5 second delay and a lot of 'jiggling' as,
apparently, the database requeries 2,000 or so records. The Activate event
looked like a likely contender but it apparently doesn't work in a subform.
I tried docmd.gotocontrol "<control name>" but that didn't do the trick
either. Once in the form that I want to show/hide controls on, if I simply
tab from control to control it will, at some point, trigger the Current event
changes that I wanted to see when I clicked on the subform's tab. Yep, I've
spent the better part of today experimenting....

My question, then, is: Is there a way other than the Requery method to force
the
Current event to take place? Alternatively, is there a better approach that
can be suggested (e.g., programmatically move forward one record and back to
trigger the Current event)?

Thanks in advance...for your patience and insight.

 

[Nikos Yannacopoulos]

Scott,

Trying to make the Current event fire is not the way to go! What you
actually need is to repeat the code in the Change event of the checkbox.
In a more "by the book" design you would put the code in a separate
private sub in the form's module, and call it from both events... but
even just repeating it in both will do the job (though ot a good idea if
it were 50 or 500 lines instead of four!).

HTH,
Nikos

 

[Guest]

Many, many thanks for such a prompt response (I just typed the question late
last night!). I'm anxious to give this a try and will let you know what
happens. Again, thank you!

 

[Guest]

I endeavored to follow your directions but found that (to my surprise) the
check box does not have an On Change event associated with it. As an
alternative, I went to the 'other' form's text box and wrote code it it that
looks something like this:

If Me.PetType = "Dog" Then
Forms![HSPCA]![Services].Form!HWT.Visible = True
End If
If Me.PetType = "Cat" Then
Forms![HSPCA]![Services].Form!HWT.Visible = False
End If

(The HWT control's visible property is set to No).

....but when I switched to the form (Services) that I wanted the hide/show
changes to appear in there was no associated change. I'll keep playing
around with this but in the meantime any other suggestions would be welcomed.

 

[Guest]

Dear Scott

I use the On Lost Focus Event from the Check Box which should alter the
presence of the fields

Marcel

I endeavored to follow your directions but found that (to my surprise) the
check box does not have an On Change event associated with it. As an
alternative, I went to the 'other' form's text box and wrote code it it that
looks something like this:

If Me.PetType = "Dog" Then
Forms![HSPCA]![Services].Form!HWT.Visible = True
End If
If Me.PetType = "Cat" Then
Forms![HSPCA]![Services].Form!HWT.Visible = False
End If

(The HWT control's visible property is set to No).

...but when I switched to the form (Services) that I wanted the hide/show
changes to appear in there was no associated change. I'll keep playing
around with this but in the meantime any other suggestions would be welcomed.

--
SHB

Scott,

Trying to make the Current event fire is not the way to go! What you
actually need is to repeat the code in the Change event of the checkbox.
In a more "by the book" design you would put the code in a separate
private sub in the form's module, and call it from both events... but
even just repeating it in both will do the job (though ot a good idea if
it were 50 or 500 lines instead of four!).

HTH,
Nikos

 

[Nikos Yannacopoulos]

Scott,

Sorry about this, you're absolutely right, no Change event; what was I
thinking? For a checkbox use the Click event, even though the name
suggests response to a mouse action, it fires even when changed through
the keyboard, so it will do the job.

Regards,
Nikos

 

[Marshall Barton]

I endeavored to follow your directions but found that (to my surprise) the
check box does not have an On Change event associated with it. As an
alternative, I went to the 'other' form's text box and wrote code it it that
looks something like this:

If Me.PetType = "Dog" Then
Forms![HSPCA]![Services].Form!HWT.Visible = True
End If
If Me.PetType = "Cat" Then
Forms![HSPCA]![Services].Form!HWT.Visible = False
End If

(The HWT control's visible property is set to No).

...but when I switched to the form (Services) that I wanted the hide/show
changes to appear in there was no associated change. I'll keep playing
around with this but in the meantime any other suggestions would be welcomed.

For consistency with other controls (Text Box, Combo Box,
etc), you should use the AfterUpdate event, which only fires
when the control's value is updated. For a check box, the
Click event will cause the value to change so it's safe,
just different from the other controls.

Don't forget that you need to do the same thing in the
form's Current event.

What do you expect to happen when PetType = "Bird"?

If you only want HWT to be visible when PetType = "Dog"
(regardless of any other value PetType may have), then your
code can be shortened to just one line:

Forms![HSPCA]![Services].Form!HWT.Visible = _
(Me.PetType = "Dog")

 

[Guest]

My assumption is that I'm not quite following what is undoubtedly good
advice. Can one of you confirm that I'm trying to put the right code into
the right places... -->

SubformA
Dropdown text box 'PetType' giving the choice between Dog and Cat

SubformB
Checkbox 'HWT' that should be visible when PetType = "Dog" and not
visible when = "Cat"

I have put code in the On Current event of SubformB that reads, in part:

If Forms![<ParentFormName>]![SubformA].Form!PetType = "Dog" Then
Me.HWT.Visible = True
End If

In SubformB's HWT checkbox control I have put the following code in the
After Update event:

Me.HWT.Visible = (Forms![<ParentFormName>]![SubformA].Form!PetType =
"Dog")

When I go to SubformA and change Dog to Cat or viceversa I then want to
click on the SubformB tab and see the associated controls (only). As far as
I can tell, the combination of On Current and After Update still are not
doing the trick (I have also tried On Click instead of After Update). I have
put breakpoints in SubformB's control code to see if it even goes there and
it doesn't appear to...which would make since if that particular control has
not been changed in any way.

I'm not sure if it makes a difference but SubformA is associated with TableA
and SubformB is associated (bound to) TableB and, in table relationships,
TableA is related to TableB (TableA has the primary key, autonumber ID and
TableB has a long integer ID number). Each are under a different tab and the
association seems to be working OK in the forms.

If I go much further this might turn into a dissertation so I guess I'd best
stop for now. Any thoughts? The person that gets the correct answer is good
for a free dinner on their next trip through Columbia, SC. Scott

--
SHB

I endeavored to follow your directions but found that (to my surprise) the
check box does not have an On Change event associated with it. As an
alternative, I went to the 'other' form's text box and wrote code it it that
looks something like this:

If Me.PetType = "Dog" Then
Forms![HSPCA]![Services].Form!HWT.Visible = True
End If
If Me.PetType = "Cat" Then
Forms![HSPCA]![Services].Form!HWT.Visible = False
End If

(The HWT control's visible property is set to No).

...but when I switched to the form (Services) that I wanted the hide/show
changes to appear in there was no associated change. I'll keep playing
around with this but in the meantime any other suggestions would be welcomed.

For consistency with other controls (Text Box, Combo Box,
etc), you should use the AfterUpdate event, which only fires
when the control's value is updated. For a check box, the
Click event will cause the value to change so it's safe,
just different from the other controls.

Don't forget that you need to do the same thing in the
form's Current event.

What do you expect to happen when PetType = "Bird"?

If you only want HWT to be visible when PetType = "Dog"
(regardless of any other value PetType may have), then your
code can be shortened to just one line:

Forms![HSPCA]![Services].Form!HWT.Visible = _
(Me.PetType = "Dog")

 

[Guest]

As an addendum to my previous message and to provide some general feedback to
anyone that has been following this dialogue...

In an effort to understand what triggers what when, I
a) put a debug.print <EventName> (e.g., debug.print "OnClick") in 'every'
event of one of the SubformB, bound controls.
b) put a debug.print <EventName> (e.g., debug.print "OnCurrent") in 'every'
event of SubformB itself.
I then switched back-and-forth between SubformA and SubformB, changing 'Dog'
to 'Cat' (or 'Cat' to 'Dog') each time that I passed through SubformA.
After this little experiment, I pressed Ctrl+G to look at the code and found
that the only debug.print messages that appeared related to when the subforms
were first loaded...nothing after that. From this, I have to conclude that
the strategy I had hoped to pursue simply won't work...there doesn't seem to
be an event triggered when one moves between subforms that can be used to
hide/show the controls.

In the above context, and barring any demonstration to the contrary, I
suspect that unbound controls have to be used?

--
SHB

My assumption is that I'm not quite following what is undoubtedly good
advice. Can one of you confirm that I'm trying to put the right code into
the right places... -->

SubformA
Dropdown text box 'PetType' giving the choice between Dog and Cat

SubformB
Checkbox 'HWT' that should be visible when PetType = "Dog" and not
visible when = "Cat"

I have put code in the On Current event of SubformB that reads, in part:

If Forms![<ParentFormName>]![SubformA].Form!PetType = "Dog" Then
Me.HWT.Visible = True
End If

In SubformB's HWT checkbox control I have put the following code in the
After Update event:

Me.HWT.Visible = (Forms![<ParentFormName>]![SubformA].Form!PetType =
"Dog")

When I go to SubformA and change Dog to Cat or viceversa I then want to
click on the SubformB tab and see the associated controls (only). As far as
I can tell, the combination of On Current and After Update still are not
doing the trick (I have also tried On Click instead of After Update). I have
put breakpoints in SubformB's control code to see if it even goes there and
it doesn't appear to...which would make since if that particular control has
not been changed in any way.

I'm not sure if it makes a difference but SubformA is associated with TableA
and SubformB is associated (bound to) TableB and, in table relationships,
TableA is related to TableB (TableA has the primary key, autonumber ID and
TableB has a long integer ID number). Each are under a different tab and the
association seems to be working OK in the forms.

If I go much further this might turn into a dissertation so I guess I'd best
stop for now. Any thoughts? The person that gets the correct answer is good
for a free dinner on their next trip through Columbia, SC. Scott

--
SHB

I endeavored to follow your directions but found that (to my surprise) the
check box does not have an On Change event associated with it. As an
alternative, I went to the 'other' form's text box and wrote code it it that
looks something like this:

If Me.PetType = "Dog" Then
Forms![HSPCA]![Services].Form!HWT.Visible = True
End If
If Me.PetType = "Cat" Then
Forms![HSPCA]![Services].Form!HWT.Visible = False
End If

(The HWT control's visible property is set to No).

...but when I switched to the form (Services) that I wanted the hide/show
changes to appear in there was no associated change. I'll keep playing
around with this but in the meantime any other suggestions would be welcomed.

For consistency with other controls (Text Box, Combo Box,
etc), you should use the AfterUpdate event, which only fires
when the control's value is updated. For a check box, the
Click event will cause the value to change so it's safe,
just different from the other controls.

Don't forget that you need to do the same thing in the
form's Current event.

What do you expect to happen when PetType = "Bird"?

If you only want HWT to be visible when PetType = "Dog"
(regardless of any other value PetType may have), then your
code can be shortened to just one line:

Forms![HSPCA]![Services].Form!HWT.Visible = _
(Me.PetType = "Dog")

 

[Guest]

Ah…success at last! For those of you who have been following this like a
good novel, allow me to tell you what I did (as always, any feedback on a
better approach would be appreciated).

1. In SubformB (the hide/show controls subform) I added an unbound textbox
(txtPetType). For the Control Source I typed in
“=Forms![ParentFormName]![SubformB].form!PetTypeâ€.
2. The first control in Set Tab Order is called ‘SurgeryDate’. In the On
Got Focus event of that control I put code that looked something like this:

Private Sub SurgeryDate_GotFocus()
Dim flag As Integer
Dim strPetType As String
strPetType = Me.txtPetType
Me.Refresh ‘txtPetType doesn’t change unless the page is refreshed
If Me.txtPetType <> strPetType Then 'there was a change in SubformA
If Forms![ParentFormName]![SubformA].Form!PetType =
"Dog" Then
Me.HWT.Visible = True
End If
End If
Me.Refresh ‘Need another refresh to make HWT visible
End Sub

That seemed to do the trick!

--
SHB

As an addendum to my previous message and to provide some general feedback to
anyone that has been following this dialogue...

In an effort to understand what triggers what when, I
a) put a debug.print <EventName> (e.g., debug.print "OnClick") in 'every'
event of one of the SubformB, bound controls.
b) put a debug.print <EventName> (e.g., debug.print "OnCurrent") in 'every'
event of SubformB itself.
I then switched back-and-forth between SubformA and SubformB, changing 'Dog'
to 'Cat' (or 'Cat' to 'Dog') each time that I passed through SubformA.
After this little experiment, I pressed Ctrl+G to look at the code and found
that the only debug.print messages that appeared related to when the subforms
were first loaded...nothing after that. From this, I have to conclude that
the strategy I had hoped to pursue simply won't work...there doesn't seem to
be an event triggered when one moves between subforms that can be used to
hide/show the controls.

In the above context, and barring any demonstration to the contrary, I
suspect that unbound controls have to be used?

--
SHB

My assumption is that I'm not quite following what is undoubtedly good
advice. Can one of you confirm that I'm trying to put the right code into
the right places... -->

SubformA
Dropdown text box 'PetType' giving the choice between Dog and Cat

SubformB
Checkbox 'HWT' that should be visible when PetType = "Dog" and not
visible when = "Cat"

I have put code in the On Current event of SubformB that reads, in part:

If Forms![<ParentFormName>]![SubformA].Form!PetType = "Dog" Then
Me.HWT.Visible = True
End If

In SubformB's HWT checkbox control I have put the following code in the
After Update event:

Me.HWT.Visible = (Forms![<ParentFormName>]![SubformA].Form!PetType =
"Dog")

When I go to SubformA and change Dog to Cat or viceversa I then want to
click on the SubformB tab and see the associated controls (only). As far as
I can tell, the combination of On Current and After Update still are not
doing the trick (I have also tried On Click instead of After Update). I have
put breakpoints in SubformB's control code to see if it even goes there and
it doesn't appear to...which would make since if that particular control has
not been changed in any way.

I'm not sure if it makes a difference but SubformA is associated with TableA
and SubformB is associated (bound to) TableB and, in table relationships,
TableA is related to TableB (TableA has the primary key, autonumber ID and
TableB has a long integer ID number). Each are under a different tab and the
association seems to be working OK in the forms.

If I go much further this might turn into a dissertation so I guess I'd best
stop for now. Any thoughts? The person that gets the correct answer is good
for a free dinner on their next trip through Columbia, SC. Scott

--
SHB

Scott wrote:

I endeavored to follow your directions but found that (to my surprise) the
check box does not have an On Change event associated with it. As an
alternative, I went to the 'other' form's text box and wrote code it it that
looks something like this:

If Me.PetType = "Dog" Then
Forms![HSPCA]![Services].Form!HWT.Visible = True
End If
If Me.PetType = "Cat" Then
Forms![HSPCA]![Services].Form!HWT.Visible = False
End If

(The HWT control's visible property is set to No).

...but when I switched to the form (Services) that I wanted the hide/show
changes to appear in there was no associated change. I'll keep playing
around with this but in the meantime any other suggestions would be welcomed.


For consistency with other controls (Text Box, Combo Box,
etc), you should use the AfterUpdate event, which only fires
when the control's value is updated. For a check box, the
Click event will cause the value to change so it's safe,
just different from the other controls.

Don't forget that you need to do the same thing in the
form's Current event.

What do you expect to happen when PetType = "Bird"?

If you only want HWT to be visible when PetType = "Dog"
(regardless of any other value PetType may have), then your
code can be shortened to just one line:

Forms![HSPCA]![Services].Form!HWT.Visible = _
(Me.PetType = "Dog")

 

[Marshall Barton]

Ah…success at last! For those of you who have been following this like a
good novel, allow me to tell you what I did (as always, any feedback on a
better approach would be appreciated).

1. In SubformB (the hide/show controls subform) I added an unbound textbox
(txtPetType). For the Control Source I typed in
“=Forms![ParentFormName]![SubformB].form!PetType”.
2. The first control in Set Tab Order is called ‘SurgeryDate’. In the On
Got Focus event of that control I put code that looked something like this:

Private Sub SurgeryDate_GotFocus()
Dim flag As Integer
Dim strPetType As String
strPetType = Me.txtPetType
Me.Refresh ‘txtPetType doesn’t change unless the page is refreshed
If Me.txtPetType <> strPetType Then 'there was a change in SubformA
If Forms![ParentFormName]![SubformA].Form!PetType =
"Dog" Then
Me.HWT.Visible = True
End If
End If
Me.Refresh ‘Need another refresh to make HWT visible
End Sub

That may work in some cases, but it's awfully convoluted.

When I said to use the AfterUpdate (and Current) event, I
should have stated that it was subformA's Dog/Cat combo
box's AfterUpdate event and subformA's Current event.
(these abstract form names are difficult for me to keep
straight). This way, whenever the combo box selection is
changed, subformB's HWT's visible property is set
accordingly. Similarly, when subformA changes to a
different record (with a potentially different value in the
PetType combo box), the Current event will set HWT's
visibility to agree with the newly current record.

The line of code in subformA will be something like:

Me.Parent.subformB.Form.Hwt.Visible = (Me.PetType = "Dog")

Be sure to replace subformB with the name of the subform
**control** on the main form.

 

[Guest]

Marsh, I'm indebted...thanks for the additional feedback (it's starting to
click).
--
SHB

Ah…success at last! For those of you who have been following this like a
good novel, allow me to tell you what I did (as always, any feedback on a
better approach would be appreciated).

1. In SubformB (the hide/show controls subform) I added an unbound textbox
(txtPetType). For the Control Source I typed in
“=Forms![ParentFormName]![SubformB].form!PetTypeâ€.
2. The first control in Set Tab Order is called ‘SurgeryDate’. In the On
Got Focus event of that control I put code that looked something like this:

Private Sub SurgeryDate_GotFocus()
Dim flag As Integer
Dim strPetType As String
strPetType = Me.txtPetType
Me.Refresh ‘txtPetType doesn’t change unless the page is refreshed
If Me.txtPetType <> strPetType Then 'there was a change in SubformA
If Forms![ParentFormName]![SubformA].Form!PetType =
"Dog" Then
Me.HWT.Visible = True
End If
End If
Me.Refresh ‘Need another refresh to make HWT visible
End Sub

That may work in some cases, but it's awfully convoluted.

When I said to use the AfterUpdate (and Current) event, I
should have stated that it was subformA's Dog/Cat combo
box's AfterUpdate event and subformA's Current event.
(these abstract form names are difficult for me to keep
straight). This way, whenever the combo box selection is
changed, subformB's HWT's visible property is set
accordingly. Similarly, when subformA changes to a
different record (with a potentially different value in the
PetType combo box), the Current event will set HWT's
visibility to agree with the newly current record.

The line of code in subformA will be something like:

Me.Parent.subformB.Form.Hwt.Visible = (Me.PetType = "Dog")

Be sure to replace subformB with the name of the subform
**control** on the main form.

 

[Guest]

For the record, it did work!!!!!!! Thanks again Marsh.
--
SHB

Marsh, I'm indebted...thanks for the additional feedback (it's starting to
click).
--
SHB

Ah…success at last! For those of you who have been following this like a
good novel, allow me to tell you what I did (as always, any feedback on a
better approach would be appreciated).

1. In SubformB (the hide/show controls subform) I added an unbound textbox
(txtPetType). For the Control Source I typed in
“=Forms![ParentFormName]![SubformB].form!PetTypeâ€.
2. The first control in Set Tab Order is called ‘SurgeryDate’. In the On
Got Focus event of that control I put code that looked something like this:

Private Sub SurgeryDate_GotFocus()
Dim flag As Integer
Dim strPetType As String
strPetType = Me.txtPetType
Me.Refresh ‘txtPetType doesn’t change unless the page is refreshed
If Me.txtPetType <> strPetType Then 'there was a change in SubformA
If Forms![ParentFormName]![SubformA].Form!PetType =
"Dog" Then
Me.HWT.Visible = True
End If
End If
Me.Refresh ‘Need another refresh to make HWT visible
End Sub

That may work in some cases, but it's awfully convoluted.

When I said to use the AfterUpdate (and Current) event, I
should have stated that it was subformA's Dog/Cat combo
box's AfterUpdate event and subformA's Current event.
(these abstract form names are difficult for me to keep
straight). This way, whenever the combo box selection is
changed, subformB's HWT's visible property is set
accordingly. Similarly, when subformA changes to a
different record (with a potentially different value in the
PetType combo box), the Current event will set HWT's
visibility to agree with the newly current record.

The line of code in subformA will be something like:

Me.Parent.subformB.Form.Hwt.Visible = (Me.PetType = "Dog")

Be sure to replace subformB with the name of the subform
**control** on the main form.