unbound control with old values and moving focus

[SimonG via AccessMonster.com]

I have an unbound combo box as part of a form header, the value in this
control effects the detail contents of the rest of the form. Due to its
nature I want to put a user warning on this control “Are you sure you want to
change this? - y/nâ€, sort of thing. Where the user decides not to make the
change I need to re-set the original value, and ideally move focus to a data
input control.

As the combo box is unbound, the BeforeUpdate event doesn't hold the old
values, so I understand I need to add some variables to the class module of
the form. Can someone advise me where I find this please?
On attempting to move the focus I get an error message 2108 “You must save
the field before you execute the GoToControl action or SetFocusâ€, how can I
overcome this limitation?

Many thanks,
Simon

ps
Access 2007 SP1

 

[Arvin Meyer [MVP]]

You might try something like (untested):
In the Declarations section add the following:

Dim strCombo As String

Then in the form's current event:

Sub Form_Current()
strCombo = Me.cboMyCombo.Value
End Sub

Then in your Before Update:

Sub cboMyCombo_BeforeUpdate(Cancel As Integer)
If Me.cboMyCombo <> strCombo Then
If MsgBox("Are you sure you want to change this?", vbYesNo,
"Warning" = vbNo Then
Me.cboMyCombo.Undo ' or Me.cboMyCombo = strCombo
Cancel = True
Me.txtAnotherControl.SetFocus
End If
End If
End Sub

 

[Marshall Barton]

I have an unbound combo box as part of a form header, the value in this
control effects the detail contents of the rest of the form. Due to its
nature I want to put a user warning on this control “Are you sure you want to
change this? - y/n”, sort of thing. Where the user decides not to make the
change I need to re-set the original value, and ideally move focus to a data
input control.

As the combo box is unbound, the BeforeUpdate event doesn't hold the old
values, so I understand I need to add some variables to the class module of
the form. Can someone advise me where I find this please?
On attempting to move the focus I get an error message 2108 “You must save
the field before you execute the GoToControl action or SetFocus”, how can I
overcome this limitation?

Try using code like:

Private varT0 As Variant

Private Sub Text0_Enter()
varT0 = Text0
End Sub

Private Sub Text0_AfterUpdate()
If MsgBox("are you sure",vbYesNo) = vbNo Then
Text0 = varT0
End If
End Sub

 

[SimonG via AccessMonster.com]

Hi Arvin,

Thanks for the reply.

My first problem is is a little more basic, where do I find the 'Declarations
section'?

Your code is similar to what I have (a relief to know I'm not to far out!).
Me.txtAnotherControl.SetFocus is the line currently causing the 2108 error
message.
Cancel = True doesn't appear to do much – I'm guessing because this field
is unbound(?)


Many thanks for your help,
Regards,
Simon

 

[SimonG via AccessMonster.com]

Hi Marshall,

Thanks for the code (much neater than mine!), this is along the lines of what
I have. My problem is I don't know where in Access 2007 I find the correct
section of the form to place the Private deceleration in, so that it's
variable(s) can be accessed by other code in that form.

Regards,
Simon

 

[Arvin Meyer [MVP]]

If you paste the code into the top of the code window, just after the:

Option Compare Database
Option Explicit

statements, you'll see the Declarations in the upper right hand corner.

txtAnotherControl is a surrogate placeholder for your controlname.

Cancel = True cancels the Update event from happening.

 

[Marshall Barton]

Thanks for the code (much neater than mine!), this is along the lines of what
I have. My problem is I don't know where in Access 2007 I find the correct
section of the form to place the Private deceleration in, so that it's
variable(s) can be accessed by other code in that form.

The module level declarations must be before any procedure
declarations.

Change my example control name (Text0) to the name of your
control and make sure that the control's Enter and
AfterUpdate event properties contain:
[Event Procedure]

 

[Marshall Barton]

You might try something like (untested):
In the Declarations section add the following:

Dim strCombo As String

Then in the form's current event:

Sub Form_Current()
strCombo = Me.cboMyCombo.Value
End Sub

Then in your Before Update:

Sub cboMyCombo_BeforeUpdate(Cancel As Integer)
If Me.cboMyCombo <> strCombo Then
If MsgBox("Are you sure you want to change this?", vbYesNo,
"Warning" = vbNo Then
Me.cboMyCombo.Undo ' or Me.cboMyCombo = strCombo
Cancel = True
Me.txtAnotherControl.SetFocus
End If
End If
End Sub

Arvin,

Am I misunderstanding the problem?

Seems to me that since this is an unbound control, Undo is
inappropriate.

On the other hand, the alternative to Undo requires the use
of the AfterUpdate event because the control's value can not
changed in the BeforeUpdate event.

I don't understand the need to use SetFocus.

 

[SimonG via AccessMonster.com]

Hi Arvin,

Thanks for that, variable declarations now inserted in correct place.

New issue, similar to 2108 error: original value reassign line (Me.cboMyCombo
= strCombo from your code) now gives 2115 error “macro or function set to
BeforeUpdate or Validation Rule property for this field is preventing
Microsoft Office Access from saving the dataâ€. Suspect this has the same
root cause as the move focus problem, but not sure where to look.

Regards,
Simon

 

[SimonG via AccessMonster.com]

Hi Marshall,

The reason for the SetFocus is,
the combo control sets parameters for the form (hence the confirmation when
changing), the SetFocus is a refinement on the undo change, by moving the
user away from the parameter area of the form to the data entry area.

Regards,
Simon

 

[SimonG via AccessMonster.com]

Hello Marshall,

Sorry; just realised I may have mis-understood some of your original reply.

Your re-assignment of the original value (Text0 = varT0) is happening as part
of an AfterUpdate, where my approach has been similar to Arvin, using
BeforeUpdate.

Could you please explain why its AfterUpdate, and not BeforeUpdate (not
trying to question your reply just to understand it - so that next time I
might know what to use and why)?

Many thanks,
Simon

 

[Marshall Barton]

Sorry; just realised I may have mis-understood some of your original reply.

Your re-assignment of the original value (Text0 = varT0) is happening as part
of an AfterUpdate, where my approach has been similar to Arvin, using
BeforeUpdate.

Could you please explain why its AfterUpdate, and not BeforeUpdate (not
trying to question your reply just to understand it - so that next time I
might know what to use and why)?

I thought I explained all that in my reply to Arvin. You
replied to that post with something about set focus.

 

[SimonG via AccessMonster.com]

On the other hand, the alternative to Undo requires the use
of the AfterUpdate event because the control's value can not
changed in the BeforeUpdate event.

Hi Marshall,

Many thanks for you patience and replys. My bad, again I had focussed on the
“Undo†and hadn't picked up the total relevance to Before and AfterUpdate you
were saying.

My understanding has changed, BeforeUpdate and AfterUpdate refer to updating
the database (bound field) and not to the control. Hence for an unbound
field the events are very similar, but BeforeUpdate has procedures and
features built-in (relative to database) which are still active; and in this
example stop us achieving our goal. Therefore AfterUpdate is used with
appropriate code.

Features are now up and running. Many thanks to Marshall and Arvin for
helping resolve this.

Regards,
Simon

 

[Arvin Meyer [MVP]]

Marsh was correct about my code not working with Undo. Unbound controls
cannot be reversed to anything in the Before Update event. Instead try:

Sub cboMyCombo_AfterUpdate()
If Me.cboMyCombo <> strCombo Then
If MsgBox("Are you sure you want to change this?", vbYesNo,
"Warning" = vbNo Then
Me.cboMyCombo = strCombo
Me.txtAnotherControl.SetFocus
End If
End If
End Sub

 

[Marshall Barton]

Many thanks for you patience and replys. My bad, again I had focussed on the
“Undo” and hadn't picked up the total relevance to Before and AfterUpdate you
were saying.

My understanding has changed, BeforeUpdate and AfterUpdate refer to updating
the database (bound field) and not to the control. Hence for an unbound
field the events are very similar, but BeforeUpdate has procedures and
features built-in (relative to database) which are still active; and in this
example stop us achieving our goal. Therefore AfterUpdate is used with
appropriate code.

I think you are still confused about the event.

First, there are control events and there are form events.
Despite their names being the same, their purpose and
details are very different.

The control BeforeUpdate event happens when the control's
value is about to be changed and the AfterUpdate event
happens after the control's value has been changed.

The form BeforeUpdate event happens just before all the
changes to a record will be saved. The form's AfterUpdate
event happens immediately after the record was saved.