Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25

    re sequencing consecutive number field after delete or addition to form or datasheet?

    Hello, this is my first post. Ive been reading the web for a few days and trying to design an access form around a table I created. The form is pretty much complete, except for one detail. Im hoping someone here can help me out, as I just cant seem to find much info on this.



    I have a number field called "ID" in my table that is not a primary key and is not an autonumber, although it does not allow duplicates. Every record entered into my form is given a new number in a consecutive order 1,2,3,4,etc. I would like it to work like excel where I can delete or add a row anywhere in the form (i.e. adding a record after row 200, even if there are already 500 total rows) and the row number will always stay sequential, never skipping a digit.

    Right now I have this statement running after update:
    Private Sub Form_BeforeInsert(Cancel As Integer)

    ID = Nz(DMax("ID", "Schedule Status")) + 1

    End Sub

    Its very simple and allows me to manipulate numbers as required and gets around autonumber skipping numbers utilized by records that have been deleted, but if I have 500 rows, that is a lot of typing to renumber if I delete or add a record that is not the last row.

    I am new to databases, so my programming skill is close to null, but I will learn. This is just driving me nuts.

    This form is given to coworkers and customers and they just cant seem to get over a skipped number, which is why autnumber does not work. When I used autonumber and deleted records, I would have larges gaps in the numbering scheme and would constantly get emails and phone calls about missing entries (because autnonumber does not reuse previous numbers). Its even worse if I add a new record at row 200, but the ID order went 200,522,201.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Assuming you are deleting the records from a form, why not have a table of deleted numbers and check there first before using the DMax() method. You could create a UserDefinedProcedure in a standard module that does all of the work for you so the code in the BeforeInsert code would be simplified.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Join Date
    May 2010
    Posts
    339

    Untested air code

    Try this:
    Code:
    Dim strCriteria As String
    Me.ID = Nz(DMax("ID", "Schedule Status", strCriteria),0) + 1
    Is this a multi-user database? Imagine three people assigning the same id number to the same table?

    Regards

    Richard

  4. #4
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by RuralGuy View Post
    Assuming you are deleting the records from a form, why not have a table of deleted numbers and check there first before using the DMax() method. You could create a UserDefinedProcedure in a standard module that does all of the work for you so the code in the BeforeInsert code would be simplified.
    The problem for me isnt keeping track of deleted records for numbers that I can use over again, its that if I delete a record, I have a gap in the numbering sequence. To make things even more difficult, this form is used to manage a project so I have series of dates in each row (scheduled, actual, forecasted) and while I number the rows in order from least to greatest for easier tracking, the true sort order is by scheduled date, then by work site.

    I just want to be able to delete a row out (usually this happens because I screwed up and entered duplicate information, or I find out later that it was unneeded info) and have all the rows that come after it renumber so that there is no gap. Same with adding a new row. Unfortunately I cant just add a new row at the end and then reorder by number, due to the other constraints
    .
    Quote Originally Posted by Access_Blaster View Post
    Try this:
    Code:
    Dim strCriteria As String
    Me.ID = Nz(DMax("ID", "Schedule Status", strCriteria),0) + 1
    Is this a multi-user database? Imagine three people assigning the same id number to the same table?

    Regards

    Richard
    Its viewed by others (hopefully), but data entry is done only by me.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Access_Blaster brings up a good point about MultiUser which is why I *also* use the BeforeUpdate event of the form to get the number again (smaller window for duplicates) and be prepared to retry if a duplicate number. The number will probably be the same as the one retriever in the BeforeInsert event but if not the user will probably never even know that fact. You should also set a flag so you can delete this number from that "Deleted Number" table in the AfterUpdate event of the form.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Quote Originally Posted by jc1234 View Post
    The problem for me isnt keeping track of deleted records for numbers that I can use over again, its that if I delete a record, I have a gap in the numbering sequence. To make things even more difficult, this form is used to manage a project so I have series of dates in each row (scheduled, actual, forecasted) and while I number the rows in order from least to greatest for easier tracking, the true sort order is by scheduled date, then by work site.
    Would it work to just create a ReNumbering routine and run it when you need it, simply replacing the existing numbers?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by RuralGuy View Post
    Would it work to just create a ReNumbering routine and run it when you need it, simply replacing the existing numbers?
    Yes. But I have no idea how to do it. What I could do is go into the datasheet view and order the records by scheduled date, then have the records simply numbered by least to greatest in the order that they are already in.

    If I could figure out how to sort by scheduled date 1st, then alphabetical in datasheet or form view using a routine and then have the current order renumbered that would be perfect. Im willing to start small though.

    Programming like speaking chinese for me. However, if there is anyway anyone can give me some hints, I can probably figure something out.

  8. #8
    TG_W is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    You said the form is given to co-workers and clients, but you are the only one entering data - I apologize if this come off rudely, but are you giving them a hard copy? Or are they seeing this in some soft copy form? If the number is that unimportant to the "meat" of the data they are viewing, then why do you need it? you can drop it and save yourself the headache.

    Just trying to help simplify life from one project reporter to what sounds like another.

  9. #9
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by TG_W View Post
    You said the form is given to co-workers and clients, but you are the only one entering data - I apologize if this come off rudely, but are you giving them a hard copy? Or are they seeing this in some soft copy form? If the number is that unimportant to the "meat" of the data they are viewing, then why do you need it? you can drop it and save yourself the headache.

    Just trying to help simplify life from one project reporter to what sounds like another.
    The ID field serves no purpose on a row by row basis, however, Ive found that without it, if I am on a conference call with others or looking for a single record on my own, a row number helps to make sure we are all looking at the same record. For instance I might have three rows consecutively and the only difference on a particular day in the 18 fields of each row for the most part is that the name of each work site differs by one number (i.e. L.A. Plant 6, L.A. Plant 7, etc which are the actual names not my laziness in naming). without a numerical identifier all the way to the left, in a table of hundreds of records, its just too hard to make sure everyone is looking at the same thing.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Start by creating a query of your table with the query builder. Then set the sort direction on the fields you want to sort on. You can move the columns by selecting it and dragging it to the left to change the sort order. It will sort the left most column first. One you have the RecordSet in the order you want then post back and we can renumber the column.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  11. #11
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by RuralGuy View Post
    Start by creating a query of your table with the query builder. Then set the sort direction on the fields you want to sort on. You can move the columns by selecting it and dragging it to the left to change the sort order. It will sort the left most column first. One you have the RecordSet in the order you want then post back and we can renumber the column.
    I have a question. I just found the properties tab of the table where I can set the order that I would like. If I set the order there, would I still need a query?

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    A query has far more power than a table and it is simply a string of SQL instructions that you can see in the SQL View of the query. YES, create the query is the short answer.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  13. #13
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    ok, finished the query. Do I need to use the recordset function for something yet? Im sort of at a loss on that one, lots of stuff online, but the instructions seem to vary.

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    So the records return in the order you need them in when you view the query in the Datasheet view, right? Did you look at then SQL view to see what it takes? Pretty simple right?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  15. #15
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by RuralGuy View Post
    So the records return in the order you need them in when you view the query in the Datasheet view, right? Did you look at then SQL view to see what it takes? Pretty simple right?
    LOL, actually, I never noticed the sql view before. Very simple.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Delete all records in a field
    By cotri in forum Forms
    Replies: 6
    Last Post: 01-29-2010, 02:44 PM
  2. Replies: 0
    Last Post: 07-19-2009, 12:19 PM
  3. Replies: 0
    Last Post: 12-25-2008, 10:05 AM
  4. addition with 0
    By Peljo in forum Access
    Replies: 0
    Last Post: 02-28-2008, 08:12 PM
  5. Form - Datasheet View
    By kalove in forum Forms
    Replies: 0
    Last Post: 06-20-2007, 11:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums