I need to create a unique reference number

[accesnovice]

Hi!

I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.

The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.

This is kind of like library catalog numbers. Not sure how to do this. Any help?

 

[GinaWhipp]

Welcome to the Forum!

Have a look here...
http://regina-whipp.com/blog/?p=704

 

[vbaInet]

You also need to consider whether it's worth concatenating and saving data that already exists in separate fields. It's a duplicated effort if you're doing this. Perhaps GinaWhipp's link highlights this, not sure.

The three parts already exist in separate fields, and it doesn't necessarily need to exist as a single field because you can calculate that field each time you want to present a reference number.

But then again, I won't rule it out completely because it depends on your usage of the reference field in searches and linking. Just highlighting this as it's something to consider.