Renumber a List

[BillBee]

Had noticed and corrected the Special spelling at the time of mentioning the error TOF but didn't mention in my post. Had wondered if I was being tested. Will go back again to inspect code to see if I can find anything else. While I have been working through this I can see it working as I would like for creating the space and adding a new Special. But am still wondering where an item is simply removed and the List is now renumbered.

 

[MagicMan]

Do you want working code, or do you have time to work on it?
When an item is removed, it is first removed (deleted from the table), then the user must perform the renumber specifying the number removed, when asked. The procedure then renumbers strating from that number (Eg: deleted #3, click command button to renumber, answer 3, and the procedure will identify #3 is missing and renumber #4 to #3, #5 to #4, etc.

 

[BillBee]

Success at last, am now creating space and renumbering. Scrolled up and down code and got it to run right through. Adding new records okay but as said in first post some are removed completely or moved from one position to another as previously mentioned with respect of National Body moving awards from One section to another. Yes I would like to see your code and Table. Bill

 

[MagicMan]

At this point, you probably have the same code I have. To move an item to a new number, EG: from 3 to 8 assuming 8 is not the last number, use the function with first input = 9 .... then retrieve #3, and change it to 9, then delete #3, and call the function with #3...the renumber will then make #4>>>#3, #5>>>#4, #9 >>> #8. You may consider removing the request for the Input number from the function and pass it as a parameter. Enhance your messages to describe what renumbering has occurred, EG: Renumber from #3 to make space for a new entry as #3 is successful. In addition, you may want to also renumber to allow more than one record add.

 

[BillBee]

I am missing something. Trying, using your numbers moving 3 to 8. First step okay - renumbering successful. Retrieve #3 (on my Form using navigation buttons) and change to #9. At this point I have no #3 to Delete (now #9). Tried going back to table to see what had happened and get message respect of duplicate numbers. I am working with indexed and no duplicates because I do not want duplicate numbers in final List.

 

[MagicMan]

Sorry, I am assuming you have the same code I have. The code below will make space for a new entry. It also will renumber from a deleted entry. So lets say your entries are 1 to 15.
To move a record from number 3 to number 8,
First you dispatch the renumber function enter 9 to the function InputBox.
The result is 1a,2c,3c,4d,5e,6f,7g,8h,10i,11j,12k,13l,14m,15n,16o.
You then change entry #3 to #9,
The result is 1a,2c,4d,5e,6f,7g,8h,9c,10i,11j,12k,13l,14m,15n,16o.
You then dispatch the renumber function and answer 3 to the InputBox
The result is 1a,2c,3d,4e,5f,6g,7h,8c,9i,10j,11k,12l,13m,14n,15o.

Using the code that follows, all the above functions are completed successfully.

Option Compare Database
Option Explicit
Function UpdateSpecial() As Boolean
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim LUpdate As String
    Dim LSpecial As Long
 
    On Error GoTo Err_Execute
 
    '    Query user for the starting Special number
    LSpecial = InputBox("Please enter starting Special Number.", _
                                 "Renumber Specials")
    UpdateSpecial = False
    Set db = CurrentDb()
    LSpecial = LSpecial - 1
    '  Retrieve the last record less than LSpecial
    strSQL = "SELECT Special FROM Prizes WHERE Special >= " & _
                  LSpecial & " ORDER BY Special"
 
    Set rs = db.OpenRecordset(strSQL)
    If rs.BOF = False Or rs.EOF = False Then
        rs.MoveNext
        If rs.EOF = False Then
            LSpecial = LSpecial + 1
            If rs("Special") = LSpecial Then
                If MsgBox("Making space to add Special Number #" & _
                    LSpecial & ". Continue?", vbYesNo, _
                    "Add a new Special Number") = vbYes Then
                    rs.MoveLast
                    Do Until rs.BOF = True Or rs("Special") < LSpecial
                        '    Renumber Special Numbers
                        rs.Edit
                        rs("Special") = rs("Special") + 1
                        rs.Update
                        rs.MovePrevious
                        UpdateSpecial = True
                    Loop
                    If UpdateSpecial = False Then
                        MsgBox "Special number entered is > largest number or invalid"
                    End If
                Else
                    MsgBox "Renumbering cancelled"
                End If
            Else
                If MsgBox("Renumbering after Special Number #" & _
                    LSpecial & " was deleted. Continue?", vbYesNo, _
                    "Renumber Special Number") = vbYes Then
                    Do Until rs.EOF
                        '    Renumber Special Numbers
                        rs.Edit
                        rs("Special") = LSpecial
                        LSpecial = LSpecial + 1
                        rs.Update
                        rs.MoveNext
                    Loop
                    UpdateSpecial = True
                Else
                    MsgBox "Renumbering cancelled"
                End If
            End If
        Else
            MsgBox "No records found with Special > " & LSpecial
        End If
    Else
        ' Record preceeding LSpecial is not found
        MsgBox "No record found with Special = " & LSpecial
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    If UpdateSpecial = True Then
        MsgBox "Renumbering the Special Numbers has successfully completed."
    Else
        MsgBox "Updating the Special Numbers failed."
    
    End If
Exit Function
 
Err_Execute:
    MsgBox "Special Renumbering Unexpected error encountered"
End Function
Private Sub Field1_AfterUpdate()
    UpdateSpecial
End Sub

 

[BillBee]

Have not gone through new code line for lies as yet. Obvious differences are option Explicit and the Field1_ AfterUpdate at the end. Had to Change "Field1" to Special (my field name) to get Code to work. Other thing after changing #3 to #9 have move to another record

 

[MagicMan]

No....this is working code, you may have to change the table name and field name but the code has no errors.
the code

Private Sub Field1_AfterUpdate()
    UpdateSpecial
End Sub

is my trigger for the function...you can ignore that code.

 

[BillBee]

Okay. then I am getting it to work. Only other thing is also must close form and reopen for to show renumber entries also most move manually to next record after line 6 of your earlier notes( change 3 to). This could be a little inconvenient when you have several changes to make in the list

 

[MagicMan]

Use Me.Requery to refresh the screen after the function is completed. Then you do not have to close the form.
Read up on SetFocus, DoCmd.SearchForRecord, and Bookmark (recordset clone).
One of these may help you position on the record you want after the process.
Smiles
Bob

 

[BillBee]

Code is the same. Set afterupdate on form which initially does not show reordered numbers but moving back or forwards does. At least I do not have to close and reopen form. Am I on right track?
Tried. But this didn't work
Private Sub Form_Afterupdate()
Me![Specials].Requery
End Sub.

 

[MagicMan]

Place Me.Requery right after the successful message. See code:

    If UpdateSpecial = True Then
        MsgBox "Renumbering the Special Numbers has successfully completed."
        Me.Requery
    Else
        MsgBox "Updating the Special Numbers failed."
    End If

 

[BillBee]

You're a very patient man Bob. Two words on single line and I couldn't get that right. Maybe coding isn't for me. Better to stop at "Hello World". Did Try Me.Requery higher up. However have inserted it in correct place and as soon as I click on Update Special numbers Button I get a compilation error message "Invalid use of Me Keyword". (Using your final code just to make sure there are no errors in mine). Searching for reason for this has told me you cannot use Me in a Module. Should I have not had this in a module all along. Is this another Test?(Not complaining) Reading up on items you suggested thinking one of these may but the answer, but can't find any thing related to DoCmd.SearchForRecord. By the way did search on Rules for Data Normalization (DataModel.org). The model I looked at had 8 and number five related to a Boyce-Codd Normal Form which makes no sense at all to me. Bill

 

[MagicMan]

Your lucky you understood the 4th rule of normalization, after that it gets very technical, and is accurate, but most table designs usually end up somewhere bewtween the 2nd and third rule, with a few elements to the 4th. In the function, My does not apply, since no recordsource(form) object is bound to the function. The application that calls the function, or macro finds the Successful message as the last process of the function. Remove the My.Requery from the function and place it in the VB code / or in a macro (Requery is a selection). Add the requery on the line after the call of the function.

 

[WayneRyan]

Bill,

This was an interesting thread.

But it is the weekend. I couldn't help but submit an example ... it's a little
rough, but should give you something to look at.

I should have commented the code. The concepts of a "block" and the
"Target" row are a little sketchy.

Hopefully it gives you something to look at.

btw, I really wanted to use Conditional Formatting to highlight the "block",
but I would have to move through the block to make it fire. Darn ...

hth,
Wayne

 

[BillBee]

Wayne Glad you find thread interesting. Did not really follow what you are saying. Have now looked at your attachment. Can understand the concept. The Autonumbers seem to rise very quickly with my messing around. On my original table I didn't want to use autonumbers because of the very reason of additions deletions and moving records with in the List.
Bob. Haven't had a lot of time today but a little research has come up with DoCmd.Requery. This I have placed in line 75 where I had Me.Requery. It will only work once then I get a Duplicate numbers message. Have to be back to the drawing board. Bill

 

[MagicMan]

The application that calls the function, or macro finds the Successful message as the last process of the function. Remove the Me.Requery from the function and place it in the VB code / or in a macro (Requery is a selection). Add the requery on the line after the call of the function.

Earlier you said the Renumber is dispatched from a command button. Can you please describe the code behind the command button, is it:
a) a macro
b) expression Builder
c) Visual basic code

It would help if you code paste or describe the command button, or the process that launches the Function. The function works, now the way the function is dispatched is the point in issue. I need to know more about that process to provide the exact answer to remove the need to close/open the form.

As for the Duplicates issue, you have a very basic form. The steps taken to renumber an item must be followed in a precise manner (as identified earlier) or a duplicate entry will occur.

 

[WayneRyan]

Bill,

The AutoNumber field could be entirely removed. It has nothing to do with
the way the s/w functions. I just added it to the table. It isn't referenced
in the code or anything.

Just wanted to give you another view into things.

Wayne

 

[BillBee]

Wayne. The main purpose of what I want is to reorganise the Field then renumber the list sequentially. Probably many ways to do this but Magicman was working from a code that I had downloaded and adapted.

 

[WayneRyan]

Bill,

I know.

Wayne