Our company policy allows duplicate records to be created. Each duplicate record is expected to increment by 1 and grouped by the entire like operant preceding alphanumeric
for example - 20080409-KW-TA-1140-112-DM
.
I am attaching a breakdown of tblMain showing group of LIKEOPERANTS in tblMain_a, tblMain_b, tblMain_c and tblMain_d respectiely.
http://www.geocities.com/bombastikbill/AutoSequenseJobNo-v2.zipIn this sample you will find a module that is used by multiple forms in creating JobNos. This module may be modified by experts if needed.
The tblMain table is not related to any other tables and each Job No is created using the following: In tbMain, the following fields already exist in columns which
are utilized to create the JobNo value.
(1). tblMain.DateCreated - e.g 20080410 -- Date
(2). "KW" is added right after the date as Tag name
as such form part of the "Like Operant" when grouping
the JobNo IDs.
(3). tblMain.WConnTypeID has these values "WC", "TA", "TP" etc..
.... each value is inserted as well in Creating the JobNo
Like Operant
(4). tblMain.BlockNo is the next in line field utilized in that sequence
(5). tblMain.LotNo is the next in line field utilized in that sequence
(6). tblMain.ServiceType is the next in line field utilized in that sequence.
If items #1 through 6 are followed in that sequence, we'll end up with values shown below:
ID. LikeOperants: dateCreated Tag WConnTypeID ServiceTypeID BlockNo LotNo Sequence
1. KW-TA-1747-14-CM 1/2/2004 KW TA CM 1741 14 01
3. KW-TA-1747-14-CM 6/1/2004 KW TA CM 1741 14 02
4 KW-TA-1747-14-CM 6/2/2004 KW TA CM 1741 14 03
**************************
The same table with the JobNo is shown below:
ID. LikeOperants: JobNo (see tblMain_a)
1. KW-TA-1747-14-CM 20040102-KW-TA-1747-14-CM-
01
3. KW-TA-1747-14-CM 20040601-KW-TA-1747-14-CM-
02
4 KW-TA-1747-14-CM 20040602-KW-TA-1747-14-CM-
04
The current code in creating this JobNo (see attached) seems to work fine
from a data entry form.
New Request.
There are times when JobNo(s) might be deleted due to wrong entries or that a JobNo may have been created in error; or a member of a group of Like Operant changed and as such it becomes necessary to re-number (resequenced) the affected groups. As such, my company wants to the following:
(1). Delete the specific JobNo in a group of Like Operant as such would like to - create a new column in
the tblMain containing a tag that indicates that the JobNo has been deleted. Then show the Job on
the data entry form or possibly "grey out" the record on the form by making it uneditable after it has
been "deleted".
(2). "re-number" (re-sequence) the JobNo(s) from the tblMain table in the group of "Like Operants" such
that the last two digits numbered 01, 03, 04, 06 etc preceeding the Like Operant are re-ordered
(re-numbered)
(3). When a value in a group of like Operant changes where it becomes necessary to re-group the
changed JobNo to the new group of Like Operant, and then "re-number" (re-sequence) the changed
JobNo in the affected groups of Like-Operants.
(Also see the attached text file for examples of Like Operants)
I am willing to accept new ideas in order to be able to achieve my desired objective
