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.zip
In 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
(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.
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
(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
(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 Re-numbering.txt
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.