Change order of existing items 2

[ptuck]

Okay GURU's I need your help again.

I have a form with a subform that holds steps to take to complete a task. At times we need to add a step in the middle of an existing task. What I would like to do is be able to click a button (add new step) that would allow me to add the step with the correct step number and then reorder the existing task. For example, I have "x" task that has 5 steps in it. We realize after trial and error that we should have one more step between steps 3 and 4. We would click the add new step button enter the step to take and then click save. This would take the new step entered (which is now step 4) and make step 4 step 5 and step 5 step 6. Is this possible?? I was thinking something along the lines of when the user clicks the add new step button it could dump the steps to a temp table and then do the massaging of the order. Please let me know what the GURU's think.

Thanks for the help,
Paul

 

[KenReay]

Hi

To begin, a simple answer that requires no changes to your application. Why not number the steps 10, 20, 30...etc instead of 1, 2, 3...etc, then you can insert up 9 steps at any point.

If you really want to renumber the steps, then provided the step number is not a foreign key in any other table, in principle it should not be too difficult. Use of a temporary table as you suggest is wise. If the Step number is a foreign key in another table(s), you may wish to look up cascade update.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ptuck]

After the data is in the temp table, how would I go about renumbering the steps??? I will have two steps with the same number, but will need to change one of them to a higher number and then all the steps after them.

 

[KenReay]

Hi

So you are not going to use my steps of 10 solution..

What I would do is:

For each step, have a unique id (say an autonumber) to uniquely identify each step, and act as PrimaryKey. Sthe Step Number can then become simply a sequence number which you can allow the user to amend. In this way you can not only insert steps, but completely resequence them as the user wishes. If having unique step numbers (in addition to a unique id) is vital to your design, simply run a check for duplicates query before the user is allowed to close the form in which the amendment is done, and cancel the close if duplicates are found.

There is a wizard to guide you through making a check for duplicates query.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ptuck]

Thanks for the advice..Unfortunately, we must have the correct step number for each step. This is used for training and we can use the steps of 10. I liked the idea, but the training folks wants the exact step number.

 

[KenReay]

Hi

OK I can live with rejection, what about the rest of the post, do you follow it?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ptuck]

I think so. I can dump the current list of steps into a temp table and assign each a unique id. The user can then add their new step with the number. I can grab the new step unique id and then everything above that number add one to it. It will take a little bit of head scratching, but I think I can get there (with the help of this forum).

I do have another problem that is common in Access. Sorting numbers in a text field. The numbers are 1.xxx or 2.xxx or 10.xxx or 20.xxx, you get the idea. Of course, Access sorts anything that starts with a 1 first then it goes to the two's which puts 10.xx before 2.xxx. Any ideas on how to correct this???

 

[KenReay]

Hi

Yes, write a user function to pad teh numbers with leaving zeros, just about to leave teh office, so if you bear with me I will give more info later if you need it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ptuck]

That sounds great...I will be on the look out for more information from you...

Thanks for all your help...

 

[TheAceMan1]

How are ya ptuck . . . . .

Already worked up some code for ya. Need a few things for fill in.

Post the names of the following controls in the subform, along with [blue]data type[/blue] (string/numeric):

PrimaryKey
Step (the one you use to enumerate the steps)

See Ya! . . . . . .

 

[TheAceMan1]

ptuck . . . . .

1.xxx, 10.xxx . . . .

.xxx are numbers?

Could there ever be post appended characters like"

.xxxA or.xxxB ?

See Ya! . . . . . .

 

[ptuck]

The primary key for the sub form is as follows:

All three fields make up the primary key
TaskNo - text/string (this is a hidden field)
StepID - number
Classification - text/string (this is a hidden field)

1.xxx and 10.xxx... the x's are numbers and there are a few occassions where there are post append characters..
1.xxxa, 1.xxxb

Thanks for the help.

 

[TheAceMan1]

Hmmmmm . . . . .

[blue]I do have another problem that is common in Access. [purple]Sorting numbers in a text field[/purple]. The numbers are 1.xxx or 2.xxx or 10.xxx or 20.xxx, you get the idea.[/blue]

Surely the above field is not [blue]StepID[/blue] which is numeric. What is the fields name?

[purple]Extremely important: Is [blue]StepID[/blue] a uniquely generated number?[/purple]

See Ya! . . . . . .

 

[ptuck]

TaskNo is the field that holds the 1.xxx. This is a unique number for each classification. In other words, TaskNo will never be duplicated for the Programmer classification. However, the same TaskNo could be used for the Technician classification. That is why it takes the TaskNo and Classification to make the Primary Key in the main table.

In the steps table the primary key is TaskNo, Classification and StepID. The StepID (which is the one I am trying to renumber when a new step is required to complete the task) always starts at 1 and goes up in increments of 1. A StepID can never be duplicated within the same task number and classification in the Steps table. This is why it takes TaskNo, Classification and StepID to makeup the primary key.

Example:
TaskNo Classification StepID Step
10.001 Programmer 1 Start
10.001 Programmer 2 ...
10.001 Programmer 3 ...
10.001 Programmer 4 ...

Different Classification
10.001 Technician 1 Start
10.001 Technician 2 ...
10.001 Technician 3 ...
10.001 Technician 4 ...

If a step was required before step 4 then I would enter new step with a StepID of 4 and then manually change the existing 4 to a 5. Not a problem if I only have to change one, but what if I had to change 30?

The number sorting problem is completely separate from the stepid problem. The number sorting involves a dropdown with all the TaskNo (1.001, 10.001, 2.001). Hope I did not confuse you and hopefully this will make better sense.

Thanks for all the help,
Paul

 

[ptuck]

One more note...the main table is the main form and the steps table is the subform.

Thanks again for the help.

 

[TheAceMan1]

OK ptuck . . . . .

No . . . no confusion, just need to understand to a certain degree what your doing.

So . . . . if I read you correctly, then according to the example you gave above, when your looking at your subform, your seeing [blue]Classification/Programmer[/blue] or [blue]Classification/Technician[/blue] or some other [blue]Classification/?[/blue] with appropriate steps. Is this correct?

I'm also assuming the subform [blue]RecordSource[/blue] is query or SQL, sorted on [blue]StepID[/blue]. Is this correct?
Gonna need query/SQL as Requery is required, no matter what.

I'm gonna have to modify the code as its dependant on a single unique PK. As you have it, only [blue]StepID[/blue] is unique per classification in the subform. Already though of another schema I can use. May take a couple of evenings depending on the time I can put it.

Have two routines. One allows you to step a record forward/back thru the StepID. The other, takes an ordinal positon from an input box and performs a jump and reserializes accordingly. Both routines operate from any selected record, not just a new entry.

See Ya! . . . . . .

 

[KenReay]

Hi

The problem of renumbering/resequencing the steps. It is almost certain that you are going to get all kinds of combinations of inserting new Steps and re-ordering existing Steps. Why not just leave it to the user to sort this out, they know what they want after all?, you could do the following:

Copy the existing Steps into a temporary table. The temporary table has the same format as the 'live' table, but also has an additional column NewStep. When you copy in the existing information, populate NewStep with the value from the existing StepNumber. Do not have any indexes on the temporary table. Display the temporary table in a form so the user can delete Steps, Add Steps, and renumber existing steps. DO NOT show the user the original Step number column OR allow them to change it. Have an Update or Save button which when clicked does the following:

checks that each Step has a unique 'New' Step Number (a simple 'find duplicates' query will do this, wizard will guide you through making such a query. If you really want to you could step through the records ensuring they are also numbered N, N+1, N+2 etc. Once you are happy all Steps are uniquely numbered according to the users wishes, delete the existing 'live' lines, and copy the content of the temporary table to the live table. If your referential integrity settings on the live table preclude you from simply deleting the live data, you could instead build an set of queries to update the live table from the temporary table using the 'original' values for step number which you have carefully preserved to match up the two sets of data, so you would need an insert query, an update query and a delete query.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

To sort your n.nna type 'numbers', place the function below in a module

In the Query on which report / form is based add a calculated column so

SortNo:SortableColumn([YourColumnName],5,5)

eg

SortNo:SortableColumn([TaskId],5,5)

Note the code below is an example of how to do it, it needs error trapping etc

In summary what it does is to split your column into three "zones' and pad and align the expected Numeric zones so they are left aligned as you would expect for numbers

Public Function SortableColumn(strIn As String, L As Integer, R As Integer)
' strIn is the String to be Sorted eg 12.35a
' L is the maximum length of the part before the dot
' R is the maximumn length of the Numeric part after the dot
' Usage ? SortableColumn("1.2a",3,4)
' 001.00002a
Dim i As Integer
Dim str As String
Dim strLeft As String
Dim strRightN As String
Dim strRightA As String
'
i = InStr(1, strIn, ".")
If i = 0 Then
SortableColumn = Right(String(L, "0") & Trim(strIn), L) & "." & String(R, "0")
Else
strLeft = Right(String(L, "0") & Trim(Left(strIn, i)), L + 1)
strRightN = ""
strRightA = ""
str = Trim(Mid(strIn, i + 1))
For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
strRightN = strRightN & Mid(str, i, 1)
Else
strRightA = strRightA & Mid(str, i, 1)
End If
Next i
SortableColumn = strLeft & Right(String(R, "0"), R) & strRightN & strRightA
End If

End Function


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TheAceMan1]

OK ptuck . . . . .

Code Dependancies:
1) SubForm [blue]RecordSource[/blue] is Query or SQL [blue]sorted[/blue] on StepID.
2) StepID is [blue]Numeric[/blue] data type.

Set the subForms [blue]Key Preview[/blue] event to [purple]Yes[/purple].

Put the following in the subForms code module:

[blue]Public Sub movRecOnce(Inc As Boolean)
   [green]'Inc = True = Up[/green]
   [green]'Inc = False = Down[/green]
   
   If Not Me.NewRecord Then
      Dim rst As DAO.Recordset, srcStep As Long, Offset As Integer
      
      Set rst = Me.RecordsetClone
      
      rst.FindFirst "StepID = " & Me!StepID
      srcStep = Me!StepID
      
      If Inc Then
         Offset = -1
         rst.MovePrevious
      Else
          Offset = 1
         rst.MoveNext
      End If
      
      If Not rst.BOF And Not rst.EOF Then
         Application.Echo False
            rst.Edit
            rst!StepID = srcStep
            rst.Update
            
            If Inc Then
               rst.MoveNext
            Else
               rst.MovePrevious
            End If
            
            rst.Edit
            rst!StepID = srcStep + Offset
            rst.Update
            
            Me.Requery
            Set rst = Me.RecordsetClone
            rst.FindFirst "StepID = " & srcStep + Offset
            Me.Bookmark = rst.Bookmark
         Application.Echo True
      End If
      
      Set rst = Nothing
   End If
   
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
   Dim AltDown As Integer
   AltDown = (Shift And acAltMask) > 0
   
   If AltDown And KeyCode = 38 Then [green]'Alt + Up[/green]
      Call movRecOnce(True)
   ElseIf AltDown And KeyCode = 40 Then [green]'Alt + Down[/green]
      Call movRecOnce(False)
   ElseIf AltDown And KeyCode = 74 Then [green]'Alt + J[/green]
      Call movRecJump(10)
   ElseIf AltDown And KeyCode = 83 Then [green]'Alt + S[/green]
      Call Serialize
   End If

End Sub[/blue]

In the code above, the [blue]Application.Echo False[/blue] method, prevents repainting of the display screen. While its false, if for any reason code execution is interrupted (power glitches . . . ect), it will appear as if the system has locked-up. The following sets up a hotkey [purple]Ctlr + Shift + E[/purple] to turn Echo back on. I don't expect you'll ever have to use it, but . . . . you never know. You should write the hotkey down somewhere.

Open a new macro in the Macros Window. Enter the following:

[blue]Macro Name   Action     Function Name
----------  ---------  ---------------
^+e         RunCode    RestoreEchoHG()
            StopMacro[/blue]

Close the macro and be sure to save it as [purple]Autokeys[/purple].

In a module in the module window, add the following (this is the Restore Echo function):

[blue]Public Function RestoreEchoHG()
   Dim Msg As String, Style As Integer
   Dim Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   DoCmd.Hourglass False
   Application.Echo True
   
   Msg = "Application Echo is On!" & DL _
       & "HourGlass is Off!"
   Style = vbInformation + vbExclamation + vbOKOnly
   Title = "Echo & HourGlass Defaults Set!"
   MsgBox Msg, Style, Title

End Function[/blue]

Close the module and test the Echo Restore hotkey. A form should pop-up stating defaults are set.

[blue]New Interface Operations:[/blue]
You have two hotkeys [blue]Alt + UpArrow [green]'Move selected record up[/green]
Alt + DownArrow [green]'Move selected record down[/green][/blue]

Select any existing record and use the hotkeys to move the record to any ordinal position.

Adding new record: Add new record with next highest stepID. Then use the hotkeys.

See Ya! . . . . . .

 

[ptuck]

Thanks AceMan...I will give this a shot tomorrow morning, but I will probably have a few questions. One that jumps out at me, is the user will still need to manually change the StepID (which is the exact number of the step 1,2,3). However, they can use hot keys to place them in the correct order?? I can't thank you enough for your help and I will let you know how it works tomorrow.

Paul