Key Violation message

[SelfTaught]

While attempting to insert a duplicate record into an indexed field using the
DoCmd.OpenForm method after clicking on a command button I, of course,
receive a key violation warning. Based on that warning, I would like to open
a form that lists all of the records rather than attempting to insert that
duplicate record. So in other words, I would like to test for a duplicate
record and then take appropriate action based on if a duplicate is found or
not found. What is the best way to accomplish this? This is my current code
and I think that I've gone about this the wrong way from the beginning:

Private Sub Command26_Click()
On Error GoTo Err_Command26_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stLinkCriteria As Date
Dim stDocName3 As String
Dim DataErr As Integer

stDocName = "CurrentPeriodEntry"
stDocName2 = "Main Menu"
stDocName3 = "EditPeriodEntry"
stLinkCriteria = Forms![Main Menu]!Text28

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, stDocName2

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
If DataErr = 3022 Then
DoCmd.OpenForm stDocName2
DoCmd.Close acForm, stDocName2
Else
MsgBox Err.Description
End If

End Sub

 

[Tom Wimpernads]

uh if you used SQL Server then you could utilize triggers

this would pretty much do EXACTLY what you're looking for

 

[SelfTaught]

Tom,

Thanks for the reply but unfortunately I'm not able to use SQL Server in
this situation. Also, I tried just trapping for error 3022 and creating a
custom message but I'm still getting the warning message, it's like I'm not
getting that particular error message but something else.

uh if you used SQL Server then you could utilize triggers

this would pretty much do EXACTLY what you're looking for

While attempting to insert a duplicate record into an indexed field using
the
DoCmd.OpenForm method after clicking on a command button I, of course,
receive a key violation warning. Based on that warning, I would like to
open
a form that lists all of the records rather than attempting to insert that
duplicate record. So in other words, I would like to test for a duplicate
record and then take appropriate action based on if a duplicate is found
or
not found. What is the best way to accomplish this? This is my current
code
and I think that I've gone about this the wrong way from the beginning:

Private Sub Command26_Click()
On Error GoTo Err_Command26_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stLinkCriteria As Date
Dim stDocName3 As String
Dim DataErr As Integer

stDocName = "CurrentPeriodEntry"
stDocName2 = "Main Menu"
stDocName3 = "EditPeriodEntry"
stLinkCriteria = Forms![Main Menu]!Text28

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, stDocName2

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
If DataErr = 3022 Then
DoCmd.OpenForm stDocName2
DoCmd.Close acForm, stDocName2
Else
MsgBox Err.Description
End If

End Sub

 

[John W. Vinson]

Tom,

Thanks for the reply but unfortunately I'm not able to use SQL Server in
this situation. Also, I tried just trapping for error 3022 and creating a
custom message but I'm still getting the warning message, it's like I'm not
getting that particular error message but something else.

"Tom" is actually Aaron Kempf, who posts messages denigrating Access mdb files
and proposing ADPs with SQL/Server as the only solution to all problems; he's
lately been posting under a variety of false names. Take his advice with that
in mind.

John W. Vinson [MVP]

 

[SelfTaught]

John,

Thanks, I will keep that in mind especially since his advice doesn't help me
because I'm not able to create an ADP with a SQL server in this situation.
This MDB has to be able to be instaled on individual desktops around our
organization, each with similar but slightly different designs and structures
and each location may or may not have a network established and may or may
not have a server available. Thi is what lead me to create an MDB rather than
a ADP.

 

[John W. Vinson]

So in other words, I would like to test for a duplicate
record and then take appropriate action based on if a duplicate is found or
not found. What is the best way to accomplish this?

I'd suggest just using DLookUp to search for the value in the BeforeUpdate
event of an appropriate form control. If DLookUp returns NULL there's no
match, otherwise there is and you can warn the user and cancel the update.

John W. Vinson [MVP]