Delete record if duplicate

[rubintheyid]

Hi, I am trying to build a form to enter invoice details. I want to ensure that no duplicates are entered so have written the below. This should identify if its a duplicate and if so a message box appears and gives the option to delete if No is pressed ammendments can be made if YES is selected I need the reecord to be deleted. Its the YES i can not get to work. Any help appreciated. Here is the script:



Option Compare Database

Private Sub InvoiceNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo err_InvoiceNumber_BeforeUpdate


Dim strCheckSQL As String
Dim strCP As String
Dim strIN As String
Dim rstCheck As Recordset
Dim intTest As Integer

strCP = Me.cboContentProvider
strIN = Me.InvoiceNumber

'This will assign SQL code to variable strCheckSQL
strCheckSQL = "SELECT ContentProvider, InvoiceNumber FROM PayableRegister WHERE (((ContentProvider)=""" & strCP & """) AND ((InvoiceNumber)=""" & strIN & """));"


Set rstCheck = CurrentDb.OpenRecordset(strCheckSQL)

If rstCheck.RecordCount <> 0 Then

Response = MsgBox("Invoice number already exists for CP. Do you want to delete the record?", vbYesNo, "Duplication Error")
If Response = vbYes Then ' User chose Yes.


Else ' User chose No.
Cancel = True
Me.InvoiceNumber.Undo
End If

End If

rstCheck.Close


exit_InvoiceNumber_BeforeUpdate:
Exit Sub


err_InvoiceNumber_BeforeUpdate:
MsgBox Err.Description
Resume exit_InvoiceNumber_BeforeUpdate

End Sub

[maximus]

Your Data entry form, is it Bound or an Unbound Form. Please make that clear.

[rubintheyid]

Hi Maximus,

the form is unbound.

Cheers

[maximus]

you say unbound then I will assume that you are using sql to append data to your table/tables. now if it is a bound form once you type the data onto a Field on the form a new entry is created so you have to delete to cancel it. but if it is unbound then you can just stop the execution on the sql and data is not entered into the table. Now bear with me, do you have another button on the form to enter data. if yes please post the code.

[rubintheyid]

Hi Maximus,

The field is added once we tab down to the next field to populate so we do not have a button to confirm the add. Hope this makes sense I am still very new to this!!

[maximus]

Option Compare Database

Private Sub InvoiceNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo err_InvoiceNumber_BeforeUpdate


Dim strCheckSQL As String
Dim strCP As String
Dim strIN As String
Dim rstCheck As Recordset
Dim intTest As Integer

strCP = Me.cboContentProvider
strIN = Me.InvoiceNumber

'This will assign SQL code to variable strCheckSQL
strCheckSQL = "SELECT ContentProvider, InvoiceNumber FROM PayableRegister WHERE (((ContentProvider)=""" & strCP & """) AND ((InvoiceNumber)=""" & strIN & """));"


Set rstCheck = CurrentDb.OpenRecordset(strCheckSQL)

If rstCheck.RecordCount <> 0 Then

Response = MsgBox("Invoice number already exists for CP. Do you want to delete the record?", vbYesNo, "Duplication Error")
If Response = vbYes Then ' User chose Yes.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Else ' User chose No.
Cancel = True
Me.InvoiceNumber.Undo
End If

End If

rstCheck.Close


exit_InvoiceNumber_BeforeUpdate:
Exit Sub


err_InvoiceNumber_BeforeUpdate:
MsgBox Err.Description
Resume exit_InvoiceNumber_BeforeUpdate

End Sub

add the lines marked in red.

[rubintheyid]

That works.. Many thanks for your help.

[maximus]

mark the thread solved please this will help somebody with the same problem find quick answers. Go to thread tools and selected solved.

[maximus]

mark this thread solved please.