By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,021 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

How to prevent duplicate based on more than one field

mseo
100+
P: 181
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 ?
May 27 '10 #1

✓ answered by 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. 'If CheckDuplicates(Me!OrderNo, Me!Customer_ID) Then
  3. If DCount("[OrderID]", "tblOrders", "[OrderNo]='" & Me!OrderNo & "' AND [Customer_ID]='" & Me!Customer_ID & "'") > 0 Then
  4.     msg = "You already have that OrderID and Customer_ID combination" & vbNewLine
  5.     msg = msg & "The record will now be undone"
  6.     MsgBox msg, vbExclamation, "System Duplication Message"
  7.     Me.Undo
  8.     Cancel = True
  9. End If
  10. 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.


Expand|Select|Wrap|Line Numbers
  1. Function CheckDuplicates(strOrderNo, strCustomerID) As Boolean
  2. On Error Resume Next
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Set db = CurrentDb
  6. Dim mysql As String
  7. 'define an SQL string for the recordset
  8. mysql = "SELECT Count([OrderID]) as Total FROM tblOrders "
  9. mysql = mysql & "WHERE [OrderNo]='" & strOrderNo & "' AND "
  10. mysql = mysql & "[Customer_ID]='" & strCustomerID & "';"
  11. Debug.Print mysql
  12. 'open a recordset based on that sql
  13. Set rs = db.OpenRecordset(mysql, dbOpenSnapshot)
  14. 'because it counts one row will be returned to grab the value
  15. 'and depending on the result of the count set the return value
  16. 'logic to to the calling procedure
  17. Debug.Print rs!Total
  18. If rs!Total > 0 Then
  19.     CheckDuplicates = True
  20. Else
  21.     CheckDuplicates = False
  22. End If
  23.  
  24. mysql=""
  25. rs.close
  26. db.close
  27. set rs=Nothing
  28. set db=Nothing
  29.  
  30. End Function  

Share this Question
Share on Google+
16 Replies


Jim Doherty
Expert 100+
P: 897
@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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. 'If CheckDuplicates(Me!OrderNo, Me!Customer_ID) Then
  3. If DCount("[OrderID]", "tblOrders", "[OrderNo]='" & Me!OrderNo & "' AND [Customer_ID]='" & Me!Customer_ID & "'") > 0 Then
  4.     msg = "You already have that OrderID and Customer_ID combination" & vbNewLine
  5.     msg = msg & "The record will now be undone"
  6.     MsgBox msg, vbExclamation, "System Duplication Message"
  7.     Me.Undo
  8.     Cancel = True
  9. End If
  10. 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.


Expand|Select|Wrap|Line Numbers
  1. Function CheckDuplicates(strOrderNo, strCustomerID) As Boolean
  2. On Error Resume Next
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Set db = CurrentDb
  6. Dim mysql As String
  7. 'define an SQL string for the recordset
  8. mysql = "SELECT Count([OrderID]) as Total FROM tblOrders "
  9. mysql = mysql & "WHERE [OrderNo]='" & strOrderNo & "' AND "
  10. mysql = mysql & "[Customer_ID]='" & strCustomerID & "';"
  11. Debug.Print mysql
  12. 'open a recordset based on that sql
  13. Set rs = db.OpenRecordset(mysql, dbOpenSnapshot)
  14. 'because it counts one row will be returned to grab the value
  15. 'and depending on the result of the count set the return value
  16. 'logic to to the calling procedure
  17. Debug.Print rs!Total
  18. If rs!Total > 0 Then
  19.     CheckDuplicates = True
  20. Else
  21.     CheckDuplicates = False
  22. End If
  23.  
  24. mysql=""
  25. rs.close
  26. db.close
  27. set rs=Nothing
  28. set db=Nothing
  29.  
  30. End Function  
May 28 '10 #2

mseo
100+
P: 181
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. 'If CheckDuplicates(Me!OrderNo, Me!Customer_ID) Then
  3. If DCount("[OrderID]", "tbl_Order", "[OrderNo]='" & Me!SON & " AND [Customer_ID]=" & Me!Customer_ID & "") > 0 Then
  4.  
  5.     MsgBox "this order is already exist", vbExclamation, "System Duplication Message"
  6.     Me.Undo
  7.     Cancel = True
  8. End If
  9. End Sub
and about the function you provided me
how can I call the function within a form?
thank you very much
May 28 '10 #3

Jim Doherty
Expert 100+
P: 897
@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.

Expand|Select|Wrap|Line Numbers
  1. 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
May 28 '10 #4

mseo
100+
P: 181
thank you very much Jim Doherty
It works great
appreciate your help
May 28 '10 #5

NeoPa
Expert Mod 15k+
P: 31,276
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.
May 28 '10 #6

mseo
100+
P: 181
@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
May 28 '10 #7

NeoPa
Expert Mod 15k+
P: 31,276
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.
May 28 '10 #8

mseo
100+
P: 181
@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
May 29 '10 #9

NeoPa
Expert Mod 15k+
P: 31,276
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.
Jun 1 '10 #10

mseo
100+
P: 181
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
Jun 1 '10 #11

NeoPa
Expert Mod 15k+
P: 31,276
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.
Jun 2 '10 #12

mseo
100+
P: 181
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
Jun 2 '10 #13

NeoPa
Expert Mod 15k+
P: 31,276
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.
Jun 2 '10 #14

jonnycakes
P: 24
@NeoPa
Thank you for this! Perfect timing :)
Feb 2 '12 #15

NeoPa
Expert Mod 15k+
P: 31,276
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.
Feb 2 '12 #16

P: 1
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?
Nov 18 '16 #17

Post your reply

Sign in to post your reply or Sign up for a free account.