Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Duplicate Entry checking with one field Null

Discussion in 'Information Technology' started by bondtk, Nov 5, 2005.

  1. bondtk

    bondtk
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Duplicate passports offered by GOI to Indians residing in Italy Breaking News Sep 19, 2009
    Sikh News Case of the duplicate signatures (San Jose Mercury News) Breaking News Apr 4, 2005
    My last entry Blogs Oct 16, 2015
    UK 80-year-old Sikh Pensioner Kicked, Punched & Spit Upon by Woman in Coventry UK Breaking News Aug 17, 2013
    India ‘No one should be denied entry in gurdwaras’ Breaking News Jun 14, 2012

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:6A0B275A-9447-4DC3-B35E-50C634857F92@microsoft.com...
    > 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
     
  4. bondtk

    bondtk
    Expand Collapse
    Guest

    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" wrote:

    > 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.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:6A0B275A-9447-4DC3-B35E-50C634857F92@microsoft.com...
    > > 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

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:A6889C1E-A82C-43A5-9D26-BF62300E252A@microsoft.com...
    > 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" wrote:
    >
    >> 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" <bondtk@discussions.microsoft.com> wrote in message
    >> news:6A0B275A-9447-4DC3-B35E-50C634857F92@microsoft.com...
    >> > 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
     
  6. bondtk

    bondtk
    Expand Collapse
    Guest

    I'm not sure how to modify it, but I'll give it a try and respond back.

    "Allen Browne" wrote:

    > 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.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:A6889C1E-A82C-43A5-9D26-BF62300E252A@microsoft.com...
    > > 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" wrote:
    > >
    > >> 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" <bondtk@discussions.microsoft.com> wrote in message
    > >> news:6A0B275A-9447-4DC3-B35E-50C634857F92@microsoft.com...
    > >> > 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

    >
    >
    >
     
  7. bondtk

    bondtk
    Expand Collapse
    Guest

    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" wrote:

    > 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.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:A6889C1E-A82C-43A5-9D26-BF62300E252A@microsoft.com...
    > > 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" wrote:
    > >
    > >> 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" <bondtk@discussions.microsoft.com> wrote in message
    > >> news:6A0B275A-9447-4DC3-B35E-50C634857F92@microsoft.com...
    > >> > 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

    >
    >
    >
     

Share This Page