Howto not duplicate a field info

[Marisa]

Hello,
In a SQL record the PK and Identity is CustomerID. CustomerNumber is a 5
digit unique #.
How do I ensure that the user does not duplicate the CustomerNumber when
inputing it in a form?
TIA
M
ACC XP -> SQL2k

 

[Al Campagna]

Marisa,
In the table design for this table/form, make your CustomerID field...
INDEXED/Yes-NoDuplicates
On your form, on the AfterUpdate event of CustomerID, do a REFRESH. If
you entered a "dupe" ID, you'll get an error message indicating a duplicate
value.
If you don't do a AfterUpdate Refresh, you'll get the same error message,
but only when you finish filling out the entire form, and try to update the
table.

 

[Marisa]

Thanks Al but I got the CustomerID under control. My question was regarding
a different field: CustomerNumber or OrderNumber. The latter do not a key
assigned to them.
Rgs.

 

[Al Campagna]

Marisa,
If I understand correctly...
Any field that you don't want dupes in can be designated
Indexed/NoDuplicates in your table design. They don't have to be "primary
key" fields... since you already have one (CustomerID?).
A table can contain a PrimaryKey field (indexed/nodupes), and as many
Non-Primary (indexed/nodupes) as you need.
Simply put, a field doesn't have to be a PrimaryKey in order to prevent
dupes. Any field with Indexed/Nodupes will take care of that.
--
HTH...
Al Campagna
Candia Computer Consulting
Candia, NH

 

[Marisa]

Hello Al,
I created an IX_tblTableA, ColumnName 'CustomerNum' Order 'Ascending',
Create Unique, Index, Ignore Duplicated Key in the Primary File Group.
However, FormA still accepts duplicates. It shouldn't allow me to go on
inputing data in the form since CustomerNum is the first field which needs
to be populated. What am I missing?