Multiple key index with null values

[bondtk]

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.

 

[tina]

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

 

[david epsom dot com dot au]

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]

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.

 

[Douglas J Steele]

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

 

[bondtk]

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]

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

(no e-mails, please!)

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.

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

(no e-mails, please!)


my
table Number
and in.
Is

 

[bondtk]

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?

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

(no e-mails, please!)

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.

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

(no e-mails, please!)


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.
:

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


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.

 

[Douglas J Steele]

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

(no e-mails, please!)

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?

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

(no e-mails, please!)

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.

:

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

(no e-mails, please!)


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.
:

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


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.

 

[bondtk]

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

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

(no e-mails, please!)

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?

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


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.
:

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


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.

 

[Douglas J Steele]

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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

(no e-mails, please!)

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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


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.
:

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


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.

 

[bondtk]

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.

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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

(no e-mails, please!)


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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


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.
:

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


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.

 

[Douglas J Steele]

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

(no e-mails, please!)

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.

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

:

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

(no e-mails, please!)


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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


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.
:

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


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.

 

[bondtk]

Yes, both fields are strings.

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

(no e-mails, please!)

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.

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

:

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

(no e-mails, please!)


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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


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.
:

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


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.

 

[Douglas J. Steele]

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

(no e-mails, please!)

Yes, both fields are strings.

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

(no e-mails, please!)

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.

:

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

:

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

(no e-mails, please!)


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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


message
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.
:

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


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.

 

[bondtk]

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

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

(no e-mails, please!)

Yes, both fields are strings.

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

(no e-mails, please!)


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.

:

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

:

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

(no e-mails, please!)


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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


message
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.
:

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


message

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

 

[Douglas J Steele]

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

(no e-mails, please!)

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

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

(no e-mails, please!)

Yes, both fields are strings.

:

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

(no e-mails, please!)


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.

:

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

:

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

(no e-mails, please!)


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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


message
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.
:

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


message

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

 

[bondtk]

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.??

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

(no e-mails, please!)

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

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

(no e-mails, please!)



Yes, both fields are strings.

:

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

(no e-mails, please!)


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.

:

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

:

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

(no e-mails, please!)


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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


message
This would work for just one field, but I need to

 

[Douglas J. Steele]

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

(no e-mails, please!)

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.??

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

(no e-mails, please!)

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

:

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

(no e-mails, please!)



Yes, both fields are strings.

:

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

(no e-mails, please!)


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.

:

Exactly what did you type in the debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

:

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

(no e-mails, please!)


message
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?

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


in
message
This would work for just one field, but I need
to