Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

autonumbering woes -- missing numbers caused by deleted records

Discussion in 'Information Technology' started by Patience, Nov 9, 2005.

  1. Patience

    Patience
    Expand Collapse
    Guest

    I discovered some duplicate records in my database, so I deleted them. The
    only problem is that each record is autonumbered, and now when I add a
    record, it says I have 100, but there are really only 92 (due to the deleted
    ones). Is there a way to force Access to create records with the numbers that
    were removed?

    Thanks so much!
     
  2. Loading...

    Similar Threads Forum Date
    Is SARBAT KHALSA some magic word that will cure Sikh woes? Sikh Sikhi Sikhism Jan 9, 2016
    Leaders Blame Babudom for Land Woes of Sikhs Hard Talk Aug 25, 2013
    India Bills worth Rs 1600 cr pending as Punjab’s Fiscal Woes Worsen Breaking News Feb 7, 2012
    Heritage Preservation Woes: French Officer's Monument in Shambles History of Sikhism Jun 9, 2011
    Partition Partition Woes History of Sikhism Jan 14, 2010

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    No, there isn't, nor is there any reason for Access to do that.

    Autonumbers exist for one purpose: to provide a (practically guaranteed)
    unique value that can be used as a primary key. The presence of a gap in the
    number doesn't impact that purpose. In fact, it's actually usual not to show
    the user the value of the Autonumber field. If you happen to use replication
    to make copies of your data (so that users at multiple sites can update it),
    you'll find that the Autonumbers are changed from sequential to random, with
    no way to change back to sequential.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Patience" <Patience@discussions.microsoft.com> wrote in message
    news:32AB0D88-133A-4238-8CE7-83A72916A337@microsoft.com...
    > I discovered some duplicate records in my database, so I deleted them. The
    > only problem is that each record is autonumbered, and now when I add a
    > record, it says I have 100, but there are really only 92 (due to the

    deleted
    > ones). Is there a way to force Access to create records with the numbers

    that
    > were removed?
    >
    > Thanks so much!
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    Using Recordset you can add records in the AutoNumber, and then edit the rest
    of the records, or just isert them when you insert the auto number

    Dim mydb As DAO.Database, myrec As DAO.Recordset
    Set mydb = CurrentDb
    Set myrec = mydb.OpenRecordset("Select * from TableName")
    myrec.AddNew
    myrec![AutoFieldName]= MissingNumber
    myrec.Update

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Patience" wrote:

    > I discovered some duplicate records in my database, so I deleted them. The
    > only problem is that each record is autonumbered, and now when I add a
    > record, it says I have 100, but there are really only 92 (due to the deleted
    > ones). Is there a way to force Access to create records with the numbers that
    > were removed?
    >
    > Thanks so much!
     
  5. Patience

    Patience
    Expand Collapse
    Guest

    Re: autonumbering woes -- missing numbers caused by deleted record

    Ok, I understand the concept. But now I have a follow-up question. I was
    (until I figured out I have missing record numbers!) using the record number
    to determine the total number of records.... how can I do that now, if I
    can't force Access to recreate those record numbers?

    Thanks,
    Patience

    "Douglas J Steele" wrote:

    > No, there isn't, nor is there any reason for Access to do that.
    >
    > Autonumbers exist for one purpose: to provide a (practically guaranteed)
    > unique value that can be used as a primary key. The presence of a gap in the
    > number doesn't impact that purpose. In fact, it's actually usual not to show
    > the user the value of the Autonumber field. If you happen to use replication
    > to make copies of your data (so that users at multiple sites can update it),
    > you'll find that the Autonumbers are changed from sequential to random, with
    > no way to change back to sequential.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Patience" <Patience@discussions.microsoft.com> wrote in message
    > news:32AB0D88-133A-4238-8CE7-83A72916A337@microsoft.com...
    > > I discovered some duplicate records in my database, so I deleted them. The
    > > only problem is that each record is autonumbered, and now when I add a
    > > record, it says I have 100, but there are really only 92 (due to the

    > deleted
    > > ones). Is there a way to force Access to create records with the numbers

    > that
    > > were removed?
    > >
    > > Thanks so much!

    >
    >
    >
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Re: autonumbering woes -- missing numbers caused by deleted record

    One way is to use the DCount function on the table:

    DCount("*", "MyTable")

    In a form, you can move to the end of the RecordsetClone, move to the end
    and check the RecordCount property:

    Dim rst As Recordset

    Set rst = Me.RecordsetClone
    rst.MoveLast
    MsgBox "There are " & rst.RecordCount & " records"


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Patience" <Patience@discussions.microsoft.com> wrote in message
    news:447E8A5F-8777-40D8-BB64-BEDCA22F9C42@microsoft.com...
    > Ok, I understand the concept. But now I have a follow-up question. I was
    > (until I figured out I have missing record numbers!) using the record

    number
    > to determine the total number of records.... how can I do that now, if I
    > can't force Access to recreate those record numbers?
    >
    > Thanks,
    > Patience
    >
    > "Douglas J Steele" wrote:
    >
    > > No, there isn't, nor is there any reason for Access to do that.
    > >
    > > Autonumbers exist for one purpose: to provide a (practically guaranteed)
    > > unique value that can be used as a primary key. The presence of a gap in

    the
    > > number doesn't impact that purpose. In fact, it's actually usual not to

    show
    > > the user the value of the Autonumber field. If you happen to use

    replication
    > > to make copies of your data (so that users at multiple sites can update

    it),
    > > you'll find that the Autonumbers are changed from sequential to random,

    with
    > > no way to change back to sequential.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Patience" <Patience@discussions.microsoft.com> wrote in message
    > > news:32AB0D88-133A-4238-8CE7-83A72916A337@microsoft.com...
    > > > I discovered some duplicate records in my database, so I deleted them.

    The
    > > > only problem is that each record is autonumbered, and now when I add a
    > > > record, it says I have 100, but there are really only 92 (due to the

    > > deleted
    > > > ones). Is there a way to force Access to create records with the

    numbers
    > > that
    > > > were removed?
    > > >
    > > > Thanks so much!

    > >
    > >
    > >
     
  7. Bill Edwards

    Bill Edwards
    Expand Collapse
    Guest

    Re: autonumbering woes -- missing numbers caused by deleted record

    SELECT count(*) from TableName
    "Patience" <Patience@discussions.microsoft.com> wrote in message
    news:447E8A5F-8777-40D8-BB64-BEDCA22F9C42@microsoft.com...
    > Ok, I understand the concept. But now I have a follow-up question. I was
    > (until I figured out I have missing record numbers!) using the record
    > number
    > to determine the total number of records.... how can I do that now, if I
    > can't force Access to recreate those record numbers?
    >
    > Thanks,
    > Patience
    >
    > "Douglas J Steele" wrote:
    >
    >> No, there isn't, nor is there any reason for Access to do that.
    >>
    >> Autonumbers exist for one purpose: to provide a (practically guaranteed)
    >> unique value that can be used as a primary key. The presence of a gap in
    >> the
    >> number doesn't impact that purpose. In fact, it's actually usual not to
    >> show
    >> the user the value of the Autonumber field. If you happen to use
    >> replication
    >> to make copies of your data (so that users at multiple sites can update
    >> it),
    >> you'll find that the Autonumbers are changed from sequential to random,
    >> with
    >> no way to change back to sequential.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Patience" <Patience@discussions.microsoft.com> wrote in message
    >> news:32AB0D88-133A-4238-8CE7-83A72916A337@microsoft.com...
    >> > I discovered some duplicate records in my database, so I deleted them.
    >> > The
    >> > only problem is that each record is autonumbered, and now when I add a
    >> > record, it says I have 100, but there are really only 92 (due to the

    >> deleted
    >> > ones). Is there a way to force Access to create records with the
    >> > numbers

    >> that
    >> > were removed?
    >> >
    >> > Thanks so much!

    >>
    >>
    >>
     
  8. dylan touati

    dylan touati
    Expand Collapse
    Guest

    salut comment tu t'appelles moije m' ppelle dylan touati repon moi on fera
    connaissanse

    "Patience" <Patience@discussions.microsoft.com> a écrit dans le message de
    news: 32AB0D88-133A-4238-8CE7-83A72916A337@microsoft.com...
    >I discovered some duplicate records in my database, so I deleted them. The
    > only problem is that each record is autonumbered, and now when I add a
    > record, it says I have 100, but there are really only 92 (due to the
    > deleted
    > ones). Is there a way to force Access to create records with the numbers
    > that
    > were removed?
    >
    > Thanks so much!
     

Share This Page