Hi, i am new to Access
I have a form based on a query and have written code to display a msgbox if a duplicate entry is inputted in the NHS_Number field in the form. I have also added code "me.undo" to clear the form so that it is not saved. I am looking for a piece of code that will display the duplicate record. Here is my code thus far:
Private Sub NHs_Number_BeforeUpdate(Cancel As Integer)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim x As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Info 4 Access to medical records M", dbOpenTable, dbReadOnly)
With rst
For x = 1 To rst.RecordCount
If rst![NHs_Number] = Forms![FRM Info 4 Access to medical records M]![NHS Number] Then
Me.Undo
MsgBox "duplicate NHS NUmber"
End If
.MoveNext
Next x
.Close
End With
Set rst = Nothing
Set dbs = Nothing
End Sub
Any help will be greatly appreciated!!
I have a form based on a query and have written code to display a msgbox if a duplicate entry is inputted in the NHS_Number field in the form. I have also added code "me.undo" to clear the form so that it is not saved. I am looking for a piece of code that will display the duplicate record. Here is my code thus far:
Private Sub NHs_Number_BeforeUpdate(Cancel As Integer)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim x As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Info 4 Access to medical records M", dbOpenTable, dbReadOnly)
With rst
For x = 1 To rst.RecordCount
If rst![NHs_Number] = Forms![FRM Info 4 Access to medical records M]![NHS Number] Then
Me.Undo
MsgBox "duplicate NHS NUmber"
End If
.MoveNext
Next x
.Close
End With
Set rst = Nothing
Set dbs = Nothing
End Sub
Any help will be greatly appreciated!!