Renumber a List

[BillBee]

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

 

[MagicMan]

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.

 

[BillBee]

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.

 

[MagicMan]

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

 

[BillBee]

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.

 

[gemma-the-husky]

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.

 

[BillBee]

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.

 

[MagicMan]

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

 

[BillBee]

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.

 

[MagicMan]

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

 

[gemma-the-husky]

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.

 

[MagicMan]

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

 

[BillBee]

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'

 

[MagicMan]

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

 

[BillBee]

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

 

[MagicMan]

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

 

[BillBee]

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"

 

[MagicMan]

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.

 

[BillBee]

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

 

[MagicMan]

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.