Clearing a textbox after Cancelling

[Mark A. Sam]

Hello,

I have an unbound control on a form with the following code:

Private Sub FullName_BeforeUpdate(Cancel As Integer)

With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full Name
or cancel!"
Cancel = True
FullName.Undo
End If

End With

End Sub

After the Cancel I want the textbox cleared. The Undo of the textbox
doesn't accomplish this.

Thank you for any help and God Bless,

Mark A. Sam

 

[BruceM via AccessMonster.com]

I think you cannot use Undo for an unbound control. As a workaround you can
set the Tag property of the control to a zero-length string, and it the
Before Update event rather than Me.TextBoxName.Undo you can use Me.
TextBoxName = Me.TextBoxName.Tag

You can also use this idea to set the Tag property in the After Update event
for the control, and revert to the Tag value (the previous search value) in
the Before Update event if the search is not successful.

Hello,

I have an unbound control on a form with the following code:

Private Sub FullName_BeforeUpdate(Cancel As Integer)

With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full Name
or cancel!"
Cancel = True
FullName.Undo
End If

End With

End Sub

After the Cancel I want the textbox cleared. The Undo of the textbox
doesn't accomplish this.

Thank you for any help and God Bless,

Mark A. Sam

 

[Mark A. Sam]

Bruce,

Using TextBoxName = Me.TextBoxName.Tag doesn't work. I had already tried
setting the value to Null, and got the same error. Using the AfterUpdate
isn't useful becuase the focus goes to the next field. The undo method
highlights the control, so I'll go with that. It allows tabbing to the next
control, keeping the incorrect value, but I have another check on the Add
button. Not the best way to go, but this form will be seldom used.

Thank you and God Bless,

Mark

I think you cannot use Undo for an unbound control. As a workaround you
can
set the Tag property of the control to a zero-length string, and it the
Before Update event rather than Me.TextBoxName.Undo you can use Me.
TextBoxName = Me.TextBoxName.Tag

You can also use this idea to set the Tag property in the After Update
event
for the control, and revert to the Tag value (the previous search value)
in
the Before Update event if the search is not successful.

Hello,

I have an unbound control on a form with the following code:

Private Sub FullName_BeforeUpdate(Cancel As Integer)

With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full
Name
or cancel!"
Cancel = True
FullName.Undo
End If

End With

End Sub

After the Cancel I want the textbox cleared. The Undo of the textbox
doesn't accomplish this.

Thank you for any help and God Bless,

Mark A. Sam

 

[J_Goddard via AccessMonster.com]

Hi -

You can use the After Update event of the Textbox, using the DoCmd.
GotoControl method. Using just DoCmd.gotocontrol "Textbox", won't work, as
you have seen, but if you goto some OTHER control, then go back to Textbox,
it works! So, you get something like this:

Private Sub FullName_AfterUpdate
With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full Name.
"
doCmd.GotoControl "SomeOtherControl"
doCmd.GotoControl "FullName"
End If
me!FullName=""
End With
End Sub

A bit of a kluge - but it works!

John

Bruce,

Using TextBoxName = Me.TextBoxName.Tag doesn't work. I had already tried
setting the value to Null, and got the same error. Using the AfterUpdate
isn't useful becuase the focus goes to the next field. The undo method
highlights the control, so I'll go with that. It allows tabbing to the next
control, keeping the incorrect value, but I have another check on the Add
button. Not the best way to go, but this form will be seldom used.

Thank you and God Bless,

Mark

I think you cannot use Undo for an unbound control. As a workaround you
can

[quoted text clipped - 34 lines]

 

[J_Goddard via AccessMonster.com]

Oops - I have an error in the code - it should be like this:

Private Sub FullName_AfterUpdate
With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full Name.
"
doCmd.GotoControl "SomeOtherControl"
doCmd.GotoControl "FullName"
me!FullName=""
End If
End With
End Sub

with me!FullName="" inside the IF....

Sorry 'bout that!

John

Hi -

You can use the After Update event of the Textbox, using the DoCmd.
GotoControl method. Using just DoCmd.gotocontrol "Textbox", won't work, as
you have seen, but if you goto some OTHER control, then go back to Textbox,
it works! So, you get something like this:

Private Sub FullName_AfterUpdate
With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full Name.
"
doCmd.GotoControl "SomeOtherControl"
doCmd.GotoControl "FullName"
End If
me!FullName=""
End With
End Sub

A bit of a kluge - but it works!

John

Bruce,

[quoted text clipped - 14 lines]

 

[Mark A. Sam]

John,

I tried both .SetFocus and .GoToControl and neither worked. The .SetFocus
gave a message that I need to save the field and .GoToControl said that the
Action or Method was not available at this time.

God Bless,

Mark

Oops - I have an error in the code - it should be like this:

Private Sub FullName_AfterUpdate
With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full
Name.
"
doCmd.GotoControl "SomeOtherControl"
doCmd.GotoControl "FullName"
me!FullName=""
End If
End With
End Sub

with me!FullName="" inside the IF....

Sorry 'bout that!

John

Hi -

You can use the After Update event of the Textbox, using the DoCmd.
GotoControl method. Using just DoCmd.gotocontrol "Textbox", won't work,
as
you have seen, but if you goto some OTHER control, then go back to
Textbox,
it works! So, you get something like this:

Private Sub FullName_AfterUpdate
With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full
Name.
"
doCmd.GotoControl "SomeOtherControl"
doCmd.GotoControl "FullName"
End If
me!FullName=""
End With
End Sub

A bit of a kluge - but it works!

John

Bruce,

[quoted text clipped - 14 lines]

Mark A. Sam

 

[BruceM via AccessMonster.com]

I knew I had made this work in the past, so I did some checking. In my
situation I had a series of Search text boxes and combo boxes to set up the
filter. After entering values in several of the Search controls, a command
button ran code to combine the values into a filter string. If no matching
record was found, the controls were reset to their Tag value. More on that
in a a moment.

What I discovered in a test is that I cannot reset a control in its Before
Update event. However, if I execute the code in a command button click event,
or the control's After Update event for that matter, then I can reset the
text box.

Back to the tags, say I have two unbound controls, a combo box and a text box,
with these values entered:

Acme Company
December 2009

I search for records by clicking a Search command button and running code
along the lines of yours. There are some records, so I set the Tag property
of the controls to Acme Company and December 2009 (actually, it would have
been the CompanyID for Acme Company, but the point remains the same).

The next search is for Beta Company, November 2009. There are no records, so
the controls are reset in the command button click event to their Tag values,
so that they show the values of the last successful search.

I know you said the After Update event will not work, but I do not understand
the reasoning. Have you tried running the code in the After Update event,
except without Cancel = True, and with
Me.FullName = ""
instead of
Me.FullName.Undo
if the search returns nothing?

Alternatively, you can reset FullName in the Enter or Got Focus event of next
control (the one to which the focus goes). It seems you can reset it almost
anywhere except the Before Update event. I expect there is a way of doing
this that will work within the context of how the form works.

Bruce,

Using TextBoxName = Me.TextBoxName.Tag doesn't work. I had already tried
setting the value to Null, and got the same error. Using the AfterUpdate
isn't useful becuase the focus goes to the next field. The undo method
highlights the control, so I'll go with that. It allows tabbing to the next
control, keeping the incorrect value, but I have another check on the Add
button. Not the best way to go, but this form will be seldom used.

Thank you and God Bless,

Mark

I think you cannot use Undo for an unbound control. As a workaround you
can

[quoted text clipped - 34 lines]

 

[BruceM via AccessMonster.com]

I think you would have needed to reset the control to "" before returning to
it. I would probably have used Me.ControlName.SetFocus instead of
GoToControl, but I don't think that would make a difference in this case, as
long as you reset the control to "" while the focus is not on it.

John,

I tried both .SetFocus and .GoToControl and neither worked. The .SetFocus
gave a message that I need to save the field and .GoToControl said that the
Action or Method was not available at this time.

God Bless,

Mark

Oops - I have an error in the code - it should be like this:

[quoted text clipped - 50 lines]

 

[Mark A. Sam]

This worked. It gave an error, which I handled with Resume Next.

Private Sub FullName_BeforeUpdate(Cancel As Integer)
On Error Resume Next

With Me.RecordsetClone
.FindFirst "[FullName] = '" & Me.ActiveControl & "'"
If Not .NoMatch Then
MsgBox Me.ActiveControl & " is already in use. Choose another Full Name
or cancel!"
Cancel = True
FullName.Text = ""
End If

End With

End Sub