Code preventing duplicate entry is preventing edits to the existing records

[Hrithika]

I have a save button with the following code in the on click event of the button. It does 50 percent of what I want which is to prevent duplicate entry into the database. However, if I try to edit an existing record, this codes prevents saving of the record. What need to be changed in this code to allow editing of an existing record. Please advise.

Private Sub SaveRecord_Click()
Dim val As Integer
If Me.CaseNumber <> "" And Me.HearingDate <> "" Then
' If Len(Me.Title & vbNullString) <> 0 And Len(Me.StartDate & vbNullString) <> 0 And Len(Me.EndDate & vbNullString) <> 0 Then
' If DCount("*", "tblLeaveRequest", "[Title]= '" & [Title] & "' And [StartDate] = #" & [StartDate] & "# And [EndDate] = #" & [EndDate] & "# ") > 0 Then
If DCount("*", "HearingTranslation", "[CaseNumber]= '" & [CaseNumber] & "' And [HearingDate] = #" & [HearingDate] & "# ") > 0 Then
MsgBox "Record already exists."
val = val + 1
Dim rs As Object
Dim VarRecord As String
VarRecord = DLookup("[ID]", "HearingTranslation", "[CaseNumber]= '" & [CaseNumber] & "' And [HearingDate] = #" & [HearingDate] & "# And [ID]<> " & [ID] & " ")
Cancel = True
Me.Undo
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & VarRecord
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End If

End Sub

 

[Solo712]

I have a save button with the following code in the on click event of the button. It does 50 percent of what I want which is to prevent duplicate entry into the database. However, if I try to edit an existing record, this codes prevents saving of the record. What need to be changed in this code to allow editing of an existing record. Please advise.

Private Sub SaveRecord_Click()
Dim val As Integer
If Me.CaseNumber <> "" And Me.HearingDate <> "" Then
' If Len(Me.Title & vbNullString) <> 0 And Len(Me.StartDate & vbNullString) <> 0 And Len(Me.EndDate & vbNullString) <> 0 Then
' If DCount("*", "tblLeaveRequest", "[Title]= '" & [Title] & "' And [StartDate] = #" & [StartDate] & "# And [EndDate] = #" & [EndDate] & "# ") > 0 Then
If DCount("*", "HearingTranslation", "[CaseNumber]= '" & [CaseNumber] & "' And [HearingDate] = #" & [HearingDate] & "# ") > 0 Then
MsgBox "Record already exists."
val = val + 1
Dim rs As Object
Dim VarRecord As String
VarRecord = DLookup("[ID]", "HearingTranslation", "[CaseNumber]= '" & [CaseNumber] & "' And [HearingDate] = #" & [HearingDate] & "# And [ID]<> " & [ID] & " ")
Cancel = True
Me.Undo
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & VarRecord
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End If

End Sub

You need to restrict the query to new records , i.e.

If Me.NewRecord Then ...
 
' your existing code
 
Endif

This will ignore existing records.

Best,
Jiri

 

[Hrithika]

Thanks Jiri. Sometimes easy things just don't come to mind.

 

[gemma-the-husky]

it might not just be that though

is it possible that an edit of a previously acceptable record could lead to a rejection because the particular change is not allowed, as it introduces a duplicate.

if so then you probably need a general "error event" to intercept the "changes you have made were not successful" error. (possibly 3422 from memory or something similar for a duplicate key) and replace it with something more user-friendly.

 

[Solo712]

Thanks Jiri. Sometimes easy things just don't come to mind.

So it is. You are most certainly welcome.

 

[Solo712]

it might not just be that though

is it possible that an edit of a previously acceptable record could lead to a rejection because the particular change is not allowed, as it introduces a duplicate.

Fair comment but in most cases fields establishing the uniqueness of the record should be locked/disabled during edits of existing rows.

Best,
Jiri