Results 1 to 7 of 7

Thread: autonumber

  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: autonumber

    Hello,

    I wonder if there's any possibility to keep autonumber field in the table without any "holes". For example:
    -I have a primary key called ID which is of an autonumber type
    -I have 5 records
    -I deleted 3rd record
    Now I have 1st, 2nd, 4th and 5th record (these are ID's), but I would like to have 1st, 2nd, 3rd and 4th record and would like to have possibility to create new 5th record.

    If anybody knows the answer - please let me know.

    Thank you,
    Greetings,
    Pawel

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Sorry, it is not possible

    S-

  3. #3
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Lightbulb There a few alternatives

    I do the following in 3 of my forms now.

    In the Before Insert Box I have a macro that adds the next number:
    Echo Echo On (No)
    SetValue Item ([Forms]![FormName]![ControlNameID]
    Expression (DMax("[ControlNameID]","TableName")+1)
    StopMacro

    I like doing it instead of an Auto Number just for the reason you gave.
    You can always manually go in and enter a number 3 record as needed. But this works very well for me. Also, by doing it this way if I want to cancel before all the data is entered I just ESC and no number is added.
    Gotta to do some code

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    I see this question asked a lot - do you mind if I ask why you dont want any gaps? I know aesthetically its easier to not see any gaps - but I'm assuming you are just creating a primary key (some ID).

    Would it really make a huge difference if there was a gap in the sequence?

    I'm just curious - I await your answer.

  5. #5
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Smile One Idea

    I just is decieving that maybe someone seeing the number on a form may think you have a lot more records than you really do?

    Just a thought
    Gotta to do some code

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I do the following in 3 of my forms now.

    In the Before Insert Box I have a macro that adds the next number:
    Echo Echo On (No)
    SetValue Item ([Forms]![FormName]![ControlNameID]
    Expression (DMax("[ControlNameID]","TableName")+1)
    StopMacro

    I like doing it instead of an Auto Number just for the reason you gave.
    You can always manually go in and enter a number 3 record as needed. But this works very well for me. Also, by doing it this way if I want to cancel before all the data is entered I just ESC and no number is added.
    Consider,

    tableA
    id, val
    --------
    1 A
    2 B

    1)
    insert into tableA 'C'
    Maximum number is 2, therefore id is 3.
    2)
    insert into tableA 'D', id = 4;
    3)
    delete from tableA where val = 'C'
    4)
    tableA
    ---------
    1 A
    2 B
    4 D

    To remove "gaps" completely the table must be updated after every delete DML statement where the id of the deleted record is less than the maximum id of the table.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Mar 2004
    Posts
    16
    I think you're right - this should be done just after deleting a record.

    And the reason for doing that is simple as syrfacesys said - somebody might thought that there is so many records in the database.

    Thanks a lot,
    Pawel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •