Auto Enter Two Digit Number Sequentially

[stealth162]

I have a table within my database where the user will create a record and a field called "record number". For other calculations and relationships to work within the database it has to be a two digit number.

If possible I'd like to have it setup so that when the user creates the records it starts with auto-entering 01 then moves to 02, 03, 04 etc...

I'd also like to make it modifiable by the user but make it so that it has to be two characters if they choose to change the auto-enter number.

Ideally the auto-enter number would look at the previous record number and always go to the next sequential number even if the user created a record and then deleted and then chose to creat it again.

Any help/thoughts are appreciated. Thanks.

[mr_vodka]

Take a look at these threads.

Serial Thread 1

Serial Thread 2

[stealth162]

Thing with those posts is that the number is going sequentially in a standard format. I need it to go sequentially in a two digit format. 01, 02, 03, 10, 11, 12 etc...

[T-Square]

stealth162--

Your post has me concerned, for a couple of reasons. You say "For other calculations and relationships to work within the database it has to be a two digit number." and then you say "if they choose to change the auto-enter number..."

If you are building relationships on this value, it's really not wise to allow users to change this value, as you will without a doubt end up with cross-linked child records and orphans.

Additionally, you appear to be trying to get other information out of your record number field (such as how many are in a given sequence, or how far into a sequence you are), which would be better served in other ways.

The immediate answer to your questions would be: to enable leading zeroes, set the field up as text, with an autoenter calc something like: Right("0" & (Max(GetAsNumber(SelfJoin::RecordNumber)) + 1)).

However, it's likely that a better answer would use different techniques to achieve your ends--such as a hidden unique serial number for relationships and a combination of calculation fields that inform you of how many entries are in a given sequence.

Without a description of what you're trying to do and why, though, it's difficult to offer assistance.

David

[mr_vodka]

Stealth,

David hit the head on the nail. The point of those posts were to demonstrate to you that you shouldnt being using serial number in that way.

[comment]

David hit the head on the nail.

I hope, for David's sake, it was the other way around.

[stealth162]

David,

The reason I say for other calculations to work is that I use these records in the table to provide grouping when I generate print layouts. I have it set so that the print layout is triggered with a sort script and therefore it can create a subsummary above the records showing the group and info and then the related records below. For example my primary group set as 01 and then records each have individual assignable numbers begining with 01, 02 or 03 etc. So I have the sort setup so that this primary group acts as the summary and then the related records fall below. If a record doesn't have the a matching primary it just shows under a "unsummarized" section in the report.

The reason why the user might want to change that number is only if for some reason the sequential number got off by the user creating a record in this primary group table and then deleting the record only to recreate it. Otherwise they wouldn't need to change it.

[mr_vodka]

Haha. Oops. B)