Insert and delete records and then reorder

[Guest]

I have a database to contain procedures. The main detail of the procedure is
in the main form and has an autonumber (PID)

On the subform I have an autonumber (SID) and a procedural step no (Seq) ,
the subform contains all the steps of the procedure .

Seq is automatically stepped up by 1 by using the following code :

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![Seq]) Then
Me![Seq] = (Nz(DMax("[Seq]", "Steps", "[Pid]=" & [PID]), 0) + 1)
Else
End If
End Sub

My problem is I will need to delete, insert and move steps around. eg I can
delete a step but then the Seq no will show a gap I need to delete by
selecting it and then clicking a button OR pressing the delete key and then
fire off some code to reorder Seq without gaps.

Any ideas on this deletion to start with; or even better getting my head
round a method to insert which seems very tricky , I am desperate !!!!

 

[Allen Browne]

Hi Martyn

You have asked several questions about this. It is not a simple thing to do,
and I will not be able to walk you through all the steps to get the result
you need. But if you are able to figure it out from an example, you are
welcome to this one:
http://allenbrowne.com/unlinked/ReorderRecords.txt
I've used a link rather than attempt to post that code, due to the length of
the lines that get mashed in the newsgroup messages.

Basically, the code assumes your table (strTable argument) has a numeric
field (strOrderField) that indicates the sort order of the items, and it
uses the AfterUpdate event of the form (frm argument), to resequence all
matching values, where "matching values" is determined by the strWhere
argument. Form_BeforeUpdate saves values in form-level variables, and passes
those values in Form_AfterUpdate so the reordering takes place only when
necessary (to avoid constant inefficient processing.)

If it is any use, you are welcome to it. If not, that's fine too. As I say,
it is too big to explain in detail.

A couple of Access bugs arose when developing this code. A significant one
is that if the Form_BeforeUpdate and Form_AfterUpdate were triggered by
tabbing into a subform, the subform's Enter event does not fire. Not sure if
this is because the code requeries the form in Form_AfterUpdate, but it's
worth being aware of.