Clearing a duplicate record

[Guest]

The error duplicate error message appears if a record with the monthEnd and
FundName already exist in the SQL table. How do I clear the record so the
user can start entering data again?

strsql = "SELECT Count(*) AS CountOfMonthEnd FROM dbo_tbl_MonthlyData " & _
" WHERE (((dbo_tbl_MonthlyData.FundName)= " & Chr(34) & cboFundName& Chr(34)
& ") AND ((dbo_tbl_MonthlyData.MonthEnd)=#" & cboMonthEnd & "#));"

Set db = CurrentDb
Set rst = db.OpenRecordset(strsql)

If rst.Fields(0).Value > 0 Then
strMsg = msgbox("This Fund and Date already Exist", vbOKOnly +
vbInformation, "Duplicates")

Me.Undo

 

[George Nicholson]

Dim iResponse as Integer
................

If rst.Fields(0).Value > 0 Then
iResponse = Msgbox("This Fund and Date already Exist. Click OK to clear
record, Cancel to edit record.", vbOKCancel +
vbInformation, "Duplicates")

If iResponse = vbOK Then
Me.Undo 'Clears active control
Me.Undo 'Clears form
Else
' Do nothing: returning to record
End If
' Cancel update? Not sure where you are calling this
Else
' ?Do something when there isn't a duplicate, or just let Update
continue?
End If

Set rst = Nothing
Set db = Nothing


HTH

 

[Guest]

George,

Your code is working great for the "Duplicate" message and OK and Cancel,
however, I'm still getting the Microsoft Access Error message box "Violation
of Key Constraint" for duplicates after I press OK or Cancel. How can I
supress the system message?

 

[George Nicholson]

Where is the code located? If it's in Form_BeforeUpdate event then replace
' Cancel update? Not sure where you are calling this
in my code with
Cancel = True

HTH,