Cancel button's ActiveControl reflects as value of text box

[Hugh self taught]

Hi All,

On my form I have an "on exit" code to test whether its text box is "empty"
or not. When I click the "Cancel" button I still get the msg that the txt box
is empty.

Using Debug.print to show the Me.ActiveControl gives me the value of the
preceeding txt box.

What I'm trying to achieve is to ignore the "empty" test if cancel has been
selected by exiting the "on exit" before the msg box sub & clearly I'm
misunderstanding the usage here somewhere.

 

[Jeff Boyce]

Hugh

I'm a bit unclear what you want to accomplish...

You've provided a description of "how" you are trying to do something, but
I'm not sure I understand "what" that is.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Allen Browne]

If you have code associated with the text box's events, Access will run that
code *before* letting the cursor get to the Cancel button. This means you
cannot use a Cancel button on your form to achieve what you want to.

Suggestions:

1) Remove the code from the text box's Exit event. Instead, put it into the
BeforeUpdate event of the *form* (not control.) That's actually the only way
to ensure an entry was made anyway. If the user clicks past the control
(never visits it), its Exit event won't fire, and so your test won't occur.
Form_BeforeUpdate fires just before the entry is made, so the test will
always be applied.

2) In table design view, select this field, and in the lower pane set the
Required property to Yes. This way Access won't save the record if the box
is left blank, and you don't need any code at all.

3) Set up your Cancel button's event procedure so it undoes any edits. That
way there's no attempt to save. This kind of thing:
Private Sub Command1_Click()
If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name
End Sub

4. If the field's Required property is set, and user has started entering
something in the text box and backspaces it out, Access will still trigger
an error. Teach the user to press <Esc> to undo the text box, or click the
Undo button on the toolbar.

5. It is also possible to create a custom menu/toolbar and put your Cancel
button there instead of on the form.

 

[Hugh self taught]

Hi Allen,

You're on the opposite side of the world from me so timing is a mission.
Thanks for your composite response. I will give some of the options a try on
the weekend & I'm sure I will come right. I thought about the field being
made a required one but sometimes people are daft & send in a form with just
one name on it so I check on first name then on surname & then both so that
at least one name is present. Hence the reason for using the "on exit"

I'll do my usual marking of "helpful" as soon as I've tried your suggestions
or get back to this post if I have a problem.

Many thanks

 

[Hugh self taught]

Just had a quick test of cut & paste my code from the "on exit" of the
control to your suggested beforeUpdate of the form. I see that it only then
triggers when I try to save the record. I just need to get my head around
whether that still suits my needs or not but it's a great new little note in
the back of my overtaxed brain.

 

[Allen Browne]

Yes, that's right: I find it best to let the user flick in'n'out of whatever
text boxes they wish, and run the tests just before saving the record.

That seems to be especially true if you're comparing text boxes e.g.
requiring either FirstName or Surname, but not necessarily both, because you
don't know if they are going to put something in the other box or not. So:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim strMsg As String

If IsNull(Me.Surname) Then
If IsNull(Me.FirstName) Then
Cancel = True
strMsg = "Can't save when both names are blank."
ElseIfIsNull(Me.FirstName) Then
strMsg = "Save with a blank First Name?"
End If
ElseIf IsNull(me.FirstName) Then
strMsg = "Save with a blank First Name?"
End If

If Cancel Then
MsgBox strMsg, vbExclamation, "Invalid data"
Else
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Are you sure?") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
End Sub

 

[Hugh self taught]

Thanks Allen,

This was one of those threads that puts a few things into focus in one's
mind. When one is aware of things but don't quite put them into perspective
plus the fact I've become aware of 2 neat tricks to use.

Cheers for now