billcute
asked on
Renumbering (re-sequencing) Autonumber
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
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-
3. KW-TA-1747-14-CM 20040601-KW-TA-1747-14-CM-
4 KW-TA-1747-14-CM 20040602-KW-TA-1747-14-CM-
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
Re-numbering.txt
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
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-
3. KW-TA-1747-14-CM 20040601-KW-TA-1747-14-CM-
4 KW-TA-1747-14-CM 20040602-KW-TA-1747-14-CM-
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
Re-numbering.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note to discussion participants: This is a design question more than a technical implementation issue. BillCute needs help designing 'something' which meets needs that are not clearly defined.
For future readers and current discussion experts, here is a link to a related question on this problem:
https://www.experts-exchange.com/questions/23309179/Re-ordering-an-Autonumber.html
For future readers and current discussion experts, here is a link to a related question on this problem:
https://www.experts-exchange.com/questions/23309179/Re-ordering-an-Autonumber.html
ASKER
aikimark:
Further to your assistance in the other closed post. I will appreciate your assistance on this new listing.
Regards
Bill
Further to your assistance in the other closed post. I will appreciate your assistance on this new listing.
Regards
Bill
ASKER
aikimark:
Did you see my last post?
Did you see my last post?
@BillCute
I've read all these posts and expected more respondents than you've had. Please take the time to fully answer all of my questions.
"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..."
1. Are these truely duplicate records? (identical data)
2. What value is the duplicate record?
3. What value is viewing multiple duplicate records, even if some of them are deleted?
4. Grouping is done either by column or in left-to-right character order. What significance is the date and why does this appear on the left side of the JobNo string?
"...my company wants to the following:"
5. We are now into the second EE question on this topic and neither you nor your boss have answered my questions about WHY you want to do something. This is a question you need to ask while wearing your Business Analyst hat. I can not help you until I know these things. As I posted in the prior discussion "Just because something CAN be done, doesn't mean that it SHOULD be done."
6. Given that there is no relationship between tables, how is the JobNo used?
I've read all these posts and expected more respondents than you've had. Please take the time to fully answer all of my questions.
"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..."
1. Are these truely duplicate records? (identical data)
2. What value is the duplicate record?
3. What value is viewing multiple duplicate records, even if some of them are deleted?
4. Grouping is done either by column or in left-to-right character order. What significance is the date and why does this appear on the left side of the JobNo string?
"...my company wants to the following:"
5. We are now into the second EE question on this topic and neither you nor your boss have answered my questions about WHY you want to do something. This is a question you need to ask while wearing your Business Analyst hat. I can not help you until I know these things. As I posted in the prior discussion "Just because something CAN be done, doesn't mean that it SHOULD be done."
6. Given that there is no relationship between tables, how is the JobNo used?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jeff:
I have resolved the question using an outside help..but I'll still award you for trying...if I need more assistance on the new code..I'll post a new question and post a link here.
Thanks for your effort.
Regards
bill
I have resolved the question using an outside help..but I'll still award you for trying...if I need more assistance on the new code..I'll post a new question and post a link here.
Thanks for your effort.
Regards
bill
@BillCute
"I'll still award you for trying"
This makes two questions about JobNo with no clear resolution. What was the resolution? (for future readers)
==========================
FOR FUTURE QUESTIONS:
I done a lot of "trying" on these two questions with very little adequate response on your part. If you don't answer our questions, we can't effectively help you with your analysis, design, or implementation -- your problem solution.
"I'll still award you for trying"
This makes two questions about JobNo with no clear resolution. What was the resolution? (for future readers)
==========================
FOR FUTURE QUESTIONS:
I done a lot of "trying" on these two questions with very little adequate response on your part. If you don't answer our questions, we can't effectively help you with your analysis, design, or implementation -- your problem solution.
ASKER
Aikiman:
Here you go:
Public Function ReorderJobNos()
On Error GoTo Error_Routine
Dim rst3 As New ADODB.Recordset
Dim NewDate As String
Dim NewJobNo As String
Set cn = CurrentProject.Connection
Dim i As Integer
If strOperantValue = "ALL" Then
rst3.Open "qry................", cn, adOpenKeyset, adLockOptimistic
XCount = Nz(DCount("[zOperant]", "qry................"), 0)
Else
rst3.Open "qry.............", cn, adOpenKeyset, adLockOptimistic
XCount = Nz(DCount("[zOperant]", "qry..............", "[zOperant] Like'" & (strOperantValue) & "'"), 0)
End If
With rst3
If XCount = 0 Then
GoTo Exit_Continue
Else
.Requery
strPriorOperant = ""
strCurrentOperant = ""
y = 0
.MoveFirst
Do While Not .EOF
DoCmd.Hourglass True
For i = 1 To XCount
zProgressBar i, XCount
strPriorOperant = strCurrentOperant
strCurrentOperant = .Fields("zOperant")
If strCurrentOperant Like strPriorOperant Then
y = y + 1
Else
y = 1
End If
NewDate = Format(.Fields("datCreated
UniqueID = CStr(y)
NewJobNo = CStr(NewDate & "-" & .Fields("zOperant") & "-" & Format(UniqueID, "00"))
.Fields("JobNo") = NewJobNo
.Update
If i = XCount Then Exit Do
.MoveNext
Next i
Loop
DoCmd.Hourglass False
DoEvents
.Requery
End If
rst3.Close
End With
Exit_Continue:
Set rst3 = Nothing
i = 0
XCount = 0
strOperantValue = Empty
strPriorOperant = Empty
strCurrentOperant = Empty
y = 0
strFrmX = Empty
Exit Function
Error_Routine:
MsgBox "Error# " & Err.Number & " " & Err.Description
Resume Exit_Continue
End Function
Here you go:
Public Function ReorderJobNos()
On Error GoTo Error_Routine
Dim rst3 As New ADODB.Recordset
Dim NewDate As String
Dim NewJobNo As String
Set cn = CurrentProject.Connection
Dim i As Integer
If strOperantValue = "ALL" Then
rst3.Open "qry................", cn, adOpenKeyset, adLockOptimistic
XCount = Nz(DCount("[zOperant]", "qry................"), 0)
Else
rst3.Open "qry.............", cn, adOpenKeyset, adLockOptimistic
XCount = Nz(DCount("[zOperant]", "qry..............", "[zOperant] Like'" & (strOperantValue) & "'"), 0)
End If
With rst3
If XCount = 0 Then
GoTo Exit_Continue
Else
.Requery
strPriorOperant = ""
strCurrentOperant = ""
y = 0
.MoveFirst
Do While Not .EOF
DoCmd.Hourglass True
For i = 1 To XCount
zProgressBar i, XCount
strPriorOperant = strCurrentOperant
strCurrentOperant = .Fields("zOperant")
If strCurrentOperant Like strPriorOperant Then
y = y + 1
Else
y = 1
End If
NewDate = Format(.Fields("datCreated
UniqueID = CStr(y)
NewJobNo = CStr(NewDate & "-" & .Fields("zOperant") & "-" & Format(UniqueID, "00"))
.Fields("JobNo") = NewJobNo
.Update
If i = XCount Then Exit Do
.MoveNext
Next i
Loop
DoCmd.Hourglass False
DoEvents
.Requery
End If
rst3.Close
End With
Exit_Continue:
Set rst3 = Nothing
i = 0
XCount = 0
strOperantValue = Empty
strPriorOperant = Empty
strCurrentOperant = Empty
y = 0
strFrmX = Empty
Exit Function
Error_Routine:
MsgBox "Error# " & Err.Number & " " & Err.Description
Resume Exit_Continue
End Function
ASKER
Thanks for responding on this post.
My major question entitled "Renumbering (re-sequencing) Autonumber" can be summarized as follows:
User deletes or makes changes a record and MUST re-order (re-number) the remaining records in the group. Therefore, there must an action taken by the user before re-numbering or resequencing is needed.
I devoted time to present the problem in the body of my question and presented sample db and text document to assist experts who wants to assist with my major request.
I appreciated the code you posted but did not really address my major question. Therefore if you could assist with the major question it would be appreciated.
Regards
Bill