 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / May 2008Numbering
I have a list of work orders in a database that I would like to assign a priority number. This will be the order in which the work orders will be worked. As new work orders are created the priority for the work orders may be reassigned. If I have a list of work orders and they are numbered from 1 to 100, when the next work order comes in, I may assign that work order a priority of 10. I would like to have the rest of the work orders to automatically be reassigned priority numbers 11 through 101. Any ideas? >I have a list of work orders in a database that I would like to assign a >priority number. This will be the order in which the work orders will be [quoted text clipped - 3 lines] >priority of 10. I would like to have the rest of the work orders to >automatically be reassigned priority numbers 11 through 101. Execute an Update query like this in the AdterUpdate event of form text box (named txtpriority) bound to the priority field: UPDATE thetable SET priority = priority + 1 WHERE Priority >= txtpriority And . . . The ... in the where clause is whatever criteria you would use to isolate the work orders that are either completed or not ready to have a priority. If you also allow work orders to be deleted, then you would need an analagous update query to reduce priorities above the one being deleted.  SignatureMarsh MVP [MS Access] Marshall- Thank you for your response. I am a beginner Access user. I have never had to create a script before. I am using Access 2007. Do I place this in a Macro? Thank you for your help. > >I have a list of work orders in a database that I would like to assign a > >priority number. This will be the order in which the work orders will be [quoted text clipped - 20 lines] > need an analagous update query to reduce priorities above > the one being deleted. This exercise might be beyond your current skill level. The technique I outlined was intended to be done using VBA code in a form text box's AfterUpdate event procedure. I can not even begin to help with the code until you provide a lot more information about where/how you expect users to set/reset the priority value (including form, control, table and field names). I have yet to use A2007 beyond just poking around, so I don't know if the new macro features are sufficient to this task and I certainly could not help with it. SignatureMarsh MVP [MS Access] >Thank you for your response. I am a beginner Access user. I have never had >to create a script before. I am using Access 2007. Do I place this in a Macro? [quoted text clipped - 23 lines] >> need an analagous update query to reduce priorities above >> the one being deleted. I work for a help desk. The requests have become extremely backed up. We are attempting to clean up the requests by working on them according to a priority number assigned by a teammember. One person will be assigning a priority to each work order. Right now, I have a text box with a field called Priority in the IS_Reporting Table. I have also created a form IS_Reporting which will allow for easy input of data for the end user. As new tickets come in, some of the priorities previously assigned will be changed. I would like to have the remaining orders automatically renumbered when these changes are made. > This exercise might be beyond your current skill level. The > technique I outlined was intended to be done using VBA code [quoted text clipped - 34 lines] > >> need an analagous update query to reduce priorities above > >> the one being deleted. I understand your general goal about adding a new record and setting its priority You have to provide details about other possible interactions with your data such as: - adding a new record without specifying its priority - deleting an exiting record - just clearing an exiting record's priority - editing an existing priority You also failed to explain how you identify other records that should not have their priority adjusted (e.g. closed calls). SignatureMarsh MVP [MS Access] >I work for a help desk. The requests have become extremely backed up. We are >attempting to clean up the requests by working on them according to a [quoted text clipped - 48 lines] >> >> >priority of 10. I would like to have the rest of the work orders to >> >> >automatically be reassigned priority numbers 11 through 101. -adding a new record without specifying its priority. The work orders that have an open status are downloaded into an Excel spredsheet nightly. The new requests are submitted with no priority level assigned. I would just do an append query to grab the new records and add them to MS Access. Once the new records are retrieved that is when the priorities are assigned. -deleting an existing record once the status has changed to resolved or closed i would need to delete those records. currently i just have the filter set on the status field to show only the open orders. -just clearing an existing record's priority it was my hope that I would be able to clear the existing priority and update it with a new assigned priority. because i have no experience with Access programming then I assumed it would not present a problem. -how to identify other records that should not have their priority adjusted I have not reached this part of the process. I was assuming that I could start with just the records that had been reassigned. The records that had not been reassigned would not need to be changed. > I understand your general goal about adding a new record and > setting its priority [quoted text clipped - 60 lines] > >> >> >priority of 10. I would like to have the rest of the work orders to > >> >> >automatically be reassigned priority numbers 11 through 101. >-adding a new record without specifying its priority. >The work orders that have an open status are downloaded into an Excel [quoted text clipped - 17 lines] >start with just the records that had been reassigned. The records that had >not been reassigned would not need to be changed. Start with this in the priority text box's AfterUpdate event procedure: Dim db As Database Dim SQL As String Set db = CurrentDb() SQL = "UPDATE [IS_Reporting Table] " _ & "SET Priority = Priority + 1 " _ & "WHERE Priority >= " & Me.Priority _ & " And Status <> 'resolved' " _ & " And Status <> 'closed' " db.Execute SQL That should take care of the situation when you are setting a priority on a record that did not previously have a priority. If you will want to edit the priority on a record that already has a priority, it will be a different situation, but I'm not sure if you said you needed to do this. SignatureMarsh MVP [MS Access] What you have outlined would assign a new priority to the new records. Thank you! However, it would not take of the records that currently have priorities assigned. If the currents records are numbered 1 to 10, I may want to assign the new record a priority of 1. I would need the current records to be renumbered 2 to 11. > >-adding a new record without specifying its priority. > >The work orders that have an open status are downloaded into an Excel [quoted text clipped - 41 lines] > already has a priority, it will be a different situation, > but I'm not sure if you said you needed to do this. >What you have outlined would assign a new priority to the new records. Thank >you! [quoted text clipped - 3 lines] >the new record a priority of 1. I would need the current records to be >renumbered 2 to 11. The code I posted is supposed to renumber all the existing records that have priority >= the new record's priority. Did you try it? If you did, what, exactly, was wrong with the result? SignatureMarsh MVP [MS Access] Marshall- I'm Back. I tried this query again today and I noticed something that I didn't notice before. If I have list numbered from 1 to 5 and I want to reassign priority 5 to number 2, the result that I am getting is the list is being numbered 1,2,4,5,6. If I run the query again, the result I get is 1,2,5,6,7. So I am getting gaps in the numbering sequence. I have tried for loops and counter variables but I am not getting the desired results. Thanks! > >What you have outlined would assign a new priority to the new records. Thank > >you! [quoted text clipped - 9 lines] > Did you try it? If you did, what, exactly, was wrong with > the result? >I'm Back. I tried this query again today and I noticed something that I >didn't notice before. [quoted text clipped - 4 lines] >getting gaps in the numbering sequence. I have tried for loops and counter >variables but I am not getting the desired results. That's what I meant when I asked if you would be reassigning an existing priority. Here's an aircode version that will do that too: Dim db As Database Dim SQL As String Dim SetCond As String Set db = CurrentDb() If Not ((IsNull(Me.Priority.OldValue) _ And IsNull(Me.Priority)) _ Or (Me.Priority.OldValue = Me.Priority)) Then If IsNull(Me.Priority.OldValue) Then SetCond = "SET Priority = Priority + 1 " _ & "WHERE Priority >= " & Me.Priority ElseIf IsNull(Me.Priority) Then SetCond = "SET Priority = Priority - 1 " _ & "WHERE Priority > " & Me.Priority.OldValue Else If Me.Priority.OldValue < Me.Priority Then SetCond = "SET Priority = Priority - 1 " _ & "WHERE Priority > " & Me.Priority.OldValue _ & " And Priority <= " & Me.Priority ElseIf Me.Priority.OldValue > Me.Priority Then SetCond = "SET Priority = Priority + 1 " _ & "WHERE Priority < " & Me.Priority.OldValue _ & " And Priority >= " & Me.Priority End If End If SQL = "UPDATE [IS_Reporting Table] " _ & SetCond _ & " And Status <> 'resolved' And Status <> 'closed'" db.Execute SQL End If SignatureMarsh MVP [MS Access] Thanks. This works fine for the existing records. Now I am getting duplicate priority numbers when a new record is added. I am importing the new data data from a table. These new records are appended with no assigned priority. A form is used to assign priorities to the new work orders and this is where I am using the query that you have outlined below. > >I'm Back. I tried this query again today and I noticed something that I > >didn't notice before. [quoted text clipped - 39 lines] > db.Execute SQL > End If Marshall you are awesome! This works perfect. There was a pilot error before. I have never coded in VBA before this assignment. This was a great exercise and I learned how powerful Access and VBA are. THANKS SO MUCH!! > >-adding a new record without specifying its priority. > >The work orders that have an open status are downloaded into an Excel [quoted text clipped - 41 lines] > already has a priority, it will be a different situation, > but I'm not sure if you said you needed to do this.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.