I am currently creating a form, for which I want to generate an automatic number reference.

The number will be based on the current month/year, followed by a 3 digit sequential number, e.g. 0905-001. I want this sequential number to start at 001 then every time the form is opened it will read previous forms that have been saved in a particular location and take the next number. If there is no previous form for that month, I want the number to start again at 001.

Another problem I must consider is if 2 or more people open the form at the same time because, until the form is saved the number will not be reserved. How can I prevent the same number being allocated twice?

I am new to vba and, although I have several books, I can't seem to find anything that will provide a solution.

Any help, even a pointer on what topic to research, will be much appreciated.