renumbering a unique value'd field

[Ethereal]

Hi, I was wondering if there is a way to renumber a series of records in a field that uniquely identify's the record. Say my field was PROBLEMfield with records containing values
1, 2, 3 , 4, 5
these values represent the order in which something is implemented. So if the user wants to implement a new "step 2"
then it would go in like this
1,2,3,4,5,2
and after renumbering would be like this
1,2(new),3(old2),4(old3),5(old4),6(old5)

Please help !

 

[Bat17]

I think that I would use dCount in the beforeupdate event of the control to check for duplicates then run code to renumber the other records - counting down to the new record.
Can this control only be altered by adding a new record?
what would happen if they went in and changed an existing number?

Peter

 

[Ethereal]

as of now I am only letting them add/remove the "steps" and it would renumber accordingly in both these cases to represent a sequential system. On your "run code to renumber the other records" I am not good at working with recordsets, and need somebody to point me in the right direction.

 

[richary]

Do you actually need to store the step numbers? Really what you want (I suggest) is to store the order and then display them as step 1, step 2 etc.

How about storing teh step numbers as floating point numbers so that a new step 2 would be, say, 1.5. The step numbers (as stored in the db) would look like

1, 1.5, 2, 3...

lets call the field [newstepnumber]

To display on a report the step numbers as "step 1", "step 2" etc., use the Dcount function to count the number of records less than the current value+1. So the field's source on the report would be something like

="Step " & Dcount("myTable","[newstepnumber]","newstepnumber < " &  me.newstepnumber)+1

 

[Ethereal]

I would like to avoid using a float value because of its use elsewhere in my application

 

[Ethereal]

BUMP.... Please help!

 

[Bat17]

I think this code will do what you need, but I have had to make a lot of assumptions!

tblSteps = Table containing the data to change
ID = unique record identifier - number
Group = the field you are grouping on - number
Step - the field with the step - number
TimeStamp = a date time field

Time stamp is used to break draws in the step list and always obeys the last order. This field will need to be updated whenevewr the value of step is added/changed in a record.

Sub CleanSteps()
Dim strSql As String
Dim strSqlUpdate As String
Dim rst As DAO.Recordset
Dim lngGroup As Long
Dim lngStep As Long

lngGroup = 0
strSql = "SELECT tblSteps.ID, tblSteps.group, tblSteps.step, tblSteps.TimeStamp "
strSql = strSql & "FROM tblSteps "
strSql = strSql & "WHERE (((tblSteps.group) In ((SELECT tblSteps.group FROM tblSteps GROUP BY tblSteps.group HAVING (((Count([step])-Max([step]))<>0)))))) "
strSql = strSql & "ORDER BY tblSteps.group, tblSteps.step, tblSteps.TimeStamp DESC;"

Set rst = CurrentDb.OpenRecordset(strSql)
If rst.EOF = True And rst.BOF = True Then ' no records need updating
    Exit Sub
End If
rst.MoveFirst
Do While Not rst.EOF
    If lngGroup <> rst!Group Then
        lngGroup = rst!Group
        lngStep = 1
    Else
        lngStep = lngStep + 1
    End If
    strSqlUpdate = "UPDATE tblSteps SET tblSteps.step = " & lngStep & " WHERE (((tblSteps.ID)=" & rst!ID & "));"
    CurrentDb.Execute strSqlUpdate
    rst.MoveNext
Loop
Set rst = Nothing
End Sub

Hope that all makes sense

Peter

 

[Ethereal]

Ok, I got that all into my code with updated names etc... but um, it doesn't do anything .. lol When i pop the SQL into a query and run it, it seems to be coming up blank even when there are duplicate values for [Step] and when i took out the WHERE clause so the query would just be the table, i ran the code and on OpenRecordSet command it said "Too few parameters"

 

[Bat17]

can you post a small sample so I can see what is actualy happening?

Peter

 

[Ethereal]

I think everything on this sample is working properly
Note: had to make mach table for the odbc's but i'm pretty sure it is working properly, or more correctly, working as it was!

 

[Bat17]

only had a chance for a quick look, but it looks as if you are running my code before adding the data try it in this order.

        DoCmd.GoToRecord , , acNewRec
        txtSamID = 1
        CleanSteps
        Me.lstSamAlfak.Requery
        MsgBox "Data Saved!"

HTH

Peter

 

[Ethereal]

Thanks for that, At least now it will work once the "cleansteps" method changes the records correctly

 

[Bat17]

As far as I can see it does change the records in your sample correctly. If I add a record seq# gets reordered

Peter

 

[Ethereal]

but I need it renumbered as well
like when i enter seq#3 into 1,2,3,4,5 the end result should be 123456, pushing the old records down the line so to speak

 

[Bat17]

Thats just what it does.
After reodering the code.
On your sample data using SamID 1,
If I add Seq# 3 using AlfakNo4 - desc 4
then the list is reordered

Seq#	Article Number 	Description
2	120002		2.5MM CLEAR GLASS
3	4		desc 4
4	120008		8MM CLEAR GLASS

Peter

 

[Ethereal]

Right, I was requerying the listbox before i updated the table... then being silly enough to not look at the table.... It does work in the sample! Another reason i was getting duplicates ... switching from design mode to run mode mid write state tt