I would like to create a unique ID number (currently the Primary Key) for a table in Access 2000.
I am using 4 elements for the current manual entry, and want to automate it after I make one selection on the Combo box.
The elements are:
1: The species of the wood I am entering into the inventory.
2: The last two digits of the current year.
3: A ":" as a separator.
4: A sequential number unique for each species of wood as purchased in the current year. This would reset when the year changes.
For example, I can use the Combo Box to select either Ash, Maple, Myrtle or Elm. When I make the selection, I want the system to automatically generate the rest of the ID code.
If I had the sixth piece of Maple for 2007 I would select Maple in the box, then the generated code would be Maple07:006. The next piece of wood might be the fiftieth piece of Myrtle, so it would be Myrtle07:050. On Jan 1, 2008 the sequence numbers would all reset to 001 - so the first piece of Maple that year would be Maple08:001.
Can this be done? It would help me to avoid the problem of skipping numbers when I loose track of the last piece of the same species I entered. Can I do this with a simply formula macro, or would it need VBA?
And can this code ten be used in the Primary Key for that table - and will I need to re-enter all the 300+ pieces I already have in the table?
I had thought this could be done with a 3 field key - but I'd like to keep it as simple as possible.
I am using 4 elements for the current manual entry, and want to automate it after I make one selection on the Combo box.
The elements are:
1: The species of the wood I am entering into the inventory.
2: The last two digits of the current year.
3: A ":" as a separator.
4: A sequential number unique for each species of wood as purchased in the current year. This would reset when the year changes.
For example, I can use the Combo Box to select either Ash, Maple, Myrtle or Elm. When I make the selection, I want the system to automatically generate the rest of the ID code.
If I had the sixth piece of Maple for 2007 I would select Maple in the box, then the generated code would be Maple07:006. The next piece of wood might be the fiftieth piece of Myrtle, so it would be Myrtle07:050. On Jan 1, 2008 the sequence numbers would all reset to 001 - so the first piece of Maple that year would be Maple08:001.
Can this be done? It would help me to avoid the problem of skipping numbers when I loose track of the last piece of the same species I entered. Can I do this with a simply formula macro, or would it need VBA?
And can this code ten be used in the Primary Key for that table - and will I need to re-enter all the 300+ pieces I already have in the table?
I had thought this could be done with a 3 field key - but I'd like to keep it as simple as possible.