Alphanumeric Sequential numbering

[Chris W via AccessMonster.com]

Hi
Background
I am developing a job file database that, stores all the client data and the
job details. Now the job forms the transaction table with other peripheral
tables such as industry code ECT. The Database is to operate along with the
physical file which contains details of working notes and details which must
be kept for legal reasons.

Problem
This means that the primary key of the transaction field would ideally be the
number used in the existing physical filing system (as that can’t change).
This existing system is an alphanumeric sequential numbering system, i.e.
VPM001, VPM002, VPM003; the good news is that the “VPM” never changes so it
is only the numbers that are sequential.

I was able to find the following code in a previous post

=InvNum: “newinvoice’ & Format(Max(Val[Cnt#]))+1,“000”)

But I don’t know exactly what to do with it, wether I put it in an event code
for a field attached to the table via a form.

So if any one could help me that would be really good.

 

[Vincent Johns]

Hi
Background
I am developing a job file database that, stores all the client data and the
job details. Now the job forms the transaction table with other peripheral
tables such as industry code ECT. The Database is to operate along with the
physical file which contains details of working notes and details which must
be kept for legal reasons.

Problem
This means that the primary key of the transaction field would ideally be the
number used in the existing physical filing system (as that can’t change).
This existing system is an alphanumeric sequential numbering system, i.e.
VPM001, VPM002, VPM003; the good news is that the “VPM” never changes so it
is only the numbers that are sequential.

I was able to find the following code in a previous post

=InvNum: “newinvoice’ & Format(Max(Val[Cnt#]))+1,“000”)

Suppose you have a Table with these records:

[VPM] Table:
VPMID Name
------ ------
VPM001 One
VPM003 Three
VPM007 Seven
VPM009 Nine

Then you can define a Query with the following SQL, which examines the
existing [VPMID] values, beginning with the 4th character (following the
"VPM") and constructs a new one:

[Q_NewID] SQL:
SELECT "VPM"+Format$((Max(Val(Mid$([VPMID],4))))+1,"000")
AS New_ID
FROM VPM;

[Q_NewID] Datasheet View:
New_ID
------
VPM010

Then you can construct a Form, [F_VPM], which contains text box [VPMID]
with the following properties:

Name: VPMID
Control Source: VPMID
Default Value: =DMax("[New_ID]","Q_NewID")
Enabled: No

It also contains another Text Box with
Control Source: Name
Enabled: Yes

Since Text Box VPMID is not enabled, it is shown with grayed text,
making it obvious that the value can't be changed via data entry.

You can use this Form for data entry. (Note: It doesn't work too well
in Form Datasheet View, as the Query doesn't get automatically updated
after adding a record. It does get updated in Form View.)

But I don’t know exactly what to do with it, wether I put it in an event code
for a field attached to the table via a form.

So if any one could help me that would be really good.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Chris W via AccessMonster.com]

Thanks mate that works a charm exactly what I want.

I would use autonumber to create the ID in the table and add the "VPM"
in Queries, Forms, or Reports.
Here is a description of how to start the ID field where you want:
http://www.mvps.org/access/tables/tbl0005.htm
I beleive you could do this by using your current table as the second
table in the above. However, make a copy of your table before doing
this.

Hi
Background

[quoted text clipped - 28 lines]

So if any one could help me that would be really good.