troubleshooting Question

Renumbering (re-sequencing) Autonumber

Avatar of billcute
billcute asked on
Microsoft Access
10 Comments2 Solutions399 ViewsLast Modified:
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.

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
     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
(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
Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”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