Emmanuel Katto Dubai : Help Needed with Generating Unique Customer Codes in Access

[emmanuelkatto24]

Hi everyone,

I’m Emmanuel Katto from Dubai, United Arab Emirates (UAE) new to Access and could use some assistance. I have a table with two fields: Customer Code and Customer Type (with options for Retail or Wholesale). I want to generate a unique customer code that consists of the first letter of the customer type, the current date, and a random number at the end. This code needs to be indexed and unique.

Here’s what I’ve done so far:

1. I created a combo box for selecting the Customer Type.
2. I have an unbound text box that uses an IIF statement to get the initial (R or W) based on the customer type selection.
3. I can create an unbound text box to generate the code in the format CustomerT & Date & Random Number, but this code isn't indexed and doesn't save back to the table.

The challenge I’m facing is:

• If I bind the Customer Code field to the table with a default value like W-Date-RndN, it doesn’t update when I change the customer type since it’s bound to the table.

I’m having trouble figuring out how to make this work. Any guidance on how to set this up would be greatly appreciated!

Thanks in advance for your help!
Regards
Emmanuel Katto

 

[GaP42]

Some questions/ advice:
1. Do not use your Customer Code as the PK ID for the customer. I assume this is a user/customer-facing code they might recognise., use in communications - invoices etc
2. Do you expect any cases where a customer may change or be both a retail and a wholesale customer?
3. The format includes "-" between each segment of the code?
4. Do you have a defined format for the current date? yyyymmdd or ...?
5. Recommend that random number is replaced by sequential numbering, reset at the start of each day, How many customers would you expect to add in a day? Do you want the Customer code to be the same length for all customers? If so increment the number with leading 0, so if max per day is less than a 1000, start at 001 up to max 999. it will be a text value. possibly use dmax to check the last max value assigned to customer on the current date and add 1.
6. Your table should already have a field for Customer Type, and for CustomerDateStart. So all you then need is to add a CustomerNoforDay field. This would be bound. The CustomerCode is then a concatenation of these values.

Having created the code in the unbound control - how are you trying to update the record that must exist in order for it to be assigned to this current record? (update SQL). How are you avoiding overwriting the customer code for an existing customer? (Does it only apply to the new customer form?)
Index - have you established the index in the underlying table?

 

[arnelgp]

you can call a UDF from Datamacro of your table.
see this demo. Open table1 in datasheet view.
select the CustomerType and leave the customer code field blank.
press Enter until the record is saved.
see the Datamacro (BeforeChange) of the table in design view.

 

[Gasman]

I know you check for the value, but I still would not have Duplicates OK?

I do like the example of the data macro though.

 

[The_Doc_Man]

If I bind the Customer Code field to the table with a default value like W-Date-RndN, it doesn’t update when I change the customer type since it’s bound to the table.

Note that this statement essentially disqualifies this field as a prime key, since PKs are supposed to be permanently immutable yet you want to update (change) this. You can still, of course, generate such a field as your own "public" key field, but for anything involving relationships - the kind that you define from the ribbon >> Database Tools >> Relationships - this field is not a good candidate key. Usually, if you are going to have things that use a relationship based on the customer record, e.g. a customer's purchase records, you generate an autonumber PK and just never show it to anyone. Use that PK for the linkage from dependent records to the independent customer record.

 

[Gasman]

If I bind the Customer Code field to the table with a default value like W-Date-RndN, it doesn’t update when I change the customer type since it’s bound to the table.

Rubbish. If you change the customerType, the customer code changes via the data macro.
You might need to refresh the form on the Afterupdate event of the combo, but the data is updated.

 

[Pat Hartman]

Changing this customer code when one of the underlying pieces of data changes is seriously poor practice. What happens if someone is looking at an old printed report or invoice and uses the old code? Remove the customerType from the generated code. UniqueID's are just that. They should never be changed even when you don't use them as the PK.

 

[Edgar_]

I think the OP has posted 4 threads already and has not replied to any.