Renumber records in subform

[ILoveAccess via AccessMonster.com]

Hello!

I am using access 97 and have searched for an hour for an anwer to my
question. hopefully someone can help!

I have a subform, frmTools, that each record is a direction of how to put
together a machine. My issue is, how do I setup of the subform or records so
that if step 3 needs to be deleted than all the other numbers renumber
accordingly? Or if there is a step 1 through 5 already, and I need to add a
step after step 2, then I need all the other number to renumber accordingly.
I currently do not have the steps numbered because I wanted to find out how
to set it up first.

Thanks!

 

[Graham Mandeno]

Records in a recordset have no intrinsic order unless the recordset has an
Order By clause. Therefore you must have a field on which to sort, and so
that the order cannot be ambiguous the numbers must be unique for each group
(i.e. the MachineID and StepNumber fields need to form a unique composite
key)

To add a step at the end, you can determine the number easily - one greater
than the current last step, or 1 if there are currently no steps.

To insert a step before step N, you need to , then add the new record.

After deleting step N, you need to open a recordset of all steps greater
than N in ASCENDING order, and decrement each step number.

To move step N up (or down) one place, you are effectively swapping it's
number with the step before (or after) it. Choose a step number that can't
be used (say 0 or -1) and temporarily change N to that value. Then
increment (or decrement) the step number before (or after) N. Finally, set
the original step number to N-1 (or N+1).

 

[Marshall Barton]

I am using access 97 and have searched for an hour for an anwer to my
question. hopefully someone can help!

I have a subform, frmTools, that each record is a direction of how to put
together a machine. My issue is, how do I setup of the subform or records so
that if step 3 needs to be deleted than all the other numbers renumber
accordingly? Or if there is a step 1 through 5 already, and I need to add a
step after step 2, then I need all the other number to renumber accordingly.
I currently do not have the steps numbered because I wanted to find out how
to set it up first.

Add the step field to the base table used by frmTools.

Assuming you have a button for users to insert a new step
aftert the currnt record, you can renumber the steps using
an Update query in the button's Click event:

Dim db As Database
Dim strSQL As String
strSQL = "UPDATE yourtable SET Step = Step + 1 " _
& "WHERE machineid = " & Me.machineid _
& " AND Step > " & Me.Step
Set db = CurrentDb()
db.Execute strSQL

Use the same code, except for SET Step = Step - 1
in the delete button's Click event.

 

[ILoveAccess via AccessMonster.com]

This sound like it will work! But,

1.) Since the "Add new Record (Step)" button already has an Event Procedure
on the On Click event, how do I add the Event you gave me to On Click?

Thanks!

I am using access 97 and have searched for an hour for an anwer to my
question. hopefully someone can help!

[quoted text clipped - 6 lines]

I currently do not have the steps numbered because I wanted to find out how
to set it up first.

Add the step field to the base table used by frmTools.

Assuming you have a button for users to insert a new step
aftert the currnt record, you can renumber the steps using
an Update query in the button's Click event:

Dim db As Database
Dim strSQL As String
strSQL = "UPDATE yourtable SET Step = Step + 1 " _
& "WHERE machineid = " & Me.machineid _
& " AND Step > " & Me.Step
Set db = CurrentDb()
db.Execute strSQL

Use the same code, except for SET Step = Step - 1
in the delete button's Click event.

 

[Graham Mandeno]

The code can just be added to your existing event procedure. It doesn't
need to be in a procedure all by itself.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

This sound like it will work! But,

1.) Since the "Add new Record (Step)" button already has an Event
Procedure
on the On Click event, how do I add the Event you gave me to On Click?

Thanks!

I am using access 97 and have searched for an hour for an anwer to my
question. hopefully someone can help!

[quoted text clipped - 6 lines]

I currently do not have the steps numbered because I wanted to find out
how
to set it up first.

Add the step field to the base table used by frmTools.

Assuming you have a button for users to insert a new step
aftert the currnt record, you can renumber the steps using
an Update query in the button's Click event:

Dim db As Database
Dim strSQL As String
strSQL = "UPDATE yourtable SET Step = Step + 1 " _
& "WHERE machineid = " & Me.machineid _
& " AND Step > " & Me.Step
Set db = CurrentDb()
db.Execute strSQL

Use the same code, except for SET Step = Step - 1
in the delete button's Click event.

 

[ILoveAccess via AccessMonster.com]

I tried adding it to the bottom of the following existing procedure and it
did not work. Where in this procedure should I enter your procedure?

Private Sub AddNewRecord_Click()
On Error GoTo Err_AddNewRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_AddNewRecord_Click:
Exit Sub

Err_AddNewRecord_Click:
MsgBox Err.Description
Resume Exit_AddNewRecord_Click

The code can just be added to your existing event procedure. It doesn't
need to be in a procedure all by itself.

This sound like it will work! But,

[quoted text clipped - 27 lines]

 

[Graham Mandeno]

You should renumber the existing steps before going to the new record. Try
this:


Private Sub AddNewRecord_Click()
On Error GoTo Err_AddNewRecord_Click

Dim db As Database
Dim strSQL As String
Dim iCurrentStep as Integer
' turn off screen updating
Me.Painting = False
' save the step of the current record
iCurrentStep = Me.Step
' move all steps below and including the current one down a notch
strSQL = "UPDATE yourtable SET Step = Step + 1 " _
& "WHERE machineid = " & Me.MachineID _
& " AND Step >= " & iCurrentStep
Set db = CurrentDb()
db.Execute strSQL, dbFailOnError
' go to a new record and enter the step number
DoCmd.GoToRecord , , acNewRec
Me.Step = iCurrentStep
' requery the form to put the new step in the right place
Me.Requery
' position the record to the new step
With Me.RecordsetClone
.FindFirst "Step=" iCurrentStep
Me.Bookmark = .Bookmark
End With

Exit_AddNewRecord_Click:
Me.Painting = True
Exit Sub

Err_AddNewRecord_Click:
MsgBox Err.Description
Resume Exit_AddNewRecord_Click
End Sub

You might need to change some field names (Step and/or MachineID). Also, if
any other fields (say, StepDescription) are required, you will need to put
some dummy values into them before the Me.Requery.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I tried adding it to the bottom of the following existing procedure and it
did not work. Where in this procedure should I enter your procedure?

Private Sub AddNewRecord_Click()
On Error GoTo Err_AddNewRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_AddNewRecord_Click:
Exit Sub

Err_AddNewRecord_Click:
MsgBox Err.Description
Resume Exit_AddNewRecord_Click

The code can just be added to your existing event procedure. It doesn't
need to be in a procedure all by itself.

This sound like it will work! But,

[quoted text clipped - 27 lines]

Use the same code, except for SET Step = Step - 1
in the delete button's Click event.

 

[ILoveAccess via AccessMonster.com]

When it says "yourtable" do I put my table name in there?

">You might need to change some field names (Step and/or MachineID). Also,
if

any other fields (say, StepDescription) are required, you will need to put
some dummy values into them before the Me.Requery."

I don't see any other fields that say "StepDescription" and
What type of dummy values do you mean?

You should renumber the existing steps before going to the new record. Try
this:

Private Sub AddNewRecord_Click()
On Error GoTo Err_AddNewRecord_Click

Dim db As Database
Dim strSQL As String
Dim iCurrentStep as Integer
' turn off screen updating
Me.Painting = False
' save the step of the current record
iCurrentStep = Me.Step
' move all steps below and including the current one down a notch
strSQL = "UPDATE yourtable SET Step = Step + 1 " _
& "WHERE machineid = " & Me.MachineID _
& " AND Step >= " & iCurrentStep
Set db = CurrentDb()
db.Execute strSQL, dbFailOnError
' go to a new record and enter the step number
DoCmd.GoToRecord , , acNewRec
Me.Step = iCurrentStep
' requery the form to put the new step in the right place
Me.Requery
' position the record to the new step
With Me.RecordsetClone
.FindFirst "Step=" iCurrentStep
Me.Bookmark = .Bookmark
End With

Exit_AddNewRecord_Click:
Me.Painting = True
Exit Sub

Err_AddNewRecord_Click:
MsgBox Err.Description
Resume Exit_AddNewRecord_Click
End Sub

You might need to change some field names (Step and/or MachineID). Also, if
any other fields (say, StepDescription) are required, you will need to put
some dummy values into them before the Me.Requery.

I tried adding it to the bottom of the following existing procedure and it
did not work. Where in this procedure should I enter your procedure?

[quoted text clipped - 18 lines]

 

[Graham Mandeno]

When it says "yourtable" do I put my table name in there?
Precisely!

">You might need to change some field names (Step and/or MachineID).
Also,
if

I don't see any other fields that say "StepDescription" and
What type of dummy values do you mean?

I assume that MachineID and Step are "required" fields in your table. What
I meant is that if you have any *other* required fields, your code must put
values into them before the Me.Requery line, because requerying the form
forces a save.

As an example, say you have a required field named StepDescription. Your
code might read:
Me.Step = iCurrentStep
Me.StepDescription = "Enter the description here"
Me.Requery

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

You should renumber the existing steps before going to the new record.
Try
this:

Private Sub AddNewRecord_Click()
On Error GoTo Err_AddNewRecord_Click

Dim db As Database
Dim strSQL As String
Dim iCurrentStep as Integer
' turn off screen updating
Me.Painting = False
' save the step of the current record
iCurrentStep = Me.Step
' move all steps below and including the current one down a notch
strSQL = "UPDATE yourtable SET Step = Step + 1 " _
& "WHERE machineid = " & Me.MachineID _
& " AND Step >= " & iCurrentStep
Set db = CurrentDb()
db.Execute strSQL, dbFailOnError
' go to a new record and enter the step number
DoCmd.GoToRecord , , acNewRec
Me.Step = iCurrentStep
' requery the form to put the new step in the right place
Me.Requery
' position the record to the new step
With Me.RecordsetClone
.FindFirst "Step=" iCurrentStep
Me.Bookmark = .Bookmark
End With

Exit_AddNewRecord_Click:
Me.Painting = True
Exit Sub

Err_AddNewRecord_Click:
MsgBox Err.Description
Resume Exit_AddNewRecord_Click
End Sub

You might need to change some field names (Step and/or MachineID). Also,
if
any other fields (say, StepDescription) are required, you will need to put
some dummy values into them before the Me.Requery.

I tried adding it to the bottom of the following existing procedure and
it
did not work. Where in this procedure should I enter your procedure?

[quoted text clipped - 18 lines]

Use the same code, except for SET Step = Step - 1
in the delete button's Click event.