Generating Serial Number

[Eye Doc]

I apologise if this is a complete 'noob' question.

I am writing a database to record scientific sample data. The established convention has been to name the samples with a 2 letter designation describing what the type of sample is and then a 2 digit serial number based on the sample type

e.g. OF01 OM01 OF02 SC01 OM02 OF03

I want to write a script that will generate that id number.

The only problem is that I have absolutely no idea how to do it!

I am presuming that I will have to find all the records that start OF. Then strip the last 2 characters, find the highest number and then add one.

All help will be greatly appreciated.

Many Thanks

[Fitch]

First, to get the number portion of the text, use the GetAsNumber function.

Next, to get the highest number, use the Max function.

But the Max of what?

Set up a relationship of the table to itself based on the sample type. So your final calc would be:

Max( GetAsNumber( SelfBySampleType::ID ) ) + 1

You should be able to use that for auto entry, so you wouldn't necessarily need a script. If you do want to use a script, there are other ways you could accomplish the same thing. E.g., you could pop up a new window, find matching records, sort by id, then go to the last record, add one to that, then close the window.

[Eye Doc]

Thanks very much for your help! I had been going round in circles for over an hour before giving up!

Thanks again.