Record auto numbering

I suppose I shouldn't be concerned, but my record autonumbering jumped from 
135 to 432,233 when I was trying to copy and paste common info.  Any way to 
reset?  Access Rookie 
0
Utf
2/23/2010 4:00:01 PM
access 16762 articles. 3 followers. Follow

6 Replies
867 Views

Similar Articles

[PageSpeed] 55

If the numbers don't have "meaning" nor need to be sequential, no problem. 
Actually if they needed to be sequential or have meaing, there would be a 
problem as autonumbers often aren't as you just found out.

Still that is quite a jump. Copying and pasting can be a problem. It's 
better to import instead.

You could empty the table, like exporting to Excel, then do a compact and 
repair. Next import the records back. But it really isn't worth the effort.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Hondo" wrote:

> I suppose I shouldn't be concerned, but my record autonumbering jumped from 
> 135 to 432,233 when I was trying to copy and paste common info.  Any way to 
> reset?  Access Rookie 
0
Utf
2/23/2010 4:19:02 PM
Hello,
Something like :
Public Sub InitNumAuto(A_Table As String, NumAuto As String)
'Ex : InitNumAuto "Table1","Num"
Dim Rst As DAO.Recordset, SqlStr As String
On Error Resume Next
SqlStr = "SELECT * FROM " & A_Table
Set Rst = CurrentDb.OpenRecordset(SqlStr)
With Rst
' Delete all records
    .MoveFirst
    Do Until .EOF
        .Edit
        .Delete
        .MoveNext
    Loop
' Update field "AutoNumber"
    .AddNew
    .Fields(NumAuto) = 0    'To start at 1, 99 to start at 100, ...
    .Update
' Delete first record
    .MoveFirst
    .Edit
    .Delete
    .Update
    .Close
End With
Set Rst = Nothing
End Sub
Bye
"Hondo" <Hondo@discussions.microsoft.com> a �crit dans le message de news: 
D5C7513B-D66F-4ED1-8B28-10921EADF040@microsoft.com...
>I suppose I shouldn't be concerned, but my record autonumbering jumped from
> 135 to 432,233 when I was trying to copy and paste common info.  Any way 
> to
> reset?  Access Rookie 


0
jero
2/23/2010 4:23:18 PM
On Tue, 23 Feb 2010 08:00:01 -0800, Hondo <Hondo@discussions.microsoft.com>
wrote:

>I suppose I shouldn't be concerned, but my record autonumbering jumped from 
>135 to 432,233 when I was trying to copy and paste common info.  Any way to 
>reset?  Access Rookie 

Don't bother.

Autonumbers have one purpose, and one purpose only: to provide a meaningless
unique identifier for a record. They'll always have gaps. They have a range up
to two billion odd (2^31 - 1) and will then pick up at negative two billion
odd and count up toward zero.

Copy and paste can introduce large gaps, as you have seen; for this and for
other reasons, it's often preferable to use Append queries or other techniques
to add new records. Where are you copying and pasting from?

For what it's worth, the only way to get back to gapless sequential numbers is
to create a new table, with an autonumber ID; and run an Append query
appending all the fields except for the ID. If your table is related to other
tables this can get complicated because you'll need to be sure that the
records link up.
-- 

             John W. Vinson [MVP]
0
John
2/23/2010 4:28:19 PM
On Tue, 23 Feb 2010 17:23:18 +0100, "jero" <~jero@jero.net> wrote:

>Hello,
>Something like :
>Public Sub InitNumAuto(A_Table As String, NumAuto As String)
>'Ex : InitNumAuto "Table1","Num"
>Dim Rst As DAO.Recordset, SqlStr As String
>On Error Resume Next
>SqlStr = "SELECT * FROM " & A_Table
>Set Rst = CurrentDb.OpenRecordset(SqlStr)
>With Rst
>' Delete all records
>    .MoveFirst
>    Do Until .EOF
>        .Edit
>        .Delete
>        .MoveNext
>    Loop
>' Update field "AutoNumber"
>    .AddNew
>    .Fields(NumAuto) = 0    'To start at 1, 99 to start at 100, ...
>    .Update
>' Delete first record
>    .MoveFirst
>    .Edit
>    .Delete
>    .Update
>    .Close
>End With
>Set Rst = Nothing
>End Sub
>By

Hondo, just note that Jero's code will a) completely destroy all the data
currently in your table and b) will fail since Autonumber fields are not
updateable.
-- 

             John W. Vinson [MVP]
0
John
2/23/2010 5:37:10 PM
Why does this matter?

Who's looking at the Autonumbers?

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Hondo" <Hondo@discussions.microsoft.com> wrote in message 
news:D5C7513B-D66F-4ED1-8B28-10921EADF040@microsoft.com...
>I suppose I shouldn't be concerned, but my record autonumbering jumped from
> 135 to 432,233 when I was trying to copy and paste common info.  Any way 
> to
> reset?  Access Rookie 


0
Jeff
2/23/2010 6:15:23 PM
"Jeff Boyce" <nonsense@nonsense.com> wrote in message 
news:e2T5rQLtKHA.5612@TK2MSFTNGP05.phx.gbl...
> Why does this matter?
>
> Who's looking at the Autonumbers?
>
> Regards
>
> Jeff Boyce
> Microsoft Access MVP
>
> -- 
> Disclaimer: This author may have received products and services mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
>
> Any code or pseudocode included in this post is offered "as is", with no
> guarantee as to suitability.
>
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
>
> "Hondo" <Hondo@discussions.microsoft.com> wrote in message 
> news:D5C7513B-D66F-4ED1-8B28-10921EADF040@microsoft.com...
>>I suppose I shouldn't be concerned, but my record autonumbering jumped 
>>from
>> 135 to 432,233 when I was trying to copy and paste common info.  Any way 
>> to
>> reset?  Access Rookie
>
> 

0
De
3/13/2010 6:04:11 PM
Reply:

Similar Artilces:

How do I add a record counter to my form
I would like to add my own record counter, like "Record # of #", to my form. How can I do this? Private Sub Form_Load() DoCmd.GoToRecord , , acNext DoCmd.GoToRecord , , acFirst End Sub Private Sub Form_Current() Me.Caption = "Record " & CurrentRecord & " Of " & RecordsetClone.RecordCount & " Records" End Sub This code places the info in the form's title area. To place it in a Label, substitute Me.LableName.Caption for Me.Caption. To place it in a TextBox, substitute Me.TextBoxName.Va...

change signs in a column of numbers
I have a column of figures that includes both positive and negative numbers. I want to change the positive numbers to negative ones, and vice-versa. Any way to do this in Excel 2002? Thanx. Type -1 to any empty cell Copy that cell select the range that you will change the number sign On the worksheet menu, EDIT > PASTE SPECIAL Choose values and multiply, click OK "bob" <anonymous@discussions.microsoft.com> wrote in message news:065601c39b8e$a0babdc0$a301280a@phx.gbl... > I have a column of figures that includes both positive and > negative numbers. I want to c...

Record recording
I need help in creating an automatic record form a file to an individual record. Example: You would make a work order for three different employees the first employees work order enter name, date, location, time, cost, and then this information would automatically be recorded to a permanent work sheet record specific to that employee. I have tried to manipulate VLOOKUP for this but to no avail. Any clue or help would be greatly appreciated. -- cdixon ------------------------------------------------------------------------ cdixon's Profile: http://www.excelforum.com/member.php?action=ge...

How do I find and replace a number with a minus sign in front?
Hi We need a bit more information to help. Are you wanting to return a 0 rather than a negative result from a calculation? =MAX(0,A1-B1) -- Regards Roger Govier "D.Sommer" <D.Sommer@discussions.microsoft.com> wrote in message news:4E015C20-5675-4979-BE01-28690DF3AF86@microsoft.com... > ...

Help with sql which counts records
Could someone help me to extend this sql to include: 1. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 2. a count of txtmulti where the field is a YES/NO field and I want a count of where the answer is YES 3. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 4. a count of txtnbrparts where the field is a number field and I want a count of where the answer greater than 1 I think I need to extend the WHERE statement? SELECT tblhvdealspt1.txtablhybrid, Count(*) AS totals, tblhvdealspt...

Selecting specific records
Is it possible to create a form with a "select" field (yes/no box), to select several items (say employees) and then output this information to a report with only the fields selected? -- Lori A. Pong How about multi-select list box? HTH -- -Larry- -- "Lori" <lpong@sternconstruction.com> wrote in message news:CAE5922D-92E5-4A01-83B2-6484024725C5@microsoft.com... > Is it possible to create a form with a "select" field (yes/no box), to select > several items (say employees) and then output this information to a report > with only the fields sele...

Multiple records in table to display single records
My table has 3 columns, A is account numbers, B is first name, C is last name. If an account has 2 owners, the account number is listed twice in C1, like this: A B C 123 Hank Jones 123 Ann Jones 231 Mike Smith 321 Jim John I want to query this table to display the names side by side if there is more than one owner, so that my data appears like this: A B C D E 123 Hal Jones Ann Jones 231 Tim Smith 321 Jim John Help please! Sorry, got the names mixed up! "Hurrikane4" wrote: > My table has 3 columns, A is account numb...

How do I trigger an event on opening a record
I have an Access 2003 form where I would like to take an action (e.g. via an event procedure or macro) whwnever the user opens a record for editing (e.g. via the arrowed record selector buttons). Effectively what I want is something like an On Enter or On Open control but relating to a whole record. It seems that such a control is only available for (e.g.) a text box, which relates to a single field in a record. I thought there would be something in the Detail section of the form as I thought that section related to whole records (?) but there isn't such a control there. Any idea...

Object Source / Record Source
Hello I have created a database regarding the School System. I have created three forms (one form and two subforms) one main form is (Students) where i enter student's name, father's name, phone, address etc. now on the main Student form i want to place a subform. But the thing i want to know is: I want to make two buttons on my main form (Fees) and (Progress).. because i have two subforms (sbffees and sbfprogress) now the record source of subformbox should be controled with these two buttons. when i click the ( Fee) button the Source Object should be (sbf-fee) and when i c...

Outlook client 'View in CRM' does not open the record
My 'View in CRM' button is not opening the CRM record. Pop-up blocker is turned off. Any other ideas? Thank you very much! Suzy ...

How to get monitor,memory serail numbers
Dear all, Please help me how to get serial numbers of memory and monitor - whether we can get this details in registry or by using any api or wmi - plz help me Regards Sankar Memory rarely, if ever, has a serial number. As for the monitor, that depends on the nature of the video device driver, and that's getting into areas I've never worked in. joe On 30 Jun 2004 23:10:41 -0700, swsiintr@hclinsys.com (Sankar-R) wrote: >Dear all, > Please help me how to get serial numbers of memory and monitor - >whether we can get this details in registry or by using any api o...

4.0 enterprise edition, max. number of organization
Hi all, Does anyone have idea about the max. number of organizations can be defined in enterprise edition of 4.0? Is there an upper limit for this? Hi, I asked the same question from Microsoft long time back and i was replied that there is no speicfic limitation. In order to be on safe side you better check it from Microsoft yourself and its better =EDf you get the response in written . /Aamir Thank you Aamir "Aamir" wrote: > Hi, > > I asked the same question from Microsoft long time back and i was > replied that there is no speicfic limitation. > In order to b...

Record Count 10-02-07
Experts, I'm trying to dynamically stored the record count from a subform into a field on my form. Now I have to look at the record count on the subform and manually entered the count into a field on form. There must be a better way. Please help! In the Control Source property of a text box on the main form: =[SubFormControlName].[Form].[recordset].[RecordCount] Where SubFormControlName is the name of the subform control, Not the name of the form that is the Source Object of the subform control. -- Dave Hargis, Microsoft Access MVP "Shiller" wrote: > Experts, >...

auto accept in outlook
I have a user who wants to have his meeting requests automatically accepted or declined if he is already booked at that time. This also has to work when his outlook is not open, is this request possible we are using outlook 2003 in and exchange 2003 enivronment. 1. Set Calendar Options Click Tools, Options, Calendar Options, Resource Scheduling. Click "Automatically accept meeting requests and process cancellations". Click "Automatically decline conflicting meeting requests". "newadmin" <eli_ayoub@hotmail.com> wrote in message news:1140719121.250414.129...

MCI recording silence, but Sound Recorder records OK.
Hello everybody, This code using MCI is working fine in many PCs, but one (in which only a long silence is recorded): // // OPEN // sMsg.Format("open new type waveaudio alias %s", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("status %s mode wait", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("set %s format tag PCM", m_sWavName); mciError = mciSendString(sMsg, lpszReturnString, 256, NULL); (...) sMsg.Format("set %s channels 1", m_sWavName); mciE...

Control the number of records shown in a report/subreport.
I've posted this question before and when I follow the advice, I keep getting a circular reference error. So I'm hoping that if I post some more info, I may get insight as to what is going wrong. I have a report (BartS1report) it uses a query (BartS1Report). This report as a subreport (BartS1Sub2) that uses the query (BartS1). The report lists the customer name, address, and systems serviced. The subreport lists the systems serviced. The reports are connected through the Master/Child links Customer ID and the ServiceAddress. When there are more than 20 systems per ...

How to Replace Numbers with Phrases
-- Jerry ...

What determines the order in which records are viewed
Hi all What determines the order in which records are viewed in a table If I import data or create a new table from a query regards, Garry ...

New Record
I have a form/subform. I have an event that opens a popup. I select from a list on the popup that copies the value onto a txtbox on the Main form and the popup then automatically closes. In the same code I place the focus in the subform to begin adding data to a new record. The problem I 'm having is that when the focus is set in the sub, there are two lines. The top line, which is the one I'm entering data in, does not automatically put the autonumber in for the ID that links the tables, but the record below it does, which is one that I'm not using. When I fill in the...

Recording last modification of records
I have found out how to record the date and time of the last modification to a record, which is great. Can I also record the user that made the changes? If so,how? Something like this can give you the network login name. http://www.mvps.org/access/api/api0008.htm -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "splitz" wrote: > I have found out how to record the date and time of the last modification to > a record, which is great. Can I also record the user that made the changes? > If so,how? ...

Predicting a number in a sequence
I have a series of numbers, and I want to estimate what a missing one might be. They are x, 50, 210, 2074, 277, 14,11,15,99,78. Is there any formula in Excel I could use to predict x? -- Bob ***** Bob, No. You would need to know the relationship of order to value, if there is one. A polynomial of sufficient order could be used to fit any number. HTH, Bernie Excel MVP "Bob" <f@ke.address.to.avoid.spam> wrote in message news:urdjruKVDHA.2316@TK2MSFTNGP09.phx.gbl... > I have a series of numbers, and I want to estimate what a missing one might > be. > They are ...

change number field to text field
importing an excel workbook from one of our clients. the field in question should be text but was described as numeric by client. so when i import the info, any alpha numeric info in that field rejects the record because of type conversion how do i import the workbook using vb and change the field in question to text, so i can import into an access table? thanks for your help =?Utf-8?B?am5ld2w=?= <jnewl@discussions.microsoft.com> wrote in news:F96E9D32-32F7-42F4-870D-343B537C6B9C@microsoft.com: > importing an excel workbook from one of our clients. the field in > question ...

auto save freature
Hi: We are experinceing some difficulties with our use of the Excel program. we use it to enter data in such a manner as to use it as scheduling program. Several workbooks used as months, with mutiple worksheets for days. Two main issues, one: we are unable to cut and paste from workbook to workbook, with out the program crashing, and secondly: we use the auto save freature so as we enter data, on a regualr basis, the data is saved. lately, we have ben noticing that the "saved" data is either not there, or not in the original place were it was entered. We use 3 PC's,...

Database Deleting Record Problem
Just launched a new database. Using an Autonumber field to generate document numbers (sequence is not important - just need a unique identifier). There are a number of fields that are required fields based on an option group selection. Problem; If all required fields (they are required fields programmatically) are not completed and you attempt to exit the form, a message box appears asking " Do you want to save changes". If you say "Yes" the form closes, does not save, and the record is deleted - but users are not aware of this deletion - and they think the recor...

Report Not Showing All Records
Dear All, I Have a Report that Shows all the records in a Table, at the moment the table hold 200 Records. but the report which is related to the table, is only displaying 32 pages, with the one record on the last page. the report is only showing 160 Records, What would cause the report to exclude the other 40 Records? Theres no filters set or anything. Any one got any suggestions? Regards Dan ...