Updating and resequencing display of records based on user input

[TESA0_4]

Hi. I'm a little bit past newbie status, but only just!
I'm developing a Risk Assessment database. The user has to break 'processes'
into a series of discrete 'tasks'. Each 'task' has to be Risk Assessed. I
have a table that records header information about the 'process' and another
table to record information about the 'tasks'. This information is presented
in a form with subform. Each 'task' record is autonumbered.
In addition the user gives each 'task' a task number eg task 1, task 2, task
3 etc that represents the sequence of each 'task' in the 'process'. However,
as the user develops the task list they might decide a new task needs to be
inserted between tasks 2 and 3 or that the task sequencing needs to be
changed from what they have entered.
What I have thought to do is prompt users to enter 'task' numbers in steps
of 10 ie 10, 20, 30 etc so that they can add an extra task with an in-between
number and resort the display of the records.
When the user is happy, I'd like to provide a Command Button that will
automatically run through the 'task' records according to the user defined
sequence giving the 'tasks' sequential numbers 1, 2, 3... etc.
Advice on the coding for the Command Button would be appreciated.

 

[John Spencer]

First I would use a number type Double is good that allows decimal
fractions. That would make it easier to insert steps

Then entry could proceed something like the following.
1.
2
3
oops forgot two steps between 1 and 2
1.3
1.4
Darn forgot a step between those 2
1.35

I would use a query to update the numbering when needed to whole numbers.
That might look something like the following
Assumptions: You have a process identifier - ProcessID- that is a number
field. If text the DCount statement would need modification

Your button code might look something like the following

Dim strSQL as String
StrSQL = "UPDATE YourTaskTable" & _
" SET TaskOrderNumber = " & _
"DCount(""*"",""YourTaskTable"","ProcessID="" & ProcessID & "" AND
TaskOrderNumber<="" & TaskOrderNumber)" & _
"WHERE ProcessID =" & Me.ControlWithProcessIDentifier
If Me.Dirty = True then Me.Dirty = False

Currentdb().execute strSQL, DbFailOnError

Me.Requery

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[TESA0_4]

After a bit more work reading threads and copying code suggestions I have
achieved the result I wanted. Goodness knows if the code is sound, but it
seems to be working!

Public Function Renumber() As Integer
Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Dim I As Double
I = 0
Set MyDb = CurrentDb
'Open the table
Set MyRec = MyDb.OpenRecordset("select [TaskNo] from qryTaskList")
While Not MyRec.EOF
MyRec.Edit
I = I + 1
'Add a number
MyRec!TaskNo = I
MyRec.Update
MyRec.MoveNext
Wend
End Function

The Sub that calls the function includes:
Me.Refresh
Renumber
Me.Requery

 

[TESA0_4]

Hi. Thanks John. My crude understanding of code enables me to understanding
your suggested solution. Your post appearing a few minutes after my second
post on this thread! I'll have play with your code and decide which solution
to run with.
Your assistance is much appreciated.
Terry
(Sydney Australia)