How to check duplicate records

[wongray]

Hello,

I need help.

I have a form and I would like to do a check when I enter a data into the field and once I press enter into the field it will check whether is duplicate or not.

currently I have a form with a subform and when I enter the data into the field it does not give me an error till I goto the subform, which is not good.

Please advise how to fix this.

Many Many thanks

[dima69]

Hello,

I need help.

I have a form and I would like to do a check when I enter a data into the field and once I press enter into the field it will check whether is duplicate or not.

currently I have a form with a subform and when I enter the data into the field it does not give me an error till I goto the subform, which is not good.

Please advise how to fix this.

Many Many thanks

Try this (substitue your names). DAO reference required.

Private Sub txtNewData_BeforeUpdate(Cancel As Integer)
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("MyDataTable", dbOpenSnapshot)
rs.FindFirst "txtDataField = """ & Me![txtNewData] & """"
If Not rs.NoMatch Then
    Cancel = True
    MsgBox "Duplicate Value"
End If
End Sub

[wongray]

Try this (substitue your names). DAO reference required.

Private Sub txtNewData_BeforeUpdate(Cancel As Integer)
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("MyDataTable", dbOpenSnapshot)
rs.FindFirst "txtDataField = """ & Me![txtNewData] & """"
If Not rs.NoMatch Then
    Cancel = True
    MsgBox "Duplicate Value"
End If
End Sub

Thank you it works perfectly

[euflorin]

Thank you very much, it works for me also!