Renumber records in subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ILoveAccess - 09 Nov 2005 20:07 GMT

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!

Reply to this Message

Graham Mandeno - 09 Nov 2005 22:43 GMT

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).

Signature

Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Hello!
>
[quoted text clipped - 14 lines]
>
> Thanks!

Reply to this Message

Marshall Barton - 09 Nov 2005 23:21 GMT

>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

ILoveAccess - 10 Nov 2005 19:05 GMT

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 - 18 lines]
>Use the same code, except for SET Step = Step - 1
>in the delete button's Click event.

Reply to this Message

Graham Mandeno - 10 Nov 2005 19:22 GMT

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

Signature

Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

> 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.

Reply to this Message

ILoveAccess - 10 Nov 2005 20:15 GMT

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.
[quoted text clipped - 3 lines]
>>>Use the same code, except for SET Step = Step - 1
>>>in the delete button's Click event.

Reply to this Message

Graham Mandeno - 10 Nov 2005 21:25 GMT

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.

Signature

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?
[quoted text clipped - 18 lines]
>>>>Use the same code, except for SET Step = Step - 1
>>>>in the delete button's Click event.

Reply to this Message

ILoveAccess - 11 Nov 2005 21:04 GMT

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:
[quoted text clipped - 43 lines]
>>>>>Use the same code, except for SET Step = Step - 1
>>>>>in the delete button's Click event.

Reply to this Message

Graham Mandeno - 12 Nov 2005 01:46 GMT

> 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,
[quoted text clipped - 4 lines]
> 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

Signature

Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

>>You should renumber the existing steps before going to the new record.
>>Try
[quoted text clipped - 46 lines]
>>>>>>Use the same code, except for SET Step = Step - 1
>>>>>>in the delete button's Click event.

Reply to this Message