No Dup's index not giving an error

[Junior1544]

I have an index that doesn't allow dup's. When one of my users enters a record where that field is dupp'd, the record just wont save. It wont give any error to tell the user what's wrong... i've gotten a short way around this for the moment, but i still need it to give an error... any help??

--James Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[grtammi]

Junior:

I've ran into this problem also...the way I solved the issue was to use the following code in the BeforeUpdate event of the control:

Private Sub txtInfo_BeforeUpdate(Cancel As Integer)

Dim dbs As Database, rst As Recordset, strTable As String

Set dbs = CurrentDb
strTable = <enter name of source table here>
Set rst = dbs.OpenRecordset(strTable, dbOpenSnapshot)

With rst
'check for records first
If .RecordCount <> 0 Then
Exit Sub 'no duplicates if there are no records
Else
.FindFirst txtInfo
If .NoMatch = False Then
'Value already exists in database; undo changes
MsgBox &quot;This value is already contained in the database. Please re-enter the value.&quot;
txtInfo.Undo
Exit Sub
Else
'do nothing
End If
End If
End With

'clear variables
Set strTable = Nothing
Set rst = Nothing
Set dbs = Nothing

End Sub