Re-number records if one deleted 1

[Survane]

I need to have a piece of code look at all my records and re-number them if one has been deleted or if there is a gap in the numbering sequence.

It can do this whenever a form is closing or opening.

Has anyone ever had to do this before?

Thanks,
Suvane.

 

[WildHare]

Has anyone ever had to do this before?

No. Because it's probably a bad idea.

What you are in effect doing is re-ranking or re-keying the entire table everytime there's a maintenance change. This will cause Ted Codd to come to your house and kick your cat.

Can you elucidate on your reasoning behind this question, and perhaps we can come up with a better solution.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Survane]

It is a user request. I don't sanction it but a user requested it.

Survane.

 

[WildHare]

If you really MUST do this, write a short procedure to open the recordset, loop through every record, and renumber a numeric field.

.....
x = 1
While not rst.EOF
Do
rst.next
rst.BogusNumber = x
x = x + 1
rst.update
Loop
rst.close
..............

or words to that effect.

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ruthcali]

Jim,
I am interested in this post. I have a similar situation. I have pieces of equipment that are connected to each other in a chain fashion.
ex:
equipAB--equipGH--equipZZ--equipPL, etc. (the equip names vary and are not in alphabetical order).

If equipGH is removed, then engineers have to connect equipAB to equipZZ to keep the chain alive.

Later, the customer might decide to insert equipQQ between equpAB and equipZZ.

So my order is very important. i created a Number field to force Access to keep my order.
ex: Number EquipName
1 equipAB
2 equipGH
3 equipZZ
4 equipPL

If equipGH is removed, i re-number:
1 equipAB
2 equipZZ
3 equipPL

then later, if equipQQ is inserted, i re-number yet again:
1 equipAB
2 equipQQ
3 equipZZ
4 equipPL

i've been manually re-numbering fields when equipment is removed or inserted. Your code above can help me have Access re-number automatically.

Since you are against re-numbering, do you have another way to force Access to keep the order i need?
Thanks,
Ruth

 

[MichaelRed]

Ruth, what you (and others) are asking for is just a dpubly linked list. In the general scheme of it (the doubly linked list), you maintain the predecessor and sucessor to each element. To use your example:

EquipId Pred Suces
EqGh EqAb EqZz
EqZz DqGh EqPl
EqPl EqZz Tail
EqAb Head EqGh

So when you remove an element (EqGh),


EquipId Pred Suces
EqAb Head EqZz
EqPl EqZz Tail
EqZz DqAb EqPl


And to Add QQ


EquipId Pred Suces
EqAb Head EqQq
EqPl EqZz Tail
EqZz DqQq EqPl
EqQq EqAb EqZz

It is a bit harder for some (at least conceptually), but it involves changing only a FEW records, so it is MUCH easier in the pratical sense. Also a lot easier to manage, as you can easily write (generate) SQL statements to list the sucession either foewards or backwards from any point in the list.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WildHare]

I knew if I went out to run some errands, a wiser soul than I would chime in here - MikeRed is exactly correct in his portrayal of a doubly-linked list and his solution is elegant.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ruthcali]

wow. thanks michael and jim!

michael, your linked list looks, to quote jim, elegant.

i played around a little with the code to manipulate the data, but no success yet. i'm still trying and i'll let you know tomorrow how i succeed.

Thanks again!

PS--it's called a 'linked' list, but it's not really linked, right? All 3 fields (equipid, pred and suces are all in the same table, right?).

 

[WildHare]

Ruth - yes, they actually call it a "double-linked list". It's the way most relational tables are structured physically at the bits 'n' bytes level.

Each record has an internal "record number". There is another internal field for the "next" logical record (contains the record number) and another internal field for the "previous" record (again, just the internal record number).

When maintenance (adds/deletes) are done, the linked list chain is revised by updating the NEW record's Next & Previous values, and the NEXT Record's PREVIOUS guy, and the Previous Record's NEXT guy, to point to this new record.

As you can see, the logical order and the physical order are rarely the same - but a compact function rewrites the table, reordering the physical records to match the logical chain.

Actually, there's a third 'invisible' field too - the LogicalDelete flag. Often, it's just a bit turned on to indicate that this record is no longer valid. Compacting removes them too...

Probably more than you cared to know.. LOL

Jim


Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ruthcali]

Jim,
Thanks for the explanation. So every record in every table has these 3 hidden internal placeholders? interesting. The secret life of Access!

Ok, let's say the table is set up correctly with the pred and the suces fields. How do you properly display the data in the correct order?

My weak attempt:

Set db = CurrentDb
Set rec = db.OpenRecordset("select * from table1&quot

rec.MoveFirst
Do Until rec.EOF
'look for the 'head' field. That should be first.
If rec!pred = "head" Then
'when you find the head field, take those values and put them into temp fields.
tempEquip = rec!equip
tempPred = rec!pred
TempSuces = rec!suces
'move to the first record
rec.MoveFirst
rec.Edit
'put the temp values there
rec!equip = tempEquip
rec!pred = tempPred
rec!suces = TempSuces
rec.Update
Form.Refresh
Exit Sub
End If
rec.MoveNext
Loop

I have a feeling my logic is too confusing. Michael had written "you can easily write (generate) SQL statements to list the sucession either forewards or backwards from any point in the list."

I must smile at his word 'easily'. My way isn't right, is it? What about when it comes to adding or deleting a record? Is there 'easy' code for that too or should that do done manually?

Thanks!

 

[WildHare]

1) I don't know if JET actually uses these N/P/D fields (Next/Previous/Deleted) or not, but I was taught (way back in the Stone Age) that this was how relational tables were built, on a physical level. I suppose if you dug around in the MSYS tables a bit you might find something about this stuff..or dig through the trash bins out in Redmond..LOL

Does anyone out there know of a "technical reference" to the JET engine? Is one publicly available?

I haven't given this too much thought, but I'm sure that Mike or I or almost anyone else here could whip you up something, at least in pseudocode, that would do what you want.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MichaelRed]

Oh, well I woould have thought SOMEBODY would have picked this up during the day.

More or less (e.g. COMPLETLY) as a demo to explicitly show the operations, I hacked up the following:

Public Function basRtnLnkLst(Optional StItem As Variant = "Head", _
                             Optional TheOrder As Variant = "Asc") As String

    'Michael L. Red     8/7/02
    'Demo retrievial of Linked List from Table

    'Sample Usage
    '? basrtnlnklst()
    'EqAb , EqQq, EqZz, EqPl

    '? basRtnLnkLst("Tail")
    'EqPl , EqZz, EqQq, EqAb

    '? basRtnLnkLst("EqZZ")
    'EqZz , EqPl

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef

    Dim strSql(1) As String
    Dim MyItem As String
    Dim MyOrder
    Dim MyNext As String
    Dim MyCSVList As String

    Set dbs = CurrentDb

    MyItem = StItem

    strSql(0) = "Select EquipId, Pred, Suces from tblLinkedList "
    
    Select Case MyItem

        Case Is = "Head"
        strSql(1) = strSql(0) & "Where [Pred] = " & Chr(34) & MyItem & Chr(34) & ";"
        MyOrder = "Asc"

        Case Is = "Tail"
        strSql(1) = strSql(0) & "Where [Suces] = " & Chr(34) & MyItem & Chr(34) & ";"
        MyOrder = "Dec"

        Case Else
        strSql(1) = strSql(0) & "Where [EquipId] = " & Chr(34) & MyItem & Chr(34) & ";"
        MyOrder = TheOrder
    End Select

    Set qdf = dbs.QueryDefs("qdfTemp")
    qdf.SQL = strSql(1)
    Set rst = dbs.OpenRecordset("qdfTemp")

    MyCSVList = rst!EquipId      'First Item

    MyNext = ""
    While MyNext <> &quot;Head&quot; And MyNext <> &quot;Tail&quot;

        Select Case MyOrder
            Case Is = &quot;Asc&quot;
                MyNext = rst!Suces
            Case Is = &quot;Dec&quot;
                MyNext = rst!Pred
        End Select

        strSql(1) = strSql(0) & &quot;Where EquipId = &quot; & Chr(34) & MyNext & Chr(34) & &quot;;&quot;
        Set rst = Nothing
        qdf.SQL = strSql(1)
        Set rst = dbs.OpenRecordset(&quot;qdfTemp&quot;)
        If (rst.BOF <> True Or rst.EOF <> True) Then
            MyCSVList = MyCSVList & &quot;, &quot; & rst!EquipId      'Next Item
        End If
    Wend

    basRtnLnkLst = MyCSVList

End Function

Since the techniques for finding the predecessor and sucessor are seen (albeit some what briefly?), I would think most would be able to see how to use the process to either add or delete an item in the List. To wit, to add an item at a spot, find the spot (assume that you have either the pred or suces). Follow the links to the other relevant record. Add the new item with the pred and suces from the spot and othe relevant rec. Edit the spot and other relevant rec suces and pred to point to the new item. Be sure to follow father codd's rules (wrap this in a TRANSACTION!!!!!) other wise a crash in the midst of this couls leave you w/o hair, sanity and/or a job.

This really short list is based on the table(s) posted previously - with the exception of fixing the typo (EqZz Pred = EsSq, NOT "DqQq&quot.

Assuming that All will sleep well and not need any more hand holding untill at least Thurs.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ruthcali]

Michael,

WOW. that is amazing. it works perfectly. thank you so much. That is really impressive. i wish i could give you a second star. you deserve it!!

That is much easier than renumbering all my records when one changes! Very impressive code.

thanks again!

 

[MichaelRed]

Hmmmmmmmmmmmm,

so you've done the insert and delete procedures?

you could share that with the wimp[y | ies] - perhaps they would then give YOU a star?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ruthcali]

Hi Michael,

I haven't done the insert/delete procedure. That is advanced stuff! i'm still on the first phase: pasting your code and running it to see the results. Then, studying your code to try to understand it in order to incorporate it into my form.

But i ran the code and no matter what value i tested it with, Access returned the correct pred and suces values.

So i am in awe of you.
Very impressive like i said before.