How do i stop duplicate records in access form.

[jadeja]

I have one database with table name Billings and form name Billings subform. I want users to prevent entering duplicate records by two fields. Cust_Ref and Bill_Date these are the two fields in the form.

User can not make the bill if the cust_code is already entered for the date. Means Cust_Code+Date must not be the same.

Field type for Cust_Code is Text and Bill_Date is Date/Time.

Please guide me how do i stop this with afterupdate event. Please help me as soon as possible on this as i am not a programer i need your valuable help.

Thank you

 

[binbin]

I think you have to make 2 primary keys from Cust_Code and Bill_Date. You can have the two fields to be index (duplicates ok).

This will allow duplicate values in Bill_Date and also Cust_Code, HOWEVER, it will not allow two records having the same Bill_Date and Cust_Code.

Ex.

Cust_Code     Bill_Date
001              9/22/2012   Allowed
002              9/22/2012   Allowed
001              9/23/2012   Allowed
001              9/22/2012 <<<NOT ALLOWED. You will not be able to add this record because it has the same value as the first record due to matching Cust_Code and Bill_Date.

Hope this helps.

 

[jadeja]

Thank you

The method you describe is correct. I want like that only.
I checked both fields and both are Indexed (Duplicates OK). But with this it doesn't work. I mean nothing happens. It creates duplicate value.

I think i need to put something code at form level it self. May be after update event on Bill Date.. Because Patient_Ref comes 1st then Bill_Date.

 

[bob fitz]

I think i need to put something code at form level it self. May be after update event on Bill Date.. Because Patient_Ref comes 1st then Bill_Date.

Data validation is usually done in the forms Before Update event.

 

[jadeja]

Could be i don't know how to put the validation.
Fine even if it warns to me saying "Check, Could Be Duplicate Bill" then also will fine. I don't want completely stop over there. With warning user can go ahead with duplicate. I just need a warning at that point.

Please guide how to i set warning like this ?

 

[bob fitz]

What is the name of the table?

 

[jadeja]

I am writing what exactly i want.. My Table name is Billings and form name is Billings subform.

Table is containing these fields. Bill_ID, Bill_Date, Cust_Ref, Amount, Type

Now i want if Bill_Date, Cust_Ref and the Type is matching for the record it will warn me with this functions. Record Already Exists. Click OK to Continue or Cancel to Go Back. Press Edit to Open the matched record. When pressing Edit it will display the record.

It is difficult for me to work out on this but it is very important for me.

thanks.

 

[bob fitz]

Table is containing these fields. Bill_ID, Bill_Date, Cust_Ref, Amount, Type

"Type" is a reserved word in Access. Please change this to some other name eg:BillType and tell me its new name and its data type ie: text, number etc.
Is Bill_ID the primary key of the table?

 

[jadeja]

Name is changed to Bill_Type. Data type for Bill_Type is Text only. And yes Bill_ID is primary key of the table. field type if autonumber for the Bill_ID.

 

[bob fitz]

Try this as the forms Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As Object
Dim varBillID As Variant
  varBillID = DLookup("[Bill_ID]", "Billings", "[Cust_Ref] = '" & [Cust_Ref] & "' AND [Bill_Type] = '" & [Bill_Type] & "' AND [Bill_Date] = " & Format([Bill_Date], "\#mm\/dd\/yyyy#"))
  
  If Not IsNull(varBillID) Then
    If MsgBox("Record Already Exists." & vbCrLf & vbCrLf & "Continue?", vbYesNo, "Confirmation Required") = vbNo Then
      Cancel = True
      Me.Undo
    ' Find the record that matches the control.
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Bill_ID] = " & Str(varBillID)
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
  End If
End Sub

 

[jadeja]

Working.....
The code is working with giving option to continue Yes or NO...

Now how do i go back to the detected duplicate record. Is it not possible warning will display the Edit Record button as well to navigate directly to the record ?

 

[bob fitz]

If you select Yes the record is saved.
If you select No the new, unsaved, current record is abandoned and you are taken to the existing record.

 

[pivottable]

Resurrecting this old thread as I have a very similar requirement.

Sample database and screengrab attached.

I want to prevent duplicate NRIC in tblMothers. NRIC is the natural primary key, but the surrogate primary key is the MothersPK field, which is an AutoNumber.

I changed the field names in the VBA code that was posted above to suit my purposes for the BeforeUpdate event.

However, the message box opens every time I enter a value in frmMothers.NRIC.

Clicking Yes will allow me to move on to Mother's Name, and the record saves if the NRIC is unique. It won't allow saving if the NRIC already exists.

Clicking No unfortunately causes MS Access to crash!

I would like the message box to say, "This record already exists. Go to existing record?"

Clicking Yes should take the user to the existing record.
Clicking No should clear the text box.

Can anyone help?

 

[bob fitz]

Try the attached file.