me.undo

[hughess7]

Hi all, I am trying to undo a control value if incorrect item is chosen. I
have tried:

If [Activity] = "IA" Or [Activity] = "FA" Then
MsgBox "IA and FA Activities are now Invalid, please reselect an
appropriate Activity"
Me![Activity].Undo
End If

The error message appears ok but the value is not undon (reset to its
original value or blank if new record). I've entered the code in the
afterupdate event of the Activity field. I can't set it to Null because it is
a required field and this produced an error.

Thanks in advance for any help.
Sue

 

[Wayne Morgan]

Is Activity the name of the control, field, or both? Try Me.Activity.Undo.
If that doesn't work and the names of both are the same, try renaming the
control to txtActivity and try again using that name.

 

[hughess7]

They were both Activity. Changed control name to txtactivity and replaced !
with fullstop but still doesn't work?

Thanks in advance for any help.
Sue

Is Activity the name of the control, field, or both? Try Me.Activity.Undo.
If that doesn't work and the names of both are the same, try renaming the
control to txtActivity and try again using that name.

--
Wayne Morgan
MS Access MVP

Hi all, I am trying to undo a control value if incorrect item is chosen. I
have tried:

If [Activity] = "IA" Or [Activity] = "FA" Then
MsgBox "IA and FA Activities are now Invalid, please reselect an
appropriate Activity"
Me![Activity].Undo
End If

The error message appears ok but the value is not undon (reset to its
original value or blank if new record). I've entered the code in the
afterupdate event of the Activity field. I can't set it to Null because it
is
a required field and this produced an error.

Thanks in advance for any help.
Sue

 

[Wayne Morgan]

I just tried this and Undo won't work in the controls AfterUpdate or Exit
event. Instead, use the BeforeUpdate event and Cancel the update if the
value is wrong. You can then leave the user there to choose another value or
Undo.

Private Sub txtActivity_BeforeUpdate(Cancel As Integer)
If Me.txtActivity = "IA" Or Me.txtActivity = "FA" Then
MsgBox "IA and FA Activities are now Invalid, please reselect an
appropriate Activity"
Cancel = True
Me.txtActivity.Undo
End If
End Sub

 

[hughess7]

Thanks a bunch this worked perfectly!

Do you also know how I stop an event from happening if the save failed?
Access saves a record when current record loses focus, I have navigation
buttons on my form for movenext, addnew etc when pressed Acess will try to
save the current record first by performing my code in the beforeupdate of my
form, before the movenext (etc) command. If it fails to save because a match
is found I get a runntime error 'can not move to specified record'. How would
I stop it from trying to perform the movenext (etc) command if the save has
failed to prevent the error occurring?

If RecExists = False Then
SpecName = Me![Specialist].Column(1)
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True
End If

 

[Wayne Morgan]

One way to prevent this is to check for the possibility of the error in the
form's BeforeUpdate event. If a duplicate in a field is likely and having
one will cause a problem, you can use the DLookup() function to check for
that value before you try to commit the record. Again, just as in the
control's BeforeUpdate, you can set Cancel = True to cancel the update.

Another option would be to use the form's Error event. A data error will
probably be returned to this event. Here is an example of handling a
Duplicate Index value when the indexed field was set to No Duplicates.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const DUPLICATE_INDEX = 3022
If DataErr = DUPLICATE_INDEX Then
MsgBox "This exercise is/was previously issued to this trainee. Please" &
vbCrLf & _
"select another exercise from this category or press the close form button."
Response = acDataErrContinue
End If
End Sub


--
Wayne Morgan
MS Access MVP

Thanks a bunch this worked perfectly!

Do you also know how I stop an event from happening if the save failed?
Access saves a record when current record loses focus, I have navigation
buttons on my form for movenext, addnew etc when pressed Acess will try to
save the current record first by performing my code in the beforeupdate of
my
form, before the movenext (etc) command. If it fails to save because a
match
is found I get a runntime error 'can not move to specified record'. How
would
I stop it from trying to perform the movenext (etc) command if the save
has
failed to prevent the error occurring?

If RecExists = False Then
SpecName = Me![Specialist].Column(1)
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True
End If


--
Thanks in advance for any help.
Sue

I just tried this and Undo won't work in the controls AfterUpdate or Exit
event. Instead, use the BeforeUpdate event and Cancel the update if the
value is wrong. You can then leave the user there to choose another value
or
Undo.

Private Sub txtActivity_BeforeUpdate(Cancel As Integer)
If Me.txtActivity = "IA" Or Me.txtActivity = "FA" Then
MsgBox "IA and FA Activities are now Invalid, please reselect an
appropriate Activity"
Cancel = True
Me.txtActivity.Undo
End If
End Sub

 

[Trini Gal]

Wayne,

I'm having the same problem. Below is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varADDRESS As Variant

If Me.NewRecord Then

varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" &
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION & "' and [STREET1] = '" &
Me.STREET1 _
& "'")

If Not IsNull(varADDRESS) Then

If MsgBox("This record already exists." & _
"Do you want to cancel these changes and go to that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS]='" & varADDRESS & "'"
End If

End If

This works fine but for one problem, when I am taken back to the existing
duplicate entry, I get an error "You can't go to the specified record."

I read your responses, but I didn't quite understand. I'm new to all of
this and I think this is a duplicate posting, for which I'm apologizing. I
entered the same question in the forms section. I'm not sure of the proper
etiquette on posting.

Anyways, do you think you can help me?
Thanks

One way to prevent this is to check for the possibility of the error in the
form's BeforeUpdate event. If a duplicate in a field is likely and having
one will cause a problem, you can use the DLookup() function to check for
that value before you try to commit the record. Again, just as in the
control's BeforeUpdate, you can set Cancel = True to cancel the update.

Another option would be to use the form's Error event. A data error will
probably be returned to this event. Here is an example of handling a
Duplicate Index value when the indexed field was set to No Duplicates.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const DUPLICATE_INDEX = 3022
If DataErr = DUPLICATE_INDEX Then
MsgBox "This exercise is/was previously issued to this trainee. Please" &
vbCrLf & _
"select another exercise from this category or press the close form button."
Response = acDataErrContinue
End If
End Sub


--
Wayne Morgan
MS Access MVP

Thanks a bunch this worked perfectly!

Do you also know how I stop an event from happening if the save failed?
Access saves a record when current record loses focus, I have navigation
buttons on my form for movenext, addnew etc when pressed Acess will try to
save the current record first by performing my code in the beforeupdate of
my
form, before the movenext (etc) command. If it fails to save because a
match
is found I get a runntime error 'can not move to specified record'. How
would
I stop it from trying to perform the movenext (etc) command if the save
has
failed to prevent the error occurring?

If RecExists = False Then
SpecName = Me![Specialist].Column(1)
Msg = SpecName & " has an existing Activity on that date, please choose
another date or Person"
MsgBox Msg, vbCritical, "Duplicate Data"
Cancel = True
End If


--
Thanks in advance for any help.
Sue

I just tried this and Undo won't work in the controls AfterUpdate or Exit
event. Instead, use the BeforeUpdate event and Cancel the update if the
value is wrong. You can then leave the user there to choose another value
or
Undo.

Private Sub txtActivity_BeforeUpdate(Cancel As Integer)
If Me.txtActivity = "IA" Or Me.txtActivity = "FA" Then
MsgBox "IA and FA Activities are now Invalid, please reselect an
appropriate Activity"
Cancel = True
Me.txtActivity.Undo
End If
End Sub

--
Wayne Morgan
MS Access MVP


They were both Activity. Changed control name to txtactivity and
replaced
!
with fullstop but still doesn't work?

 

[Wayne Morgan]

I just tried a simple example of what you have and was taken to the
duplicate without any problems. By reading your message, it appears that you
are also going to the duplicate but getting the error after you've gone to
the duplicate record. If so, the trick may be to find what is generating the
error. The easiest answer may just be to "ignore" it. See if this helps:

Me.Undo

On Error Resume Next
'Do this as a test
'if this doesn't remove the error, then the error is
'coming from somewhere else

Me.Recordset.FindFirst "[ADDRESS]='" & varADDRESS & "'"
End If

--
Wayne Morgan
MS Access MVP

Wayne,

I'm having the same problem. Below is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varADDRESS As Variant

If Me.NewRecord Then

varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" &
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION & "' and [STREET1] = '" &
Me.STREET1 _
& "'")

If Not IsNull(varADDRESS) Then

If MsgBox("This record already exists." & _
"Do you want to cancel these changes and go to that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS]='" & varADDRESS & "'"
End If

End If

This works fine but for one problem, when I am taken back to the existing
duplicate entry, I get an error "You can't go to the specified record."

I read your responses, but I didn't quite understand. I'm new to all of
this and I think this is a duplicate posting, for which I'm apologizing.
I
entered the same question in the forms section. I'm not sure of the
proper
etiquette on posting.

 

[Trini Gal]

Wayne,

It does work, I am taken to the duplicate data, and then that when the
message pops up "You can't go to the specified record."

I tried your code. It does not work, I still get the error. Any other
suggestions?

I just tried a simple example of what you have and was taken to the
duplicate without any problems. By reading your message, it appears that you
are also going to the duplicate but getting the error after you've gone to
the duplicate record. If so, the trick may be to find what is generating the
error. The easiest answer may just be to "ignore" it. See if this helps:

Me.Undo

On Error Resume Next
'Do this as a test
'if this doesn't remove the error, then the error is
'coming from somewhere else

Me.Recordset.FindFirst "[ADDRESS]='" & varADDRESS & "'"
End If

--
Wayne Morgan
MS Access MVP

Wayne,

I'm having the same problem. Below is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varADDRESS As Variant

If Me.NewRecord Then

varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" &
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION & "' and [STREET1] = '" &
Me.STREET1 _
& "'")

If Not IsNull(varADDRESS) Then

If MsgBox("This record already exists." & _
"Do you want to cancel these changes and go to that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS]='" & varADDRESS & "'"
End If

End If

This works fine but for one problem, when I am taken back to the existing
duplicate entry, I get an error "You can't go to the specified record."

I read your responses, but I didn't quite understand. I'm new to all of
this and I think this is a duplicate posting, for which I'm apologizing.
I
entered the same question in the forms section. I'm not sure of the
proper
etiquette on posting.

 

[Trini Gal]

Also,

Like I said, I had posted this message before in the forms section and
someone mentioned that I need to use a recordset clone and bookmarks, but I
don't
know how to do it.

You wouldn't by chance know how to do that do you?

I just tried a simple example of what you have and was taken to the
duplicate without any problems. By reading your message, it appears that you
are also going to the duplicate but getting the error after you've gone to
the duplicate record. If so, the trick may be to find what is generating the
error. The easiest answer may just be to "ignore" it. See if this helps:

Me.Undo

On Error Resume Next
'Do this as a test
'if this doesn't remove the error, then the error is
'coming from somewhere else

Me.Recordset.FindFirst "[ADDRESS]='" & varADDRESS & "'"
End If

--
Wayne Morgan
MS Access MVP

Wayne,

I'm having the same problem. Below is my code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varADDRESS As Variant

If Me.NewRecord Then

varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" &
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION & "' and [STREET1] = '" &
Me.STREET1 _
& "'")

If Not IsNull(varADDRESS) Then

If MsgBox("This record already exists." & _
"Do you want to cancel these changes and go to that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS]='" & varADDRESS & "'"
End If

End If

This works fine but for one problem, when I am taken back to the existing
duplicate entry, I get an error "You can't go to the specified record."

I read your responses, but I didn't quite understand. I'm new to all of
this and I think this is a duplicate posting, for which I'm apologizing.
I
entered the same question in the forms section. I'm not sure of the
proper
etiquette on posting.

 

[Wayne Morgan]

You used to have to use a recordset clone and bookmarks, but the new
versions of Access let you address the form's recordset directly. Besides,
when you set the form's bookmark to the clone's bookmark, you're still going
to move to that record and the error will occur. Using the clone has the
advantage of not moving the form to the record it you want to work on that
record but leave the form's display where it is.

Place the following in the form's Error event and see what happens:

MsgBox "The Error Number is " & DataErr

If this comes up, then this is where we can handle the error. Is there any
other code in that form? Perhaps in the Current event?

 

[Trini Gal]

Wayne,

First of all, thanks for explaining Recordset Clone and Bookmarks to me, as
I said I am a total newbie at this VB thing.

Second, that code you gave me to put in the OnError Event, was it supposed
to create a pop up box? If it was, there was no pop up box. There is no
other code in this subform. There is however code in the main form but none
of it is in the OnCurrent Event.

I'm sorry, this is such a headache. Any other ideas?

Thank you so much for your help and patience.

 

[Wayne Morgan]

I would like to see it. Will you zip it and email it to (e-mail address removed)?

 

[Trini Gal]

What exactly do you want to see?

I would like to see it. Will you zip it and email it to (e-mail address removed)?

 

[Wayne Morgan]

The mob file.

 

[Wayne Morgan]

Oops,

I think spell check messed up the last message.

Please zip and send a copy of the mdb file.

 

[Wayne Morgan]

I looked at the code in the file. It appears that after entering the
duplicate record, you are moving to the next new record by clicking a button
you've added to the form. The code in this button uses
DoCmd.GoToRecord,,acNewRec. Yet, you've just canceled the update and told
the form to go to the duplicate record in the BeforeUpdate event. It won't
do both, hence the error. I added a "flag" variable to catch this situation
and skip the attempt to go to the new record.

 

[Trini Gal]

I was unable to download the code...yet again. Maybe you can just paste it
on a reply email or on here?

Thanks.

 

[Trini Gal]

It worked, it worked, it worked....

Thank you so much Wayne, for all your help with this issue. Thanks for your
patience, and time. You are the MAN!!!!!!!!!!!!!!