Clearing a duplicate record

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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?
 
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,
 
Back
Top