I have a situation where I need to renumber a field occasionally. It is a prize list for awards which needs added to and occasionally items deleted from. I do not have a good understanding of code but managed to find the below code and have adapted it to suit the Field I am trying to renumber. I have entered it as a module and run it 'on click' from a macro on the Form where I make the alterations. I have been told this is not a normal procedure for Access but is what I need to do. The code is giving me "updating the Special numbers has Failed.
Function UpdateSpecial() As Boolean

Dim db As Database
Dim LSQL As String
Dim Lrs As DAO.Recordset
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")

Set db = CurrentDb()

'Retrieve each record
LSQL = "select [Special]from DATA"
LSQL = LSQL & " where[Special] <" & LSpecial
LSQL = LSQL & " order by [Special]"

Set Lrs = db.OpenRecordset(LSQL)

Do Until Lrs.EOF
'Renumber Special Numbers
LUpdate = "update DATA"
LUpdate = LUpdate & " set[Special] = " & LSpecial
LUpdate = LUpdate & " where[Special] = " & Lrs("Special")

db.Execute LUpdate, dbFailOnError

'Increment Ring Number
LSpecial = LSpecial + 1
Lrs.MoveNext
Loop

Set Lrs = Nothing
Set db = Nothing

MsgBox "Renumbering the Special Numbers has successfully completed."

UpdateSpecial = True

On Error GoTo 0

Exit Function

Err_Execute:
MsgBox "Updating the Special Numbers failed."
UpdateSpecial = False
End Function

Can anyone point out what is wrong? Thanks

A few things ...
1) Your doing a recordset loop and using a sql update in the midst of the recordset process. Personally, I would use the recordset edit....update functions.
2) You are referencing the recordset field value inside the sql...I would use a dim'd value inside the sql statement.
3) I think you problem is in the sql statement you have "set[Special]..." it should be
"set Special ..." ....same comment for the Where[Special]...

From the Access Help for sql Update:

Sub UpdateX()
Dim dbs As Database
Dim qdf As QueryDef

' Modify this line to include the path to Northwind
' on your computer.

Set dbs = OpenDatabase("Northwind.mdb")

' Change values in the ReportsTo field to 5 for all
' employee records that currently have ReportsTo
' values of 2.
dbs.Execute "UPDATE Employees " _
& "SET ReportsTo = 5 " _
& "WHERE ReportsTo = 2;"

dbs.Close
End Sub


It is not obvious what is causing the error, you could try commenting the On Error GoTo Err_Execute and let the procedure go to debug, at which point you can see the error and single step thru.

Thanks for reply tried removing brackets but got same result. did not mention I do get first message box asking for start number. Could you please explain more 'On Error GoTo Err_Execute' as I mentioned I do not know a lot about code.

Line #6 in your posted code is "On Error GoTo Err_Execute". If you place a single quote >> ' << in front of that statement it will read " 'On Error GoTo Err_Execute ".
That single quote comments the code line. Currently when an error occurs after that line, the procedure goes to the Err_Execute line that displays your error message. As I said, I did not see anything obviously wrong in your code, but then, I do not know your table either. By commenting that statement and then going to the form and doing the your process, when the error occurs, you will be directed to the VBA code (Debug mode). That will at least show you which statement had the error. When the error occurs, you get the options to end or debug....select Debug. The line in error will be highlighted yellow. A small yellow arrow will be there as well. You can change the code, drag the yellow arrow up a line or 2, and at the top (File Edit View Insert Debug....) select Debug and single step or press PF8 to step line by line through the code to the error. You can do this countless times until it works (done that ... been there) or report back to the forum, the line that failed. Hope this helps.
Bob
Oh, by the way...I hope your Special is NOT defined as Autonumber. Renumbering Autonumber fields is not recommended and extremely difficult (not impossible but ... you really don't want to know what needs to be done).

The Special number is not an autonumber field. Have included the single quote and the error line is (shows line 21 on my screen)
Set Lrs = db.Openrecordset(LSQL).
Did make 2 other changes in lines 17 and 25. Changed the word DATA to Prizes which is the name of the Table.

assuming the special places can never have duplicate values, then the following will work

lets say you have special places 1,2,3,4,5,6
and delete entries 1 and 4

so you need to renumber 2,3,5,6 to be 1,2,3,4

so have a select query sorted on the special position

and then this code snippet will renumber all the special positions, starting from 1



dim newpos as long
newpos=1

set rst=openrecordset("my special query")

while not rst.eof
rst.edit
rst!specialplace=newpos
newpos=newpos+1
rst.update
rst.movenext
wend

----------
I think this will probably work, even if you reseed from a starting value other than 1. I am not sure whether doing it your way is guaranteed to work - there may be an issue with your update statement - since the statement "update tblx set field = 12 where field = 6" for instance, may actually cause MORE than 1 record to be updated to the new value 12, if not every item is guaranteed to be unique

Iterating a recordset will not give this issue, as even if there are two items with the same initial value they will be processed separately.


--------------
one other point
in your error handler, you need a resume statement, otherwise you cannot reactivate this or another error handler.

Thanks for your reply. The principle you have set out is correct. I have the field set to have Duplicates because when I add a new "Special" I have to add it at the bottom of the file and I can do that by entering the field with a decimal point. Suppose the new record must be placed after the 2nd I would enter it as 2.5 and because the field is indexed it moves the record into the correct place. That's the principle anyway. Going back to my code I have removed the DAO in the Dim statement (Line 5)to match Line 21 (Set Lrs = db.OpenRecordSet(LSQL). The Code runs right through but in fact it is not renumbering the file. While I am trying to get this to work I only have 5 records in the field. I have already raised new issues 1. after making a new entry at bottom of file I need to close the form then reopen the form with the new item where it need to be. When I click on the button to renumber the Special Numbers the code runs okay and I get the message Renumbering the Special numbers has successfully completed. 2. Now I have to close and then open the form again and althought I entered 2 as the starting point (as above having entered 2.5) the only change has been to the first entry and it has been renumbered 2 also. So my 5 records are 2 2 2 4 5.

1) The code from Gemma is an excellent example that you should use as a model.
2) To see the updated results use the Requery command.
Was fishing yesterday so could not get back to you...you may want to post your modified code. By the way, a code window which will retain your indenting, etc can be used by putting {code} prior to the code and {/code} after the code....replace the {} with [].

Hope fishing was okay. Still struggling with what I have, seem to be going backwards. Looked a Gemma's code looks a lot simpler, but not sure how and where to apply. She did mention about Duplicates which I need to "insert" new Special. Get lost with the terms error handler and resume statements. Very frustrating not knowing enough about code.

Fishing was great, got a couple nice Bass, one was over 2lbs. Made a great dinner today.
I read your response to Gamma more carefully, just skimmed it earlier. May I suggest, you renumber first, then add the new record. For example, you want to add a new entry as #3, therefore, everything greater than #3 must be renumbered ... #3 become #4, #4 become #5, etc. When the renumbering is completed, the new entry can be made by adding #3. If you wish to remove entry #3, that can be done as part of the renumber as well, by deleting #3, and then hitting the renumber function. The code will always asume the items following the LSpecial will be sequential integers.

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("Specail") = LSpecial Then
If Msgbox("Making space to add Special Number #" & _
LSpecial & ". Continue?",vbYesNo, _
"Add a new Special Number") = vbYes Then
Do Until rs.EOF
' Renumber Special Numbers
rs.Edit
LSpecial = LSpecial + 1
rs("Special") = LSpecial
rs.Update
rs.MoveNext
Loop
UpdateSpecial = True
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
Else
Msgbox "No records found with Special > " & LSpecial
End if
Else
' Record preceeding LSpecial is not found
Msgbox "No record found with Special = " & LSpecial
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

the point sbout the duplicates was that if you have, say a series

2,3,5,6 then you can renumber them as 1,2,3,4 without a problem

but, if you have say

2,2,2,3,3,4, and you want to renumber them as 1,2,3,4,5,6, you may have a few issues

first, unless you have some other way of distinguishing them, you wont know which order you process the "2" values, which may or may not be an issue.

more importantly, if you dont think carefully about the update mechanism, you may find that you are changing the sequence as you modify records - ie changing a 2 to a 3 or 4 will then give you multiple values of 3 or 4 which may affect subsequent operations - and having a query that changes value 2 to value 3 will change ALL the values 2 to value 3

------
so if you base the renumber on processing a single (ie static) query a line at a time, Access will effectively process a temporary copy of the query, and changes you make to the data IN that processing, will not affect the processing.

Gemma, I think BillBee has the indexed field Special set up as integer, and has duplicates to allow a decimal entry to place the record in the correct position. The decimal value gets stripped by Access, but Access slots it appropriately as the 2nd occurance. This gimmick works, but is a gimmick. BillBee, correct me if I am wrong, but I believe you want an end result of sequential integer values in the Special field.
If this is correct, the code in my previous post should work...you may want to put a description on the form to instruct the user to renumber, then add...delete and renumber, AND adding no longer requires you to enter a decimal, you can actually enter the true value. BillBee, if you do want to have duplicates (EG: 3 People tied for 4th place), let me know and I can provide code to renumber and maintain the duplicates in higher positions. (EG: 1, 2, 3 , 4, 4, ,4 and you want to add a new #3 results in 1, 2, 3(new), 4(was 3) , 5(any of the #4), 5(one of the 2 unchanged #4), 5(the remaining #4)). If the order of the duplicates must remain the same, then gemma is correct, some other property must be known to retain that sub-sequence.
Smiles
Bob
Added thought,
If you infact do not want duplicates in the end result, and want to set duplicates to no on the Special Index, then the update process for add changes, since you have to start at the highest value (last record, and move backwards through the records until you hit the new Special number to be added. The code would look like this:

If Msgbox("Making space to add Special Number #" & _
LSpecial & ". Continue?",vbYesNo, _
"Add a new Special Number") = vbYes Then
rs.MoveLast
Do Until rs.TOF = 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

Yes I have indexed the Special field. I do want sequential numbers they could without duplicates. I have only used duplicates allowed to be able to place the entries where I want them to be. To explain what I am doing. The whole file is to do with a Bird Show. Receiving entries Placing winning bird for various sections, Producing individual certificates for winners showing awards etc. The Specials table is a list of 500 plus awards. It is related to class and cage numbers to receive each award. This Schedule has 17 sections for each type of bird exhibited. Each section has a small list of awards from each specialist body, plus the national body awards 2 medals. Each specialist body may or may not make change from year to year but the national body rotates its awards through the various sections each successive year. Previously I have restructured the Schedule in Excel because it has been easier to Insert a new item where you want it then paste it into Access. I would like to be able to do this process within access. Next I have tried your code Magicman and get the following. When I run it the first line is highlighted with the arrow in the column but the code is stopping on line 59 highlighted in Blue. I get a message box Compilation Error 'Else without if'

Sorry I took your code and typed in what I thought would work. The code is missing 2 end if statement. One Immediately preceeding line 59 and another that should preceed the rs.close. This code example handles both adding a new record without needing a decimal point and deleting an existing record. The second block of code is a replacement for the Add a record code if you want to set Dupicates = no on the index.
Here is the code if you leave duplacates = yes

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("Specail") = LSpecial Then
If MsgBox("Making space to add Special Number #" & _
LSpecial & ". Continue?", vbYesNo, _
"Add a new Special Number") = vbYes Then
Do Until rs.EOF
' Renumber Special Numbers
rs.Edit
LSpecial = LSpecial + 1
rs("Special") = LSpecial
rs.Update
rs.MoveNext
Loop
UpdateSpecial = True
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


Here is the code if you want set duplicates = no on Special

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("Specail") = 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.TOF = 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


I have actually compiked this code, but that doesn't mean it is perfect. I wanted to give you a fairly good starting point....that is all. This should work though.

Smiles
Bob

Bob. Thanks for all your help so far. Must be frustrating helping non techies like myself. Will try latest code later this afternoon. Couple of questions. 1. can you recommend a staring point for learning more about code, and secondly before I started down this track I was looking at other code which required a second Identical Temp Table with same named temp fields, the Data was entered through a form same way as before ie using the decimal point on an indexed field then when the code ran it rewrote renumbered list back to empty Table. As A point of interest can I Post this to Thread for you to look at or should I start a new Post
Bill

In general practice, the correct way logically, is usually the best way in code. Temp tables, and gimmick get arounds will just make things worse down the road, not that they do have have their place.

The first place to start in learning a coding language such as Visual Basic is to:

1) Understand the data storage ... do a google scan on the 5 levels of data normalization, you will probably understand normalization to step 3 ... steps 4 and 5 are accurate but for most applications, not feasible....a Database Adminstrator saying ... normalize till it hurts, de-normalize until it works. If your tables are not structured properly, the applications will be a nightmare to code and maintain.
2) Plan...analyse your requirements, ask dumb questions, and when you know your requirements 100&#37; you will be ready to code. After a day of coding, you will realise you only know 20% of the requirements, and will have to go back and analyse again.
3) To learn Access and VB...read the tutorial, then the help pages that come with Access. After reading it once, read it again. Then use the northwinds database (sample from IBM) and read the code in that comes with that database. This will force you to read the help several times. Then come back to the forums here and read the forums. The more you read, the more you learn.
4) Now you are ready to try to write your application. Before you create any form, chart the forms required, the process flows (what does the user do, what path is followed to get an answer) and identify the data for each step of the process. That data becomes the input/output, combo, etc fields on the form. Make sure you have all the data integrity rules (is it requires, what values are valid, what table(s) does it impact, etc). Go ahead and create that form.
5) Oh dear, now you need code to support things you want to do that are beyond normal Access capabilities. (Access is very powerful and an entire sofisticated application can be built without 1 line of code.) Make sure you read the new help that comes with Microsoft Visual Basic. You will find there are several ways to access data using visual basic. You can use DAO, ADO, T-SQL if your connected to a SQL Server back end, or you could write specific Other Data Base Code statements and pass them through Access to that database handler. DAO is an old but easy to understand and use set of commands. Understand the data definitions, and try some code. Experiment with the debuging tools available (place stops, view value of variables as you single step through the code). Even if your program works.
6) Now that you have created several tables, a dozen forms, and hundreds of lines of code. Stop, look at he mess you have created, think about how it could be done better. Restructure your tables, create new forms and code (hopefully less will be needed now) and then continue on with the project. Thats right, trash the first effort, because if you are stuborn, you will lose hundreds of hours trying to force things to work with the bad starting point.
7) Set up milestones....im 1/4 of the way done. At each milestone, test ... test...test, and remember that a test plan that finds no errors is a failed test plan. The test plan should also roll back to the user requirements...are you doing what the user wants, not what you think they want?
8) After many many hours you will start to believe you can program. After many months of programming you will believe you are a good if not great programmer, after many years of programming, you will realise you barely know a fraction of what you should know, and your programming skills are great but limited to a fraction of the whole. (I started programming in 1970...created the ATM microcode and server code, designed the first 16 bit encryption algorith, built countless major systems, was one of the IBM's top experts who help Bill get Dos going so we could sell the IBM PC's (64k PC's for $8000 each ... floppy drives extra and 20 meg hard drives coming soon at $1220) and today I can honestly say I know almost 5% of the total wealth of programming languages, databases and tools). Just identify what your personal goals are and focus on a language, master that to the degree that you know perfectly how to read the help and manuals on the language. Once you have achieved that status, you can attempt to learn the next language/database.

Appreciate advice above you sent. Working with No Duplicates code. Picked up a couple of typing errors. Line 17 FROM Prizes Should have read FROM Specials. (Major error from me as I should have had field name as SpecialNbr but renamed the field to Work with code. Too confusing With Field Special and Table Specials) Line 30 changed TOF to EOF.
Tried to renumber first, to create a space for new record as you suggested. Entered #3 in input box and get a message box "No Records Found With Special > 2". Placed single Quote > ' < in front of statement "On Error GoTo Err_Execute" to find the code is stopping on Line 63 "End If"

Your starting to learn. You caught the TOF error....but did you change it to the correct statement.
From Microsoft Help:
If you use the MoveNext method while the EOF property is set to True or the MovePrevious method while the BOF property is set to True, an error occurs.
You should have changed TOF to BOF (BOF = Beginning of File, EOF = End of File).
Since we are moving from the last towards the first, we are approaching the beginning of the file.
I also have a second error that is not obvious, and was hoping you would set a stop at the function statement ( click the tan bar to the left of the word Function and that will place a program stop (a Brown dot)....click it again and you remove the stop). When you go back to the form and cause this function to occur, the Visual Basic screen will pop up. You can then press PF8 to single step through the code. You can place the cursor over the code literals and the value will be displayed. The yellow arrow shows you which statement you are about to execute. You can drag that arrow back to the top to run previous code again. By the way, the error is in the SQL statement which should be >= (not =). Please try the debugging functions I just described. They are one of your most valuable tools.

Placed ">" in line 17 column 28. Have been working with debugging several times as you suggested. Entered #4 in first input box. Watching as I scroll down every thing works okay, worked through loop 4 times. Go back to Table and see that the records after 4 (Only 8 in List). Back to Visual Basic Screen and continue down to line 75 MsgBox "Updating Special Number Failed". Also do not understand message on Line 39 MsgBox "Special number entered is > largest number or invalid". Get same result when working from Form
with same result and gap in Specials List. Did notice other things(?) sometimes when I went back to Table instead of Arrow head in Left hand column there was a circle with a diagonal line in its place. Also couldn't edit in table. You said ask the dumb questions. Line 15 - 1 then Line 24 + 1 to LSpecial. What was the reason for this? Lastly sometimes when I have a error in code when I go back to the Form it has frozen. Any comments. (Using Access 2003).
Bill

Great, your getting use to the tools. Try looking for a mis-spelled special...spelled specail....that is causing your problems. I left the most obscure error (yet one that commonly happens) till this point. If you use the Explicit option, this type of error would not compile clean. If you need this immediately, let me know...I have a copy of your database and form with working code. I have been leaving errors in to help you learn. You are almost there. I know...it's like school, but you mentioned you wanted to learn, so the answers I have submitted had errors imbedded. I do this at the office as well (your not alone) to people who work for me and these people get big bucks/hour. If I tell them the answer, they put it in place and are back asking me (at bigger bucks) a week later the same question. If I give them direction, they learn and don't come back with that question again. In fact, they usually spread the knowledge because they are proud they did it themselves (meaning even fewer questions come to me).
Once the errors are resolved, the other questions go away. You should use the resume (check help) after the error routine. If the visual basic debug screen is still open, the form will remain frozen, close the visual basic screen. I built a form, not sure if it like yours, and I have no trouble updaing the data.

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.

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.

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

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.

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.

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,16 o.
You then change entry #3 to #9,
The result is 1a,2c,4d,5e,6f,7g,8h,9c,10i,11j,12k,13l,14m,15n,16 o.
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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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.

Bill,

I know.

Wayne

Bob. I have a command button which runs a macro. The macro runs the code which is a module. Does that make sense. I did try to run the code as a an event procedure from On Click But was having trouble. That aside I have re-looked at the requery and am getting "DoCmd.Requery" to work. Yes I was having trouble with incorrect data entry. Say moving the number to 7 typed 8 at first stage then typing 7 to replace the number instead of 8 again

The DoCmd.Requery is ok, but if you launch the function with a macro, you should do the requery with the same macro. Open the macro, and on the blank line below the function call, click the selection box, and you will find Requery is a selection. This way you do not need vba code.
Your next step (if you wish) is to design a form that does not need carefull entry to avoid duplicates. The design could also permit a block of entries to be processed with one click. The most complicated design would be to allow you to change several non consecutive entries with one click by tagging each entry with the new position, untill all entries are tagged, then process the changes in one step. That is very possible, but takes a lot of thought and desgin. There are several ways to accomplish that task.
Smiles
Bob

Done that okay. Yes I want to go to next step however before that, with the code I have I cannot see a way where I am simply deleting an entry from the list. I would have used a query on another Command Button to Open a Table used the record selector to delete record then go back to the code to renumber. Can code be written for this new button to open a form as a Datasheet to delete or ask for Special Number to be selected and then automatically renumber the following records. Bill

That is why I suggested a new form be designed. Access is a very powerful tool, but in that power comes an array of options. There are many, many ways to do the same thing in Access. So any suggestion I have, may or may not be the way you would like to do it. Myself, I am very good at using the help and reading the forums. I satisfy most of my requirements using VB code, and I tend to avoid macros. Simply because in a future release, the macro can change what it does, where my code will generally always work far into the future. With code, I can control the process, with a macro, it sometimes controls me....grrr...which I do not like. One of these days, I will get stuck and ask for help, but so far, so good......
I do not know your form, but, assuming it is record based, then when you have the record you wish to delete as the active/current record, that record can be easily deleted.
Add a command button, the command button wizard should pop up. In the categories, select Record Operations, and in the Actions, Select Delete Record, on the next page ... click text and enter Delete Current Record. That should give you a quick way to delete a record.

Bob.Working on Record Deletion setup. Would like to proceed with new form to that would allow easier entry of records. Also should I go back and try to get the code to work from a command button to avoid the Macro. I am going away on Saturday not returning home till Wednesday next week. That will give you a break. Wondering if I am allowed to continue this thread. Bill

Hi Bill,
The forum is a place where a question is asked and hopefully you can get an answer. Since you are learning, you have had a number of followup questions. I believe you have a fair start now, and if you believe designing a new form will improve your application. Feel free to continue with questions. I do strongly suggest you attempt to get a form working, using Access form design, Macros where applicable, or VB code to achieve your results. Use the Access help and don't be afraid to try it, debug it, try again, etc until it works. The best way to learn is to do it. Second suggestion is to step back and take some time to identify all the possible things you might want to do with your data, document that for yourself, then design one or more forms to perform those requirements. You may want one form for each action or one form with multiple actions available on that form.
Planning saves a ton of development time.
Smiles
Bob

Bob. Have already had pen and paper out looking at altering at least one table. Would like to proceed as follows if that is okay with you. I will try to get something in place to run the code and remove that macro. Also you said in post 42 where one does not need careful entry to avoid duplicates and that a block of entries could be processed with one click. Would like to get that in somehow. Do not see the need for more complicated Procedures, but will need some guidance for that. Bill

I am back to assumption mode. I assume you have a main form (Sections) and a subform Specials. The Subform is a continuous form or a datasheet. You want to be able to
1) add a special to the subform
2) delete a special from the subform
3) move a special from postion x to position y within this section
4) move a special from position x to position y within another section
5) move specials x to x+no from this section to position y to y+no in another section
6) after any activity (1-5) occurs, the specials in each affected section must have consecutive numbers from 1 to the end
There was a lot of gueswork in this. Please correct any errors.
Smiles
Bob

Form at present just a separate file outside database till I get what I wanted. Set as a Datasheet. This file is a complete prize list up to approx 560 entries. The sections I mentioned are not separated within this list. Eg the numbers 1 to 50 are for Yorkshire Canaries, 51 to 100 Norwich Canaries, 500 to 550 Lovebirds. Each year the National Federation awards two medals (one each in different Sections)and what I meant previously was, this year say they were for Yorkshires, next next year Norwich, next year next section and so on. What you have provided works. I can move Specials up and down the list. I can add and delete entries. Each operation is a little different and entering what number for each procedure which is taking a little time to come to grips with. I was thinking your thoughts may have been simplifying that and that multipule entries could be moved. Bill

Ok, so is this not the time to create a database?
EG:
Sections table
SectionID
Section Description
Section info
Srction National this year (Y/N)
Special table
Special SectionId
Special Prize Number
Special Prize Info

Yes it is a good time to new DB (or did you mean Table).Off for a few days need to look more closely at this when I get home.