Need validation rule to pevent duplicating a customer number

[Ron Weaver]

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

 

[John Vinson]

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]

 

[Ron Weaver]

I decided to use the meaningless number idea.
Thanks

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]

 

[BruceM]

If the number is to be seen you may want to use an incrementing number such
as is described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

I decided to use the meaningless number idea.
Thanks

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]

 

[Ron Weaver]

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.

If the number is to be seen you may want to use an incrementing number such
as is described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

I decided to use the meaningless number idea.
Thanks

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

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]

 

[BruceM]

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.

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.

If the number is to be seen you may want to use an incrementing number
such
as is described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

I decided to use the meaningless number idea.
Thanks

:

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

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]

 

[Ron Weaver]

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.

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.

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.

If the number is to be seen you may want to use an incrementing number
such
as is described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb


I decided to use the meaningless number idea.
Thanks

:

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

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]

 

[BruceM]

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.

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.

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.

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.

:

If the number is to be seen you may want to use an incrementing number
such
as is described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb


I decided to use the meaningless number idea.
Thanks

:

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

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]

 

[John Vinson]

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]

 

[Ed Warren]

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.

 

[Ron Weaver]

I agree John, but this is a small internal operation. I do get your point.
Thanks

 

[Ron Weaver]

How would you go about it??

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.

 

[Ron Weaver]

I can do that. I would appreciate any help.
Thanks

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.

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.

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.

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.

:

If the number is to be seen you may want to use an incrementing number
such
as is described here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb


I decided to use the meaningless number idea.
Thanks

:

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

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]

 

[John Vinson]

I agree John, but this is a small internal operation. I do get your point.
Thanks

<puzzlement> Ok, so use the SIMPLE technique - a concealed Autonumber
customerID, with a Combo Box such as

SELECT CustomerID, LastName & ", " & FirstName, Address
FROM Customers
ORDER BY LastName, FIrstName;

as the rowsource. This will display the full customer name if the
CustomerID column is set to zero width (as the combo wizard will do
for you) - you'll get BETTER mnemonics (the entire name, not a cryptic
five letter code), plus the address if you have two Jim Smiths or the
like.... using NO CODE AT ALL.

If it's a small internal operation... keep it simple. If it's a big
international operation... keep it a simple as possible. Maintaining
an intelligent key does NOT keep it simple!

John W. Vinson[MVP]

 

[BruceM]

I agree with John and Ed about "intelligent" keys being inadvisable in a
case where the name may change, or several people have the same combination
of letters, but particularly in the event of a name change. Does the ID
change in that case? I have known people who want no vestige of a former
name in any records.
I have used "intelligent" keys to do something like have a department code
followed by the last two digits of the year followed by a sequence number,
like this: SP-06-05. It can be done, but I won't get into how to do it
unless you are sure it is what you want to do. I would suggest using an
autonumber as John has suggested. You can add the other number afterward if
you like, but the autonumber key will be there in case you change your mind
some day about the constructed key.

 

[Ed Warren]

What's been missing from this discussion is the justification for the
'intelligent code'
One use I could think of for this type code would be to generate a code so
you can maintain system logons? eg. ewar01, ewar02

If so then you could use a table like: (or better generate it via a query)

EmployeeID (key) EmployeeLogon1 (text) EmployeeLogon2 (long)
1 -->(Ed Warren) ewar 01
25-->(Ellie Warkeim) ewar 02
Then using VBA code you could determine when you need to add another user
e.g. (Ester Warden)
that the next 'ewar' entry should be 03

Ed Warren.

 

[BruceM]

I had thought these were customer codes. If you want to have some sort of
security logon you would do better to use Access user-level security. A
newsgroup search will turn up lots of posts on the topic. I really can't
help there. I have been struggling to understand it myself.

It's up to you whether you choose to use them. Maintaining a separate
primary key is definitely a good idea, as you may not want to maintain the
name-based system. After she gets married it may be more of a chore than
it's worth to explain that Mary Anderson's code is MJon03 because she used
to be Mary Jones, and MAnd03 stands for Marilyn Smith, who used to be
Marilyn Andrews.

But if you wish to implement the system anyhow, here is one way. Create a
command button on a form (one based on the Employee table?) with the
following in the Click event:

If Me.NewRecord Then

Dim strWhere, strFirst, strLast As String
Dim varResult As Variant

strFirst = Left(FirstName, 1)
strLast = Left(LastName, 3)

strWhere = "NameCode Like """ & strFirst & strLast & "*"""
varResult = DMax("NameCode", "tblNameCode", strWhere)

If IsNull(varResult) Then
Me.txtNameCode = strFirst & strLast & "00"
Else
Me.txtNameCode = Left(varResult, 4) & Format(Val(Mid(varResult,
5, 2)) + 1, "00")
End If
End If

NameCode is the ID field (text), and txtNameCode is the name of the text box
bound to that field. FirstName and LastName are table fields. You would
need to complete those fields before running the code.

 

[Ron Weaver]

Thanks John

How do you get around the unique number when you need to pull up all of Jim
Smith's orders for the last year?? There is a CustomerID AutoNumber, but it
gives Jim Smith a different number on every order. The only customer
information they input is customer name. This is an internal audio visual
operation at a hospital.

 

[BruceM]

Oops. Lost track of who was who in this thread. I thought I was replying
to Ron.

 

[Ron Weaver]

Thanks everybody. I think I will try to approach this from another direction.