Access 97 duplicate record flag

[knasir]

I am making modifications to an existing access database where there sometimes needs to be a duplicate record in a field, but I want the user to see a popup box notifying them that they are about to duplicate a record and allowing user to continue. How do I do this?

 

[bitbrain]

As far as your table is concerned, dups are allowed so the field cannot be a primary key and, if indexed, must allow dups.<br>
<br>
Assuming you are using a form for data entry, add code to the form's BeforeInsert event that checks the table for the value. You will have to work with VBA code & data objects. The following example should point you in the right direction:<br>
<br>
<br>
Dim dbs As Database, rsProcStat As Recordset<br>
Dim strSQL As String<br>
<br>
Set dbs = CurrentDb<br>
strSQL = "SELECT * FROM tblProcessStats " _<br>
& "WHERE ((ProcName)=""" & strMsg & """)"<br>
Set rsProcStat = dbs.OpenRecordset(strSQL)<br>
<br>
If rsProcStat.EOF Then<br>
Else<br>
intReturn = MsgBox "Duplicate value, continue?", vbOKCancel<br>
If intReturn = vbCancel Then<br>
Cancel = True<br>
exit Sub<br>
End If<br>
End If<br>
<br>
Set rsProcStat = Nothing<br>
Set dbs = Nothing