[RESOLVED] SQL (when an ID is deleted, the following ID will -1 to take the new position.

[Astro]

i have a ss_table which consist of sID, qnsID, ansID.

the values of sID are s0001, s0002, s0003 and so on.

If i were to remove s0002, i would like s0003 to shift up and set as s0002.

can someone show me how i can do this in sQL

my approach is rst.Open "SQL", cn, adOpenDynamic, adLockOptimistic


note: sID is set as 'Text' in Access table but autonumber-ed by SQL
rst!sID = "ss" & Right("0000" & CStr(CInt(rst1!ansID) + 1), 4)

Thank You

Astro

[Mark Gambo]

Run this query right after you delete the record:

UPDATE ss_table 
SET ss_table.sID = Left([sID],1) & Format(CLng(Mid([sID],2))-1,"0000")
WHERE (((ss_table.sID)>"s0002")); 

[Astro]

Hey Mark,

Brilliant! =) Pardon me for not having to mention that ss0002 will be removed when the referenced ansID (ans0001, ans0002, ans0003) is double clicked on.

rst1.Open "UPDATE ss_table SET ss_table.sID = Left([sID],1) & Format(CLng(Mid([sID],2))-1,'000000') WHERE '" & List3.Column(0, List3.ListIndex) & "' < ss_table.ansID", cn, adOpenDynamic, adLockOptimistic

How does this looks? note: i've changed 0000 to 000000.

[Astro]

Hey Mark,

I got it. Well actually i am asked to drop the idea. Great help anyway!

Thanks

Astro