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

 

[TheAceMan1]

ptuck . . . .

[purple]Dont forget to backup the DB before making any changes![/purple]

[blue]is the user will still need to manually change the StepID (which is the exact number of the step 1,2,3).[/blue]

No . . . . They are place in the correct order with the right number, per your request. Example:

[blue]StepID  Step
------  ----
   1     AB
   2     CD
   3     EF

Moving StepID 3 Up Becomes

StepID  Step
------  ----
   1     AB
   2     EF
   3     CD[/blue]

I have two more routines, but need to know everything here works for you.

See Ya! . . . . . .

 

[ptuck]

You are the best...I am about to give it a shot now. Will let you know how it works.

 

[ptuck]

I am getting the following error message.
user-defined type not defined and it is highlighting
rst AS DAO.Recordset

Don't understand this, but I would prefer to use ADO if possible. I changed the recordset to ADO and then I get other error messages. Any thoughts??

Also, the autohotkeys macro is not working. I put the function in its own module, so does it matter what I name the module??

Thanks again for your help..

 

[TheAceMan1]

OK ptuck . . . . .

It can be converted to ADO (currently taking classes), but for now gotta use DAO. In any code module select Tools - Reference. Put a check next to [purple]Microsoft DAO 3.6 Object Library[/purple]. Push it up as high in priority as it will go.

[blue]Also, the autohotkeys macro is not working. I put the function in its own module, so does it matter what I name the module??[/blue]

No . . . it does'nt matter what you call the nodule. I assume your talking about the [blue]Function RestoreEchoHG()[/blue] here.

The problem above has to be in the calling macro.
Make sure when you go to the macro window the macro is named [purple]AutoKeys[/purple].
Next, open the macro in design view and check your spelling.

Post back what you have in the macro if it still does'nt work. I'd rather you get [blue]RestoreEchoHG()[/blue] function, working first.

See Ya! . . . . . .

 

[ptuck]

The macro still does not work. I have enter everything you have above for the macro with one exception. I was not sure what you meant by this below.

Macro Name
----------
^+e

I have the RunCode set to the correct Function and the Maro named AutoKeys. It is telling me now that it cannot find the function. I can use the build button to select the function from the user defined functions, but I still get the same error message.

Also, when I add a new step id with the next number and then try to move it, I get and error stating it can't perform the action because it would create duplicate records.

Sorry to be such trouble, but I really appreciate your help.

 

[TheAceMan1]

OK ptuck . . . .

[blue]^+e[/blue] is the hotkey for restoring echo.
^ Ctrl Key
+ Shift Key
e e key

Apparently its trying to run. The function [blue]RestoreEchoHG()[/blue] has to be in a module in the module window, not in a form module, and the [blue]RunCode[/blue] function call should be:

[blue]RestoreEchoHG()[/blue]
not
[blue][purple]=[/purple]RestoreEchoHG()[/blue]
Post back exactly what you have in the runcode function call. Also what version Access?

[blue]error stating it can't perform the action because it would create duplicate records.[/blue]

This is an error on my part. I design with [blue]Indexed - Duplicates Allowed[/blue] and temporarily duplicates are made. So I'm gonna have to modify for [blue]Duplicates Not Allowed[/blue]. Sorry about that.

See Ya! . . . . . .

 

[ptuck]

I did have the function in a module by itself. However, the problem was that when I copied the name of the function to use as the name of the module, I forgot to delete the HG off. The module and function had the same name, which will not work. Thanks for your help and I will wait to see the updated code for the StepID's. I am getting excited about this working. Thanks again for all your help.

 

[TheAceMan1]

OK ptuck . . . . .

Here's the mod. Replace the old.

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

Give it a whirl and let me know. Converting the other two routines, Jump & Serialize.

See Ya! . . . . . .

 

[ptuck]

You are absolutely my hero.... This worked like a champ..

I still would like to convert it to ADO, but I may be able to do this.

Do you have a suggestion about the sorting problem?

 

[ptuck]

Fogot to mention..I gave you three well deserved stars...

 

[ptuck]

It did not take the three stars. I have ran into a little problem. What if we need to delete a step? I tried this, but it did not renumber. Also, if there is ever an error it locks up access and the autokeys macro does not unfreeze it. You have done enough, but if you have some suggestion I would be most appreciative.

Thanks,
Paul

 

[TheAceMan1]

Good to go ptuck . . . . .

I just finished fully testing and found one flaw (I check for top and bottom to late). If you select StepID = 1 at the top and attempt to move it up, the routine aborts after the StepID is changed to -1. This is what you'll see. So one last mod to take care of that.

[blue]Public Sub movRecOnce(Inc As Boolean)
   [green]'Inc = True = Up
   'Inc = False = Down[/green]
   
   If Not Me.NewRecord Then [green]'Skip nw record.[/green]
      Dim rst As DAO.Recordset
      Dim hldSrc As Long, hldDes As Long
      
      Set rst = Me.RecordsetClone
      rst.FindFirst "StepID = " & Me!StepID
      
      Application.Echo False
         If Inc Then[green][b] 'Move to check for BOF/EOF.[/b][/green]
            rst.MovePrevious
         Else
            rst.MoveNext
         End If
         
         If Not rst.BOF And Not rst.EOF Then 
            If Inc Then [green]'Move back to start position.[/green]
               rst.MoveNext
            Else
               rst.MovePrevious
            End If
            
            hldSrc = Me!StepID
            rst.Edit
            rst!StepID = -1 [green]'Prevent duplicate record.[/green]
            rst.Update
            
            If Inc Then
               rst.MovePrevious
            Else
               rst.MoveNext
            End If
         
            hldDes = rst!StepID
            rst.Edit
            rst!StepID = hldSrc
            rst.Update
            
            If Inc Then
               rst.MoveNext
            Else
               rst.MovePrevious
            End If
            
            rst.Edit
            rst!StepID = hldDes
            rst.Update
            
            Me.Requery
            Set rst = Me.RecordsetClone
            rst.FindFirst "StepID = " & hldDes
            Me.Bookmark = rst.Bookmark
         End If
      Application.Echo True
      
      Set rst = Nothing
   End If
   
End Sub[/blue]

As soon as I finish the other two routines, I'll pass them on. Not sure if I'll finish tonight.

See Ya! . . . . . .

 

[TheAceMan1]

[blue]What if we need to delete a step?[/blue]

No . . . . the routine does'nt serialize, thats one I'm trying to finish. When I do you'll be able to stick a call to it the [blue]AfterDeleteConfirm[/blue] event of the form and have that automated.

[blue]Also, if there is ever an error it locks up access and the autokeys macro does not unfreeze it.[/blue]

Eariler on you said:

[blue]However, the problem was that when I copied the name of the function to use as the name of the module, I forgot to delete the HG off. The module and function had the same name, which will not work..[/blue]

This is indicating you got it working. Hve you tried the right HoKeys: [purple]Ctrl + Shift + E[/purple]?

See Ya! . . . . . .

 

[TheAceMan1]

ptuck . . . . .

Finished the [blue]Serialize Routine[/blue]. You can call it whenever to need to re-serialize. The HotKey is:
[purple]Alt + S[/purple]

Put the following in the subForms code module:

[blue]Public Sub Serialize()
   Dim rst As DAO.Recordset, n As Long, PosMax As Long
   
   Set rst = Me.RecordsetClone
   PosMax = MaxPos() + 1
   n = 1
   
   Application.Echo False
      Do
         rst.Edit
         rst!StepID = rst!StepID + PosMax
         rst.Update
         rst.MoveNext
      Loop Until rst.EOF
      
      Me.Requery
      Set rst = Me.RecordsetClone
      rst.MoveFirst
      
      Do
         rst.Edit
         rst!StepID = n
         rst.Update
         n = n + 1
         rst.MoveNext
      Loop Until rst.EOF
      
      Me.Requery
   Application.Echo True
   
   Set rst = Nothing

End Sub[/blue]

To solve your deletion problem, add the following to the subForms code module:

[blue]Private Sub Form_AfterDelConfirm(Status As Integer)
   
   If Status = acDeleteOK Then
      Call Serialize
   End If

End Sub[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

ptuck . . . . .

You also need the following code for Serialize routine to work. Put it in the subform code module:

[blue]Public Function MaxPos() As Long
   Dim rst As DAO.Recordset
   
   Set rst = Me.RecordsetClone
   rst.MoveLast
   MaxPos = rst.RecordCount
End Function[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

ptuck . . . . .

Found problem with Serialize routine (after fresh coffee). Replace with the following:

[blue]Public Sub Serialize()
   Dim rst As DAO.Recordset, n As Long
   
   Set rst = Me.RecordsetClone
   rst.MoveLast
   n = -1
   
   Application.Echo False
      Do
         rst.Edit
         rst!StepID = n
         rst.Update
         rst.MovePrevious
         n = n - 1
      Loop Until rst.BOF
      
      Me.Requery
      Set rst = Me.RecordsetClone
      rst.MoveFirst
      n = 1
      
      Do
         rst.Edit
         rst!StepID = n
         rst.Update
         n = n + 1
         rst.MoveNext
      Loop Until rst.EOF
      
      Me.Requery
   Application.Echo True
   
   Set rst = Nothing

End Sub[/blue]

See Ya! . . . . . .

 

[KenReay]

Hi

You asked again about the Sort problem, I did post a solution, did you try it?

I repeat it here, jut in case you missed it

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

 

[ptuck]

I can't thank you guys enough.

TheAceMan1, (how do you post in the nifty little text box??)you asked/stated this about the program locking up when error occurs in the move routine...

"This is indicating you got it working. Hve you tried the right HoKeys: Ctrl + Shift + E?"

I did get the macro working and the Ctr + Shift + E works when it is not locked up, but when it locks up Access will not respond to anything. I can't exit the form or do anything except hit the "X" button of Access. So I don't have to end the process, but my DB inside Access is locked up not actually Access (hope that makes sense).

KenReay, I did try your function, but I think I did the same thing about I named the module the same name as the function. I will try it again and let you know.

Thanks again guys and I will post back in a couple of hours the results.

Thanks,
Paul

 

[TheAceMan1]

[blue](how do you post in the nifty little text box??)[/blue]

Using [blue]TGML's[/blue] [blue][ignore][/ignore][/blue] & [purple][ignore]

[/ignore][/purple]. Preview a post when your posting & scroll to the bottom. Look for Click [blue]Here[/blue], or see below.

http://www.tipmaster.com/includes/tgmlinfo.cfm?w=450&h=450

Hmmmmmm what version Access?

See Ya! . . . . . .

 

[ptuck]

Access 2002...about to try the new updates