Solved How to set up unique customer codes based on customer types

[CJ_London]

i was trying make each client code unique but similar

The danger with that is users look at the code and not the real differentiator (such as full name and address) - and can then select the wrong record and not notice

 

[amorosik]

I believe the ONLY way to have a valid data pair is to program the database so that it CANNOT accept duplicate pairs, simply by inserting a unique index that includes the customer code and customer type
This is because any customer-code/customer-type read you perform could have just been performed by another workstation and could use it to write a new record to the database
The code running on your workstation 'thinks' it has a unique pair, attempts to save it, and baamm, it clashes with the pair just saved
So the only real option, in a multi-user environment, is to:
- define a unique key that includes the two fields
- attempt to save and handle the error that could arise from the presence of a pair of values in the archive

 

[CJ_London]

So the only real option, in a multi-user environment, is to:
- define a unique key that includes the two fields
- attempt to save and handle the error that could arise from the presence of a pair of values in the archive

in this scenario what's the difference between handling a a multi column index where a clash arises and a unique key as you describe?

 

[Ken Sheridan]

To show the first three characters of the client's name in upper case extend the expression I posted earlier as below:

UCase(Left(ClientName,3)) & Format(ClientID,"0000") & "-" & IIf(ClientType = "Individual", "IND","BIZ")