Guidance :: vba inconsistent :: Checkbox condition in form

[jason]

Hi Guys...I really need some help here. I am frustrated by the results of
some basic vba
code to create a condition based on a the status of a CHECK box in my form.

I wish to control the immediate VISIBILITY of other elements on the form
based
on whether the CHECKBOX is ticked.

I placed the code inside the CHECKBOX sub.

HOWEVER, it does not work when the FORM loads.

It only works if you first click inside the checkbox and then out again to
force the condition.

I really don't see why this. Surely when the form loads the condition of
the CHECKBOX is
determined.

I know you will come back to me and say place this code in Form Onload
event. But, I have
tried this in the past and also achieved inconsistent results. Can you help
me understand how
to create a universal application of my condition everytime a user opens up
the form or even
scrolls to a next record. I think it must be something very simple...I
really appreciate your help


See below:

Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If



End Sub

 

[Dirk Goldgar]

Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the check box
to respond to the change. The same code is called by the form's
Current event.

Since the code can be triggered by changing record, you need to avoid
hiding the control that has the focus. It's also more efficient to
change the visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()

[snip]

Since Allen's using the AfterUpdate event rather than the Click event, I
think he meant the Form_Current() procedure to read like this:

Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
End Sub

 

[jason]

Hold - now it has stopped working again - this I don't understand - the
unchecked boxes are ignoring the turn-off-visibility rules for the other
conditioned form elements....very frustrating!

- Jason

Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the check box to
respond to the change. The same code is called by the form's Current event.

Since the code can be triggered by changing record, you need to avoid hiding
the control that has the focus. It's also more efficient to change the
visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()
On Error Goto Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Guys...I really need some help here. I am frustrated by the results of
some basic vba
code to create a condition based on a the status of a CHECK box in my form.

I wish to control the immediate VISIBILITY of other elements on the form
based
on whether the CHECKBOX is ticked.

I placed the code inside the CHECKBOX sub.

HOWEVER, it does not work when the FORM loads.

It only works if you first click inside the checkbox and then out again to
force the condition.

I really don't see why this. Surely when the form loads the condition of
the CHECKBOX is
determined.

I know you will come back to me and say place this code in Form Onload
event. But, I have
tried this in the past and also achieved inconsistent results. Can you help
me understand how
to create a universal application of my condition everytime a user opens up
the form or even
scrolls to a next record. I think it must be something very simple...I
really appreciate your help


See below:

Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub

 

[jason]

Dirk - I am really confused:

I substituted:

Call Status_Price_Reduced_AfterUpdate

for

Call Status_Price_Reduced_Click

....and it works great if you create a new record.

BUT

When you scroll through previously flagged records it is ignoring the rules
set up.

For instance, a flagged checkbox is not showing the eg: Current_Price -
which should be made visible.

This is what confused the hell out of me by VBA there does not seem to be a
clear way to make
things like this work...

I really don't know how to correct the code

- Jason

Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the check box
to respond to the change. The same code is called by the form's
Current event.

Since the code can be triggered by changing record, you need to avoid
hiding the control that has the focus. It's also more efficient to
change the visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()

[snip]

Since Allen's using the AfterUpdate event rather than the Click event, I
think he meant the Form_Current() procedure to read like this:

Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
End Sub

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Jason -

Is your form in single-form view or in continuous forms view? I haven't
analyzed Allen's code, but it will really only work for a form in
single-form view. In continuous forms, the mere act of scrolling via
the scroll bar won't change the current record -- so the Current event
won't fire -- and all records on the form will be formatted according to
properties set for the current record.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk - I am really confused:

I substituted:

Call Status_Price_Reduced_AfterUpdate

for

Call Status_Price_Reduced_Click

...and it works great if you create a new record.

BUT

When you scroll through previously flagged records it is ignoring the
rules set up.

For instance, a flagged checkbox is not showing the eg: Current_Price
- which should be made visible.

This is what confused the hell out of me by VBA there does not seem
to be a clear way to make
things like this work...

I really don't know how to correct the code

- Jason

Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the check box
to respond to the change. The same code is called by the form's
Current event.

Since the code can be triggered by changing record, you need to
avoid hiding the control that has the focus. It's also more
efficient to change the visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()

[snip]

Since Allen's using the AfterUpdate event rather than the Click
event, I think he meant the Form_Current() procedure to read like
this:

Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
End Sub

 

[jason]

Hey Allen,

Thanks - I corrected this: Call Status_Price_Reduced_AfterUpdate
in the sub_form_Current()

....BUT...basically the form is not working.

1. I am not using single form - not continous
2. I am not sure what you mean by bound, but the form is built on an
underlying table which does have foreign keys established in some of the
other fields.

Current problem:

The unchecked records (status_current_price=ON) are still showing the items
which should be INVISIBLE including:

Current_Price
Price_Reduction_frame.Visible
Price_Reduction_Date.Visible

Additionally:

If I attempt to create a new record, the immediately present unchecked box
is not hiding or making invisible the:

Current_Price
Price_Reduction_frame.Visible
Price_Reduction_Date.Visible

....I am really confused - I find it really difficult to work out the problem
inside VBA. Here is my full revised code:

Private Sub Form_Current()

Call Status_Price_Reduced_AfterUpdate
End Sub


Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub




Private Sub Status_Price_Reduced_AfterUpdate()
On Error GoTo Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value
MsgBox (bShow)

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub

The Current event fires whenever to form loads a record. It's the one to
manipulate changes you want to see when a record loads.

This assumes:
- this is single form view (not Continuous or Datasheet);
- it's a bound form;
- Current_Price is bound to a field (not an expression starting with =).

I've picked up the names of the controls from your code, so you should not
have to change any of the names except this one:
SomeOtherControlThatIsAlwaysVisible

The error handling is needed because it causes an error if a control has
focus at the time you try to hide it. That could happen in the Current event
of the form.

Dirk is right. I should have called the AfterUpdate since that's the event I
used:
Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hold - now it has stopped working again - this I don't understand - the
unchecked boxes are ignoring the turn-off-visibility rules for the other
conditioned form elements....very frustrating!

- Jason

Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the check box to
respond to the change. The same code is called by the form's Current event.

Since the code can be triggered by changing record, you need to avoid hiding
the control that has the focus. It's also more efficient to change the
visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()
On Error Goto Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Guys...I really need some help here. I am frustrated by the results
of
some basic vba
code to create a condition based on a the status of a CHECK box in my
form.

I wish to control the immediate VISIBILITY of other elements on the form
based
on whether the CHECKBOX is ticked.

I placed the code inside the CHECKBOX sub.

HOWEVER, it does not work when the FORM loads.

It only works if you first click inside the checkbox and then out

again

to
force the condition.

I really don't see why this. Surely when the form loads the

condition
of

the CHECKBOX is
determined.

I know you will come back to me and say place this code in Form Onload
event. But, I have
tried this in the past and also achieved inconsistent results. Can you
help
me understand how
to create a universal application of my condition everytime a user opens
up
the form or even
scrolls to a next record. I think it must be something very simple...I
really appreciate your help


See below:

Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub

 

[jason]

Last post: I am using SINGLE....

The Current event fires whenever to form loads a record. It's the one to
manipulate changes you want to see when a record loads.

This assumes:
- this is single form view (not Continuous or Datasheet);
- it's a bound form;
- Current_Price is bound to a field (not an expression starting with =).

I've picked up the names of the controls from your code, so you should not
have to change any of the names except this one:
SomeOtherControlThatIsAlwaysVisible

The error handling is needed because it causes an error if a control has
focus at the time you try to hide it. That could happen in the Current event
of the form.

Dirk is right. I should have called the AfterUpdate since that's the event I
used:
Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hold - now it has stopped working again - this I don't understand - the
unchecked boxes are ignoring the turn-off-visibility rules for the other
conditioned form elements....very frustrating!

- Jason

Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the check box to
respond to the change. The same code is called by the form's Current event.

Since the code can be triggered by changing record, you need to avoid hiding
the control that has the focus. It's also more efficient to change the
visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()
On Error Goto Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Guys...I really need some help here. I am frustrated by the results
of
some basic vba
code to create a condition based on a the status of a CHECK box in my
form.

I wish to control the immediate VISIBILITY of other elements on the form
based
on whether the CHECKBOX is ticked.

I placed the code inside the CHECKBOX sub.

HOWEVER, it does not work when the FORM loads.

It only works if you first click inside the checkbox and then out

again

to
force the condition.

I really don't see why this. Surely when the form loads the

condition
of

the CHECKBOX is
determined.

I know you will come back to me and say place this code in Form Onload
event. But, I have
tried this in the past and also achieved inconsistent results. Can you
help
me understand how
to create a universal application of my condition everytime a user opens
up
the form or even
scrolls to a next record. I think it must be something very simple...I
really appreciate your help


See below:

Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub

 

[jason]

Hi Allen,

But it seems to be ignoring the VALUE of Status_Price_Reduced
completely.....

- Jason

At the moment, it appears to be ignoring

The code does not change anything based on the value of:
Status_Current_Price.

It looks only at the value of:
Status_Price_Reduced

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hey Allen,

Thanks - I corrected this: Call Status_Price_Reduced_AfterUpdate
in the sub_form_Current()

...BUT...basically the form is not working.

1. I am not using single form - not continous
2. I am not sure what you mean by bound, but the form is built on an
underlying table which does have foreign keys established in some of the
other fields.

Current problem:

The unchecked records (status_current_price=ON) are still showing the items
which should be INVISIBLE including:

Current_Price
Price_Reduction_frame.Visible
Price_Reduction_Date.Visible

Additionally:

If I attempt to create a new record, the immediately present unchecked box
is not hiding or making invisible the:

Current_Price
Price_Reduction_frame.Visible
Price_Reduction_Date.Visible

...I am really confused - I find it really difficult to work out the problem
inside VBA. Here is my full revised code:

Private Sub Form_Current()

Call Status_Price_Reduced_AfterUpdate
End Sub


Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub




Private Sub Status_Price_Reduced_AfterUpdate()
On Error GoTo Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value
MsgBox (bShow)

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub

The Current event fires whenever to form loads a record. It's the one to
manipulate changes you want to see when a record loads.

This assumes:
- this is single form view (not Continuous or Datasheet);
- it's a bound form;
- Current_Price is bound to a field (not an expression starting with =).

I've picked up the names of the controls from your code, so you should not
have to change any of the names except this one:
SomeOtherControlThatIsAlwaysVisible

The error handling is needed because it causes an error if a control has
focus at the time you try to hide it. That could happen in the Current event
of the form.

Dirk is right. I should have called the AfterUpdate since that's the

event

I
used:
Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hold - now it has stopped working again - this I don't understand - the
unchecked boxes are ignoring the turn-off-visibility rules for the other
conditioned form elements....very frustrating!

- Jason
Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the check

box
to

respond to the change. The same code is called by the form's Current
event.

Since the code can be triggered by changing record, you need to avoid
hiding
the control that has the focus. It's also more efficient to change the
visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()
On Error Goto Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Guys...I really need some help here. I am frustrated by the
results
of
some basic vba
code to create a condition based on a the status of a CHECK box

in
my

form.

I wish to control the immediate VISIBILITY of other elements on the
form
based
on whether the CHECKBOX is ticked.

I placed the code inside the CHECKBOX sub.

HOWEVER, it does not work when the FORM loads.

It only works if you first click inside the checkbox and then out
again
to
force the condition.

I really don't see why this. Surely when the form loads the condition
of
the CHECKBOX is
determined.

I know you will come back to me and say place this code in Form Onload
event. But, I have
tried this in the past and also achieved inconsistent results.

Can
you

help
me understand how
to create a universal application of my condition everytime a user
opens
up
the form or even
scrolls to a next record. I think it must be something very simple...I
really appreciate your help


See below:

Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub

 

[jason]

Hi Allen,

I re-copied your code and now it works - I obviously messed up some setting
at a later date - but it works!

I will use your de-bugging proc for the future - very helpful!

Could I ask you one last thing:

Supposing I wished to do the same thing your script does but this time use a
drop-down list value as the condition qualifer eg:

-- select--
NEW
USED

.....If they select NEW I would want to control visibility of other form
elements. Is there anything I need to be careful of?

Many thanks for your help!
- Jason

Open your form in design view.
Open the Properties box (View menu).
What is the property setting for the On Current property of the form?

It needs to be:
[Event Procedure]
for the code to work.

If that setting is present, set a break point in the first line of the
AfterUpdate event, by pressing F9. Open the form. When it stops in your
code, you can single-step through the code, checking the value of the check
box by pausing the mouse over its name in the code. Check that bShow is true
when the controls should be shown, and false when they should not be shown.

You can comment out this line by adding a single quote in front of it:
'If Me.Current_Price.Visible <> bShow Then
and comment out the matching End If as well.

This should let you debug what's going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Allen,

But it seems to be ignoring the VALUE of Status_Price_Reduced
completely.....

- Jason

At the moment, it appears to be ignoring

The code does not change anything based on the value of:
Status_Current_Price.

It looks only at the value of:
Status_Price_Reduced

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hey Allen,

Thanks - I corrected this: Call Status_Price_Reduced_AfterUpdate
in the sub_form_Current()

...BUT...basically the form is not working.

1. I am not using single form - not continous
2. I am not sure what you mean by bound, but the form is built on an
underlying table which does have foreign keys established in some of the
other fields.

Current problem:

The unchecked records (status_current_price=ON) are still showing the
items
which should be INVISIBLE including:

Current_Price
Price_Reduction_frame.Visible
Price_Reduction_Date.Visible

Additionally:

If I attempt to create a new record, the immediately present

unchecked
box

is not hiding or making invisible the:

Current_Price
Price_Reduction_frame.Visible
Price_Reduction_Date.Visible

...I am really confused - I find it really difficult to work out the
problem
inside VBA. Here is my full revised code:

Private Sub Form_Current()

Call Status_Price_Reduced_AfterUpdate
End Sub


Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub




Private Sub Status_Price_Reduced_AfterUpdate()
On Error GoTo Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value
MsgBox (bShow)

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub







The Current event fires whenever to form loads a record. It's the

one

to
manipulate changes you want to see when a record loads.

This assumes:
- this is single form view (not Continuous or Datasheet);
- it's a bound form;
- Current_Price is bound to a field (not an expression starting

with
=).

I've picked up the names of the controls from your code, so you should
not
have to change any of the names except this one:
SomeOtherControlThatIsAlwaysVisible

The error handling is needed because it causes an error if a

control
has

focus at the time you try to hide it. That could happen in the Current
event
of the form.

Dirk is right. I should have called the AfterUpdate since that's the
event
I
used:
Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hold - now it has stopped working again - this I don't understand -
the
unchecked boxes are ignoring the turn-off-visibility rules for the
other
conditioned form elements....very frustrating!

- Jason
Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the

check
box

to
respond to the change. The same code is called by the form's Current
event.

Since the code can be triggered by changing record, you need to
avoid
hiding
the control that has the focus. It's also more efficient to change
the
visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()
On Error Goto Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Guys...I really need some help here. I am frustrated by the
results
of
some basic vba
code to create a condition based on a the status of a CHECK

box

in
my
form.

I wish to control the immediate VISIBILITY of other elements on
the
form
based
on whether the CHECKBOX is ticked.

I placed the code inside the CHECKBOX sub.

HOWEVER, it does not work when the FORM loads.

It only works if you first click inside the checkbox and

then
out

again
to
force the condition.

I really don't see why this. Surely when the form loads the
condition
of
the CHECKBOX is
determined.

I know you will come back to me and say place this code in Form
Onload
event. But, I have
tried this in the past and also achieved inconsistent

results.
Can

you
help
me understand how
to create a universal application of my condition everytime

a
user

opens
up
the form or even
scrolls to a next record. I think it must be something very
simple...I
really appreciate your help


See below:

Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub

 

[Allen Browne]

Same process, Jason.

The AfterUpdate event of the combo is the one to use to respond to changes.
Kinda makes sense, doesn't it.

Then in the Current event of the form you can call that event if you want
Access to do the same thing every time it loads a record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Allen,

I re-copied your code and now it works - I obviously messed up some setting
at a later date - but it works!

I will use your de-bugging proc for the future - very helpful!

Could I ask you one last thing:

Supposing I wished to do the same thing your script does but this time use a
drop-down list value as the condition qualifer eg:

-- select--
NEW
USED

....If they select NEW I would want to control visibility of other form
elements. Is there anything I need to be careful of?

Many thanks for your help!
- Jason

Open your form in design view.
Open the Properties box (View menu).
What is the property setting for the On Current property of the form?

It needs to be:
[Event Procedure]
for the code to work.

If that setting is present, set a break point in the first line of the
AfterUpdate event, by pressing F9. Open the form. When it stops in your
code, you can single-step through the code, checking the value of the check
box by pausing the mouse over its name in the code. Check that bShow is true
when the controls should be shown, and false when they should not be shown.

You can comment out this line by adding a single quote in front of it:
'If Me.Current_Price.Visible <> bShow Then
and comment out the matching End If as well.

This should let you debug what's going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Allen,

But it seems to be ignoring the VALUE of Status_Price_Reduced
completely.....

- Jason

At the moment, it appears to be ignoring
The code does not change anything based on the value of:
Status_Current_Price.

It looks only at the value of:
Status_Price_Reduced

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hey Allen,

Thanks - I corrected this: Call Status_Price_Reduced_AfterUpdate
in the sub_form_Current()

...BUT...basically the form is not working.

1. I am not using single form - not continous
2. I am not sure what you mean by bound, but the form is built on an
underlying table which does have foreign keys established in some

of
the

other fields.

Current problem:

The unchecked records (status_current_price=ON) are still showing the
items
which should be INVISIBLE including:

Current_Price
Price_Reduction_frame.Visible
Price_Reduction_Date.Visible

Additionally:

If I attempt to create a new record, the immediately present unchecked
box
is not hiding or making invisible the:

Current_Price
Price_Reduction_frame.Visible
Price_Reduction_Date.Visible

...I am really confused - I find it really difficult to work out the
problem
inside VBA. Here is my full revised code:

Private Sub Form_Current()

Call Status_Price_Reduced_AfterUpdate
End Sub


Private Sub Status_Price_Reduced_Click()

Status_Price_Reduced.SetFocus

If Status_Price_Reduced = -1 Then

Current_Price.Visible = True
Price_Reduction_frame.Visible = True
Price_Reduction_Date.Visible = True

Else

Current_Price.Visible = False
Price_Reduction_frame.Visible = False
Price_Reduction_Date.Visible = False

End If

End Sub




Private Sub Status_Price_Reduced_AfterUpdate()
On Error GoTo Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value
MsgBox (bShow)

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub







The Current event fires whenever to form loads a record. It's

the
one

to
manipulate changes you want to see when a record loads.

This assumes:
- this is single form view (not Continuous or Datasheet);
- it's a bound form;
- Current_Price is bound to a field (not an expression starting with
=).

I've picked up the names of the controls from your code, so you should
not
have to change any of the names except this one:
SomeOtherControlThatIsAlwaysVisible

The error handling is needed because it causes an error if a control
has
focus at the time you try to hide it. That could happen in the Current
event
of the form.

Dirk is right. I should have called the AfterUpdate since that's the
event
I
used:
Private Sub Form_Current()
Call Status_Price_Reduced_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hold - now it has stopped working again - this I don't understand -
the
unchecked boxes are ignoring the turn-off-visibility rules for the
other
conditioned form elements....very frustrating!

- Jason
Use the Current event of the form.

The code below uses the AfterUpdate event procedure of the check
box
to
respond to the change. The same code is called by the form's
Current
event.

Since the code can be triggered by changing record, you need to
avoid
hiding
the control that has the focus. It's also more efficient to change
the
visibility of the controls only if needed.

Private Sub Form_Current()
Call Status_Price_Reduced_Click
End Sub

Private Sub Status_Price_Reduced_AfterUpdate()
On Error Goto Err_Handler
Dim bShow As Boolean

bShow = Me.Status_Price_Reduced.Value

If Me.Current_Price.Visible <> bShow Then
Current_Price.Visible = bShow
Price_Reduction_frame.Visible = bShow
Price_Reduction_Date.Visible = bShow
End If

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 2164, 2165 'Can't disable/hide the control with
focus.
Me.[SomeOtherControlThatIsAlwaysVisible].SetFocus
Resume
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Select
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi Guys...I really need some help here. I am frustrated

by
the

results
of
some basic vba
code to create a condition based on a the status of a

CHECK
box

in
my
form.

I wish to control the immediate VISIBILITY of other

elements
on

the
form
based
on whether the CHECKBOX is ticked.

I placed the code inside the CHECKBOX sub.

HOWEVER, it does not work when the FORM loads.

It only works if you first click inside the checkbox and then
out
again
to
force the condition.

I really don't see why this. Surely when the form loads the
condition
of
the CHECKBOX is
determined.

I know you will come back to me and say place this code in Form
Onload
event. But, I have
tried this in the past and also achieved inconsistent results.
Can
you
help
me understand how
to create a universal application of my condition

everytime

a