Welcome to SPN

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

Sign Up Now!

Multiple key index with null values

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

  1. bondtk

    bondtk
    Expand Collapse
    Guest

    I have already read how to create a table with multiple key index, but here's
    my problem. When I attempt to create this type of index, it says the index
    can't contain a null value. I certainly understand this, but in my table I
    have 3 fields I want as keys. Contract ID (auto number), Contract Number,
    and Order Number. I really need to do the check on Contract Number and Order
    Number to make sure there are no duplicate entries. However, On many
    occassions, there is no order number associated with a contract number (it's
    just a stand-alone contract) so that's where my null values come in. Is
    there any work around for this or another way to check for duplicate values
    check both those fields? I don't want there to be any record with the same
    contract number and delivery order.
     
  2. Loading...


  3. tina

    tina
    Expand Collapse
    Guest

    you can validate the data in those fields at the form level, at the point of
    data entry. try using the form's BeforeUpdate event, and use a DCount()
    function to check the table for existing data that matches the newly entered
    data in the form. something along the lines of

    If DCount(1, "MyTableName", "ContractNumber = " _
    & Me!ContractNumber) > 0 Then
    Cancel = True
    MsgBox "This contract number already exists in the table."
    End If

    if you're not familiar with the DCount function, or with the BeforeUpdate
    event, take a look at them in Help - and post back if you need further
    assistance.

    hth


    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > I have already read how to create a table with multiple key index, but

    here's
    > my problem. When I attempt to create this type of index, it says the

    index
    > can't contain a null value. I certainly understand this, but in my table

    I
    > have 3 fields I want as keys. Contract ID (auto number), Contract Number,
    > and Order Number. I really need to do the check on Contract Number and

    Order
    > Number to make sure there are no duplicate entries. However, On many
    > occassions, there is no order number associated with a contract number

    (it's
    > just a stand-alone contract) so that's where my null values come in. Is
    > there any work around for this or another way to check for duplicate

    values
    > check both those fields? I don't want there to be any record with the

    same
    > contract number and delivery order.
     
  4. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    What are you trying to achieve with the multi-field
    index?

    To enforce the requested constraint, the Join table
    should be a separate table.

    If there is no Order Number, the
    Order Number: Contract Number
    record should be missing.

    (david)




    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    >I have already read how to create a table with multiple key index, but
    >here's
    > my problem. When I attempt to create this type of index, it says the
    > index
    > can't contain a null value. I certainly understand this, but in my table
    > I
    > have 3 fields I want as keys. Contract ID (auto number), Contract Number,
    > and Order Number. I really need to do the check on Contract Number and
    > Order
    > Number to make sure there are no duplicate entries. However, On many
    > occassions, there is no order number associated with a contract number
    > (it's
    > just a stand-alone contract) so that's where my null values come in. Is
    > there any work around for this or another way to check for duplicate
    > values
    > check both those fields? I don't want there to be any record with the
    > same
    > contract number and delivery order.
     
  5. bondtk

    bondtk
    Expand Collapse
    Guest

    This would work for just one field, but I need to validate two fields. There
    can only be one record containing a unique set of two fields.

    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)

    I need to check both the contract and order number together.
    "tina" wrote:

    > you can validate the data in those fields at the form level, at the point of
    > data entry. try using the form's BeforeUpdate event, and use a DCount()
    > function to check the table for existing data that matches the newly entered
    > data in the form. something along the lines of
    >
    > If DCount(1, "MyTableName", "ContractNumber = " _
    > & Me!ContractNumber) > 0 Then
    > Cancel = True
    > MsgBox "This contract number already exists in the table."
    > End If
    >
    > if you're not familiar with the DCount function, or with the BeforeUpdate
    > event, take a look at them in Help - and post back if you need further
    > assistance.
    >
    > hth
    >
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > I have already read how to create a table with multiple key index, but

    > here's
    > > my problem. When I attempt to create this type of index, it says the

    > index
    > > can't contain a null value. I certainly understand this, but in my table

    > I
    > > have 3 fields I want as keys. Contract ID (auto number), Contract Number,
    > > and Order Number. I really need to do the check on Contract Number and

    > Order
    > > Number to make sure there are no duplicate entries. However, On many
    > > occassions, there is no order number associated with a contract number

    > (it's
    > > just a stand-alone contract) so that's where my null values come in. Is
    > > there any work around for this or another way to check for duplicate

    > values
    > > check both those fields? I don't want there to be any record with the

    > same
    > > contract number and delivery order.

    >
    >
    >
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    If DCount("*", "MyTableName", "ContractNumber = " _
    & Me!ContractNumber & " AND OrderNumber = " _
    & Me!OrderNumber) > 0 Then
    Cancel = True
    MsgBox "This contract number already exists in the table."
    End If


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > This would work for just one field, but I need to validate two fields.

    There
    > can only be one record containing a unique set of two fields.
    >
    > 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)
    >
    > I need to check both the contract and order number together.
    > "tina" wrote:
    >
    > > you can validate the data in those fields at the form level, at the

    point of
    > > data entry. try using the form's BeforeUpdate event, and use a DCount()
    > > function to check the table for existing data that matches the newly

    entered
    > > data in the form. something along the lines of
    > >
    > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > & Me!ContractNumber) > 0 Then
    > > Cancel = True
    > > MsgBox "This contract number already exists in the table."
    > > End If
    > >
    > > if you're not familiar with the DCount function, or with the

    BeforeUpdate
    > > event, take a look at them in Help - and post back if you need further
    > > assistance.
    > >
    > > hth
    > >
    > >
    > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > I have already read how to create a table with multiple key index, but

    > > here's
    > > > my problem. When I attempt to create this type of index, it says the

    > > index
    > > > can't contain a null value. I certainly understand this, but in my

    table
    > > I
    > > > have 3 fields I want as keys. Contract ID (auto number), Contract

    Number,
    > > > and Order Number. I really need to do the check on Contract Number

    and
    > > Order
    > > > Number to make sure there are no duplicate entries. However, On many
    > > > occassions, there is no order number associated with a contract number

    > > (it's
    > > > just a stand-alone contract) so that's where my null values come in.

    Is
    > > > there any work around for this or another way to check for duplicate

    > > values
    > > > check both those fields? I don't want there to be any record with the

    > > same
    > > > contract number and delivery order.

    > >
    > >
    > >
     
  7. bondtk

    bondtk
    Expand Collapse
    Guest

    Doug,

    Here's what I put in the code (After update on the contract number field)
    and I'm getting a syntax 3075 error (missing operator in query expression
    '[Contract Number]=W25G1V AND [Order Number]='.:

    If DCount("*", "Contract Order Table", "[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

    Any ideas what I'm doing. I should also say that there was no order number
    in this particular record required.

    "Douglas J Steele" wrote:

    > If DCount("*", "MyTableName", "ContractNumber = " _
    > & Me!ContractNumber & " AND OrderNumber = " _
    > & Me!OrderNumber) > 0 Then
    > Cancel = True
    > MsgBox "This contract number already exists in the table."
    > End If
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > This would work for just one field, but I need to validate two fields.

    > There
    > > can only be one record containing a unique set of two fields.
    > >
    > > 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)
    > >
    > > I need to check both the contract and order number together.
    > > "tina" wrote:
    > >
    > > > you can validate the data in those fields at the form level, at the

    > point of
    > > > data entry. try using the form's BeforeUpdate event, and use a DCount()
    > > > function to check the table for existing data that matches the newly

    > entered
    > > > data in the form. something along the lines of
    > > >
    > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > & Me!ContractNumber) > 0 Then
    > > > Cancel = True
    > > > MsgBox "This contract number already exists in the table."
    > > > End If
    > > >
    > > > if you're not familiar with the DCount function, or with the

    > BeforeUpdate
    > > > event, take a look at them in Help - and post back if you need further
    > > > assistance.
    > > >
    > > > hth
    > > >
    > > >
    > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > I have already read how to create a table with multiple key index, but
    > > > here's
    > > > > my problem. When I attempt to create this type of index, it says the
    > > > index
    > > > > can't contain a null value. I certainly understand this, but in my

    > table
    > > > I
    > > > > have 3 fields I want as keys. Contract ID (auto number), Contract

    > Number,
    > > > > and Order Number. I really need to do the check on Contract Number

    > and
    > > > Order
    > > > > Number to make sure there are no duplicate entries. However, On many
    > > > > occassions, there is no order number associated with a contract number
    > > > (it's
    > > > > just a stand-alone contract) so that's where my null values come in.

    > Is
    > > > > there any work around for this or another way to check for duplicate
    > > > values
    > > > > check both those fields? I don't want there to be any record with the
    > > > same
    > > > > contract number and delivery order.
    > > >
    > > >
    > > >

    >
    >
    >
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Since Contract Number is a text field, as opposed to a numeric one, you need
    to enclose the value in quotes:

    If DCount("*", "Contract Order Table", "[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

    If Order Number is also a text field, you'll need to do the same with it:

    If DCount("*", "Contract Order Table", "[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

    Exagerated for clarity, that's

    If DCount("*", "Contract Order Table", "[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


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > Doug,
    >
    > Here's what I put in the code (After update on the contract number field)
    > and I'm getting a syntax 3075 error (missing operator in query expression
    > '[Contract Number]=W25G1V AND [Order Number]='.:
    >
    > If DCount("*", "Contract Order Table", "[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
    >
    > Any ideas what I'm doing. I should also say that there was no order

    number
    > in this particular record required.
    >
    > "Douglas J Steele" wrote:
    >
    > > If DCount("*", "MyTableName", "ContractNumber = " _
    > > & Me!ContractNumber & " AND OrderNumber = " _
    > > & Me!OrderNumber) > 0 Then
    > > Cancel = True
    > > MsgBox "This contract number already exists in the table."
    > > End If
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > This would work for just one field, but I need to validate two fields.

    > > There
    > > > can only be one record containing a unique set of two fields.
    > > >
    > > > 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)
    > > >
    > > > I need to check both the contract and order number together.
    > > > "tina" wrote:
    > > >
    > > > > you can validate the data in those fields at the form level, at the

    > > point of
    > > > > data entry. try using the form's BeforeUpdate event, and use a

    DCount()
    > > > > function to check the table for existing data that matches the newly

    > > entered
    > > > > data in the form. something along the lines of
    > > > >
    > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > > & Me!ContractNumber) > 0 Then
    > > > > Cancel = True
    > > > > MsgBox "This contract number already exists in the table."
    > > > > End If
    > > > >
    > > > > if you're not familiar with the DCount function, or with the

    > > BeforeUpdate
    > > > > event, take a look at them in Help - and post back if you need

    further
    > > > > assistance.
    > > > >
    > > > > hth
    > > > >
    > > > >
    > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > > I have already read how to create a table with multiple key index,

    but
    > > > > here's
    > > > > > my problem. When I attempt to create this type of index, it says

    the
    > > > > index
    > > > > > can't contain a null value. I certainly understand this, but in

    my
    > > table
    > > > > I
    > > > > > have 3 fields I want as keys. Contract ID (auto number), Contract

    > > Number,
    > > > > > and Order Number. I really need to do the check on Contract

    Number
    > > and
    > > > > Order
    > > > > > Number to make sure there are no duplicate entries. However, On

    many
    > > > > > occassions, there is no order number associated with a contract

    number
    > > > > (it's
    > > > > > just a stand-alone contract) so that's where my null values come

    in.
    > > Is
    > > > > > there any work around for this or another way to check for

    duplicate
    > > > > values
    > > > > > check both those fields? I don't want there to be any record with

    the
    > > > > same
    > > > > > contract number and delivery order.
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  9. bondtk

    bondtk
    Expand Collapse
    Guest

    The error is now gone (so I assume it is doing some kind of checking, but it
    does not pop up when I knowingly put in a duplicate contract/order number.
    Any guesses why not?

    "Douglas J Steele" wrote:

    > Since Contract Number is a text field, as opposed to a numeric one, you need
    > to enclose the value in quotes:
    >
    > If DCount("*", "Contract Order Table", "[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
    >
    > If Order Number is also a text field, you'll need to do the same with it:
    >
    > If DCount("*", "Contract Order Table", "[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
    >
    > Exagerated for clarity, that's
    >
    > If DCount("*", "Contract Order Table", "[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
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > Doug,
    > >
    > > Here's what I put in the code (After update on the contract number field)
    > > and I'm getting a syntax 3075 error (missing operator in query expression
    > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > >
    > > If DCount("*", "Contract Order Table", "[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
    > >
    > > Any ideas what I'm doing. I should also say that there was no order

    > number
    > > in this particular record required.
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > If DCount("*", "MyTableName", "ContractNumber = " _
    > > > & Me!ContractNumber & " AND OrderNumber = " _
    > > > & Me!OrderNumber) > 0 Then
    > > > Cancel = True
    > > > MsgBox "This contract number already exists in the table."
    > > > End If
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > > This would work for just one field, but I need to validate two fields.
    > > > There
    > > > > can only be one record containing a unique set of two fields.
    > > > >
    > > > > 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)
    > > > >
    > > > > I need to check both the contract and order number together.
    > > > > "tina" wrote:
    > > > >
    > > > > > you can validate the data in those fields at the form level, at the
    > > > point of
    > > > > > data entry. try using the form's BeforeUpdate event, and use a

    > DCount()
    > > > > > function to check the table for existing data that matches the newly
    > > > entered
    > > > > > data in the form. something along the lines of
    > > > > >
    > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > > > & Me!ContractNumber) > 0 Then
    > > > > > Cancel = True
    > > > > > MsgBox "This contract number already exists in the table."
    > > > > > End If
    > > > > >
    > > > > > if you're not familiar with the DCount function, or with the
    > > > BeforeUpdate
    > > > > > event, take a look at them in Help - and post back if you need

    > further
    > > > > > assistance.
    > > > > >
    > > > > > hth
    > > > > >
    > > > > >
    > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > > > I have already read how to create a table with multiple key index,

    > but
    > > > > > here's
    > > > > > > my problem. When I attempt to create this type of index, it says

    > the
    > > > > > index
    > > > > > > can't contain a null value. I certainly understand this, but in

    > my
    > > > table
    > > > > > I
    > > > > > > have 3 fields I want as keys. Contract ID (auto number), Contract
    > > > Number,
    > > > > > > and Order Number. I really need to do the check on Contract

    > Number
    > > > and
    > > > > > Order
    > > > > > > Number to make sure there are no duplicate entries. However, On

    > many
    > > > > > > occassions, there is no order number associated with a contract

    > number
    > > > > > (it's
    > > > > > > just a stand-alone contract) so that's where my null values come

    > in.
    > > > Is
    > > > > > > there any work around for this or another way to check for

    > duplicate
    > > > > > values
    > > > > > > check both those fields? I don't want there to be any record with

    > the
    > > > > > same
    > > > > > > contract number and delivery order.
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  10. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    What happens if you go to the Debug window (Ctrl-G) and type:

    ?DCount("*", "Contract Order Table", "[Contract Number] = 'xxxx' AND [Order
    Number] = n)

    where you replace xxxx and n with values that you know should raise a
    duplicate?

    You could also try putting square brackets around your table name, since
    you've got embedded spaces in it:

    DCount("*", "[Contract Order Table]", "[Contract Number] = ...


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > The error is now gone (so I assume it is doing some kind of checking, but

    it
    > does not pop up when I knowingly put in a duplicate contract/order number.
    > Any guesses why not?
    >
    > "Douglas J Steele" wrote:
    >
    > > Since Contract Number is a text field, as opposed to a numeric one, you

    need
    > > to enclose the value in quotes:
    > >
    > > If DCount("*", "Contract Order Table", "[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
    > >
    > > If Order Number is also a text field, you'll need to do the same with

    it:
    > >
    > > If DCount("*", "Contract Order Table", "[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
    > >
    > > Exagerated for clarity, that's
    > >
    > > If DCount("*", "Contract Order Table", "[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
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > > Doug,
    > > >
    > > > Here's what I put in the code (After update on the contract number

    field)
    > > > and I'm getting a syntax 3075 error (missing operator in query

    expression
    > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > > >
    > > > If DCount("*", "Contract Order Table", "[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
    > > >
    > > > Any ideas what I'm doing. I should also say that there was no order

    > > number
    > > > in this particular record required.
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > If DCount("*", "MyTableName", "ContractNumber = " _
    > > > > & Me!ContractNumber & " AND OrderNumber = " _
    > > > > & Me!OrderNumber) > 0 Then
    > > > > Cancel = True
    > > > > MsgBox "This contract number already exists in the table."
    > > > > End If
    > > > >
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > > > This would work for just one field, but I need to validate two

    fields.
    > > > > There
    > > > > > can only be one record containing a unique set of two fields.
    > > > > >
    > > > > > 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)
    > > > > >
    > > > > > I need to check both the contract and order number together.
    > > > > > "tina" wrote:
    > > > > >
    > > > > > > you can validate the data in those fields at the form level, at

    the
    > > > > point of
    > > > > > > data entry. try using the form's BeforeUpdate event, and use a

    > > DCount()
    > > > > > > function to check the table for existing data that matches the

    newly
    > > > > entered
    > > > > > > data in the form. something along the lines of
    > > > > > >
    > > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > > > > & Me!ContractNumber) > 0 Then
    > > > > > > Cancel = True
    > > > > > > MsgBox "This contract number already exists in the

    table."
    > > > > > > End If
    > > > > > >
    > > > > > > if you're not familiar with the DCount function, or with the
    > > > > BeforeUpdate
    > > > > > > event, take a look at them in Help - and post back if you need

    > > further
    > > > > > > assistance.
    > > > > > >
    > > > > > > hth
    > > > > > >
    > > > > > >
    > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > > > > I have already read how to create a table with multiple key

    index,
    > > but
    > > > > > > here's
    > > > > > > > my problem. When I attempt to create this type of index, it

    says
    > > the
    > > > > > > index
    > > > > > > > can't contain a null value. I certainly understand this, but

    in
    > > my
    > > > > table
    > > > > > > I
    > > > > > > > have 3 fields I want as keys. Contract ID (auto number),

    Contract
    > > > > Number,
    > > > > > > > and Order Number. I really need to do the check on Contract

    > > Number
    > > > > and
    > > > > > > Order
    > > > > > > > Number to make sure there are no duplicate entries. However,

    On
    > > many
    > > > > > > > occassions, there is no order number associated with a

    contract
    > > number
    > > > > > > (it's
    > > > > > > > just a stand-alone contract) so that's where my null values

    come
    > > in.
    > > > > Is
    > > > > > > > there any work around for this or another way to check for

    > > duplicate
    > > > > > > values
    > > > > > > > check both those fields? I don't want there to be any record

    with
    > > the
    > > > > > > same
    > > > > > > > contract number and delivery order.
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  11. bondtk

    bondtk
    Expand Collapse
    Guest

    Doug,

    I tried placing your code below in the debug window. However, it seems I
    can't get the code to function with two fields being checked. I think the
    syntax is incorrect. I can get a correct response with either [Contract
    Number] by itself or [Order Number] by itself, but when I try to place the
    AND, I always get an error. Could you give me another example? Both fields
    are text. Don't forget that some of the order number fields can contain null.

    Thanks

    "Douglas J Steele" wrote:

    > What happens if you go to the Debug window (Ctrl-G) and type:
    >
    > ?DCount("*", "Contract Order Table", "[Contract Number] = 'xxxx' AND [Order
    > Number] = n)
    >
    > where you replace xxxx and n with values that you know should raise a
    > duplicate?
    >
    > You could also try putting square brackets around your table name, since
    > you've got embedded spaces in it:
    >
    > DCount("*", "[Contract Order Table]", "[Contract Number] = ...
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > > The error is now gone (so I assume it is doing some kind of checking, but

    > it
    > > does not pop up when I knowingly put in a duplicate contract/order number.
    > > Any guesses why not?
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > Since Contract Number is a text field, as opposed to a numeric one, you

    > need
    > > > to enclose the value in quotes:
    > > >
    > > > If DCount("*", "Contract Order Table", "[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
    > > >
    > > > If Order Number is also a text field, you'll need to do the same with

    > it:
    > > >
    > > > If DCount("*", "Contract Order Table", "[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
    > > >
    > > > Exagerated for clarity, that's
    > > >
    > > > If DCount("*", "Contract Order Table", "[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
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > > > Doug,
    > > > >
    > > > > Here's what I put in the code (After update on the contract number

    > field)
    > > > > and I'm getting a syntax 3075 error (missing operator in query

    > expression
    > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > > > >
    > > > > If DCount("*", "Contract Order Table", "[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
    > > > >
    > > > > Any ideas what I'm doing. I should also say that there was no order
    > > > number
    > > > > in this particular record required.
    > > > >
    > > > > "Douglas J Steele" wrote:
    > > > >
    > > > > > If DCount("*", "MyTableName", "ContractNumber = " _
    > > > > > & Me!ContractNumber & " AND OrderNumber = " _
    > > > > > & Me!OrderNumber) > 0 Then
    > > > > > Cancel = True
    > > > > > MsgBox "This contract number already exists in the table."
    > > > > > End If
    > > > > >
    > > > > >
    > > > > > --
    > > > > > Doug Steele, Microsoft Access MVP
    > > > > > http://I.Am/DougSteele
    > > > > > (no e-mails, please!)
    > > > > >
    > > > > >
    > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > > > > This would work for just one field, but I need to validate two

    > fields.
    > > > > > There
    > > > > > > can only be one record containing a unique set of two fields.
    > > > > > >
    > > > > > > 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)
    > > > > > >
    > > > > > > I need to check both the contract and order number together.
    > > > > > > "tina" wrote:
    > > > > > >
    > > > > > > > you can validate the data in those fields at the form level, at

    > the
    > > > > > point of
    > > > > > > > data entry. try using the form's BeforeUpdate event, and use a
    > > > DCount()
    > > > > > > > function to check the table for existing data that matches the

    > newly
    > > > > > entered
    > > > > > > > data in the form. something along the lines of
    > > > > > > >
    > > > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > > > > > & Me!ContractNumber) > 0 Then
    > > > > > > > Cancel = True
    > > > > > > > MsgBox "This contract number already exists in the

    > table."
    > > > > > > > End If
    > > > > > > >
    > > > > > > > if you're not familiar with the DCount function, or with the
    > > > > > BeforeUpdate
    > > > > > > > event, take a look at them in Help - and post back if you need
    > > > further
    > > > > > > > assistance.
    > > > > > > >
    > > > > > > > hth
    > > > > > > >
    > > > > > > >
    > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > > > > > I have already read how to create a table with multiple key

    > index,
    > > > but
    > > > > > > > here's
    > > > > > > > > my problem. When I attempt to create this type of index, it

    > says
    > > > the
    > > > > > > > index
    > > > > > > > > can't contain a null value. I certainly understand this, but

    > in
    > > > my
    > > > > > table
    > > > > > > > I
    > > > > > > > > have 3 fields I want as keys. Contract ID (auto number),

    > Contract
    > > > > > Number,
    > > > > > > > > and Order Number. I really need to do the check on Contract
    > > > Number
    > > > > > and
    > > > > > > > Order
    > > > > > > > > Number to make sure there are no duplicate entries. However,

    > On
    > > > many
    > > > > > > > > occassions, there is no order number associated with a

    > contract
    > > > number
    > > > > > > > (it's
    > > > > > > > > just a stand-alone contract) so that's where my null values

    > come
    > > > in.
    > > > > > Is
    > > > > > > > > there any work around for this or another way to check for
    > > > duplicate
    > > > > > > > values
    > > > > > > > > check both those fields? I don't want there to be any record

    > with
    > > > the
    > > > > > > > same
    > > > > > > > > contract number and delivery order.
    > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  12. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Exactly what did you type in the debug window?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    > Doug,
    >
    > I tried placing your code below in the debug window. However, it seems I
    > can't get the code to function with two fields being checked. I think the
    > syntax is incorrect. I can get a correct response with either [Contract
    > Number] by itself or [Order Number] by itself, but when I try to place the
    > AND, I always get an error. Could you give me another example? Both

    fields
    > are text. Don't forget that some of the order number fields can contain

    null.
    >
    > Thanks
    >
    > "Douglas J Steele" wrote:
    >
    > > What happens if you go to the Debug window (Ctrl-G) and type:
    > >
    > > ?DCount("*", "Contract Order Table", "[Contract Number] = 'xxxx' AND

    [Order
    > > Number] = n)
    > >
    > > where you replace xxxx and n with values that you know should raise a
    > > duplicate?
    > >
    > > You could also try putting square brackets around your table name, since
    > > you've got embedded spaces in it:
    > >
    > > DCount("*", "[Contract Order Table]", "[Contract Number] = ...
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > > > The error is now gone (so I assume it is doing some kind of checking,

    but
    > > it
    > > > does not pop up when I knowingly put in a duplicate contract/order

    number.
    > > > Any guesses why not?
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > Since Contract Number is a text field, as opposed to a numeric one,

    you
    > > need
    > > > > to enclose the value in quotes:
    > > > >
    > > > > If DCount("*", "Contract Order Table", "[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
    > > > >
    > > > > If Order Number is also a text field, you'll need to do the same

    with
    > > it:
    > > > >
    > > > > If DCount("*", "Contract Order Table", "[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
    > > > >
    > > > > Exagerated for clarity, that's
    > > > >
    > > > > If DCount("*", "Contract Order Table", "[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
    > > > >
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > > > > Doug,
    > > > > >
    > > > > > Here's what I put in the code (After update on the contract number

    > > field)
    > > > > > and I'm getting a syntax 3075 error (missing operator in query

    > > expression
    > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > > > > >
    > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > >
    > > > > > Any ideas what I'm doing. I should also say that there was no

    order
    > > > > number
    > > > > > in this particular record required.
    > > > > >
    > > > > > "Douglas J Steele" wrote:
    > > > > >
    > > > > > > If DCount("*", "MyTableName", "ContractNumber = " _
    > > > > > > & Me!ContractNumber & " AND OrderNumber = " _
    > > > > > > & Me!OrderNumber) > 0 Then
    > > > > > > Cancel = True
    > > > > > > MsgBox "This contract number already exists in the

    table."
    > > > > > > End If
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > > Doug Steele, Microsoft Access MVP
    > > > > > > http://I.Am/DougSteele
    > > > > > > (no e-mails, please!)
    > > > > > >
    > > > > > >
    > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > > > > > This would work for just one field, but I need to validate two

    > > fields.
    > > > > > > There
    > > > > > > > can only be one record containing a unique set of two fields.
    > > > > > > >
    > > > > > > > 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)
    > > > > > > >
    > > > > > > > I need to check both the contract and order number together.
    > > > > > > > "tina" wrote:
    > > > > > > >
    > > > > > > > > you can validate the data in those fields at the form level,

    at
    > > the
    > > > > > > point of
    > > > > > > > > data entry. try using the form's BeforeUpdate event, and use

    a
    > > > > DCount()
    > > > > > > > > function to check the table for existing data that matches

    the
    > > newly
    > > > > > > entered
    > > > > > > > > data in the form. something along the lines of
    > > > > > > > >
    > > > > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > > > > > > & Me!ContractNumber) > 0 Then
    > > > > > > > > Cancel = True
    > > > > > > > > MsgBox "This contract number already exists in the

    > > table."
    > > > > > > > > End If
    > > > > > > > >
    > > > > > > > > if you're not familiar with the DCount function, or with the
    > > > > > > BeforeUpdate
    > > > > > > > > event, take a look at them in Help - and post back if you

    need
    > > > > further
    > > > > > > > > assistance.
    > > > > > > > >
    > > > > > > > > hth
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > > > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > > > > > > I have already read how to create a table with multiple

    key
    > > index,
    > > > > but
    > > > > > > > > here's
    > > > > > > > > > my problem. When I attempt to create this type of index,

    it
    > > says
    > > > > the
    > > > > > > > > index
    > > > > > > > > > can't contain a null value. I certainly understand this,

    but
    > > in
    > > > > my
    > > > > > > table
    > > > > > > > > I
    > > > > > > > > > have 3 fields I want as keys. Contract ID (auto number),

    > > Contract
    > > > > > > Number,
    > > > > > > > > > and Order Number. I really need to do the check on

    Contract
    > > > > Number
    > > > > > > and
    > > > > > > > > Order
    > > > > > > > > > Number to make sure there are no duplicate entries.

    However,
    > > On
    > > > > many
    > > > > > > > > > occassions, there is no order number associated with a

    > > contract
    > > > > number
    > > > > > > > > (it's
    > > > > > > > > > just a stand-alone contract) so that's where my null

    values
    > > come
    > > > > in.
    > > > > > > Is
    > > > > > > > > > there any work around for this or another way to check for
    > > > > duplicate
    > > > > > > > > values
    > > > > > > > > > check both those fields? I don't want there to be any

    record
    > > with
    > > > > the
    > > > > > > > > same
    > > > > > > > > > contract number and delivery order.
    > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  13. bondtk

    bondtk
    Expand Collapse
    Guest

    Doug,

    I put the following (with various other things too just to try):

    ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' " & AND
    > "[Order
    > > > Number] = '1234' ")


    I'm sure I'm just not getting the correct syntax. It looks like it requires
    all these single quotes, underscores, and & that I have no idea which goes
    where. With my limited BASIC programming skills, it seems like a fairly
    simple check.

    "Douglas J Steele" wrote:

    > Exactly what did you type in the debug window?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    > > Doug,
    > >
    > > I tried placing your code below in the debug window. However, it seems I
    > > can't get the code to function with two fields being checked. I think the
    > > syntax is incorrect. I can get a correct response with either [Contract
    > > Number] by itself or [Order Number] by itself, but when I try to place the
    > > AND, I always get an error. Could you give me another example? Both

    > fields
    > > are text. Don't forget that some of the order number fields can contain

    > null.
    > >
    > > Thanks
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > What happens if you go to the Debug window (Ctrl-G) and type:
    > > >
    > > > ?DCount("*", "Contract Order Table", "[Contract Number] = 'xxxx' AND

    > [Order
    > > > Number] = n)
    > > >
    > > > where you replace xxxx and n with values that you know should raise a
    > > > duplicate?
    > > >
    > > > You could also try putting square brackets around your table name, since
    > > > you've got embedded spaces in it:
    > > >
    > > > DCount("*", "[Contract Order Table]", "[Contract Number] = ...
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > > > > The error is now gone (so I assume it is doing some kind of checking,

    > but
    > > > it
    > > > > does not pop up when I knowingly put in a duplicate contract/order

    > number.
    > > > > Any guesses why not?
    > > > >
    > > > > "Douglas J Steele" wrote:
    > > > >
    > > > > > Since Contract Number is a text field, as opposed to a numeric one,

    > you
    > > > need
    > > > > > to enclose the value in quotes:
    > > > > >
    > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > >
    > > > > > If Order Number is also a text field, you'll need to do the same

    > with
    > > > it:
    > > > > >
    > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > >
    > > > > > Exagerated for clarity, that's
    > > > > >
    > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > >
    > > > > >
    > > > > > --
    > > > > > Doug Steele, Microsoft Access MVP
    > > > > > http://I.Am/DougSteele
    > > > > > (no e-mails, please!)
    > > > > >
    > > > > >
    > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > > > > > Doug,
    > > > > > >
    > > > > > > Here's what I put in the code (After update on the contract number
    > > > field)
    > > > > > > and I'm getting a syntax 3075 error (missing operator in query
    > > > expression
    > > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > > > > > >
    > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > >
    > > > > > > Any ideas what I'm doing. I should also say that there was no

    > order
    > > > > > number
    > > > > > > in this particular record required.
    > > > > > >
    > > > > > > "Douglas J Steele" wrote:
    > > > > > >
    > > > > > > > If DCount("*", "MyTableName", "ContractNumber = " _
    > > > > > > > & Me!ContractNumber & " AND OrderNumber = " _
    > > > > > > > & Me!OrderNumber) > 0 Then
    > > > > > > > Cancel = True
    > > > > > > > MsgBox "This contract number already exists in the

    > table."
    > > > > > > > End If
    > > > > > > >
    > > > > > > >
    > > > > > > > --
    > > > > > > > Doug Steele, Microsoft Access MVP
    > > > > > > > http://I.Am/DougSteele
    > > > > > > > (no e-mails, please!)
    > > > > > > >
    > > > > > > >
    > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > > > > > > This would work for just one field, but I need to validate two
    > > > fields.
    > > > > > > > There
    > > > > > > > > can only be one record containing a unique set of two fields.
    > > > > > > > >
    > > > > > > > > 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)
    > > > > > > > >
    > > > > > > > > I need to check both the contract and order number together.
    > > > > > > > > "tina" wrote:
    > > > > > > > >
    > > > > > > > > > you can validate the data in those fields at the form level,

    > at
    > > > the
    > > > > > > > point of
    > > > > > > > > > data entry. try using the form's BeforeUpdate event, and use

    > a
    > > > > > DCount()
    > > > > > > > > > function to check the table for existing data that matches

    > the
    > > > newly
    > > > > > > > entered
    > > > > > > > > > data in the form. something along the lines of
    > > > > > > > > >
    > > > > > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > > > > > > > & Me!ContractNumber) > 0 Then
    > > > > > > > > > Cancel = True
    > > > > > > > > > MsgBox "This contract number already exists in the
    > > > table."
    > > > > > > > > > End If
    > > > > > > > > >
    > > > > > > > > > if you're not familiar with the DCount function, or with the
    > > > > > > > BeforeUpdate
    > > > > > > > > > event, take a look at them in Help - and post back if you

    > need
    > > > > > further
    > > > > > > > > > assistance.
    > > > > > > > > >
    > > > > > > > > > hth
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > > > > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > > > > > > > I have already read how to create a table with multiple

    > key
    > > > index,
    > > > > > but
    > > > > > > > > > here's
    > > > > > > > > > > my problem. When I attempt to create this type of index,

    > it
    > > > says
    > > > > > the
    > > > > > > > > > index
    > > > > > > > > > > can't contain a null value. I certainly understand this,

    > but
    > > > in
    > > > > > my
    > > > > > > > table
    > > > > > > > > > I
    > > > > > > > > > > have 3 fields I want as keys. Contract ID (auto number),
    > > > Contract
    > > > > > > > Number,
    > > > > > > > > > > and Order Number. I really need to do the check on

    > Contract
    > > > > > Number
    > > > > > > > and
    > > > > > > > > > Order
    > > > > > > > > > > Number to make sure there are no duplicate entries.

    > However,
    > > > On
    > > > > > many
    > > > > > > > > > > occassions, there is no order number associated with a
    > > > contract
    > > > > > number
    > > > > > > > > > (it's
    > > > > > > > > > > just a stand-alone contract) so that's where my null

    > values
    > > > come
    > > > > > in.
    > > > > > > > Is
    > > > > > > > > > > there any work around for this or another way to check for
    > > > > > duplicate
    > > > > > > > > > values
    > > > > > > > > > > check both those fields? I don't want there to be any

    > record
    > > > with
    > > > > > the
    > > > > > > > > > same
    > > > > > > > > > > contract number and delivery order.
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  14. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    The AND needs to be part of the string:

    ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' AND
    [Order Number] = '1234' ")

    Is Order Number a text field in the table?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:DA1AB95A-703D-4756-820C-75E1FFC111DD@microsoft.com...
    > Doug,
    >
    > I put the following (with various other things too just to try):
    >
    > ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' " &

    AND
    > > "[Order
    > > > > Number] = '1234' ")

    >
    > I'm sure I'm just not getting the correct syntax. It looks like it

    requires
    > all these single quotes, underscores, and & that I have no idea which goes
    > where. With my limited BASIC programming skills, it seems like a fairly
    > simple check.
    >
    > "Douglas J Steele" wrote:
    >
    > > Exactly what did you type in the debug window?
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    > > > Doug,
    > > >
    > > > I tried placing your code below in the debug window. However, it

    seems I
    > > > can't get the code to function with two fields being checked. I think

    the
    > > > syntax is incorrect. I can get a correct response with either

    [Contract
    > > > Number] by itself or [Order Number] by itself, but when I try to place

    the
    > > > AND, I always get an error. Could you give me another example? Both

    > > fields
    > > > are text. Don't forget that some of the order number fields can

    contain
    > > null.
    > > >
    > > > Thanks
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > What happens if you go to the Debug window (Ctrl-G) and type:
    > > > >
    > > > > ?DCount("*", "Contract Order Table", "[Contract Number] = 'xxxx' AND

    > > [Order
    > > > > Number] = n)
    > > > >
    > > > > where you replace xxxx and n with values that you know should raise

    a
    > > > > duplicate?
    > > > >
    > > > > You could also try putting square brackets around your table name,

    since
    > > > > you've got embedded spaces in it:
    > > > >
    > > > > DCount("*", "[Contract Order Table]", "[Contract Number] = ...
    > > > >
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > > > > > The error is now gone (so I assume it is doing some kind of

    checking,
    > > but
    > > > > it
    > > > > > does not pop up when I knowingly put in a duplicate contract/order

    > > number.
    > > > > > Any guesses why not?
    > > > > >
    > > > > > "Douglas J Steele" wrote:
    > > > > >
    > > > > > > Since Contract Number is a text field, as opposed to a numeric

    one,
    > > you
    > > > > need
    > > > > > > to enclose the value in quotes:
    > > > > > >
    > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > >
    > > > > > > If Order Number is also a text field, you'll need to do the same

    > > with
    > > > > it:
    > > > > > >
    > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > >
    > > > > > > Exagerated for clarity, that's
    > > > > > >
    > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > > Doug Steele, Microsoft Access MVP
    > > > > > > http://I.Am/DougSteele
    > > > > > > (no e-mails, please!)
    > > > > > >
    > > > > > >
    > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > > > > > > Doug,
    > > > > > > >
    > > > > > > > Here's what I put in the code (After update on the contract

    number
    > > > > field)
    > > > > > > > and I'm getting a syntax 3075 error (missing operator in query
    > > > > expression
    > > > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > > > > > > >
    > > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > > >
    > > > > > > > Any ideas what I'm doing. I should also say that there was no

    > > order
    > > > > > > number
    > > > > > > > in this particular record required.
    > > > > > > >
    > > > > > > > "Douglas J Steele" wrote:
    > > > > > > >
    > > > > > > > > If DCount("*", "MyTableName", "ContractNumber = " _
    > > > > > > > > & Me!ContractNumber & " AND OrderNumber = " _
    > > > > > > > > & Me!OrderNumber) > 0 Then
    > > > > > > > > Cancel = True
    > > > > > > > > MsgBox "This contract number already exists in the

    > > table."
    > > > > > > > > End If
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > > Doug Steele, Microsoft Access MVP
    > > > > > > > > http://I.Am/DougSteele
    > > > > > > > > (no e-mails, please!)
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > > > > > > > This would work for just one field, but I need to validate

    two
    > > > > fields.
    > > > > > > > > There
    > > > > > > > > > can only be one record containing a unique set of two

    fields.
    > > > > > > > > >
    > > > > > > > > > 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)
    > > > > > > > > >
    > > > > > > > > > I need to check both the contract and order number

    together.
    > > > > > > > > > "tina" wrote:
    > > > > > > > > >
    > > > > > > > > > > you can validate the data in those fields at the form

    level,
    > > at
    > > > > the
    > > > > > > > > point of
    > > > > > > > > > > data entry. try using the form's BeforeUpdate event, and

    use
    > > a
    > > > > > > DCount()
    > > > > > > > > > > function to check the table for existing data that

    matches
    > > the
    > > > > newly
    > > > > > > > > entered
    > > > > > > > > > > data in the form. something along the lines of
    > > > > > > > > > >
    > > > > > > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > > > > > > > > & Me!ContractNumber) > 0 Then
    > > > > > > > > > > Cancel = True
    > > > > > > > > > > MsgBox "This contract number already exists in

    the
    > > > > table."
    > > > > > > > > > > End If
    > > > > > > > > > >
    > > > > > > > > > > if you're not familiar with the DCount function, or with

    the
    > > > > > > > > BeforeUpdate
    > > > > > > > > > > event, take a look at them in Help - and post back if

    you
    > > need
    > > > > > > further
    > > > > > > > > > > assistance.
    > > > > > > > > > >
    > > > > > > > > > > hth
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in

    message
    > > > > > > > > > >

    news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > > > > > > > > I have already read how to create a table with

    multiple
    > > key
    > > > > index,
    > > > > > > but
    > > > > > > > > > > here's
    > > > > > > > > > > > my problem. When I attempt to create this type of

    index,
    > > it
    > > > > says
    > > > > > > the
    > > > > > > > > > > index
    > > > > > > > > > > > can't contain a null value. I certainly understand

    this,
    > > but
    > > > > in
    > > > > > > my
    > > > > > > > > table
    > > > > > > > > > > I
    > > > > > > > > > > > have 3 fields I want as keys. Contract ID (auto

    number),
    > > > > Contract
    > > > > > > > > Number,
    > > > > > > > > > > > and Order Number. I really need to do the check on

    > > Contract
    > > > > > > Number
    > > > > > > > > and
    > > > > > > > > > > Order
    > > > > > > > > > > > Number to make sure there are no duplicate entries.

    > > However,
    > > > > On
    > > > > > > many
    > > > > > > > > > > > occassions, there is no order number associated with a
    > > > > contract
    > > > > > > number
    > > > > > > > > > > (it's
    > > > > > > > > > > > just a stand-alone contract) so that's where my null

    > > values
    > > > > come
    > > > > > > in.
    > > > > > > > > Is
    > > > > > > > > > > > there any work around for this or another way to check

    for
    > > > > > > duplicate
    > > > > > > > > > > values
    > > > > > > > > > > > check both those fields? I don't want there to be any

    > > record
    > > > > with
    > > > > > > the
    > > > > > > > > > > same
    > > > > > > > > > > > contract number and delivery order.
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  15. bondtk

    bondtk
    Expand Collapse
    Guest

    Yes, both fields are strings.

    "Douglas J Steele" wrote:

    > The AND needs to be part of the string:
    >
    > ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' AND
    > [Order Number] = '1234' ")
    >
    > Is Order Number a text field in the table?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:DA1AB95A-703D-4756-820C-75E1FFC111DD@microsoft.com...
    > > Doug,
    > >
    > > I put the following (with various other things too just to try):
    > >
    > > ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' " &

    > AND
    > > > "[Order
    > > > > > Number] = '1234' ")

    > >
    > > I'm sure I'm just not getting the correct syntax. It looks like it

    > requires
    > > all these single quotes, underscores, and & that I have no idea which goes
    > > where. With my limited BASIC programming skills, it seems like a fairly
    > > simple check.
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > Exactly what did you type in the debug window?
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    > > > > Doug,
    > > > >
    > > > > I tried placing your code below in the debug window. However, it

    > seems I
    > > > > can't get the code to function with two fields being checked. I think

    > the
    > > > > syntax is incorrect. I can get a correct response with either

    > [Contract
    > > > > Number] by itself or [Order Number] by itself, but when I try to place

    > the
    > > > > AND, I always get an error. Could you give me another example? Both
    > > > fields
    > > > > are text. Don't forget that some of the order number fields can

    > contain
    > > > null.
    > > > >
    > > > > Thanks
    > > > >
    > > > > "Douglas J Steele" wrote:
    > > > >
    > > > > > What happens if you go to the Debug window (Ctrl-G) and type:
    > > > > >
    > > > > > ?DCount("*", "Contract Order Table", "[Contract Number] = 'xxxx' AND
    > > > [Order
    > > > > > Number] = n)
    > > > > >
    > > > > > where you replace xxxx and n with values that you know should raise

    > a
    > > > > > duplicate?
    > > > > >
    > > > > > You could also try putting square brackets around your table name,

    > since
    > > > > > you've got embedded spaces in it:
    > > > > >
    > > > > > DCount("*", "[Contract Order Table]", "[Contract Number] = ...
    > > > > >
    > > > > >
    > > > > > --
    > > > > > Doug Steele, Microsoft Access MVP
    > > > > > http://I.Am/DougSteele
    > > > > > (no e-mails, please!)
    > > > > >
    > > > > >
    > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > > > > > > The error is now gone (so I assume it is doing some kind of

    > checking,
    > > > but
    > > > > > it
    > > > > > > does not pop up when I knowingly put in a duplicate contract/order
    > > > number.
    > > > > > > Any guesses why not?
    > > > > > >
    > > > > > > "Douglas J Steele" wrote:
    > > > > > >
    > > > > > > > Since Contract Number is a text field, as opposed to a numeric

    > one,
    > > > you
    > > > > > need
    > > > > > > > to enclose the value in quotes:
    > > > > > > >
    > > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > > >
    > > > > > > > If Order Number is also a text field, you'll need to do the same
    > > > with
    > > > > > it:
    > > > > > > >
    > > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > > >
    > > > > > > > Exagerated for clarity, that's
    > > > > > > >
    > > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > > >
    > > > > > > >
    > > > > > > > --
    > > > > > > > Doug Steele, Microsoft Access MVP
    > > > > > > > http://I.Am/DougSteele
    > > > > > > > (no e-mails, please!)
    > > > > > > >
    > > > > > > >
    > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > > > > > > > Doug,
    > > > > > > > >
    > > > > > > > > Here's what I put in the code (After update on the contract

    > number
    > > > > > field)
    > > > > > > > > and I'm getting a syntax 3075 error (missing operator in query
    > > > > > expression
    > > > > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > > > > > > > >
    > > > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > > > > > >
    > > > > > > > > Any ideas what I'm doing. I should also say that there was no
    > > > order
    > > > > > > > number
    > > > > > > > > in this particular record required.
    > > > > > > > >
    > > > > > > > > "Douglas J Steele" wrote:
    > > > > > > > >
    > > > > > > > > > If DCount("*", "MyTableName", "ContractNumber = " _
    > > > > > > > > > & Me!ContractNumber & " AND OrderNumber = " _
    > > > > > > > > > & Me!OrderNumber) > 0 Then
    > > > > > > > > > Cancel = True
    > > > > > > > > > MsgBox "This contract number already exists in the
    > > > table."
    > > > > > > > > > End If
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > > Doug Steele, Microsoft Access MVP
    > > > > > > > > > http://I.Am/DougSteele
    > > > > > > > > > (no e-mails, please!)
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > > > > > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > > > > > > > > This would work for just one field, but I need to validate

    > two
    > > > > > fields.
    > > > > > > > > > There
    > > > > > > > > > > can only be one record containing a unique set of two

    > fields.
    > > > > > > > > > >
    > > > > > > > > > > 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)
    > > > > > > > > > >
    > > > > > > > > > > I need to check both the contract and order number

    > together.
    > > > > > > > > > > "tina" wrote:
    > > > > > > > > > >
    > > > > > > > > > > > you can validate the data in those fields at the form

    > level,
    > > > at
    > > > > > the
    > > > > > > > > > point of
    > > > > > > > > > > > data entry. try using the form's BeforeUpdate event, and

    > use
    > > > a
    > > > > > > > DCount()
    > > > > > > > > > > > function to check the table for existing data that

    > matches
    > > > the
    > > > > > newly
    > > > > > > > > > entered
    > > > > > > > > > > > data in the form. something along the lines of
    > > > > > > > > > > >
    > > > > > > > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > > > > > > > > > > > & Me!ContractNumber) > 0 Then
    > > > > > > > > > > > Cancel = True
    > > > > > > > > > > > MsgBox "This contract number already exists in

    > the
    > > > > > table."
    > > > > > > > > > > > End If
    > > > > > > > > > > >
    > > > > > > > > > > > if you're not familiar with the DCount function, or with

    > the
    > > > > > > > > > BeforeUpdate
    > > > > > > > > > > > event, take a look at them in Help - and post back if

    > you
    > > > need
    > > > > > > > further
    > > > > > > > > > > > assistance.
    > > > > > > > > > > >
    > > > > > > > > > > > hth
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in

    > message
    > > > > > > > > > > >

    > news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > > > > > > > > > > > I have already read how to create a table with

    > multiple
    > > > key
    > > > > > index,
    > > > > > > > but
    > > > > > > > > > > > here's
    > > > > > > > > > > > > my problem. When I attempt to create this type of

    > index,
    > > > it
    > > > > > says
    > > > > > > > the
    > > > > > > > > > > > index
    > > > > > > > > > > > > can't contain a null value. I certainly understand

    > this,
    > > > but
    > > > > > in
    > > > > > > > my
    > > > > > > > > > table
    > > > > > > > > > > > I
    > > > > > > > > > > > > have 3 fields I want as keys. Contract ID (auto

    > number),
    > > > > > Contract
    > > > > > > > > > Number,
    > > > > > > > > > > > > and Order Number. I really need to do the check on
    > > > Contract
    > > > > > > > Number
    > > > > > > > > > and
    > > > > > > > > > > > Order
    > > > > > > > > > > > > Number to make sure there are no duplicate entries.
    > > > However,
    > > > > > On
    > > > > > > > many
    > > > > > > > > > > > > occassions, there is no order number associated with a
    > > > > > contract
    > > > > > > > number
    > > > > > > > > > > > (it's
    > > > > > > > > > > > > just a stand-alone contract) so that's where my null
    > > > values
    > > > > > come
    > > > > > > > in.
    > > > > > > > > > Is
    > > > > > > > > > > > > there any work around for this or another way to check

    > for
    > > > > > > > duplicate
    > > > > > > > > > > > values
    > > > > > > > > > > > > check both those fields? I don't want there to be any
    > > > record
    > > > > > with
    > > > > > > > the
    > > > > > > > > > > > same
    > > > > > > > > > > > > contract number and delivery order.
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
     
  16. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Okay, so what happens with the corrected syntax I suggested?

    The sample code I gave way back will generate the correct syntax, by the
    way.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:44F7AA5E-20E0-4278-B627-587096C2FAB8@microsoft.com...
    > Yes, both fields are strings.
    >
    > "Douglas J Steele" wrote:
    >
    >> The AND needs to be part of the string:
    >>
    >> ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' AND
    >> [Order Number] = '1234' ")
    >>
    >> Is Order Number a text field in the table?
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    >> news:DA1AB95A-703D-4756-820C-75E1FFC111DD@microsoft.com...
    >> > Doug,
    >> >
    >> > I put the following (with various other things too just to try):
    >> >
    >> > ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' " &

    >> AND
    >> > > "[Order
    >> > > > > Number] = '1234' ")
    >> >
    >> > I'm sure I'm just not getting the correct syntax. It looks like it

    >> requires
    >> > all these single quotes, underscores, and & that I have no idea which
    >> > goes
    >> > where. With my limited BASIC programming skills, it seems like a
    >> > fairly
    >> > simple check.
    >> >
    >> > "Douglas J Steele" wrote:
    >> >
    >> > > Exactly what did you type in the debug window?
    >> > >
    >> > > --
    >> > > Doug Steele, Microsoft Access MVP
    >> > > http://I.Am/DougSteele
    >> > > (no e-mails, please!)
    >> > >
    >> > >
    >> > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    >> > > news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    >> > > > Doug,
    >> > > >
    >> > > > I tried placing your code below in the debug window. However, it

    >> seems I
    >> > > > can't get the code to function with two fields being checked. I
    >> > > > think

    >> the
    >> > > > syntax is incorrect. I can get a correct response with either

    >> [Contract
    >> > > > Number] by itself or [Order Number] by itself, but when I try to
    >> > > > place

    >> the
    >> > > > AND, I always get an error. Could you give me another example?
    >> > > > Both
    >> > > fields
    >> > > > are text. Don't forget that some of the order number fields can

    >> contain
    >> > > null.
    >> > > >
    >> > > > Thanks
    >> > > >
    >> > > > "Douglas J Steele" wrote:
    >> > > >
    >> > > > > What happens if you go to the Debug window (Ctrl-G) and type:
    >> > > > >
    >> > > > > ?DCount("*", "Contract Order Table", "[Contract Number] = 'xxxx'
    >> > > > > AND
    >> > > [Order
    >> > > > > Number] = n)
    >> > > > >
    >> > > > > where you replace xxxx and n with values that you know should
    >> > > > > raise

    >> a
    >> > > > > duplicate?
    >> > > > >
    >> > > > > You could also try putting square brackets around your table
    >> > > > > name,

    >> since
    >> > > > > you've got embedded spaces in it:
    >> > > > >
    >> > > > > DCount("*", "[Contract Order Table]", "[Contract Number] = ...
    >> > > > >
    >> > > > >
    >> > > > > --
    >> > > > > Doug Steele, Microsoft Access MVP
    >> > > > > http://I.Am/DougSteele
    >> > > > > (no e-mails, please!)
    >> > > > >
    >> > > > >
    >> > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    >> > > > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    >> > > > > > The error is now gone (so I assume it is doing some kind of

    >> checking,
    >> > > but
    >> > > > > it
    >> > > > > > does not pop up when I knowingly put in a duplicate
    >> > > > > > contract/order
    >> > > number.
    >> > > > > > Any guesses why not?
    >> > > > > >
    >> > > > > > "Douglas J Steele" wrote:
    >> > > > > >
    >> > > > > > > Since Contract Number is a text field, as opposed to a
    >> > > > > > > numeric

    >> one,
    >> > > you
    >> > > > > need
    >> > > > > > > to enclose the value in quotes:
    >> > > > > > >
    >> > > > > > > If DCount("*", "Contract Order Table", "[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
    >> > > > > > >
    >> > > > > > > If Order Number is also a text field, you'll need to do the
    >> > > > > > > same
    >> > > with
    >> > > > > it:
    >> > > > > > >
    >> > > > > > > If DCount("*", "Contract Order Table", "[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
    >> > > > > > >
    >> > > > > > > Exagerated for clarity, that's
    >> > > > > > >
    >> > > > > > > If DCount("*", "Contract Order Table", "[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
    >> > > > > > >
    >> > > > > > >
    >> > > > > > > --
    >> > > > > > > Doug Steele, Microsoft Access MVP
    >> > > > > > > http://I.Am/DougSteele
    >> > > > > > > (no e-mails, please!)
    >> > > > > > >
    >> > > > > > >
    >> > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    >> > > > > > > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    >> > > > > > > > Doug,
    >> > > > > > > >
    >> > > > > > > > Here's what I put in the code (After update on the contract

    >> number
    >> > > > > field)
    >> > > > > > > > and I'm getting a syntax 3075 error (missing operator in
    >> > > > > > > > query
    >> > > > > expression
    >> > > > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    >> > > > > > > >
    >> > > > > > > > If DCount("*", "Contract Order Table", "[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
    >> > > > > > > >
    >> > > > > > > > Any ideas what I'm doing. I should also say that there was
    >> > > > > > > > no
    >> > > order
    >> > > > > > > number
    >> > > > > > > > in this particular record required.
    >> > > > > > > >
    >> > > > > > > > "Douglas J Steele" wrote:
    >> > > > > > > >
    >> > > > > > > > > If DCount("*", "MyTableName", "ContractNumber = " _
    >> > > > > > > > > & Me!ContractNumber & " AND OrderNumber = " _
    >> > > > > > > > > & Me!OrderNumber) > 0 Then
    >> > > > > > > > > Cancel = True
    >> > > > > > > > > MsgBox "This contract number already exists in
    >> > > > > > > > > the
    >> > > table."
    >> > > > > > > > > End If
    >> > > > > > > > >
    >> > > > > > > > >
    >> > > > > > > > > --
    >> > > > > > > > > Doug Steele, Microsoft Access MVP
    >> > > > > > > > > http://I.Am/DougSteele
    >> > > > > > > > > (no e-mails, please!)
    >> > > > > > > > >
    >> > > > > > > > >
    >> > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in
    >> > > > > > > > > message
    >> > > > > > > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    >> > > > > > > > > > This would work for just one field, but I need to
    >> > > > > > > > > > validate

    >> two
    >> > > > > fields.
    >> > > > > > > > > There
    >> > > > > > > > > > can only be one record containing a unique set of two

    >> fields.
    >> > > > > > > > > >
    >> > > > > > > > > > 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)
    >> > > > > > > > > >
    >> > > > > > > > > > I need to check both the contract and order number

    >> together.
    >> > > > > > > > > > "tina" wrote:
    >> > > > > > > > > >
    >> > > > > > > > > > > you can validate the data in those fields at the form

    >> level,
    >> > > at
    >> > > > > the
    >> > > > > > > > > point of
    >> > > > > > > > > > > data entry. try using the form's BeforeUpdate event,
    >> > > > > > > > > > > and

    >> use
    >> > > a
    >> > > > > > > DCount()
    >> > > > > > > > > > > function to check the table for existing data that

    >> matches
    >> > > the
    >> > > > > newly
    >> > > > > > > > > entered
    >> > > > > > > > > > > data in the form. something along the lines of
    >> > > > > > > > > > >
    >> > > > > > > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    >> > > > > > > > > > > & Me!ContractNumber) > 0 Then
    >> > > > > > > > > > > Cancel = True
    >> > > > > > > > > > > MsgBox "This contract number already exists
    >> > > > > > > > > > > in

    >> the
    >> > > > > table."
    >> > > > > > > > > > > End If
    >> > > > > > > > > > >
    >> > > > > > > > > > > if you're not familiar with the DCount function, or
    >> > > > > > > > > > > with

    >> the
    >> > > > > > > > > BeforeUpdate
    >> > > > > > > > > > > event, take a look at them in Help - and post back if

    >> you
    >> > > need
    >> > > > > > > further
    >> > > > > > > > > > > assistance.
    >> > > > > > > > > > >
    >> > > > > > > > > > > hth
    >> > > > > > > > > > >
    >> > > > > > > > > > >
    >> > > > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in

    >> message
    >> > > > > > > > > > >

    >> news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    >> > > > > > > > > > > > I have already read how to create a table with

    >> multiple
    >> > > key
    >> > > > > index,
    >> > > > > > > but
    >> > > > > > > > > > > here's
    >> > > > > > > > > > > > my problem. When I attempt to create this type of

    >> index,
    >> > > it
    >> > > > > says
    >> > > > > > > the
    >> > > > > > > > > > > index
    >> > > > > > > > > > > > can't contain a null value. I certainly understand

    >> this,
    >> > > but
    >> > > > > in
    >> > > > > > > my
    >> > > > > > > > > table
    >> > > > > > > > > > > I
    >> > > > > > > > > > > > have 3 fields I want as keys. Contract ID (auto

    >> number),
    >> > > > > Contract
    >> > > > > > > > > Number,
    >> > > > > > > > > > > > and Order Number. I really need to do the check on
    >> > > Contract
    >> > > > > > > Number
    >> > > > > > > > > and
    >> > > > > > > > > > > Order
    >> > > > > > > > > > > > Number to make sure there are no duplicate entries.
    >> > > However,
    >> > > > > On
    >> > > > > > > many
    >> > > > > > > > > > > > occassions, there is no order number associated
    >> > > > > > > > > > > > with a
    >> > > > > contract
    >> > > > > > > number
    >> > > > > > > > > > > (it's
    >> > > > > > > > > > > > just a stand-alone contract) so that's where my
    >> > > > > > > > > > > > null
    >> > > values
    >> > > > > come
    >> > > > > > > in.
    >> > > > > > > > > Is
    >> > > > > > > > > > > > there any work around for this or another way to
    >> > > > > > > > > > > > check

    >> for
    >> > > > > > > duplicate
    >> > > > > > > > > > > values
    >> > > > > > > > > > > > check both those fields? I don't want there to be
    >> > > > > > > > > > > > any
    >> > > record
    >> > > > > with
    >> > > > > > > the
    >> > > > > > > > > > > same
    >> > > > > > > > > > > > contract number and delivery order.
    >> > > > > > > > > > >
    >> > > > > > > > > > >
    >> > > > > > > > > > >
    >> > > > > > > > >
    >> > > > > > > > >
    >> > > > > > > > >
     
  17. bondtk

    bondtk
    Expand Collapse
    Guest

    Doug,

    I still can't get the result with both fields, just one or the other. Could
    you enter the whole code again just to make sure I'm not missing any " or '.
    Just use sample string data in the comparisons. I can try to adapt it to the
    field on the form after I see your code. Thanks for all your help. Sorry I
    can't get it to work yet.

    Troy

    "Douglas J. Steele" wrote:

    > Okay, so what happens with the corrected syntax I suggested?
    >
    > The sample code I gave way back will generate the correct syntax, by the
    > way.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:44F7AA5E-20E0-4278-B627-587096C2FAB8@microsoft.com...
    > > Yes, both fields are strings.
    > >
    > > "Douglas J Steele" wrote:
    > >
    > >> The AND needs to be part of the string:
    > >>
    > >> ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' AND
    > >> [Order Number] = '1234' ")
    > >>
    > >> Is Order Number a text field in the table?
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no e-mails, please!)
    > >>
    > >>
    > >> "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > >> news:DA1AB95A-703D-4756-820C-75E1FFC111DD@microsoft.com...
    > >> > Doug,
    > >> >
    > >> > I put the following (with various other things too just to try):
    > >> >
    > >> > ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234' " &
    > >> AND
    > >> > > "[Order
    > >> > > > > Number] = '1234' ")
    > >> >
    > >> > I'm sure I'm just not getting the correct syntax. It looks like it
    > >> requires
    > >> > all these single quotes, underscores, and & that I have no idea which
    > >> > goes
    > >> > where. With my limited BASIC programming skills, it seems like a
    > >> > fairly
    > >> > simple check.
    > >> >
    > >> > "Douglas J Steele" wrote:
    > >> >
    > >> > > Exactly what did you type in the debug window?
    > >> > >
    > >> > > --
    > >> > > Doug Steele, Microsoft Access MVP
    > >> > > http://I.Am/DougSteele
    > >> > > (no e-mails, please!)
    > >> > >
    > >> > >
    > >> > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > >> > > news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    > >> > > > Doug,
    > >> > > >
    > >> > > > I tried placing your code below in the debug window. However, it
    > >> seems I
    > >> > > > can't get the code to function with two fields being checked. I
    > >> > > > think
    > >> the
    > >> > > > syntax is incorrect. I can get a correct response with either
    > >> [Contract
    > >> > > > Number] by itself or [Order Number] by itself, but when I try to
    > >> > > > place
    > >> the
    > >> > > > AND, I always get an error. Could you give me another example?
    > >> > > > Both
    > >> > > fields
    > >> > > > are text. Don't forget that some of the order number fields can
    > >> contain
    > >> > > null.
    > >> > > >
    > >> > > > Thanks
    > >> > > >
    > >> > > > "Douglas J Steele" wrote:
    > >> > > >
    > >> > > > > What happens if you go to the Debug window (Ctrl-G) and type:
    > >> > > > >
    > >> > > > > ?DCount("*", "Contract Order Table", "[Contract Number] = 'xxxx'
    > >> > > > > AND
    > >> > > [Order
    > >> > > > > Number] = n)
    > >> > > > >
    > >> > > > > where you replace xxxx and n with values that you know should
    > >> > > > > raise
    > >> a
    > >> > > > > duplicate?
    > >> > > > >
    > >> > > > > You could also try putting square brackets around your table
    > >> > > > > name,
    > >> since
    > >> > > > > you've got embedded spaces in it:
    > >> > > > >
    > >> > > > > DCount("*", "[Contract Order Table]", "[Contract Number] = ...
    > >> > > > >
    > >> > > > >
    > >> > > > > --
    > >> > > > > Doug Steele, Microsoft Access MVP
    > >> > > > > http://I.Am/DougSteele
    > >> > > > > (no e-mails, please!)
    > >> > > > >
    > >> > > > >
    > >> > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > >> > > > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > >> > > > > > The error is now gone (so I assume it is doing some kind of
    > >> checking,
    > >> > > but
    > >> > > > > it
    > >> > > > > > does not pop up when I knowingly put in a duplicate
    > >> > > > > > contract/order
    > >> > > number.
    > >> > > > > > Any guesses why not?
    > >> > > > > >
    > >> > > > > > "Douglas J Steele" wrote:
    > >> > > > > >
    > >> > > > > > > Since Contract Number is a text field, as opposed to a
    > >> > > > > > > numeric
    > >> one,
    > >> > > you
    > >> > > > > need
    > >> > > > > > > to enclose the value in quotes:
    > >> > > > > > >
    > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > >> > > > > > >
    > >> > > > > > > If Order Number is also a text field, you'll need to do the
    > >> > > > > > > same
    > >> > > with
    > >> > > > > it:
    > >> > > > > > >
    > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > >> > > > > > >
    > >> > > > > > > Exagerated for clarity, that's
    > >> > > > > > >
    > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > >> > > > > > >
    > >> > > > > > >
    > >> > > > > > > --
    > >> > > > > > > Doug Steele, Microsoft Access MVP
    > >> > > > > > > http://I.Am/DougSteele
    > >> > > > > > > (no e-mails, please!)
    > >> > > > > > >
    > >> > > > > > >
    > >> > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > >> > > > > > > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > >> > > > > > > > Doug,
    > >> > > > > > > >
    > >> > > > > > > > Here's what I put in the code (After update on the contract
    > >> number
    > >> > > > > field)
    > >> > > > > > > > and I'm getting a syntax 3075 error (missing operator in
    > >> > > > > > > > query
    > >> > > > > expression
    > >> > > > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > >> > > > > > > >
    > >> > > > > > > > If DCount("*", "Contract Order Table", "[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
    > >> > > > > > > >
    > >> > > > > > > > Any ideas what I'm doing. I should also say that there was
    > >> > > > > > > > no
    > >> > > order
    > >> > > > > > > number
    > >> > > > > > > > in this particular record required.
    > >> > > > > > > >
    > >> > > > > > > > "Douglas J Steele" wrote:
    > >> > > > > > > >
    > >> > > > > > > > > If DCount("*", "MyTableName", "ContractNumber = " _
    > >> > > > > > > > > & Me!ContractNumber & " AND OrderNumber = " _
    > >> > > > > > > > > & Me!OrderNumber) > 0 Then
    > >> > > > > > > > > Cancel = True
    > >> > > > > > > > > MsgBox "This contract number already exists in
    > >> > > > > > > > > the
    > >> > > table."
    > >> > > > > > > > > End If
    > >> > > > > > > > >
    > >> > > > > > > > >
    > >> > > > > > > > > --
    > >> > > > > > > > > Doug Steele, Microsoft Access MVP
    > >> > > > > > > > > http://I.Am/DougSteele
    > >> > > > > > > > > (no e-mails, please!)
    > >> > > > > > > > >
    > >> > > > > > > > >
    > >> > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in
    > >> > > > > > > > > message
    > >> > > > > > > > > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > >> > > > > > > > > > This would work for just one field, but I need to
    > >> > > > > > > > > > validate
    > >> two
    > >> > > > > fields.
    > >> > > > > > > > > There
    > >> > > > > > > > > > can only be one record containing a unique set of two
    > >> fields.
    > >> > > > > > > > > >
    > >> > > > > > > > > > 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)
    > >> > > > > > > > > >
    > >> > > > > > > > > > I need to check both the contract and order number
    > >> together.
    > >> > > > > > > > > > "tina" wrote:
    > >> > > > > > > > > >
    > >> > > > > > > > > > > you can validate the data in those fields at the form
    > >> level,
    > >> > > at
    > >> > > > > the
    > >> > > > > > > > > point of
    > >> > > > > > > > > > > data entry. try using the form's BeforeUpdate event,
    > >> > > > > > > > > > > and
    > >> use
    > >> > > a
    > >> > > > > > > DCount()
    > >> > > > > > > > > > > function to check the table for existing data that
    > >> matches
    > >> > > the
    > >> > > > > newly
    > >> > > > > > > > > entered
    > >> > > > > > > > > > > data in the form. something along the lines of
    > >> > > > > > > > > > >
    > >> > > > > > > > > > > If DCount(1, "MyTableName", "ContractNumber = " _
    > >> > > > > > > > > > > & Me!ContractNumber) > 0 Then
    > >> > > > > > > > > > > Cancel = True
    > >> > > > > > > > > > > MsgBox "This contract number already exists
    > >> > > > > > > > > > > in
    > >> the
    > >> > > > > table."
    > >> > > > > > > > > > > End If
    > >> > > > > > > > > > >
    > >> > > > > > > > > > > if you're not familiar with the DCount function, or
    > >> > > > > > > > > > > with
    > >> the
    > >> > > > > > > > > BeforeUpdate
    > >> > > > > > > > > > > event, take a look at them in Help - and post back if
    > >> you
    > >> > > need
    > >> > > > > > > further
    > >> > > > > > > > > > > assistance.
    > >> > > > > > > > > > >
    > >> > > > > > > > > > > hth
    > >> > > > > > > > > > >
    > >> > > > > > > > > > >
    > >> > > > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in
    > >> message
    > >> > > > > > > > > > >
    > >> news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > >> > > > > > > > > > > > I have already read how to create a table with
    > >> multiple
    > >> > > key
    > >> > > > > index,
    > >> > > > > > > but
    > >> > > > > > > > > > > here's
    > >> > > > > > > > > > > > my problem. When I attempt to create this type of
    > >> index,
    > >> > > it
    > >> > > > > says
    > >> > > > > > > the
    > >> > > > > > > > > > > index
    > >> > > > > > > > > > > > can't contain a null value. I certainly understand
     
  18. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Since both Contract Number and Order Number are text fields, you need:

    If DCount("*", "Contract Order Table", "[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

    Exagerated for clarity, that's

    If DCount("*", "Contract Order Table", "[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


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:4BC50EFE-C269-4810-93A0-6102918E458F@microsoft.com...
    > Doug,
    >
    > I still can't get the result with both fields, just one or the other.

    Could
    > you enter the whole code again just to make sure I'm not missing any " or

    '.
    > Just use sample string data in the comparisons. I can try to adapt it to

    the
    > field on the form after I see your code. Thanks for all your help. Sorry

    I
    > can't get it to work yet.
    >
    > Troy
    >
    > "Douglas J. Steele" wrote:
    >
    > > Okay, so what happens with the corrected syntax I suggested?
    > >
    > > The sample code I gave way back will generate the correct syntax, by the
    > > way.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > >
    > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > news:44F7AA5E-20E0-4278-B627-587096C2FAB8@microsoft.com...
    > > > Yes, both fields are strings.
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > >> The AND needs to be part of the string:
    > > >>
    > > >> ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234'

    AND
    > > >> [Order Number] = '1234' ")
    > > >>
    > > >> Is Order Number a text field in the table?
    > > >>
    > > >> --
    > > >> Doug Steele, Microsoft Access MVP
    > > >> http://I.Am/DougSteele
    > > >> (no e-mails, please!)
    > > >>
    > > >>
    > > >> "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > >> news:DA1AB95A-703D-4756-820C-75E1FFC111DD@microsoft.com...
    > > >> > Doug,
    > > >> >
    > > >> > I put the following (with various other things too just to try):
    > > >> >
    > > >> > ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234'

    " &
    > > >> AND
    > > >> > > "[Order
    > > >> > > > > Number] = '1234' ")
    > > >> >
    > > >> > I'm sure I'm just not getting the correct syntax. It looks like it
    > > >> requires
    > > >> > all these single quotes, underscores, and & that I have no idea

    which
    > > >> > goes
    > > >> > where. With my limited BASIC programming skills, it seems like a
    > > >> > fairly
    > > >> > simple check.
    > > >> >
    > > >> > "Douglas J Steele" wrote:
    > > >> >
    > > >> > > Exactly what did you type in the debug window?
    > > >> > >
    > > >> > > --
    > > >> > > Doug Steele, Microsoft Access MVP
    > > >> > > http://I.Am/DougSteele
    > > >> > > (no e-mails, please!)
    > > >> > >
    > > >> > >
    > > >> > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > >> > > news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    > > >> > > > Doug,
    > > >> > > >
    > > >> > > > I tried placing your code below in the debug window. However,

    it
    > > >> seems I
    > > >> > > > can't get the code to function with two fields being checked.

    I
    > > >> > > > think
    > > >> the
    > > >> > > > syntax is incorrect. I can get a correct response with either
    > > >> [Contract
    > > >> > > > Number] by itself or [Order Number] by itself, but when I try

    to
    > > >> > > > place
    > > >> the
    > > >> > > > AND, I always get an error. Could you give me another example?
    > > >> > > > Both
    > > >> > > fields
    > > >> > > > are text. Don't forget that some of the order number fields

    can
    > > >> contain
    > > >> > > null.
    > > >> > > >
    > > >> > > > Thanks
    > > >> > > >
    > > >> > > > "Douglas J Steele" wrote:
    > > >> > > >
    > > >> > > > > What happens if you go to the Debug window (Ctrl-G) and type:
    > > >> > > > >
    > > >> > > > > ?DCount("*", "Contract Order Table", "[Contract Number] =

    'xxxx'
    > > >> > > > > AND
    > > >> > > [Order
    > > >> > > > > Number] = n)
    > > >> > > > >
    > > >> > > > > where you replace xxxx and n with values that you know should
    > > >> > > > > raise
    > > >> a
    > > >> > > > > duplicate?
    > > >> > > > >
    > > >> > > > > You could also try putting square brackets around your table
    > > >> > > > > name,
    > > >> since
    > > >> > > > > you've got embedded spaces in it:
    > > >> > > > >
    > > >> > > > > DCount("*", "[Contract Order Table]", "[Contract Number] =

    ....
    > > >> > > > >
    > > >> > > > >
    > > >> > > > > --
    > > >> > > > > Doug Steele, Microsoft Access MVP
    > > >> > > > > http://I.Am/DougSteele
    > > >> > > > > (no e-mails, please!)
    > > >> > > > >
    > > >> > > > >
    > > >> > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > >> > > > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > > >> > > > > > The error is now gone (so I assume it is doing some kind of
    > > >> checking,
    > > >> > > but
    > > >> > > > > it
    > > >> > > > > > does not pop up when I knowingly put in a duplicate
    > > >> > > > > > contract/order
    > > >> > > number.
    > > >> > > > > > Any guesses why not?
    > > >> > > > > >
    > > >> > > > > > "Douglas J Steele" wrote:
    > > >> > > > > >
    > > >> > > > > > > Since Contract Number is a text field, as opposed to a
    > > >> > > > > > > numeric
    > > >> one,
    > > >> > > you
    > > >> > > > > need
    > > >> > > > > > > to enclose the value in quotes:
    > > >> > > > > > >
    > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > > >> > > > > > >
    > > >> > > > > > > If Order Number is also a text field, you'll need to do

    the
    > > >> > > > > > > same
    > > >> > > with
    > > >> > > > > it:
    > > >> > > > > > >
    > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > > >> > > > > > >
    > > >> > > > > > > Exagerated for clarity, that's
    > > >> > > > > > >
    > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > > >> > > > > > >
    > > >> > > > > > >
    > > >> > > > > > > --
    > > >> > > > > > > Doug Steele, Microsoft Access MVP
    > > >> > > > > > > http://I.Am/DougSteele
    > > >> > > > > > > (no e-mails, please!)
    > > >> > > > > > >
    > > >> > > > > > >
    > > >> > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in

    message
    > > >> > > > > > >

    news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > >> > > > > > > > Doug,
    > > >> > > > > > > >
    > > >> > > > > > > > Here's what I put in the code (After update on the

    contract
    > > >> number
    > > >> > > > > field)
    > > >> > > > > > > > and I'm getting a syntax 3075 error (missing operator

    in
    > > >> > > > > > > > query
    > > >> > > > > expression
    > > >> > > > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > > >> > > > > > > >
    > > >> > > > > > > > If DCount("*", "Contract Order Table", "[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
    > > >> > > > > > > >
    > > >> > > > > > > > Any ideas what I'm doing. I should also say that there

    was
    > > >> > > > > > > > no
    > > >> > > order
    > > >> > > > > > > number
    > > >> > > > > > > > in this particular record required.
    > > >> > > > > > > >
    > > >> > > > > > > > "Douglas J Steele" wrote:
    > > >> > > > > > > >
    > > >> > > > > > > > > If DCount("*", "MyTableName", "ContractNumber = "

    _
    > > >> > > > > > > > > & Me!ContractNumber & " AND OrderNumber = "

    _
    > > >> > > > > > > > > & Me!OrderNumber) > 0 Then
    > > >> > > > > > > > > Cancel = True
    > > >> > > > > > > > > MsgBox "This contract number already exists

    in
    > > >> > > > > > > > > the
    > > >> > > table."
    > > >> > > > > > > > > End If
    > > >> > > > > > > > >
    > > >> > > > > > > > >
    > > >> > > > > > > > > --
    > > >> > > > > > > > > Doug Steele, Microsoft Access MVP
    > > >> > > > > > > > > http://I.Am/DougSteele
    > > >> > > > > > > > > (no e-mails, please!)
    > > >> > > > > > > > >
    > > >> > > > > > > > >
    > > >> > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in
    > > >> > > > > > > > > message
    > > >> > > > > > > > >

    news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > >> > > > > > > > > > This would work for just one field, but I need to
    > > >> > > > > > > > > > validate
    > > >> two
    > > >> > > > > fields.
    > > >> > > > > > > > > There
    > > >> > > > > > > > > > can only be one record containing a unique set of

    two
    > > >> fields.
    > > >> > > > > > > > > >
    > > >> > > > > > > > > > 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)
    > > >> > > > > > > > > >
    > > >> > > > > > > > > > I need to check both the contract and order number
    > > >> together.
    > > >> > > > > > > > > > "tina" wrote:
    > > >> > > > > > > > > >
    > > >> > > > > > > > > > > you can validate the data in those fields at the

    form
    > > >> level,
    > > >> > > at
    > > >> > > > > the
    > > >> > > > > > > > > point of
    > > >> > > > > > > > > > > data entry. try using the form's BeforeUpdate

    event,
    > > >> > > > > > > > > > > and
    > > >> use
    > > >> > > a
    > > >> > > > > > > DCount()
    > > >> > > > > > > > > > > function to check the table for existing data

    that
    > > >> matches
    > > >> > > the
    > > >> > > > > newly
    > > >> > > > > > > > > entered
    > > >> > > > > > > > > > > data in the form. something along the lines of
    > > >> > > > > > > > > > >
    > > >> > > > > > > > > > > If DCount(1, "MyTableName", "ContractNumber =

    " _
    > > >> > > > > > > > > > > & Me!ContractNumber) > 0 Then
    > > >> > > > > > > > > > > Cancel = True
    > > >> > > > > > > > > > > MsgBox "This contract number already

    exists
    > > >> > > > > > > > > > > in
    > > >> the
    > > >> > > > > table."
    > > >> > > > > > > > > > > End If
    > > >> > > > > > > > > > >
    > > >> > > > > > > > > > > if you're not familiar with the DCount function,

    or
    > > >> > > > > > > > > > > with
    > > >> the
    > > >> > > > > > > > > BeforeUpdate
    > > >> > > > > > > > > > > event, take a look at them in Help - and post

    back if
    > > >> you
    > > >> > > need
    > > >> > > > > > > further
    > > >> > > > > > > > > > > assistance.
    > > >> > > > > > > > > > >
    > > >> > > > > > > > > > > hth
    > > >> > > > > > > > > > >
    > > >> > > > > > > > > > >
    > > >> > > > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote

    in
    > > >> message
    > > >> > > > > > > > > > >
    > > >> news:6AC48DDE-7C33-45AB-9167-2E2B5FA6E68A@microsoft.com...
    > > >> > > > > > > > > > > > I have already read how to create a table with
    > > >> multiple
    > > >> > > key
    > > >> > > > > index,
    > > >> > > > > > > but
    > > >> > > > > > > > > > > here's
    > > >> > > > > > > > > > > > my problem. When I attempt to create this type

    of
    > > >> index,
    > > >> > > it
    > > >> > > > > says
    > > >> > > > > > > the
    > > >> > > > > > > > > > > index
    > > >> > > > > > > > > > > > can't contain a null value. I certainly

    understand
     
  19. bondtk

    bondtk
    Expand Collapse
    Guest

    Doug,

    You're very patient to keep helping me. Your code returns no errors.
    However, it allows duplicates. I placed the code on the field's before
    update, in the form's Before Update with the same result. I have figured out
    everything else in my database but this one thorn in the flesh. There's got
    to be something we're missing. Maybe I should explain my scenario more to
    see if that helps. I have a master for with a sub form. The master form is
    where I need to check for duplicates. There are only 3 fields in the bound
    table for the master form, Contract Number, Order Number, and the Master ID
    (primary key-auto number). Your code must recognize the fields or we would
    get an error on that. Some of the order numbers are null and I've now been
    able to get some correct results in the "Immediate" window only when I us
    IsNull([Order Number])") on the end. Because there is always a contract
    number, but not always an order number. I deduct that it's having trouble
    when there is nothing typed into that field.??

    "Douglas J Steele" wrote:

    > Since both Contract Number and Order Number are text fields, you need:
    >
    > If DCount("*", "Contract Order Table", "[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
    >
    > Exagerated for clarity, that's
    >
    > If DCount("*", "Contract Order Table", "[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
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > news:4BC50EFE-C269-4810-93A0-6102918E458F@microsoft.com...
    > > Doug,
    > >
    > > I still can't get the result with both fields, just one or the other.

    > Could
    > > you enter the whole code again just to make sure I'm not missing any " or

    > '.
    > > Just use sample string data in the comparisons. I can try to adapt it to

    > the
    > > field on the form after I see your code. Thanks for all your help. Sorry

    > I
    > > can't get it to work yet.
    > >
    > > Troy
    > >
    > > "Douglas J. Steele" wrote:
    > >
    > > > Okay, so what happens with the corrected syntax I suggested?
    > > >
    > > > The sample code I gave way back will generate the correct syntax, by the
    > > > way.
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > >
    > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > news:44F7AA5E-20E0-4278-B627-587096C2FAB8@microsoft.com...
    > > > > Yes, both fields are strings.
    > > > >
    > > > > "Douglas J Steele" wrote:
    > > > >
    > > > >> The AND needs to be part of the string:
    > > > >>
    > > > >> ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234'

    > AND
    > > > >> [Order Number] = '1234' ")
    > > > >>
    > > > >> Is Order Number a text field in the table?
    > > > >>
    > > > >> --
    > > > >> Doug Steele, Microsoft Access MVP
    > > > >> http://I.Am/DougSteele
    > > > >> (no e-mails, please!)
    > > > >>
    > > > >>
    > > > >> "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > >> news:DA1AB95A-703D-4756-820C-75E1FFC111DD@microsoft.com...
    > > > >> > Doug,
    > > > >> >
    > > > >> > I put the following (with various other things too just to try):
    > > > >> >
    > > > >> > ?DCount("*", "[Contract Order Table]", "[Contract Number] = 'N1234'

    > " &
    > > > >> AND
    > > > >> > > "[Order
    > > > >> > > > > Number] = '1234' ")
    > > > >> >
    > > > >> > I'm sure I'm just not getting the correct syntax. It looks like it
    > > > >> requires
    > > > >> > all these single quotes, underscores, and & that I have no idea

    > which
    > > > >> > goes
    > > > >> > where. With my limited BASIC programming skills, it seems like a
    > > > >> > fairly
    > > > >> > simple check.
    > > > >> >
    > > > >> > "Douglas J Steele" wrote:
    > > > >> >
    > > > >> > > Exactly what did you type in the debug window?
    > > > >> > >
    > > > >> > > --
    > > > >> > > Doug Steele, Microsoft Access MVP
    > > > >> > > http://I.Am/DougSteele
    > > > >> > > (no e-mails, please!)
    > > > >> > >
    > > > >> > >
    > > > >> > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > >> > > news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    > > > >> > > > Doug,
    > > > >> > > >
    > > > >> > > > I tried placing your code below in the debug window. However,

    > it
    > > > >> seems I
    > > > >> > > > can't get the code to function with two fields being checked.

    > I
    > > > >> > > > think
    > > > >> the
    > > > >> > > > syntax is incorrect. I can get a correct response with either
    > > > >> [Contract
    > > > >> > > > Number] by itself or [Order Number] by itself, but when I try

    > to
    > > > >> > > > place
    > > > >> the
    > > > >> > > > AND, I always get an error. Could you give me another example?
    > > > >> > > > Both
    > > > >> > > fields
    > > > >> > > > are text. Don't forget that some of the order number fields

    > can
    > > > >> contain
    > > > >> > > null.
    > > > >> > > >
    > > > >> > > > Thanks
    > > > >> > > >
    > > > >> > > > "Douglas J Steele" wrote:
    > > > >> > > >
    > > > >> > > > > What happens if you go to the Debug window (Ctrl-G) and type:
    > > > >> > > > >
    > > > >> > > > > ?DCount("*", "Contract Order Table", "[Contract Number] =

    > 'xxxx'
    > > > >> > > > > AND
    > > > >> > > [Order
    > > > >> > > > > Number] = n)
    > > > >> > > > >
    > > > >> > > > > where you replace xxxx and n with values that you know should
    > > > >> > > > > raise
    > > > >> a
    > > > >> > > > > duplicate?
    > > > >> > > > >
    > > > >> > > > > You could also try putting square brackets around your table
    > > > >> > > > > name,
    > > > >> since
    > > > >> > > > > you've got embedded spaces in it:
    > > > >> > > > >
    > > > >> > > > > DCount("*", "[Contract Order Table]", "[Contract Number] =

    > ....
    > > > >> > > > >
    > > > >> > > > >
    > > > >> > > > > --
    > > > >> > > > > Doug Steele, Microsoft Access MVP
    > > > >> > > > > http://I.Am/DougSteele
    > > > >> > > > > (no e-mails, please!)
    > > > >> > > > >
    > > > >> > > > >
    > > > >> > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    > > > >> > > > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    > > > >> > > > > > The error is now gone (so I assume it is doing some kind of
    > > > >> checking,
    > > > >> > > but
    > > > >> > > > > it
    > > > >> > > > > > does not pop up when I knowingly put in a duplicate
    > > > >> > > > > > contract/order
    > > > >> > > number.
    > > > >> > > > > > Any guesses why not?
    > > > >> > > > > >
    > > > >> > > > > > "Douglas J Steele" wrote:
    > > > >> > > > > >
    > > > >> > > > > > > Since Contract Number is a text field, as opposed to a
    > > > >> > > > > > > numeric
    > > > >> one,
    > > > >> > > you
    > > > >> > > > > need
    > > > >> > > > > > > to enclose the value in quotes:
    > > > >> > > > > > >
    > > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > >> > > > > > >
    > > > >> > > > > > > If Order Number is also a text field, you'll need to do

    > the
    > > > >> > > > > > > same
    > > > >> > > with
    > > > >> > > > > it:
    > > > >> > > > > > >
    > > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > >> > > > > > >
    > > > >> > > > > > > Exagerated for clarity, that's
    > > > >> > > > > > >
    > > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > >> > > > > > >
    > > > >> > > > > > >
    > > > >> > > > > > > --
    > > > >> > > > > > > Doug Steele, Microsoft Access MVP
    > > > >> > > > > > > http://I.Am/DougSteele
    > > > >> > > > > > > (no e-mails, please!)
    > > > >> > > > > > >
    > > > >> > > > > > >
    > > > >> > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in

    > message
    > > > >> > > > > > >

    > news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    > > > >> > > > > > > > Doug,
    > > > >> > > > > > > >
    > > > >> > > > > > > > Here's what I put in the code (After update on the

    > contract
    > > > >> number
    > > > >> > > > > field)
    > > > >> > > > > > > > and I'm getting a syntax 3075 error (missing operator

    > in
    > > > >> > > > > > > > query
    > > > >> > > > > expression
    > > > >> > > > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    > > > >> > > > > > > >
    > > > >> > > > > > > > If DCount("*", "Contract Order Table", "[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
    > > > >> > > > > > > >
    > > > >> > > > > > > > Any ideas what I'm doing. I should also say that there

    > was
    > > > >> > > > > > > > no
    > > > >> > > order
    > > > >> > > > > > > number
    > > > >> > > > > > > > in this particular record required.
    > > > >> > > > > > > >
    > > > >> > > > > > > > "Douglas J Steele" wrote:
    > > > >> > > > > > > >
    > > > >> > > > > > > > > If DCount("*", "MyTableName", "ContractNumber = "

    > _
    > > > >> > > > > > > > > & Me!ContractNumber & " AND OrderNumber = "

    > _
    > > > >> > > > > > > > > & Me!OrderNumber) > 0 Then
    > > > >> > > > > > > > > Cancel = True
    > > > >> > > > > > > > > MsgBox "This contract number already exists

    > in
    > > > >> > > > > > > > > the
    > > > >> > > table."
    > > > >> > > > > > > > > End If
    > > > >> > > > > > > > >
    > > > >> > > > > > > > >
    > > > >> > > > > > > > > --
    > > > >> > > > > > > > > Doug Steele, Microsoft Access MVP
    > > > >> > > > > > > > > http://I.Am/DougSteele
    > > > >> > > > > > > > > (no e-mails, please!)
    > > > >> > > > > > > > >
    > > > >> > > > > > > > >
    > > > >> > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in
    > > > >> > > > > > > > > message
    > > > >> > > > > > > > >

    > news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    > > > >> > > > > > > > > > This would work for just one field, but I need to
     
  20. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Are you saying that Me![Contract Number] or Me![Order Number] can be Null?
    You'll have to trap for those.

    Dim strWhere As String

    If IsNull(Me![Contract Number]) Then
    strWhere = "[Contract Number] IS NULL AND "
    Else
    strWhere ="[Contract Number] = '" &Me![Contract Number] & "' AND "
    End If
    If IsNull(Me![Order Number]) Then
    strWhere = strWhere & "[Order Number] IS NULL"
    Else
    strWhere = strWhere & "[Order Number] = '" & Me![Order Number & "'"
    End If

    If DCount("*", "Contract Order Table", strWhere) > 0 Then
    Cancel = True
    MsgBox "This contract number already exists in the table."
    End If


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    news:3BF41630-D5DA-403B-8062-C230874D2DF0@microsoft.com...
    > Doug,
    >
    > You're very patient to keep helping me. Your code returns no errors.
    > However, it allows duplicates. I placed the code on the field's before
    > update, in the form's Before Update with the same result. I have figured
    > out
    > everything else in my database but this one thorn in the flesh. There's
    > got
    > to be something we're missing. Maybe I should explain my scenario more to
    > see if that helps. I have a master for with a sub form. The master form
    > is
    > where I need to check for duplicates. There are only 3 fields in the
    > bound
    > table for the master form, Contract Number, Order Number, and the Master
    > ID
    > (primary key-auto number). Your code must recognize the fields or we
    > would
    > get an error on that. Some of the order numbers are null and I've now
    > been
    > able to get some correct results in the "Immediate" window only when I us
    > IsNull([Order Number])") on the end. Because there is always a contract
    > number, but not always an order number. I deduct that it's having trouble
    > when there is nothing typed into that field.??
    >
    > "Douglas J Steele" wrote:
    >
    >> Since both Contract Number and Order Number are text fields, you need:
    >>
    >> If DCount("*", "Contract Order Table", "[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
    >>
    >> Exagerated for clarity, that's
    >>
    >> If DCount("*", "Contract Order Table", "[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
    >>
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    >> news:4BC50EFE-C269-4810-93A0-6102918E458F@microsoft.com...
    >> > Doug,
    >> >
    >> > I still can't get the result with both fields, just one or the other.

    >> Could
    >> > you enter the whole code again just to make sure I'm not missing any "
    >> > or

    >> '.
    >> > Just use sample string data in the comparisons. I can try to adapt it
    >> > to

    >> the
    >> > field on the form after I see your code. Thanks for all your help.
    >> > Sorry

    >> I
    >> > can't get it to work yet.
    >> >
    >> > Troy
    >> >
    >> > "Douglas J. Steele" wrote:
    >> >
    >> > > Okay, so what happens with the corrected syntax I suggested?
    >> > >
    >> > > The sample code I gave way back will generate the correct syntax, by
    >> > > the
    >> > > way.
    >> > >
    >> > > --
    >> > > Doug Steele, Microsoft Access MVP
    >> > > http://I.Am/DougSteele
    >> > > (no e-mails, please!)
    >> > >
    >> > >
    >> > >
    >> > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    >> > > news:44F7AA5E-20E0-4278-B627-587096C2FAB8@microsoft.com...
    >> > > > Yes, both fields are strings.
    >> > > >
    >> > > > "Douglas J Steele" wrote:
    >> > > >
    >> > > >> The AND needs to be part of the string:
    >> > > >>
    >> > > >> ?DCount("*", "[Contract Order Table]", "[Contract Number] =
    >> > > >> 'N1234'

    >> AND
    >> > > >> [Order Number] = '1234' ")
    >> > > >>
    >> > > >> Is Order Number a text field in the table?
    >> > > >>
    >> > > >> --
    >> > > >> Doug Steele, Microsoft Access MVP
    >> > > >> http://I.Am/DougSteele
    >> > > >> (no e-mails, please!)
    >> > > >>
    >> > > >>
    >> > > >> "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    >> > > >> news:DA1AB95A-703D-4756-820C-75E1FFC111DD@microsoft.com...
    >> > > >> > Doug,
    >> > > >> >
    >> > > >> > I put the following (with various other things too just to try):
    >> > > >> >
    >> > > >> > ?DCount("*", "[Contract Order Table]", "[Contract Number] =
    >> > > >> > 'N1234'

    >> " &
    >> > > >> AND
    >> > > >> > > "[Order
    >> > > >> > > > > Number] = '1234' ")
    >> > > >> >
    >> > > >> > I'm sure I'm just not getting the correct syntax. It looks like
    >> > > >> > it
    >> > > >> requires
    >> > > >> > all these single quotes, underscores, and & that I have no idea

    >> which
    >> > > >> > goes
    >> > > >> > where. With my limited BASIC programming skills, it seems like
    >> > > >> > a
    >> > > >> > fairly
    >> > > >> > simple check.
    >> > > >> >
    >> > > >> > "Douglas J Steele" wrote:
    >> > > >> >
    >> > > >> > > Exactly what did you type in the debug window?
    >> > > >> > >
    >> > > >> > > --
    >> > > >> > > Doug Steele, Microsoft Access MVP
    >> > > >> > > http://I.Am/DougSteele
    >> > > >> > > (no e-mails, please!)
    >> > > >> > >
    >> > > >> > >
    >> > > >> > > "bondtk" <bondtk@discussions.microsoft.com> wrote in message
    >> > > >> > > news:D9B00E19-501D-4FEE-B33B-73F97FE66919@microsoft.com...
    >> > > >> > > > Doug,
    >> > > >> > > >
    >> > > >> > > > I tried placing your code below in the debug window.
    >> > > >> > > > However,

    >> it
    >> > > >> seems I
    >> > > >> > > > can't get the code to function with two fields being
    >> > > >> > > > checked.

    >> I
    >> > > >> > > > think
    >> > > >> the
    >> > > >> > > > syntax is incorrect. I can get a correct response with
    >> > > >> > > > either
    >> > > >> [Contract
    >> > > >> > > > Number] by itself or [Order Number] by itself, but when I
    >> > > >> > > > try

    >> to
    >> > > >> > > > place
    >> > > >> the
    >> > > >> > > > AND, I always get an error. Could you give me another
    >> > > >> > > > example?
    >> > > >> > > > Both
    >> > > >> > > fields
    >> > > >> > > > are text. Don't forget that some of the order number fields

    >> can
    >> > > >> contain
    >> > > >> > > null.
    >> > > >> > > >
    >> > > >> > > > Thanks
    >> > > >> > > >
    >> > > >> > > > "Douglas J Steele" wrote:
    >> > > >> > > >
    >> > > >> > > > > What happens if you go to the Debug window (Ctrl-G) and
    >> > > >> > > > > type:
    >> > > >> > > > >
    >> > > >> > > > > ?DCount("*", "Contract Order Table", "[Contract Number] =

    >> 'xxxx'
    >> > > >> > > > > AND
    >> > > >> > > [Order
    >> > > >> > > > > Number] = n)
    >> > > >> > > > >
    >> > > >> > > > > where you replace xxxx and n with values that you know
    >> > > >> > > > > should
    >> > > >> > > > > raise
    >> > > >> a
    >> > > >> > > > > duplicate?
    >> > > >> > > > >
    >> > > >> > > > > You could also try putting square brackets around your
    >> > > >> > > > > table
    >> > > >> > > > > name,
    >> > > >> since
    >> > > >> > > > > you've got embedded spaces in it:
    >> > > >> > > > >
    >> > > >> > > > > DCount("*", "[Contract Order Table]", "[Contract Number] =

    >> ....
    >> > > >> > > > >
    >> > > >> > > > >
    >> > > >> > > > > --
    >> > > >> > > > > Doug Steele, Microsoft Access MVP
    >> > > >> > > > > http://I.Am/DougSteele
    >> > > >> > > > > (no e-mails, please!)
    >> > > >> > > > >
    >> > > >> > > > >
    >> > > >> > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in
    >> > > >> > > > > message
    >> > > >> > > > > news:D1725744-8F61-4761-A4C0-B9C1E1897117@microsoft.com...
    >> > > >> > > > > > The error is now gone (so I assume it is doing some kind
    >> > > >> > > > > > of
    >> > > >> checking,
    >> > > >> > > but
    >> > > >> > > > > it
    >> > > >> > > > > > does not pop up when I knowingly put in a duplicate
    >> > > >> > > > > > contract/order
    >> > > >> > > number.
    >> > > >> > > > > > Any guesses why not?
    >> > > >> > > > > >
    >> > > >> > > > > > "Douglas J Steele" wrote:
    >> > > >> > > > > >
    >> > > >> > > > > > > Since Contract Number is a text field, as opposed to a
    >> > > >> > > > > > > numeric
    >> > > >> one,
    >> > > >> > > you
    >> > > >> > > > > need
    >> > > >> > > > > > > to enclose the value in quotes:
    >> > > >> > > > > > >
    >> > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    >> > > >> > > > > > >
    >> > > >> > > > > > > If Order Number is also a text field, you'll need to
    >> > > >> > > > > > > do

    >> the
    >> > > >> > > > > > > same
    >> > > >> > > with
    >> > > >> > > > > it:
    >> > > >> > > > > > >
    >> > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    >> > > >> > > > > > >
    >> > > >> > > > > > > Exagerated for clarity, that's
    >> > > >> > > > > > >
    >> > > >> > > > > > > If DCount("*", "Contract Order Table", "[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
    >> > > >> > > > > > >
    >> > > >> > > > > > >
    >> > > >> > > > > > > --
    >> > > >> > > > > > > Doug Steele, Microsoft Access MVP
    >> > > >> > > > > > > http://I.Am/DougSteele
    >> > > >> > > > > > > (no e-mails, please!)
    >> > > >> > > > > > >
    >> > > >> > > > > > >
    >> > > >> > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote in

    >> message
    >> > > >> > > > > > >

    >> news:1113F4DA-FDA5-4C1C-A7CA-FA6A91F65D2E@microsoft.com...
    >> > > >> > > > > > > > Doug,
    >> > > >> > > > > > > >
    >> > > >> > > > > > > > Here's what I put in the code (After update on the

    >> contract
    >> > > >> number
    >> > > >> > > > > field)
    >> > > >> > > > > > > > and I'm getting a syntax 3075 error (missing
    >> > > >> > > > > > > > operator

    >> in
    >> > > >> > > > > > > > query
    >> > > >> > > > > expression
    >> > > >> > > > > > > > '[Contract Number]=W25G1V AND [Order Number]='.:
    >> > > >> > > > > > > >
    >> > > >> > > > > > > > If DCount("*", "Contract Order Table", "[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
    >> > > >> > > > > > > >
    >> > > >> > > > > > > > Any ideas what I'm doing. I should also say that
    >> > > >> > > > > > > > there

    >> was
    >> > > >> > > > > > > > no
    >> > > >> > > order
    >> > > >> > > > > > > number
    >> > > >> > > > > > > > in this particular record required.
    >> > > >> > > > > > > >
    >> > > >> > > > > > > > "Douglas J Steele" wrote:
    >> > > >> > > > > > > >
    >> > > >> > > > > > > > > If DCount("*", "MyTableName", "ContractNumber =
    >> > > >> > > > > > > > > "

    >> _
    >> > > >> > > > > > > > > & Me!ContractNumber & " AND OrderNumber =
    >> > > >> > > > > > > > > "

    >> _
    >> > > >> > > > > > > > > & Me!OrderNumber) > 0 Then
    >> > > >> > > > > > > > > Cancel = True
    >> > > >> > > > > > > > > MsgBox "This contract number already
    >> > > >> > > > > > > > > exists

    >> in
    >> > > >> > > > > > > > > the
    >> > > >> > > table."
    >> > > >> > > > > > > > > End If
    >> > > >> > > > > > > > >
    >> > > >> > > > > > > > >
    >> > > >> > > > > > > > > --
    >> > > >> > > > > > > > > Doug Steele, Microsoft Access MVP
    >> > > >> > > > > > > > > http://I.Am/DougSteele
    >> > > >> > > > > > > > > (no e-mails, please!)
    >> > > >> > > > > > > > >
    >> > > >> > > > > > > > >
    >> > > >> > > > > > > > > "bondtk" <bondtk@discussions.microsoft.com> wrote
    >> > > >> > > > > > > > > in
    >> > > >> > > > > > > > > message
    >> > > >> > > > > > > > >

    >> news:3702B442-36A6-46E3-8FF8-92C9800FE87F@microsoft.com...
    >> > > >> > > > > > > > > > This would work for just one field, but I need
    >> > > >> > > > > > > > > > to
     

Share This Page