Changing AutoNumber 8

[Yzerman777]

Im working with forms to enter data into a table. While creating the form, I tested with random data and deleted that from the table. Now, my AutoNumber is higher than the actual number of data in the table. How can I change the current number of the AutoNumber? I would greatly appreciate any help. Thank you, in advance.

Chris Wildes
Low Level Tech Guy

 

[braindead2]

compacting the database before adding data usually does it.

 

[JaSintoHenriqueSido]

try this

remove the autonumber and put number in the key fild

in the form put this

onopen

me!"nameoffiledinform"=dmax("fieldontable";"table&quot+1

When you open the form the access will sum +1 to the higest nunber in the table

with this you can delete the last value and when you try the next time you will get the same ID

THis not work on network

Sorry my english
if you need more help paulobar@esoterica.pt

 

[Lightning]

Another way to reset the autonumber is to

1. Delete the autonumber field from the table
2. save the table
3. Add a new field to the table, and set to autonumber
4. Save the table again.

This recreates the autonumber sequence again, commencing at 1.

HTH
Lightning

 

[MichaelRed]

A different approach is to just recognize and accept that Autonumber fields are just for record identification purposes. It is unlikely that the db will be maintained such that there are sequential values for the autonumber field throughout it's life cycle. So, in the end the value and sequence are generally meaningless for any purpose other than record identification - and even in that capacity, the only purpose is for the relationships to other tables where the Autonumber of a table is used as a secondary key to relate another table's records back to it.

So the real answer is -------- Why bother??


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[limpan]

You could also use the database tool "compress and repair database" under the tools menu. This way your autonumber fields will restart from zero, if the table doesn´t contain any data that is.

/Linus

 

[MichaelRed]

limpan,

I did not believe you. Unfortunately I am wrong. Conceptually, this appears - to me - to be completely at odds with the concept and definition of Autonumber. Do you know of any "official" documentation of tis "FEATURE"?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[TheInsider]

I also didn't know that a repair & compact would reset an autonumber field in a table without data. I have been working with Access for a couple of years and have been using repair & compact quite often, yet I never noticed this! Rob Marriott
rob@career-connections.net

 

[JoeMiller]

Tis true, it does reset the autonumber if there is no data in the table.

Michael this came right from the access online help.

Help Topic: Change the starting value of an incrementing AutoNumber field

Excerpt of text: when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value.

Joe Miller
joe.miller@flotech.net

 

[Aivars]

Hi!

By using of Autonumber proceed lot of problems when you are needed to export/import data. Because I never use autonumber but always use Long or Integer data type fields. For new record update from form I write in the procedure Form_BeforeInsert following codes:

private sub Form_BeforeInsert(cancel as integer)
if dcount("MyField","MyTable&quot=0 then
me.MyField=1
else
me.MyField=dmax("MyField","MyTable&quot+1
endif
End Sub

Aivars

 

[MrsTFB]

Joe,
Where is that Access online help? I'm a newbie and learning the hard way.

Thanks for your help,
Bsimm

 

[JoeMiller]

In Access 97 (version i have) open a database and with the database window type "autonumber" into the Office Assistant balloon and press return. It will be the second one down and will be named "Change the starting value of an incrementing AutoNumber field".

Joe Miller
joe.miller@flotech.net

 

[Guest_imported]

Hello,

Have you tried copying that table to another dummy table. The dummy table should have the same no. of field as with the problem table you have. Once you copied it browse the dummy table and see if the auto number will match the number.

If so, rename the problem table, then rename the dummy table with the original table.

I know it sound dumb but try it. Do backup your file before doing anything.


Jun Peralta
Vancouver, BC

 

[WallT]

Aivars, I like the method you used on this old thread...is there a way to use the dmax to have a field in a sub form reset to one every time there is a new record on the main form. I have orders that have several locations each, but for billing purposes, each location has a AutoNumber field to keep it unique, however I also have a field that is "Location Number", and right now my user have to enter the "Location Numbers" themselves. Is there a way to automatically have a number entered in this field starting with one consecutively, then start at number one again when you start a new order?

 

[oharab]

Have a look at thread705-341598 where I give my way of creating autonumbers. It shouldn't be too difficult to make it work the way you want it to.

HTH

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[CVigil]

you could also work on dummy data...

I have front-end databases wiht forms, etc, all of whose tables are linked tables pointing to actual tables in another database in another directory.

Since all users at this company have the drive-letter F: mapped to a particular folder on the server, my links to the Data Storage database that contains the tables all reference starting from drive F: .

When I want to test something delicate, I re-map F: on my PC to another directory where I have just put a fresh copy of the Data Storage database. When I then run my database on my machine, the table links look to F:\[...] to find the database and original tables. And they find them there; this is just a different actual drive for the mapped drive F: . And my changes affect a copy of the data, not the original data. And Autonumbers (and many other things) are not affected in the original Data Storage database.

Since we're using mapped drives anyway, this is a convenient way for me test on fake data.

If you do this, just be very careful to remap your drive appropriately when you're done testing, so that if you need to affect real data, you will. Especially be aware of the difference if someone comes up to you to ask for a data change or current report. On your machine, any copies of the database will still be looking to the same data source. Remember, you didn't change your database; you only changed what could be found at the mapped drive location!

-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[MichaelRed]

CVigil,

I suppose that your approach CAN work in a small environment, but as you note -you need to be VERY careful of the mapping. When I need to work 'off-line' from live data, I approach this whole issue somewhat differently.

First, I have a control (usually a Command button) on EVERY form. When working off-line, this button is set to FLASHING (Alternating the ForeColor). The Button's visible popoerty is set to FALSE -unless the CurrentUser is an ADMINS User, so the normal users are not even aware of the possability of the remainder of the activity. To actually reference any 'foregin' tables (through Links) I always use URL references and have links set to both the production and test dbs/tables. The Command button onClick event swaps the table names (at the link level) between the live and test data. I use another (similar) button to Copy the live data to a third 'test' db for those occassions when an issue only is aparent in the specific data set represented by the live data. It is probably more effort than your approach, but the use of mapped drives has a host of problems, including security breaches. the use of URL mapping does NOT preclude the swapping of database/table references, just approaches it in a different manner.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

I like your approach and instruction, MichaelRed . Have a star!

You're right; the environment in which I'm currently working *is* small, and the databases aren't ever used by more than about 10 people at a time at this company.

I'm curious about the particular security breach problems you mention, with respect to mapped drives. What are they, please?

I like your idea of a flashing indicator to keep in my mind, and make it *easy* to check, the fact that I'm working with dummy data . The alternating color approach is a nice simple one; for the benefit of others, I'll say that I presume you use the Timer event and TimerInterval property to initiate the flashing, just setting the TimerInterval back to 0 if/when you want it to stop flashing (or just want the computer to stop wasting time processing it if it won't be seen anyway). I think I'll use the idea by dropping a separate tiny pop-up form in the corner, if the user is one of our admins and the linked tables' actual "directory of residence" isn't the live data directory. (...or maybe I could use a toolbar, so it could be docked in a permanent place out of the way of other forms ... I'll get to learn about menu bars and cool bars and those things now, Yay :-D !...)

I get the general idea as you describe your URL-based approach, but I would appreciate more explicit explanation of the details. This might be relevant to my questions here: I use Access 97 and haven't had opportunity/reason to develop much for Access 2000 yet.

First, just how do you link through a URL? Do you simply enter a URL in the 'Link' dialog box prompting for a source table? I suspect the answer is yes; I just tested using the file:// notation, which worked, and I presume that http:// would work just as well if I had any databases in a folder set up as a web-space.

Second, if I read your description correctly, you have two sets of tables linked all the time; and your command button simply renames each set of "local tables" (the links, not the actual source tables) when you click it. In this way, your code and table/field references never need to change; the single set of names simply refers to different sets of tables, depending on your need (and clicking of the command button in response to that need ;-) ). So my question: Does the button code have to close all the objects that refer to the tables before the renaming? How "invisible" is the change when you make it (not that it has to be too invisible for administrators)? This simple one-click on-the-fly table swap seems like one of those useful ideas that one is very glad to have had suggested ! (I don't suppose that having extra links (linked tables) in the database costs much of either storage or performance?)

You mention another function: copying data to a third separate test database, for testing with specific/current data. Do you maintain three sets of links, then? Wouldn't it be easier to copy the data to the test tables you already use? I'm certainly conceding that I may not have understood something. It's why I ask questions <bright smile>. Indeed, anytime there has been a table-design change (renamed fields, changed field types, etc.), I make a new copy of my whole &quot;Data Storage&quot; database(s), so that the structure matches (including relationships). So, it seems to me that a &quot;switch links between live and test data&quot; button and a &quot;replace test tables with fresh copies of live tables&quot; button are called for. The latter button would, I think, do the job of your &quot;second button&quot; without need for a third database or set of tables. (...unless your test databases contain only certain specific data for testing purposes...)

Thanks for the lessons and ideas!

 

[CVigil]

The last message from &quot;(Visitor)&quot; was from me (CVigil). I gave MichaelRed the star and asked the questions. It seems I only *thought* I logged in after responding to the e-mail notification that there was a new response in the thread. Well, let my failure serve as warning to others...


In fact, I should have noticed that my &quot;sig&quot; didn't show up when I was previewing my post; I've caught and fixed this problem that way once before, so I'll just have to redouble my resolve to look at that carefully as well as at other editing needs.

--&quot;The New Mystery Man&quot; =)
...Well, actually, I'm...

-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[MichaelRed]

CVigil,

You have a good grasp of all which I posted.

To Clarify the &quot;Test&quot; vs. &quot;Copy of Live&quot; data issue. I do (in a multiuser development environment) usually keep three sets of links. The &quot;Test&quot; data is normally a small set of constructed data which represents the known / expected limits of the various fields as well as a documented set of normal values. It is used for &quot;formal&quot; testing, where the results of operations are 'pre-defined', and can be used to satisfy Q.A. benchmark tests and operational validation.

Actually, There is a fourth (read-only) data set which is used to reset the test data after operations which alter the table contents. This last set of data is only modified to include data used to verify &quot;approved&quot; changes, where the change is not observable with the then current test data set. As a developer, I have mixed feelings re the proliferation of the data sets. On the down side they represent effort which does not go (directly) toward improving the app. The up side is how often these have saved the day when smeone complains about 'changes' -which turn out to simply be things they hadn't noticed before.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over