Need validation rule to pevent duplicating a customer number

Hi all

I need some code to insert into the validation box in a table. I have a
field in the Customer table: "CustomerNumber". This number consists of 5
letters and an optional 2 digits. While inputing an order, I would like this
code to tell me if I'm creating a duplicate and advise me to create another
number. I would also like the input process to go no farther until something
is entered in the field. I set the "Required" property to "Yes ", but it just
tabs right on by. Access 2003.
Thanks

On Thu, 16 Mar 2006 13:45:27 -0800, Ron Weaver
wrote:

Hi all

I need some code to insert into the validation box in a table. I have a
field in the Customer table: "CustomerNumber". This number consists of 5
letters and an optional 2 digits. While inputing an order, I would like this
code to tell me if I'm creating a duplicate and advise me to create another
number. I would also like the input process to go no farther until something
is entered in the field. I set the "Required" property to "Yes ", but it just
tabs right on by. Access 2003.
Thanks

You should probably use "belt and braces" here. Make the
CustomerNumber the Primary Key of the table, and you won't be able to
enter duplicates by *any* means... but you'll also get user-hostile
error messages if you try.

To provide a friendlier interface, use the BeforeUpdate event of the
textbox on the Form you're using to enter the data (and yes, you must
use a Form; table datasheets are extremely limited in their
capabilities). Something like

Private Sub txtCustomerNumber_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerNumber]", "[Customer]", _
"[CustomerNumber] = '" & Me!txtCustomerNumber & "'") Then
MsgBox "This customer number already exists, try again", vbOKOnly
Cancel = True
Me!txtCustomerNumber.Undo
End If
End Sub

Frankly, I'd suggest using an automatically assigned meaningless
number, rather than frustrating your users by making them enter
numbers at hazard and punishing them if they guess wrong!

John W. Vinson[MVP]

I decided to use the meaningless number idea.
Thanks

"John Vinson" wrote:

On Thu, 16 Mar 2006 13:45:27 -0800, Ron Weaver
wrote:

Hi all

I need some code to insert into the validation box in a table. I have a
field in the Customer table: "CustomerNumber". This number consists of 5
letters and an optional 2 digits. While inputing an order, I would like this
code to tell me if I'm creating a duplicate and advise me to create another
number. I would also like the input process to go no farther until something
is entered in the field. I set the "Required" property to "Yes ", but it just
tabs right on by. Access 2003.
Thanks

You should probably use "belt and braces" here. Make the
CustomerNumber the Primary Key of the table, and you won't be able to
enter duplicates by *any* means... but you'll also get user-hostile
error messages if you try.

To provide a friendlier interface, use the BeforeUpdate event of the
textbox on the Form you're using to enter the data (and yes, you must
use a Form; table datasheets are extremely limited in their
capabilities). Something like

Private Sub txtCustomerNumber_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerNumber]", "[Customer]", _
"[CustomerNumber] = '" & Me!txtCustomerNumber & "'") Then
MsgBox "This customer number already exists, try again", vbOKOnly
Cancel = True
Me!txtCustomerNumber.Undo
End If
End Sub

Frankly, I'd suggest using an automatically assigned meaningless
number, rather than frustrating your users by making them enter
numbers at hazard and punishing them if they guess wrong!

John W. Vinson[MVP]

If the number is to be seen you may want to use an incrementing number such
as is described he
http://www.rogersaccesslibrary.com/d...berProblem.mdb


"Ron Weaver" wrote in message
...
I decided to use the meaningless number idea.
Thanks

"John Vinson" wrote:

On Thu, 16 Mar 2006 13:45:27 -0800, Ron Weaver
wrote:

Hi all

I need some code to insert into the validation box in a table. I have a
field in the Customer table: "CustomerNumber". This number consists of 5
letters and an optional 2 digits. While inputing an order, I would like
this
code to tell me if I'm creating a duplicate and advise me to create
another
number. I would also like the input process to go no farther until
something
is entered in the field. I set the "Required" property to "Yes ", but it
just
tabs right on by. Access 2003.
Thanks

You should probably use "belt and braces" here. Make the
CustomerNumber the Primary Key of the table, and you won't be able to
enter duplicates by *any* means... but you'll also get user-hostile
error messages if you try.

To provide a friendlier interface, use the BeforeUpdate event of the
textbox on the Form you're using to enter the data (and yes, you must
use a Form; table datasheets are extremely limited in their
capabilities). Something like

Private Sub txtCustomerNumber_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerNumber]", "[Customer]", _
"[CustomerNumber] = '" & Me!txtCustomerNumber & "'") Then
MsgBox "This customer number already exists, try again", vbOKOnly
Cancel = True
Me!txtCustomerNumber.Undo
End If
End Sub

Frankly, I'd suggest using an automatically assigned meaningless
number, rather than frustrating your users by making them enter
numbers at hazard and punishing them if they guess wrong!

John W. Vinson[MVP]

Thanks Bruce

I was trying to figure out how I could get the program to let me know when I
was duplicating a unique customer number. I have to use unique customer
numbers in another part of the program which pulls multiple orders from a
single customer down to a listbox.

"BruceM" wrote:

If the number is to be seen you may want to use an incrementing number such
as is described he
http://www.rogersaccesslibrary.com/d...berProblem.mdb


"Ron Weaver" wrote in message
...
I decided to use the meaningless number idea.
Thanks

"John Vinson" wrote:

On Thu, 16 Mar 2006 13:45:27 -0800, Ron Weaver
wrote:

Hi all

I need some code to insert into the validation box in a table. I have a
field in the Customer table: "CustomerNumber". This number consists of 5
letters and an optional 2 digits. While inputing an order, I would like
this
code to tell me if I'm creating a duplicate and advise me to create
another
number. I would also like the input process to go no farther until
something
is entered in the field. I set the "Required" property to "Yes ", but it
just
tabs right on by. Access 2003.
Thanks

You should probably use "belt and braces" here. Make the
CustomerNumber the Primary Key of the table, and you won't be able to
enter duplicates by *any* means... but you'll also get user-hostile
error messages if you try.

To provide a friendlier interface, use the BeforeUpdate event of the
textbox on the Form you're using to enter the data (and yes, you must
use a Form; table datasheets are extremely limited in their
capabilities). Something like

Private Sub txtCustomerNumber_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerNumber]", "[Customer]", _
"[CustomerNumber] = '" & Me!txtCustomerNumber & "'") Then
MsgBox "This customer number already exists, try again", vbOKOnly
Cancel = True
Me!txtCustomerNumber.Undo
End If
End Sub

Frankly, I'd suggest using an automatically assigned meaningless
number, rather than frustrating your users by making them enter
numbers at hazard and punishing them if they guess wrong!

John W. Vinson[MVP]

Autonumber is probably the easiest way of ensuring a unique number, but it
will almost ineveitably leave gaps in the numbering. My point is only that
if you need to see the number, autonumber may not be the best choice.

"Ron Weaver" wrote in message
...
Thanks Bruce

I was trying to figure out how I could get the program to let me know when
I
was duplicating a unique customer number. I have to use unique customer
numbers in another part of the program which pulls multiple orders from a
single customer down to a listbox.

"BruceM" wrote:

If the number is to be seen you may want to use an incrementing number
such
as is described he
http://www.rogersaccesslibrary.com/d...berProblem.mdb


"Ron Weaver" wrote in message
...
I decided to use the meaningless number idea.
Thanks

"John Vinson" wrote:

On Thu, 16 Mar 2006 13:45:27 -0800, Ron Weaver
wrote:

Hi all

I need some code to insert into the validation box in a table. I have
a
field in the Customer table: "CustomerNumber". This number consists
of 5
letters and an optional 2 digits. While inputing an order, I would
like
this
code to tell me if I'm creating a duplicate and advise me to create
another
number. I would also like the input process to go no farther until
something
is entered in the field. I set the "Required" property to "Yes ", but
it
just
tabs right on by. Access 2003.
Thanks

You should probably use "belt and braces" here. Make the
CustomerNumber the Primary Key of the table, and you won't be able to
enter duplicates by *any* means... but you'll also get user-hostile
error messages if you try.

To provide a friendlier interface, use the BeforeUpdate event of the
textbox on the Form you're using to enter the data (and yes, you must
use a Form; table datasheets are extremely limited in their
capabilities). Something like

Private Sub txtCustomerNumber_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerNumber]", "[Customer]", _
"[CustomerNumber] = '" & Me!txtCustomerNumber & "'") Then
MsgBox "This customer number already exists, try again", vbOKOnly
Cancel = True
Me!txtCustomerNumber.Undo
End If
End Sub

Frankly, I'd suggest using an automatically assigned meaningless
number, rather than frustrating your users by making them enter
numbers at hazard and punishing them if they guess wrong!

John W. Vinson[MVP]

Bruce

I create the customer number myself, like in the Northwind database. First 3
letters of the last name and first 2 letters of the first name, leaving 2
digits open for duplicates. Christy Brown would be: BROCH. Another customer
that would have the same letters would be BROCH01, etc. I need something that
would prompt me if I'm getting ready to enter the same letters again. I need
this so I can pull all of BROCH's orders separate from BROCH01. I hope that
makes some sense.

"BruceM" wrote:

Autonumber is probably the easiest way of ensuring a unique number, but it
will almost ineveitably leave gaps in the numbering. My point is only that
if you need to see the number, autonumber may not be the best choice.

"Ron Weaver" wrote in message
...
Thanks Bruce

I was trying to figure out how I could get the program to let me know when
I
was duplicating a unique customer number. I have to use unique customer
numbers in another part of the program which pulls multiple orders from a
single customer down to a listbox.

"BruceM" wrote:

If the number is to be seen you may want to use an incrementing number
such
as is described he
http://www.rogersaccesslibrary.com/d...berProblem.mdb


"Ron Weaver" wrote in message
...
I decided to use the meaningless number idea.
Thanks

"John Vinson" wrote:

On Thu, 16 Mar 2006 13:45:27 -0800, Ron Weaver
wrote:

Hi all

I need some code to insert into the validation box in a table. I have
a
field in the Customer table: "CustomerNumber". This number consists
of 5
letters and an optional 2 digits. While inputing an order, I would
like
this
code to tell me if I'm creating a duplicate and advise me to create
another
number. I would also like the input process to go no farther until
something
is entered in the field. I set the "Required" property to "Yes ", but
it
just
tabs right on by. Access 2003.
Thanks

You should probably use "belt and braces" here. Make the
CustomerNumber the Primary Key of the table, and you won't be able to
enter duplicates by *any* means... but you'll also get user-hostile
error messages if you try.

To provide a friendlier interface, use the BeforeUpdate event of the
textbox on the Form you're using to enter the data (and yes, you must
use a Form; table datasheets are extremely limited in their
capabilities). Something like

Private Sub txtCustomerNumber_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerNumber]", "[Customer]", _
"[CustomerNumber] = '" & Me!txtCustomerNumber & "'") Then
MsgBox "This customer number already exists, try again", vbOKOnly
Cancel = True
Me!txtCustomerNumber.Undo
End If
End Sub

Frankly, I'd suggest using an automatically assigned meaningless
number, rather than frustrating your users by making them enter
numbers at hazard and punishing them if they guess wrong!

John W. Vinson[MVP]

Sure does, but I won't be able to take a closer look before Monday. Is two
numbers for duplicates adequate? May I suggest that you use the number
suffix for all names, perhaps starting with 00 (or 01)? It would simplify
automation.

"Ron Weaver" wrote in message
...
Bruce

I create the customer number myself, like in the Northwind database. First
3
letters of the last name and first 2 letters of the first name, leaving 2
digits open for duplicates. Christy Brown would be: BROCH. Another
customer
that would have the same letters would be BROCH01, etc. I need something
that
would prompt me if I'm getting ready to enter the same letters again. I
need
this so I can pull all of BROCH's orders separate from BROCH01. I hope
that
makes some sense.

"BruceM" wrote:

Autonumber is probably the easiest way of ensuring a unique number, but
it
will almost ineveitably leave gaps in the numbering. My point is only
that
if you need to see the number, autonumber may not be the best choice.

"Ron Weaver" wrote in message
...
Thanks Bruce

I was trying to figure out how I could get the program to let me know
when
I
was duplicating a unique customer number. I have to use unique customer
numbers in another part of the program which pulls multiple orders from
a
single customer down to a listbox.

"BruceM" wrote:

If the number is to be seen you may want to use an incrementing number
such
as is described he
http://www.rogersaccesslibrary.com/d...berProblem.mdb


"Ron Weaver" wrote in message
...
I decided to use the meaningless number idea.
Thanks

"John Vinson" wrote:

On Thu, 16 Mar 2006 13:45:27 -0800, Ron Weaver
wrote:

Hi all

I need some code to insert into the validation box in a table. I
have
a
field in the Customer table: "CustomerNumber". This number
consists
of 5
letters and an optional 2 digits. While inputing an order, I would
like
this
code to tell me if I'm creating a duplicate and advise me to
create
another
number. I would also like the input process to go no farther until
something
is entered in the field. I set the "Required" property to "Yes ",
but
it
just
tabs right on by. Access 2003.
Thanks

You should probably use "belt and braces" here. Make the
CustomerNumber the Primary Key of the table, and you won't be able
to
enter duplicates by *any* means... but you'll also get user-hostile
error messages if you try.

To provide a friendlier interface, use the BeforeUpdate event of
the
textbox on the Form you're using to enter the data (and yes, you
must
use a Form; table datasheets are extremely limited in their
capabilities). Something like

Private Sub txtCustomerNumber_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerNumber]", "[Customer]", _
"[CustomerNumber] = '" & Me!txtCustomerNumber & "'") Then
MsgBox "This customer number already exists, try again",
vbOKOnly
Cancel = True
Me!txtCustomerNumber.Undo
End If
End Sub

Frankly, I'd suggest using an automatically assigned meaningless
number, rather than frustrating your users by making them enter
numbers at hazard and punishing them if they guess wrong!

John W. Vinson[MVP]

On Fri, 17 Mar 2006 12:23:27 -0800, Ron Weaver
wrote:

Bruce

I create the customer number myself, like in the Northwind database. First 3
letters of the last name and first 2 letters of the first name, leaving 2
digits open for duplicates. Christy Brown would be: BROCH. Another customer
that would have the same letters would be BROCH01, etc. I need something that
would prompt me if I'm getting ready to enter the same letters again. I need
this so I can pull all of BROCH's orders separate from BROCH01. I hope that
makes some sense.

In my experience such "intelligent keys" are essentially never worth
the hassle of maintaining them. Is BROCH01 Christy Brown, or Charles
Broward, or Chenille Broadbent? You've got to use some other controls
or controls to find out; and given that, you might as well just use
31, 219, and 332 as the customerID's instead. If Chenille gets married
and becomes Chenille Stewart, does her customer number remain BROCH03,
or do you track down all the instances and rename her STECH04?

In my experience, the supposed mnemonic benefits of storing part of
the data redundantly in a key are rarely actually beneficial, and
there's no question that the detriments of storing data redundantly
and nonuniquely ARE detrimental.

John W. Vinson[MVP]

I don't only agree with John,

I'm an evangelist!

Use keys that do not contain any "intelligence".

Then use a good realtional design to get the information you want.

Ed Warren.

"John Vinson" wrote in message
...
On Fri, 17 Mar 2006 12:23:27 -0800, Ron Weaver
wrote:

Bruce

I create the customer number myself, like in the Northwind database. First
3
letters of the last name and first 2 letters of the first name, leaving 2
digits open for duplicates. Christy Brown would be: BROCH. Another
customer
that would have the same letters would be BROCH01, etc. I need something
that
would prompt me if I'm getting ready to enter the same letters again. I
need
this so I can pull all of BROCH's orders separate from BROCH01. I hope
that
makes some sense.

In my experience such "intelligent keys" are essentially never worth
the hassle of maintaining them. Is BROCH01 Christy Brown, or Charles
Broward, or Chenille Broadbent? You've got to use some other controls
or controls to find out; and given that, you might as well just use
31, 219, and 332 as the customerID's instead. If Chenille gets married
and becomes Chenille Stewart, does her customer number remain BROCH03,
or do you track down all the instances and rename her STECH04?

In my experience, the supposed mnemonic benefits of storing part of
the data redundantly in a key are rarely actually beneficial, and
there's no question that the detriments of storing data redundantly
and nonuniquely ARE detrimental.

John W. Vinson[MVP]