Multipart Record Number

[Guest]

I have a DB where they need the record number to consist of three parts. The
first is a constant number, "43." The second part is derived from a reason
code in a table which is selected from a ComboBox. (Table contains two
columns; the 2 digit code, 01 thru 09, and the text reason.) The third part
is an auto number. I'll end up with: 43-XX-XXXXX. How do I go about
getting all this to work in my record number field? Thanks in advance!

 

[Guest]

Where is the autonumber portion of the record number coming from? Do the
record numbers have to be unique? Do the records have to increment based on
previously used records numbers?

As an example...if you have 43-01-00001 can the next record number be
43-09-00002 and the next record number be 43-01-00003?

 

[Mike Painter]

I have a DB where they need the record number to consist of three
parts. The first is a constant number, "43." The second part is
derived from a reason code in a table which is selected from a
ComboBox. (Table contains two columns; the 2 digit code, 01 thru
09, and the text reason.) The third part is an auto number. I'll
end up with: 43-XX-XXXXX. How do I go about getting all this to
work in my record number field? Thanks in advance!

Use a query and leave the individual fields as they are.
The query will have a calculated field named WhatEver
WhatEver: "43-"& comboboxresult & "-" & YourAutonumber
Index all three fields and if your auto number is a real autonumber use it
as the key.

 

[Joseph Meehan]

I have a DB where they need the record number to consist of three
parts. The first is a constant number, "43." The second part is
derived from a reason code in a table which is selected from a
ComboBox. (Table contains two columns; the 2 digit code, 01 thru
09, and the text reason.) The third part is an auto number. I'll
end up with: 43-XX-XXXXX. How do I go about getting all this to
work in my record number field? Thanks in advance!

No need to repeat what Mike wrote as that is your answer I believe. I
would note that assuming the 43 will never change you do not need to store
it at all and just add it in as a constant anytime you display the whole
"number".

I also suggest you consider the "autonumber" issue. If you are using
the Access autonumber, you should be aware that you should never count on it
providing numbers in any given order or not skipping numbers. It is
designed to provide unique numbers is a sort of consecutive or random order
only. This confuses a lot of users who expect 1, 2, 3, 4 ... and get 1, 2,
5, 6 ...

 

[Guest]

Record number
yes
yes
yes

Where is the autonumber portion of the record number coming from? Do the
record numbers have to be unique? Do the records have to increment based on
previously used records numbers?

As an example...if you have 43-01-00001 can the next record number be
43-09-00002 and the next record number be 43-01-00003?