Duplicate Entry checking with one field Null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've posed this question in a similar thread already but would like to see if
anyone else has any further ideas. This is a stumper to me. I have a master
form where two controls are filled in (tied to a table). I need to prevent
duplicates from being entered by looking at BOTH values. I already know
about the multiple field primary keys I could set in the table, but this
won't work as the second field can contain 'null' which violates this primary
key function I guess. So I'm forced to check by code. The only option I've
gotten is by using the Dcount function but it currently allows duplicates.
My firm belief is that it has to do with encountering the null values in the
2nd field. I'm also unsure of where to even place the code (form before
update, after update OR on the controls before update, after update, lost
focus??). Anybody have any other ideas. I'll place my current code below.
This code does not prevent duplicates so far but does not generate an error
either:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "Master Contracts", "[Contract Number] = ' " _
& Me![Contract Number] & " ' AND [Order Number] = ' " _
& Me![Order Number] & " ' ") > 0 Then
Cancel = True
MsgBox "This contract number already exists in the table."
End If
End Sub
 
You want to prevent 2 records that both have the same ContractNumber and
OrderNumber, but what do you want when one of these fields is blank?

Clearly several records with the same ContractNumber would be fine. So, if
the OrderNumber is not known at the time of entry, should the database
assume:
a) they might be different, so it's okay to have several nulls, or
b) they might be the same as each other, or the same as another existing
record, so nulls are not allowed at all.

The answer to that question will determine the 3rd argument for your
DCount() function. This example sets the Criteria to match another that's
also null on Order Nubmer:

Dim strWhere As String
Dim varResult As Variant
strWhere = "([Contract Number] = """ & Me.[Contract Number] & """) AND
([Order Number]"
If IsNull(Me.[Order Number]) Then
strWhere = strWhere & " Is Null)"
Else
strWhere = strWhere & " = """ & Me.[Order Number] & """)"
End If
varResult = DLookup("[Contract Number]", "Master Contracts", strWhere)
If Not IsNull(varResult) Then ...

(BTW, that needs modifying so an existing record does not find itself as a
duplicate.)
 
Let's see if I can explain this clearly. The contract number could be stand
alone with no order number (null) but there cannot be another record that has
the same contract number and null at any time. Or no contract can have the
same order number. The contract number will never be blank. The following
might help illustrate:

Record Contract Order
1 1234 Null (OK)
2 1234 238 (OK)
3 5678 978 (OK)
4 1234 238 (NO - Duplicate)
5 1234 Null (NO - Duplicate)

Also, if you reply, where would I put the code? Thanks for your help. This
is a tough one. Usually I find all my answers here.


Allen Browne said:
You want to prevent 2 records that both have the same ContractNumber and
OrderNumber, but what do you want when one of these fields is blank?

Clearly several records with the same ContractNumber would be fine. So, if
the OrderNumber is not known at the time of entry, should the database
assume:
a) they might be different, so it's okay to have several nulls, or
b) they might be the same as each other, or the same as another existing
record, so nulls are not allowed at all.

The answer to that question will determine the 3rd argument for your
DCount() function. This example sets the Criteria to match another that's
also null on Order Nubmer:

Dim strWhere As String
Dim varResult As Variant
strWhere = "([Contract Number] = """ & Me.[Contract Number] & """) AND
([Order Number]"
If IsNull(Me.[Order Number]) Then
strWhere = strWhere & " Is Null)"
Else
strWhere = strWhere & " = """ & Me.[Order Number] & """)"
End If
varResult = DLookup("[Contract Number]", "Master Contracts", strWhere)
If Not IsNull(varResult) Then ...

(BTW, that needs modifying so an existing record does not find itself as a
duplicate.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bondtk said:
I've posed this question in a similar thread already but would like to see
if
anyone else has any further ideas. This is a stumper to me. I have a
master
form where two controls are filled in (tied to a table). I need to
prevent
duplicates from being entered by looking at BOTH values. I already know
about the multiple field primary keys I could set in the table, but this
won't work as the second field can contain 'null' which violates this
primary
key function I guess. So I'm forced to check by code. The only option
I've
gotten is by using the Dcount function but it currently allows duplicates.
My firm belief is that it has to do with encountering the null values in
the
2nd field. I'm also unsure of where to even place the code (form before
update, after update OR on the controls before update, after update, lost
focus??). Anybody have any other ideas. I'll place my current code
below.
This code does not prevent duplicates so far but does not generate an
error
either:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "Master Contracts", "[Contract Number] = ' " _
& Me![Contract Number] & " ' AND [Order Number] = ' " _
& Me![Order Number] & " ' ") > 0 Then
Cancel = True
MsgBox "This contract number already exists in the table."
End If
End Sub
Click to expand...
Click to expand...
 
Try the example code in the previous answer in Form_BeforeUpdate.

Don't forget to modify it so an existing record does not find itself as a
duplicate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bondtk said:
Let's see if I can explain this clearly. The contract number could be
stand
alone with no order number (null) but there cannot be another record that
has
the same contract number and null at any time. Or no contract can have
the
same order number. The contract number will never be blank. The following
might help illustrate:

Record Contract Order
1 1234 Null (OK)
2 1234 238 (OK)
3 5678 978 (OK)
4 1234 238 (NO - Duplicate)
5 1234 Null (NO - Duplicate)

Also, if you reply, where would I put the code? Thanks for your help.
This
is a tough one. Usually I find all my answers here.


Allen Browne said:
You want to prevent 2 records that both have the same ContractNumber and
OrderNumber, but what do you want when one of these fields is blank?

Clearly several records with the same ContractNumber would be fine. So,
if
the OrderNumber is not known at the time of entry, should the database
assume:
a) they might be different, so it's okay to have several nulls, or
b) they might be the same as each other, or the same as another existing
record, so nulls are not allowed at all.

The answer to that question will determine the 3rd argument for your
DCount() function. This example sets the Criteria to match another that's
also null on Order Nubmer:

Dim strWhere As String
Dim varResult As Variant
strWhere = "([Contract Number] = """ & Me.[Contract Number] & """)
AND
([Order Number]"
If IsNull(Me.[Order Number]) Then
strWhere = strWhere & " Is Null)"
Else
strWhere = strWhere & " = """ & Me.[Order Number] & """)"
End If
varResult = DLookup("[Contract Number]", "Master Contracts",
strWhere)
If Not IsNull(varResult) Then ...

(BTW, that needs modifying so an existing record does not find itself as
a
duplicate.)

bondtk said:
I've posed this question in a similar thread already but would like to
see
if
anyone else has any further ideas. This is a stumper to me. I have a
master
form where two controls are filled in (tied to a table). I need to
prevent
duplicates from being entered by looking at BOTH values. I already
know
about the multiple field primary keys I could set in the table, but
this
won't work as the second field can contain 'null' which violates this
primary
key function I guess. So I'm forced to check by code. The only option
I've
gotten is by using the Dcount function but it currently allows
duplicates.
My firm belief is that it has to do with encountering the null values
in
the
2nd field. I'm also unsure of where to even place the code (form
before
update, after update OR on the controls before update, after update,
lost
focus??). Anybody have any other ideas. I'll place my current code
below.
This code does not prevent duplicates so far but does not generate an
error
either:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "Master Contracts", "[Contract Number] = ' " _
& Me![Contract Number] & " ' AND [Order Number] = ' " _
& Me![Order Number] & " ' ") > 0 Then
Cancel = True
MsgBox "This contract number already exists in the table."
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure how to modify it, but I'll give it a try and respond back.

Allen Browne said:
Try the example code in the previous answer in Form_BeforeUpdate.

Don't forget to modify it so an existing record does not find itself as a
duplicate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bondtk said:
Let's see if I can explain this clearly. The contract number could be
stand
alone with no order number (null) but there cannot be another record that
has
the same contract number and null at any time. Or no contract can have
the
same order number. The contract number will never be blank. The following
might help illustrate:

Record Contract Order
1 1234 Null (OK)
2 1234 238 (OK)
3 5678 978 (OK)
4 1234 238 (NO - Duplicate)
5 1234 Null (NO - Duplicate)

Also, if you reply, where would I put the code? Thanks for your help.
This
is a tough one. Usually I find all my answers here.


Allen Browne said:
You want to prevent 2 records that both have the same ContractNumber and
OrderNumber, but what do you want when one of these fields is blank?

Clearly several records with the same ContractNumber would be fine. So,
if
the OrderNumber is not known at the time of entry, should the database
assume:
a) they might be different, so it's okay to have several nulls, or
b) they might be the same as each other, or the same as another existing
record, so nulls are not allowed at all.

The answer to that question will determine the 3rd argument for your
DCount() function. This example sets the Criteria to match another that's
also null on Order Nubmer:

Dim strWhere As String
Dim varResult As Variant
strWhere = "([Contract Number] = """ & Me.[Contract Number] & """)
AND
([Order Number]"
If IsNull(Me.[Order Number]) Then
strWhere = strWhere & " Is Null)"
Else
strWhere = strWhere & " = """ & Me.[Order Number] & """)"
End If
varResult = DLookup("[Contract Number]", "Master Contracts",
strWhere)
If Not IsNull(varResult) Then ...

(BTW, that needs modifying so an existing record does not find itself as
a
duplicate.)

I've posed this question in a similar thread already but would like to
see
if
anyone else has any further ideas. This is a stumper to me. I have a
master
form where two controls are filled in (tied to a table). I need to
prevent
duplicates from being entered by looking at BOTH values. I already
know
about the multiple field primary keys I could set in the table, but
this
won't work as the second field can contain 'null' which violates this
primary
key function I guess. So I'm forced to check by code. The only option
I've
gotten is by using the Dcount function but it currently allows
duplicates.
My firm belief is that it has to do with encountering the null values
in
the
2nd field. I'm also unsure of where to even place the code (form
before
update, after update OR on the controls before update, after update,
lost
focus??). Anybody have any other ideas. I'll place my current code
below.
This code does not prevent duplicates so far but does not generate an
error
either:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "Master Contracts", "[Contract Number] = ' " _
& Me![Contract Number] & " ' AND [Order Number] = ' " _
& Me![Order Number] & " ' ") > 0 Then
Cancel = True
MsgBox "This contract number already exists in the table."
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
It looks like I hit the jackpot. It's working! Thanks so much. I'll post
the finished code I used for others to see:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
strWhere = "([Contract Number] = """ & Me.[Contract Number] & """) AND
([Order Number]"
If IsNull(Me.[Order Number]) Then
strWhere = strWhere & " Is Null)"
Else
strWhere = strWhere & " = """ & Me.[Order Number] & """)"
End If
varResult = DCount("*", "Master Contracts", strWhere)
If varResult > 0 Then
MsgBox ("There is a duplicate.")
Cancel = True
End If
End Sub

Allen Browne said:
Try the example code in the previous answer in Form_BeforeUpdate.

Don't forget to modify it so an existing record does not find itself as a
duplicate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bondtk said:
Let's see if I can explain this clearly. The contract number could be
stand
alone with no order number (null) but there cannot be another record that
has
the same contract number and null at any time. Or no contract can have
the
same order number. The contract number will never be blank. The following
might help illustrate:

Record Contract Order
1 1234 Null (OK)
2 1234 238 (OK)
3 5678 978 (OK)
4 1234 238 (NO - Duplicate)
5 1234 Null (NO - Duplicate)

Also, if you reply, where would I put the code? Thanks for your help.
This
is a tough one. Usually I find all my answers here.


Allen Browne said:
You want to prevent 2 records that both have the same ContractNumber and
OrderNumber, but what do you want when one of these fields is blank?

Clearly several records with the same ContractNumber would be fine. So,
if
the OrderNumber is not known at the time of entry, should the database
assume:
a) they might be different, so it's okay to have several nulls, or
b) they might be the same as each other, or the same as another existing
record, so nulls are not allowed at all.

The answer to that question will determine the 3rd argument for your
DCount() function. This example sets the Criteria to match another that's
also null on Order Nubmer:

Dim strWhere As String
Dim varResult As Variant
strWhere = "([Contract Number] = """ & Me.[Contract Number] & """)
AND
([Order Number]"
If IsNull(Me.[Order Number]) Then
strWhere = strWhere & " Is Null)"
Else
strWhere = strWhere & " = """ & Me.[Order Number] & """)"
End If
varResult = DLookup("[Contract Number]", "Master Contracts",
strWhere)
If Not IsNull(varResult) Then ...

(BTW, that needs modifying so an existing record does not find itself as
a
duplicate.)

I've posed this question in a similar thread already but would like to
see
if
anyone else has any further ideas. This is a stumper to me. I have a
master
form where two controls are filled in (tied to a table). I need to
prevent
duplicates from being entered by looking at BOTH values. I already
know
about the multiple field primary keys I could set in the table, but
this
won't work as the second field can contain 'null' which violates this
primary
key function I guess. So I'm forced to check by code. The only option
I've
gotten is by using the Dcount function but it currently allows
duplicates.
My firm belief is that it has to do with encountering the null values
in
the
2nd field. I'm also unsure of where to even place the code (form
before
update, after update OR on the controls before update, after update,
lost
focus??). Anybody have any other ideas. I'll place my current code
below.
This code does not prevent duplicates so far but does not generate an
error
either:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "Master Contracts", "[Contract Number] = ' " _
& Me![Contract Number] & " ' AND [Order Number] = ' " _
& Me![Order Number] & " ' ") > 0 Then
Cancel = True
MsgBox "This contract number already exists in the table."
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top