Automatically re-number…

[Glohamar]

Hi all,

I am getting ready to design a database to store EC Numbers that need to be worked, along with assigning a priority to that specific EC Number. In trying to make this as user friendly as possible, I need to be able to change the priority of an EC Number, and have the database re-number the priority of the other EC Number’s below the newly assigned priority number automatically.

So let’s say I have an ECNum1 that has a priority of 1, ECNum2 has priority of 2, and ECNum3 has Priority of 3. My boss finds out that ECNum3 needs to be done first. So he goes in that database to change the priority of ECNum3 to 1. How will I re-number the other two ECNum’s where ECNum1 priority is 2 and ECNum2 is priority 3?

Right now I see the database containing approximately 200 EC Numbers.

I want to make sure I have everything planned in my mind and on paper before I start creating the database.

Any help is appreciated.

Dave

 

[rdodge]

I also will eventually have a similar type issue with regards to my Master Daily Schedule portion of the MRP program that I am in the process of developing. Bear with me as I don't use bound forms.

The table is a routing table with the list of different jobs. Fields involved are the following:

Routing ID
Conversion Center ID
Work Order ID
Step ID (One work order may have multiple conversions involved)
Requested Quantity
Sequence

The table does have other fields in it, but these are the ones that will be impacted during the scheduling process primarily. I have also created my own Drag and Drop type feature of a listbox so as the user can drag and drop the different conversion tasks as needed within the routing form.

Once the user has made the adjustments, the user clicks on the OK button of the form, which then the code behind the form does the following tasks:

Checks to be sure no task has been cancelled/completed else those tasks are removed from the list.

For all tasks that passed the first test, the SEOUENCE field gets changed according to the new order. However, there may have been new tasks added to the conversion center so we also have to check for this and assign those new tasks to the end of the sequence order as the default. Here's a copy of the code that I have created:

Private Sub cmdOK_Click()
Dim drsDLU As DAO.Recordset, strSLT As String, X As Long, CCN As Long, I As Long, Z As Long, K As Long, J As Long
strSLT = "tblPRDRTG.fldRID, tblPRDRTG.fldRQQ, tblPRDRTG.fldSEQ"
CCN = CLng(cbxCVN.Column(2, cbxCVN.ListIndex))
Set drsDLU = modDB.ddbPD.OpenRecordset("SELECT " & strSLT & " FROM tblPRDRTG WHERE tblPRDRTG.fldCCN = " & CCN & _
" And tblPRDRTG.fldSEQ > -1 ORDER BY tblPRDRTG.fldSEQ;", dbOpenDynaset, dbSeeChanges, dbPessimistic)
X = Me.lbxDLU.ListCount - 1
If Me.lbxDLU.ListCount > 0 Then
For I = 0 To X Step 1
drsDLU.FindFirst ("tblPRDRTG.fldRID = " & Me.lbxDLU.Column(0, I))
If drsDLU.NoMatch = True Then
Me.lbxDLU.RemoveItem (I)
I = I - 1
X = X - 1
End If
Next I
drsDLU.MoveFirst
Z = X + 2
Do While Not drsDLU.EOF
K = 0
For J = 0 To X Step 1
If CLng(Me.lbxDLU.Column(0, J)) = drsDLU.Fields("fldRID&quot.Value Then
drsDLU.Edit
drsDLU.Fields("fldSEQ&quot.Value = J
drsDLU.Fields("fldRQQ&quot.Value = CDbl(Me.lbxDLU.Column(3, J))
drsDLU.Update
K = 1
Exit For
End If
Next J
If K = 0 Then
drsDLU.Edit
drsDLU.Fields("fldSEQ&quot.Value = Z
Z = Z + 1
drsDLU.Update
End If
drsDLU.MoveNext
Loop
End If
Me.cbxCVN.Value = ""
Me.cbxCVN.SetFocus
Me.lbxDLU.RowSource = ""
End Sub

I realize some of the coding is still codish like, this code however has proven to work for me. Some of the variables are on the Form's global level.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Glohamar]

Hi rdodge,

Thanks for yor input. You say that you will eventually have a similar type of issue, is your code not already changing the sequence of which Work Order will be worked first? Also, would I use something similar for changing dates? Each one of my ECNum has a date due that will change based on the priority.

Dave

 

[rdodge]

I mentioned evetually cause my program is not fully developed. However, it's something I had to plan for given the nature of an MRP system in a Production and Inventory environment, thus what had me create the above code. What I don't have programmed yet, eventually, my program will do the scheduling automatically, but will still need the above code to allow the scheduler to override the computer system should we need to have that done.

With my program, I am in the process of attempting to create a pull type system rather than a push type system. Let the customer demand what they need, and we produced from that. With this in mind, our scheduling would be based on "Requested Date" that the customer gives us.

If you would like to learn more about Production and Inventory Management, you can ask your boss about APICS. APICS has 5 courses on the subject matter, of which I went through all 5 of them. Granted, the courses doesn't teach how to implement them into a DB environment, but it does cover a lot of things related to production and inventory management in ways to cut cost and at the same time, can increase employee moral.

For the industry that my company is in, there are some things that would be nice to implement, but given the nature of the business, we can not, such as we are very much like a job shop type environment due the major flexibility we have to allow for with our finished products, but on the other hand, we can still find ways to standardize some of our processes and find places to reduce costs in other areas. Given the huge flexibility we have to allow for with finished products, that's one valid reason why to have a pull type system rather than a push type system.

The "Request Date" that I mentioned about goes on the Work Order header, thus there's a separate form for that. However, if you like to have an estimated start date/time, you can have that included within the routing table just as the Sequence number is within the routing table. Using either one would work, though the setup of the form would be different, cause if you use estimated start date/time, you about have to create a sub form (continuous) within the main form for the estimated start date/time, as opposed to a drag and drop type listbox for the sequence number.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Glohamar]

Hi rdodge,

Sounds like we are in similar working enviorments. I appreciate the code.

Thanks,
Dave