autonumber based on category

[basilyos]

i have a table contains three fields (id - category - description - code)

1- Mugs - white - MUG01
2- Plate - 3D 8" - PLA01
3- Mugs - colored - MUG02
4- Mobile Cover - iphone 6 - MOB01
5- Mobile Cover - note 5 - MOB02

so i want to enter the category and the description then the code will be automatically created if i enter a new record (6) and i choose from the combo box Mugs then i choose white from the description it will be automatically MUG01 but if i enter a new description it will be automatically MUG 03

any help?

 

[plog]

I wouldn't store the CODE field, this is technically a calculated value--1st 3 characters of Category and then a number that determines order of addition. What you do need to store and generate is that order of addition number, let's call it CategoryNumber. What you would do is whenever a new item is added and the Category is selected you would use a DMax to find the highest CategoryNumber in use for the category you selected. Then add 1 to it and that's the new record's CategoryNumber.

Then you can build your code by taking the first 3 of the category and appending the CategoryNumber to it. Storing the CODE field is both incorrect and a pain if done as you described (you'd have to parse out the number to find out which one is next).

 

[basilyos]

what am doing is enter the purchases invoices so if i buy white mugs today and i add it
if the white mugs is already in the stock it will take the white mugs code but if it's a new item it will take a new id this is why i want to store the codes

 

[plog]

My advice still stands.

 

[basilyos]

i uploaded a simple of my database could you please help me to do what you tell me

 

[plog]

Add a new numeric field called ItemNumber to tbl_items_description.
Create a new form to add/edit tbl_items_description.
When a new item is added find its ItemNumber by using DMax to find the last ItemNumber used for a product in its category.
In a query, construct your ItemCode to conform to your formatting rules.