SetFocus in Form Undo event

[Jerry Schwartz]

To make a long story short, I want to flip the state of a control when the
user undoes their changes. I can't do this if the control has the focus, so I
need to move the focus to some other control first. My code looks something
like this:

Private Sub Form_Undo(Cancel As Integer)

' If the associated publisher record requires authorization,
' we show the "Authorize order" button and hide the "Order sheet"
button.

Cancel = False
Me.SomewhereElse.SetFocus
Me.MyControl.Visible = False
Me.MyControl.Enabled = False

End Sub

It appears, however, that you cannot use the SetFocus method on any control
during the Form Undo event.

Any suggestions?

 

[Jeanette Cunningham]

Hi Jerry,
when the user undoes their changes, the bound controls will be reset back to
their OldValue.
Perhaps you can check the value of one control (not sure which control is
best for this), if it's value after undo is the same as its OldValue, then
use that to hide the order control/button instead of trying to set focus.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jerry Schwartz]

Hi Jerry,
when the user undoes their changes, the bound controls will be reset back to
their OldValue.

That is precisely what I want.

Perhaps you can check the value of one control (not sure which control is
best for this), if it's value after undo is the same as its OldValue, then
use that to hide the order control/button instead of trying to set focus.

In what event would I do that?

Unfortunately, I don't think this would help in any case. Here's a little
more explanation of what I want to do:

The user brings up an order, and it might or might not require
authorization. If the order requires authorization, then I want to display
the "Authorize" button and hide the "Order" button. If the order has already
been authorized, then I want to display the "Order" button but not the
"Authorize" button.

This all works fine, except in one corner case:

If the user authorizes the order, but then cancels the screen (without
having previously updated the underlying data), the states of the two buttons
do not return to their original values. This means the user no longer has the
ability to authorize the order without navigating to another record and then
back.

That's why I need to fiddle with the buttons when the form is cancelled. I
can't think of any other event that would do it.

As it happens, there are no bound controls that I can guarantee would have
changed, although I could make a new one and hide it somewhere. I think I'd
still have the same problem, though, because there isn't any event that gets
triggered after an Undo; so I don't see any place where I can check the value
of any control.

 

[Jeanette Cunningham]

Jerry,
some questions:
How does a user cancel an order or carry out the undo - how many different
ways can they do this?

Have you thought of having an order status field instead of needing to use
buttons.
On the order status field, in your form you could use a combo that has
values like - authorized, ordered, whatever else you need. That way there is
no tricky coding around order and authorize buttons. The user simply chooses
the status from the combo.

I use this system for an inventory database. When a job is started it is
automatically given a status of open.
When the job has been completed the user chooses 'invoice' for the status.
When the job has been invoiced the user chooses 'closed' for the status.
It is easy to find all closed jobs, open jobs etc.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jerry Schwartz]

Jerry,
some questions:
How does a user cancel an order or carry out the undo - how many different
ways can they do this?

They can only cancel changes to (or creation of) an order record with the
escape key.

Have you thought of having an order status field instead of needing to use
buttons.
On the order status field, in your form you could use a combo that has
values like - authorized, ordered, whatever else you need. That way there is
no tricky coding around order and authorize buttons. The user simply chooses
the status from the combo.

The typical user cannot authorized an order. They can enter an order, but
until the order is authorized they cannot complete the invoicing process.
Since it appears that we need to delve into the process more deeply yet,
here's what's really going on.

Certain suppliers require authorization, but most do not. This flag is set
on the supplier record.

Each order record has an "authorized by" field, but unless the supplier
requires authorization it is ignored.

When a user creates, or navigates to, an order whose supplier requires
authorization, the "authorized by" field on the record is checked. If it is
empty, then the "Authorize" button is shown and the "Order" (actually, its
function it to complete an order) button is hidden. If the supplier does not
require authorization, the "Order" button is always available.

When a user hits the "Authorize" button, they are prompted for a password.
If they type in a valid password, then their user id is entered into the
"authorized by" field; the "Authorize" button goes away; and the "Order"
button is displayed.

That all works. The problem arises if they cancel changes to an existing
order after using the "Authorize" button. The "Authorize" button will have
disappeared, the "Order" button will have appeared, but the "authorized by"
field will be reset to its previous (empty) value.

If the user navigates away from the order and back to it, the state of the
buttons will be set correctly by the Current event; but that is (IMHO) ugly.

I use this system for an inventory database. When a job is started it is
automatically given a status of open.
When the job has been completed the user chooses 'invoice' for the status.
When the job has been invoiced the user chooses 'closed' for the status.
It is easy to find all closed jobs, open jobs etc.

A drop-down would have the same problem. Remember, I don't want to show the
relevant control unless authorization is actually required.

 

[Jeanette Cunningham]

Trying to work out the real problem.
If a user makes a change to an authorized order, why do you need to show the
authorize button again if it has already been authorized?
If the user press Esc, I assume that the authorized by field becomes null.

A couple of ideas.
Idea 1: Trap the use of the Escape key and when it is used, display the
authorize button again for appropriate suppliers.

Idea2: If the order has been authorized, code around the Esc key being
pressed so that if the Authorized by field has a value it can't be undone.
The authorize button won't need to be shown again.

You may be able to use the ideas above to develop your own solution.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jerry Schwartz]

Trying to work out the real problem.

I appreciate that. I wish you could look over my shoulder, that would be a
lot easier than me having to reveal more and more of this rather tortured
process.

If a user makes a change to an authorized order, why do you need to show the
authorize button again if it has already been authorized?

If the order has been authorized prior to the current editing session, then
the "Authorize" button never has to be shown again. There is no way to
"unauthorize" a previously-authorized order. That part is fine: undoing
anything else doesn't change the state of the two buttons.

If the user press Esc, I assume that the authorized by field becomes null.

Only if the authorization is being done in the current session.

Let's see if I can enumerate the possibilities:

1. The order DOES NOT require authorization. The "Authorize" button is never
shown, the "Order" button is always shown. This is handled easily in the
Current event for the form, and none of the rest of this applies.

2. The order DOES require authorization. Authorization might occur when the
order is entered, or at any time thereafter. The form's Current event sets
the states of the two buttons, based upon whether or not the order has
already been authorized. There is no way to "unauthorize" an order, so this
is very straightforward as well.

3. When the user clicks on the "Authorize" button, the On Click event
handles the challenge and response. If the user's response is satisfactory,
the "authorized" flag is set in the record, the "Authorize" button
disappears, and the "Order" button appears. If the user's response is
rejected, the "authorized" flag is left null. Note that at this point the
record has not been updated, so the "authorized" flag is dirty.

4. If the user now saves the record, everything is fine: the "authorized"
flag is saved, the "Authorized" button is gone, and the "Order" button is
there forever.

5. If the user UNDOES the editing of the record, and has NOT authorized the
order in the current editing session, everything is fine.

6. If the user HAS authorized the order in the current editing session
(which might have consisted solely of the authorization), and then UNDOES the
editing, I want the "Authorize" button button to re-appear and the "Order"
button to disappear. In other words, I want the two buttons to go back to the
state set by the form's Current event.

The only place I can think of to do this is in the form's Undo event. That's
where I've hit a dead end.

A couple of ideas.
Idea 1: Trap the use of the Escape key and when it is used, display the
authorize button again for appropriate suppliers.

I'll have to think about that. The escape key, when hit once, only undoes
the current field. I'd have to look for it to be hit twice, with no
intervening activity. That sounds like a challenge.

Idea2: If the order has been authorized, code around the Esc key being
pressed so that if the Authorized by field has a value it can't be undone.
The authorize button won't need to be shown again.

That, unfortunately, doesn't do what I want. See 6 above.

Thanks for your help.

 

[Jeanette Cunningham]

How about this.
If the user is on a new record and they hit the authorize button, instead of
hiding the authorize button, leave it visible, as well as showing the order
button.

If the user has returned to edit a previously authorized record, they won't
be on a new record, and everything stays as you have it.

You can use code like this on the open/ load event and also the current
event.
If Me.NewRecord = True Then
'do nothing to hide the authorize button.
Else
'your code that decides when to show or hide the authorize button.
End IF

After the authorize by field is updated, change your code so that the
authorize button does not disappear when the user is on a new record.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Extending this idea a bit, to apply it to all records whether a new record
or editing an existing record.

If a user opens a previous record for editing but it has not been authorized
(and needs to be authorized),
or they start a new record, then when they authorize the record, simply
leave the authorize button still visible, and also show the order button.

If user cancels the authorization, the button is still visible, and you can
hide the order button (if you wish and I assume you have code that does this
already).

There will not be any harm caused by leaving the authorize button visible
after the record has been authorized, if they click it again, it simply puts
the same value in the authorized by field.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

If the user has authorized the order, you code the authorize button's click
event to check the authorized by field and only pop up the enter password
screen if the authorized by field is empty.

If Len(Me.[AuthorizeByControlName] ) >0 Then
'do nothing
Else
'code here to pop up the enter password screen
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jerry Schwartz]

Jeanette,

I decided to put this aside for awhile because there is another chunk of
coding that I need to do.

After modifying other, unrelated code something moved me to go back and try
this again. It occurred to me that perhaps I could set the focus to the form
itself, and that might let me change the visible and enabled states of my
buttons.

Before trying that, I gave my existing code one more run-through. Much to my
disgust, it now works. No changes, no nothing.

I hate it when that happens. Faith healing software...

Thanks for all of the effort you put into this.

It now seems to work perfectly. I no longer get error 2110 from the SetFocus

If the user has authorized the order, you code the authorize button's click
event to check the authorized by field and only pop up the enter password
screen if the authorized by field is empty.

If Len(Me.[AuthorizeByControlName] ) >0 Then
'do nothing
Else
'code here to pop up the enter password screen
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Extending this idea a bit, to apply it to all records whether a new record
or editing an existing record.

If a user opens a previous record for editing but it has not been
authorized (and needs to be authorized),
or they start a new record, then when they authorize the record, simply
leave the authorize button still visible, and also show the order button.

If user cancels the authorization, the button is still visible, and you
can hide the order button (if you wish and I assume you have code that
does this already).

There will not be any harm caused by leaving the authorize button visible
after the record has been authorized, if they click it again, it simply
puts the same value in the authorized by field.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

A good end result. I know this sort of thing has happened to me before.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

I decided to put this aside for awhile because there is another chunk of
coding that I need to do.

After modifying other, unrelated code something moved me to go back and
try
this again. It occurred to me that perhaps I could set the focus to the
form
itself, and that might let me change the visible and enabled states of my
buttons.

Before trying that, I gave my existing code one more run-through. Much to
my
disgust, it now works. No changes, no nothing.

I hate it when that happens. Faith healing software...

Thanks for all of the effort you put into this.

It now seems to work perfectly. I no longer get error 2110 from the
SetFocus

If the user has authorized the order, you code the authorize button's
click
event to check the authorized by field and only pop up the enter password
screen if the authorized by field is empty.

If Len(Me.[AuthorizeByControlName] ) >0 Then
'do nothing
Else
'code here to pop up the enter password screen
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Extending this idea a bit, to apply it to all records whether a new
record
or editing an existing record.

If a user opens a previous record for editing but it has not been
authorized (and needs to be authorized),
or they start a new record, then when they authorize the record, simply
leave the authorize button still visible, and also show the order
button.

If user cancels the authorization, the button is still visible, and you
can hide the order button (if you wish and I assume you have code that
does this already).

There will not be any harm caused by leaving the authorize button
visible
after the record has been authorized, if they click it again, it simply
puts the same value in the authorized by field.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


How about this.
If the user is on a new record and they hit the authorize button,
instead
of hiding the authorize button, leave it visible, as well as showing
the
order button.

If the user has returned to edit a previously authorized record, they
won't be on a new record, and everything stays as you have it.

You can use code like this on the open/ load event and also the
current
event.
If Me.NewRecord = True Then
'do nothing to hide the authorize button.
Else
'your code that decides when to show or hide the authorize button.
End IF

After the authorize by field is updated, change your code so that the
authorize button does not disappear when the user is on a new record.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


message

:

Trying to work out the real problem.

I appreciate that. I wish you could look over my shoulder, that would
be
a
lot easier than me having to reveal more and more of this rather
tortured
process.

If a user makes a change to an authorized order, why do you need to
show the
authorize button again if it has already been authorized?

If the order has been authorized prior to the current editing
session,
then
the "Authorize" button never has to be shown again. There is no way
to
"unauthorize" a previously-authorized order. That part is fine:
undoing
anything else doesn't change the state of the two buttons.

If the user press Esc, I assume that the authorized by field becomes
null.

Only if the authorization is being done in the current session.

Let's see if I can enumerate the possibilities:

1. The order DOES NOT require authorization. The "Authorize" button
is
never
shown, the "Order" button is always shown. This is handled easily in
the
Current event for the form, and none of the rest of this applies.

2. The order DOES require authorization. Authorization might occur
when
the
order is entered, or at any time thereafter. The form's Current event
sets
the states of the two buttons, based upon whether or not the order
has
already been authorized. There is no way to "unauthorize" an order,
so
this
is very straightforward as well.

3. When the user clicks on the "Authorize" button, the On Click event
handles the challenge and response. If the user's response is
satisfactory,
the "authorized" flag is set in the record, the "Authorize" button
disappears, and the "Order" button appears. If the user's response is
rejected, the "authorized" flag is left null. Note that at this point
the
record has not been updated, so the "authorized" flag is dirty.

4. If the user now saves the record, everything is fine: the
"authorized"
flag is saved, the "Authorized" button is gone, and the "Order"
button
is
there forever.

5. If the user UNDOES the editing of the record, and has NOT
authorized
the
order in the current editing session, everything is fine.

6. If the user HAS authorized the order in the current editing
session
(which might have consisted solely of the authorization), and then
UNDOES the
editing, I want the "Authorize" button button to re-appear and the
"Order"
button to disappear. In other words, I want the two buttons to go
back
to the
state set by the form's Current event.

The only place I can think of to do this is in the form's Undo event.
That's
where I've hit a dead end.


A couple of ideas.
Idea 1: Trap the use of the Escape key and when it is used, display
the
authorize button again for appropriate suppliers.


I'll have to think about that. The escape key, when hit once, only
undoes
the current field. I'd have to look for it to be hit twice, with no
intervening activity. That sounds like a challenge.

Idea2: If the order has been authorized, code around the Esc key
being
pressed so that if the Authorized by field has a value it can't be
undone.
The authorize button won't need to be shown again.


That, unfortunately, doesn't do what I want. See 6 above.

Thanks for your help.

You may be able to use the ideas above to develop your own solution.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

message


:

Jerry,
some questions:
How does a user cancel an order or carry out the undo - how many
different
ways can they do this?


They can only cancel changes to (or creation of) an order record
with
the
escape key.

Have you thought of having an order status field instead of
needing
to
use
buttons.
On the order status field, in your form you could use a combo
that
has
values like - authorized, ordered, whatever else you need. That
way
there
is
no tricky coding around order and authorize buttons. The user
simply
chooses
the status from the combo.


The typical user cannot authorized an order. They can enter an
order,
but
until the order is authorized they cannot complete the invoicing
process.
Since it appears that we need to delve into the process more
deeply
yet,
here's what's really going on.

Certain suppliers require authorization, but most do not. This
flag
is set
on the supplier record.

Each order record has an "authorized by" field, but unless the
supplier
requires authorization it is ignored.

When a user creates, or navigates to, an order whose supplier
requires
authorization, the "authorized by" field on the record is checked.
If
it
is
empty, then the "Authorize" button is shown and the "Order"
(actually, its
function it to complete an order) button is hidden. If the
supplier
does
not
require authorization, the "Order" button is always available.

When a user hits the "Authorize" button, they are prompted for a
password.
If they type in a valid password, then their user id is entered
into
the
"authorized by" field; the "Authorize" button goes away; and the
"Order"
button is displayed.

That all works. The problem arises if they cancel changes to an
existing
order after using the "Authorize" button. The "Authorize" button
will
have
disappeared, the "Order" button will have appeared, but the
"authorized
by"
field will be reset to its previous (empty) value.

If the user navigates away from the order and back to it, the
state
of the
buttons will be set correctly by the Current event; but that is
(IMHO)
ugly.

I use this system for an inventory database. When a job is
started
it is
automatically given a status of open.
When the job has been completed the user chooses 'invoice' for
the
status.
When the job has been invoiced the user chooses 'closed' for the
status.
It is easy to find all closed jobs, open jobs etc.


A drop-down would have the same problem. Remember, I don't want to
show
the
relevant control unless authorization is actually required.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


in
message


:

Hi Jerry,
when the user undoes their changes, the bound controls will be
reset
back
to
their OldValue.

That is precisely what I want.

Perhaps you can check the value of one control (not sure which
control
is
best for this), if it's value after undo is the same as its
OldValue,
then
use that to hide the order control/button instead of trying to
set
focus.


In what event would I do that?

Unfortunately, I don't think this would help in any case.
Here's a
little
more explanation of what I want to do:

The user brings up an order, and it might or might not require
authorization. If the order requires authorization, then I want
to

 

[Jerry Schwartz]

I finally figured out exactly what the problem was. I was testing various
things, and didn't realize the implications of one of my tests. I was testing
to see what happened if I put an invalid value in one of the other fields and
then did an Undo. That was what was causing my Undo code to fall over. I was
setting the focus to the button I wanted visible, so that I could make the
other button invisible.

What I didn't realize is that if there is an invalid value anywhere (in this
case, a letter in a numeric field) you can't move the focus away from it. I
stumbled on this when I tried to tab out of that field, and got yanked back
to it. I knew that would happen, but the implications didn't hit me until
later.

Here's my code, and it works just fine in every situation I've tested.
-----
Private Sub Form_Undo(Cancel As Integer)

' If the associated publisher record requires authorization,
' we show the "Authorize order" button and hide the "Order sheet"
button.

Cancel = False

' We can't change the visibility of the button that has the focus;
' but we also can't redirect the focus if there's a data error in some
field
' because that field won't let go of the focus. The On Error will skip
the
' SetFocus if that happens.

On Error Resume Next

If Me![Authorization Required] And WasAuthorized Then
Me![Order sheet_bottun].Visible = True
Me![Order sheet_bottun].Enabled = True
Me.Order_sheet_bottun.SetFocus
Me![BT_Authorize].Visible = False
Me![BT_Authorize].Enabled = False
Else
Me![BT_Authorize].Visible = True
Me![BT_Authorize].Enabled = True
Me.BT_Authorize.SetFocus
Me![Order sheet_bottun].Visible = False
Me![Order sheet_bottun].Enabled = False
End If

End Sub

 

[Jeanette Cunningham]

Jerry, thanks for posting your solution and explanation to help others.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia