How to prevent duplicate based on more than one field

[mseo]

I have form based on table with following column name:

OrderID PK (autonumber), OrderNo, customer_id

OrderNo and coustomer_id are required fields

You are allowed to have Same customer, orderno one time.
For example:
1, 123 , tri-g allowed
My questions is that, for orderno with customer, I would like to prevent duplicates when entering in the form.
I can't make all the fields primary key because, as stated above that would cause duplication the customer with orderno.

Is there anyway to prevent duplicates for and when the user enters the
duplicates (customer_id with orderno, I would like to have message box pop up saying Duplicate order ?

[Jim Doherty]

@mseo
Firstly mseo..... please do not duplicate your posts within a day of each other!! it is a waste of other peoples time administering for this. I have deleted your other similarly worded posting on this issue. Give sufficient time to elapse without response before reposting

The idea in this instance would be to examine all pre-existing values in your table and to count the primary key value based on criteria combination of OrderNo and Customer. Where the return value is greater than zero then undo the current record edit.

The following methods pre-suppose that your OrderID and Customer_ID are TEXT fields as opposed to numeric as apostrophes are used in wrapping field contents. If numeric then merely remove the apostrophes.

The following will do that using the DCount Function in the beforeupdate property of the form based on a table called tblOrders

Private Sub Form_BeforeUpdate(Cancel As Integer)
'If CheckDuplicates(Me!OrderNo, Me!Customer_ID) Then
If DCount("[OrderID]", "tblOrders", "[OrderNo]='" & Me!OrderNo & "' AND [Customer_ID]='" & Me!Customer_ID & "'") > 0 Then
    msg = "You already have that OrderID and Customer_ID combination" & vbNewLine
    msg = msg & "The record will now be undone"
    MsgBox msg, vbExclamation, "System Duplication Message"
    Me.Undo
    Cancel = True
End If
End Sub

Notice the first commented out line? I have put this in to give you a choice here. If you do not want to use the DCount function in favour of a 'roll your own' type function then simply comment out the DCount line and uncomment that first line to call the following function that you can paste into a new module and save.

Looking at the principles of the CheckDuplicates 'roll your own' function you are passing parameter values into it that are being treated as string values by the SQL being built servicing the creation of a recordset in code.

The ultimate value of the function is true or false and is returned to calling procedure. This boolean value is arrived at by looking at the value of the 'Total' field of that created recordset (which is then closed and variables destroyed at the end of it).

This 'roll your own' method as it were, will do essentially the same thing but over very large datasets you may find faster on attached tables. Also you can pretty much adapt the specifics and flow of it to your own need.

Function CheckDuplicates(strOrderNo, strCustomerID) As Boolean
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim mysql As String
'define an SQL string for the recordset
mysql = "SELECT Count([OrderID]) as Total FROM tblOrders "
mysql = mysql & "WHERE [OrderNo]='" & strOrderNo & "' AND "
mysql = mysql & "[Customer_ID]='" & strCustomerID & "';"
Debug.Print mysql
'open a recordset based on that sql
Set rs = db.OpenRecordset(mysql, dbOpenSnapshot)
'because it counts one row will be returned to grab the value
'and depending on the result of the count set the return value
'logic to to the calling procedure
Debug.Print rs!Total
If rs!Total > 0 Then
    CheckDuplicates = True
Else
    CheckDuplicates = False
End If
 
mysql=""
rs.close
db.close
set rs=Nothing
set db=Nothing
 
End Function  

[mseo]

thank you, Jim Doherty for you reply

the datatype of the table fields as the following:
OrderID PK - autonumber
OrderNo text
Customer_ID Number
so I modified your code by removing the single quote of the customer_id part
but when I run the code, msgbox fires with run-error 3075

Private Sub Form_BeforeUpdate(Cancel As Integer)
'If CheckDuplicates(Me!OrderNo, Me!Customer_ID) Then
If DCount("[OrderID]", "tbl_Order", "[OrderNo]='" & Me!SON & " AND [Customer_ID]=" & Me!Customer_ID & "") > 0 Then
 
    MsgBox "this order is already exist", vbExclamation, "System Duplication Message"
    Me.Undo
    Cancel = True
End If
End Sub

and about the function you provided me
how can I call the function within a form?
thank you very much

[Jim Doherty]

@mseo
you eventually will get your head around all the quotes for strings or none for numbers where and when to define them and so on but the amendments you made are not now syntax viable. You removed in the wrong places it should be this for that Dcount function.

If DCount("[OrderID]", "tbl_Order", "[OrderNo]='" & Me!SON & "' AND [Customer_ID]=" & Me!Customer_ID) > 0 Then 

As for running the CheckDuplicates function the complete code event is is already provided to you you merely paste it into the BeforeUpdate event of the form. The event will call the function before any field/record isupdated....thats the way it is

[mseo]

thank you very much Jim Doherty
It works great
appreciate your help

[NeoPa]

If your intention is merely to avoid duplicating sets of values that go across multiple fields, then a Compound Index should do this for you. In Design View of the table select View / Indexes and set it up. The Unique property for the index would need to be set to Yes.

[mseo]

@NeoPa
hi,Neopa
nice to hear from you,
If i set the unique property to yes for one field
that would prevent the duplication for this field only
even if i set the unique property to yes for other fields
indexes will treat with them separately

[NeoPa]

What you say is true, but you are ignoring the Compound Index bit. You can specify an index that is made up of multiple fields.

[mseo]

@NeoPa
hi, Neopa
Although I got the answer of my question, I need to know about the compound index and how can I make it, specially It is very useful tool
thank you

[NeoPa]

If you look at the second part of post #6 you'll see instructions on how to set this up. I suggest you have a look at that and, if you still have problems, then post back with what your problem(s) is/are specifically, and we can help from there. I expect once you find the place though, all will become clear.

[mseo]

hi,
if I set the unique property to yes for orderno and customer_id (not PK)fields
access will not allow duplication for each field alone
not as a compound PK
because if it is compound primary key will allow duplication depending on the other PK field within the table
thanks

[NeoPa]

mseo: if I set the unique property to yes for orderno and customer_id (not PK)fields
access will not allow duplication for each field alone

Absolutely. That is why a Compound Key is required for this. Handling the fields individually would have exactly that undesirable effect.

mseo: not as a compound PK
because if it is compound primary key will allow duplication depending on the other PK field within the table
thanks

I don't believe there was ever any suggestion to set the Compound Key as Primary. To be honest, I'm not really sure I understand what you're saying here. Other PK, for instance is not a possible concept. There can only ever be a single PK. Every other index is additional.

[mseo]

hi, Neopa

There can only ever be a single PK. Every other index is additional

that is right but I can select two fields and click primary key button, I will get the first field as primary key and second field will be indexed.
but compound Index:
I tried to do it as you told me but, I couldn't do it, because I can set the unique property for each field alone
thank you very much

[NeoPa]

That's one possible way of creating a Compound Index. It is certainly not the only way, nor even the normal way. Generally you do this in the Indexes window, and each field can be specified as either unique or not. It is certainly not outside of your control.

[jonnycakes]

@NeoPa
Thank you for this! Perfect timing :)

[NeoPa]

I don't know about Perfect Timing Jonny, it was posted over 18 months ago :-D

It's always good news if it helps though. I'm not convinced MSEO ever quite grasped what was being said, but at least his question allows others to learn.

[chrisfo]

Hello, I followed Jim's suggestion, but I have an error and I'm not sure this would be the right solution for me, since I need to prevent duplicates in a subform that is set to display a new entry that is auto-completed with default values and by a query search.
The project is available to download from this link: https://drive.google.com/open?id=0Bz...EppbEZGNGdtYm8
Can anyone help me, please?