Multiple key index with null values

#1 (**permalink**) 01-Nov-2005, 13:40 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6321-multiple-key-index-with-null-values.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
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 (**permalink**) 01-Nov-2005, 13:40 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
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.

#3 (**permalink**) 01-Nov-2005, 13:40 PM

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" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
> 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 (**permalink**) 02-Nov-2005, 11:47 AM

This would work for just one field, but I need to validate two fields. There
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
can only be one record containing a unique set of two fields.

Record Contract Order
1 1234 Null (OK)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
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" 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 (**permalink**) 02-Nov-2005, 11:47 AM

If DCount("*", "MyTableName", "ContractNumber = " _
& Me!ContractNumber & " AND OrderNumber = " _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
& 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" 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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
> > > check both those fields? I don't want there to be any record with the
> > same
> > > contract number and delivery order.
> >
> >
> >

#6 (**permalink**) 03-Nov-2005, 11:35 AM

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 = " _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
> & 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" 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" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
> > > 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 (**permalink**) 03-Nov-2005, 11:35 AM

Since Contract Number is a text field, as opposed to a numeric one, you need
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
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" 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" 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
> > 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" 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 (**permalink**) 03-Nov-2005, 11:35 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
> to enclose the value in quotes:
>
> If DCount("*", "Contract Order Table", "[Contract Number] = '" _
> & Me![Contract Number] & "' AND [Order Number] = " _
> & Me![Order Number]) > 0 Then
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
> 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" 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" 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" 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 (**permalink**) 03-Nov-2005, 11:35 AM

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" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
news

1725744-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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6321
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" 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" 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" 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.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >