Intercept Access's "duplicate value" error message

[RipperT]

The following code works fine until the user clicks No in the msgBox. When
No is clicked, the Access error message about duplicate values fires before
the Undo command. I would like it to just return the field to the previous
value if the user clicks No. I tried the code in the before update event,
but that didn't solve the problem. Any help will be appreciated.


Private Sub InmateId After_Update()
Dim strSQL as String
If Not IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] = '" &
Me.InmateId & "'")) Then
If MsgBox("This value already exists in the table" & vbNewLine _
& "Click yes to move the value to this record. Otherwise click No.",
vbInformation + vbYesNo, "Change Lock") = vbYes Then
strSQL = "UPDATE tblLockAllocations SET InmateId = NULL _
& " WHERE InmateId = '" & Me.InmateId & "';"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Lock Change Successful"
Else: Me.InmateId.Undo
Me.InmateId.SetFocus
End if
Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Lock Change Successful."
Me.InmateId.SetFocus
End if
End Sub

 

[RuralGuy]

The following code works fine until the user clicks No in the msgBox.
When No is clicked, the Access error message about duplicate values
fires before the Undo command. I would like it to just return the
field to the previous value if the user clicks No. I tried the code
in the before update event, but that didn't solve the problem. Any
help will be appreciated.


Private Sub InmateId After_Update()
Dim strSQL as String
If Not IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] =
'" & Me.InmateId & "'")) Then
If MsgBox("This value already exists in the table" & vbNewLine _
& "Click yes to move the value to this record. Otherwise click
No.",
vbInformation + vbYesNo, "Change Lock") = vbYes Then
strSQL = "UPDATE tblLockAllocations SET InmateId = NULL _
& " WHERE InmateId = '" & Me.InmateId & "';"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Lock Change Successful"
Else: Me.InmateId.Undo
Me.InmateId.SetFocus
End if
Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70 MsgBox "Lock Change Successful."
Me.InmateId.SetFocus
End if
End Sub

Hi RipperT,

You may want to examine the BeforeUpdate event and the Cancel feature.
It looks like a better place for a modified version of your existing
code.

Cancel = True holds the cursor in the current control which it looks like
you are doing if there is a duplicate.

HTH

 

[RuralGuy]

I guess it would help if I would read your post more carefully! What code
did you have it the BeforeUpdate event. What you posted would have to be
modified quite a bit to do what you want.

 

[RuralGuy]

By the way, I do not see where the Update is being executed. Am I just
blind *and* careless today?

 

[RipperT]

acSave Record is where the update occurs. This is after the msgBox warns
that the value already exists in another record and clicking Yes will remove
it via the SQL execution and insert it into the new record. (Not insert it
actually; it's already there, but the new record will be saved with the new
value because the SQL execution removed it from the first record and thus,
no dupes.). This portion of the code executes wonderfully; it's when the
user clicks No in response to the message box, then the "duplicate values"
error message fires before the undo command. I would like the undo to fire
first, intercepting the "duplicate values" error message.

Thanx for the help,

Rip

 

[RuralGuy]

acSave Record is where the update occurs. This is after the msgBox
warns that the value already exists in another record and clicking Yes
will remove it via the SQL execution and insert it into the new
record. (Not insert it actually; it's already there, but the new
record will be saved with the new value because the SQL execution
removed it from the first record and thus, no dupes.). This portion of
the code executes wonderfully; it's when the user clicks No in
response to the message box, then the "duplicate values" error message
fires before the undo command. I would like the undo to fire first,
intercepting the "duplicate values" error message.

Thanx for the help,

Rip

I'm sorry Rip but without one of the two following lines in your code
somewhere, tblLockAllocations![InmateId] is not set to NULL as you
expect.

1) CurrentDb.Execute strSQL, dbFailOnError
2) DoCmd.RunSQL strSQL, False

Is this form bound to tblLockAllocations and is the InmateId control
bound to the tblLockAllocations![InmateId] field?

If so, it explains why the code appears to work some of the time.

 

[RuralGuy]

Rip,

I should have asked you to tell me a little more about this form and what
you are doing?

Is this on a "new" record adding some kind of information? Or is it on an
existing record modifying information?

Are we just talking about one table or does this form deal with more than
one?

What function are you performing with this form?

Any additional information will help me better understand the flow of the
logic.

Thanks!

 

[RipperT]

My humblest apologies. I do have the Execute line included with my code, I
mistakenly omitted it in my post.
I am using one table and one form. Each record in tblLockAllocations
represents each bunk in the prison and the InmateId field shows which inmate
is living in which bunk at a given time. We move inmates around frequently.
This bit of code removes the inmate ID value from the InmateId field
(lookup) of one record in tblLockAllocations and places it in another.

As I said, the automatic deletion of the value from InmateId field in one
record and the insertion of the same value in another record is working. The
problem is when the user clicks No in the first msgBox. The Access
"duplicate values" error message appears. The code states that the typed
value in the InmateId field should just be 'undone' if the user clicks No.
How can I get it to do that? The code reappears below with the execute line:

Private Sub InmateId After_Update()
Dim strSQL as String
If Not IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] = '" &
Me.InmateId & "'")) Then
If MsgBox("This inmate already exists in the table" & vbNewLine _
& "Click Yes to move this inmate from old bunk to this new bunk.
Otherwise click No.",
vbInformation + vbYesNo, "Change Lock") = vbYes Then
strSQL = "UPDATE tblLockAllocations SET InmateId = NULL _
& " WHERE InmateId = '" & Me.InmateId & "';"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Lock Change Successful"
Else: Me.InmateId.Undo
Me.InmateId.SetFocus
End if
Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Lock Change Successful."
Me.InmateId.SetFocus
End if
End Sub

Thanx again!

 

[RuralGuy]

My humblest apologies. I do have the Execute line included with my code,
I mistakenly omitted it in my post.
I am using one table and one form. Each record in tblLockAllocations
represents each bunk in the prison and the InmateId field shows which
inmate is living in which bunk at a given time. We move inmates around
frequently. This bit of code removes the inmate ID value from the
InmateId field (lookup) of one record in tblLockAllocations and places
it in another.

As I said, the automatic deletion of the value from InmateId field in
one record and the insertion of the same value in another record is
working. The problem is when the user clicks No in the first msgBox. The
Access "duplicate values" error message appears. The code states that
the typed value in the InmateId field should just be 'undone' if the
user clicks No. How can I get it to do that? The code reappears below
with the execute line:

Private Sub InmateId After_Update()
Dim strSQL as String
If Not IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] = '"
& Me.InmateId & "'")) Then
If MsgBox("This inmate already exists in the table" & vbNewLine _
& "Click Yes to move this inmate from old bunk to this new bunk.
Otherwise click No.",
vbInformation + vbYesNo, "Change Lock") = vbYes Then
strSQL = "UPDATE tblLockAllocations SET InmateId = NULL _
& " WHERE InmateId = '" & Me.InmateId & "';"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Lock Change Successful"
Else:
Me.InmateId.Undo
Me.InmateId.SetFocus
End if
Else:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 >

MsgBox "Lock Change Successful."

Me.InmateId.SetFocus
End if
End Sub

Thanx again!

Hi Rip,

OK, that makes a little more sense. The following is right out of Help!

"This method must be applied before the form or control is updated. You may
want to include this method in a form's BeforeUpdate event or in a control's
Change event."

The short of it is if you want to use Undo it needs to be in the
BeforeUpdate. AfterUpdate is simply too late in the process.

The good news is we can make our own Undo if we haven't moved to another
record yet.

Change the: Me.InmateId.Undo
To: Me.InmateId = Me.InmateId.OldValue

I think that's all that is needed.

HTH

RuralGuy ... at another one of my computers.

 

[RipperT]

I had tried that, but I thought Me.InmateId.OldValue was all that was
needed. When it didn't work, I moved onto something else. Lucky for this
newsgroup, I am scheduled to take a beginning VB course at the local
community college in the fall

Thanx for the help.

Rip


--
Ripper T Smith
rippertsmith<nospam>@comcast.net

My humblest apologies. I do have the Execute line included with my code,
I mistakenly omitted it in my post.
I am using one table and one form. Each record in tblLockAllocations
represents each bunk in the prison and the InmateId field shows which
inmate is living in which bunk at a given time. We move inmates around
frequently. This bit of code removes the inmate ID value from the
InmateId field (lookup) of one record in tblLockAllocations and places
it in another.

As I said, the automatic deletion of the value from InmateId field in
one record and the insertion of the same value in another record is
working. The problem is when the user clicks No in the first msgBox. The
Access "duplicate values" error message appears. The code states that
the typed value in the InmateId field should just be 'undone' if the
user clicks No. How can I get it to do that? The code reappears below
with the execute line:

Private Sub InmateId After_Update()
Dim strSQL as String
If Not IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] = '"
& Me.InmateId & "'")) Then
If MsgBox("This inmate already exists in the table" & vbNewLine _
& "Click Yes to move this inmate from old bunk to this new bunk.
Otherwise click No.",
vbInformation + vbYesNo, "Change Lock") = vbYes Then
strSQL = "UPDATE tblLockAllocations SET InmateId = NULL _
& " WHERE InmateId = '" & Me.InmateId & "';"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Lock Change Successful"
Else:
Me.InmateId.Undo
Me.InmateId.SetFocus
End if
Else:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

MsgBox "Lock Change Successful."

Me.InmateId.SetFocus
End if
End Sub

Thanx again!

Hi Rip,

OK, that makes a little more sense. The following is right out of Help!

"This method must be applied before the form or control is updated. You
may
want to include this method in a form's BeforeUpdate event or in a
control's
Change event."

The short of it is if you want to use Undo it needs to be in the
BeforeUpdate. AfterUpdate is simply too late in the process.

The good news is we can make our own Undo if we haven't moved to another
record yet.

Change the: Me.InmateId.Undo
To: Me.InmateId = Me.InmateId.OldValue

I think that's all that is needed.

HTH

RuralGuy ... at another one of my computers.

 

[RuralGuy]

My humblest apologies. I do have the Execute line included with my
code, I mistakenly omitted it in my post.
I am using one table and one form. Each record in tblLockAllocations
represents each bunk in the prison and the InmateId field shows which
inmate is living in which bunk at a given time. We move inmates around
frequently. This bit of code removes the inmate ID value from the
InmateId field (lookup) of one record in tblLockAllocations and places
it in another.

As I said, the automatic deletion of the value from InmateId field in
one record and the insertion of the same value in another record is
working. The problem is when the user clicks No in the first msgBox.
The Access "duplicate values" error message appears. The code states
that the typed value in the InmateId field should just be 'undone' if
the user clicks No. How can I get it to do that? The code reappears
below with the execute line:

Hi Rip,

The more I think about it the more convinced I am that this simply needs to
be done right as Access provides. I'm almost certain by the time we get to
AfterUpdate with the duplicate InmateId we have the error. We need to
eliminate the duplicate in the BeforeUpdate event; either by setting the
existing bunk record to Null or undoing the control.

Here's your code modified for the BeforeUpdate event:

Private Sub InmateId_BeforeUpdate(Cancel As Integer)
Dim strSQL as String
If Not IsNull(DLookup("InmateId", "tblLockAllocations", _
"[InmateId] = '" & Me.InmateId & "'")) Then
If MsgBox("This inmate already exists in the table" & vbNewLine & _
"Click Yes to move this inmate from old bunk to this new bunk. " & _
"Otherwise click No.", _
vbInformation + vbYesNo, "Change Lock") = vbYes Then
strSQL = "UPDATE tblLockAllocations SET InmateId = NULL " & _
"WHERE InmateId = '" & Me.InmateId & "';"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "Lock Change Successful"
Else
'-- This will perform the Undo *and* hold the Focus here
Cancel = True
End If
Else
' Nothing to do!
' Let Access do all of the normal saving in do course!
End If

End Sub

I'm pretty sure this code will do what you desire except for maybe a typo.
Let me know how it works out. As you can see it is less complicated.

 

[RuralGuy]

I had tried that, but I thought Me.InmateId.OldValue was all that was
needed. When it didn't work, I moved onto something else. Lucky for
this newsgroup, I am scheduled to take a beginning VB course at the
local community college in the fall

Thanx for the help.

Rip

You are certainly welcome and it has been fun. Did that actually solve the
problem without an error? I thought for sure it was too late and we
already had the conflict.

At any rate, I'm sure everyone reading this news group enjoyed this thread
and some gained some new insite into Access.