Renumbering after deleting record

[Carlos1815]

Hello, all! I have a problem that from the outset seems easy,
and there's plenty of information on here and other forums on the
subject, but nothing that answers my question.

I have a form with a control called PageID whose control source is a
table with a PageID field; it is not an autonumber, I've been very
well briefed on autonumber's strengths and limitations! The form is
an interface where a designer can enter data, text, graphics,
questions, etc., and the corresponding table of data is read by
Authorware. Each record is a page in Authorware, and the PageID is
the page number. If the designer adds a new page, the PageID is
incremented up using the following code:

Private Sub Form_Current()

If Me.NewRecord Then

Me.PageID = Nz(DMax("PageID", "Topic1") + 1, 1)
DoCmd.RunCommand acCmdRefresh

End If
End Sub

That works fine. However, if a page is deleted in the middle of the
db, like page 3 of 5, then the pages are numbered thusly:

From:
PageID
1 Page 1
2 Page 2
3 Page 3 <-- delete this page
4 Page 4 <-- make this page 3
5 Page 5 <-- make this page 4

To:
PageID
1 Page 1
2 Page
2
4 <-- oops! Page 3
5 Page 4

So when I take out page 3, page 4 becomes page 3 and so on down the
line. But what I would like is for the PageID number to renumber
itself so there are no gaps in the PageID field. Maybe some sort of
code in AfterUpdate() or FormCurrent() that automatically renumbers
the records after one is deleted. The deletion, by the way, occurs
through a command button I created. I know that not ALL the numbers
have to be redone, just the ones below the record that was deleted. I
can't figure out how to make that happen; any help would be
appreciated!! Thanks in advance!

Carlos

 

[Jeff Boyce]

Carlos

There may be another way of looking at this...

If your table contained a "Sequence" field into which you put numbers (if a
number field) or alphabetic characters (if a text field) to allow you to
sort the records in the order you wished, you wouldn't need to attach a page
number to each record, and you wouldn't need to re-number after deleting OR
MOVING a record.

Then, when you wanted to determine what "page number" to associate with each
record, you could use a query to generate that dynamically.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[strive4peace]

Hi Carlos,

does this help?

'~~~~~~~~~~~~~~~
Private Sub Form_AfterDelConfirm(Status As Integer)
MsgBox "You deleted record before PageID=" & Me.PageID
End Sub
'~~~~~~~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Peter Hibbs]

Carlos,

Try this :-

Copy and paste the sub-routine below into your form's VBA module and
call it with :-

Renumber 'renumber PageID field
Me.Requery 're-display the form

'--------------------------------
Public Sub Renumber()

Dim rs As Recordset
Dim vNumber As Long

On Error GoTo ErrorCode

vNumber = 1
Set rs = CurrentDb.OpenRecordset("SELECT PageID FROM Authorware
ORDER BY PageID")
Do Until rs.EOF
rs.Edit
rs!PageID = vNumber
vNumber = vNumber + 1
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Exit Sub

ErrorCode:
MsgBox Err.Description
End Sub
'--------------------------------

Note that the Set rs....PageID") line should be all on the same line.
This assumes that you always want to start the sequence at 1 and that
PageID is a Number (Long Integer) type field. Also I assume that
Authorware is the name of your table, change it if not.

HTH

Peter Hibbs.

 

[Carlos1815]

Carlos,

Try this :-

Copy and paste the sub-routine below into your form's VBA module and
call it with :-

     Renumber      'renumber PageID field
     Me.Requery   're-display the form

'--------------------------------
Public Sub Renumber()

Dim rs As Recordset
Dim vNumber As Long

    On Error GoTo ErrorCode

    vNumber = 1
    Set rs = CurrentDb.OpenRecordset("SELECT PageID FROM Authorware
ORDER BY PageID")
    Do Until rs.EOF
        rs.Edit
        rs!PageID = vNumber
        vNumber = vNumber + 1
        rs.Update
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Exit Sub

ErrorCode:
    MsgBox Err.Description
End Sub
'--------------------------------

Note that the Set rs....PageID") line should be all on the same line.
This assumes that you always want to start the sequence at 1 and that
PageID is a Number (Long Integer) type field. Also I assume that
Authorware is the name of your table, change it if not.

HTH

Peter Hibbs.













- Show quoted text -

Got it! Thanks for your help!!
Carlos