autonumber messes up with me.undo

MS Office Access


  • 1. Design puzzle
    I have a form with several combo boxes on it, the last of which allows the user to type in new data. Depending on the contents of all of the combo boxes, a routine executed in the before_update event enables or disables a command button. The users don't learn to tab off of the combo box after typing new data into it because when they choose from the list, the command button gets enabled or disabled immediately. I can't find any way to ascertain the contents of that last combobox until the user tabs off of it. Key_up and similar events only know about the previous contents. Can anyone think of a way for my enable/disable routine to "know" about the new (visible) contents of the combobox before the user tabs off?
  • 2. Access 2007 calculating . . . for a long time
    Hi, just opened my access application made in access 2003 in my new access 2007. One issue is that calculating fields takes a very long time compared to access 2003. In the status bar to the left there is "Calculating . . ." for a long time (>10 seconds) Anyone else seen this?
  • 3. Reset form and find record
    If user enters an InmateId value and a ClassDate (a unique index) that already exits in the table, I want to undo what the user punched in and go find the record they tried to duplicate. A button click is called that fires two SendKeys "{ESC}", which undoes everything on the form, but I still get a runtime error at Me.Bookmark = rs.Bookmark: "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing db1 from saving the data in the field." Why is it trying to save anything? I've undone all the fields, like it's a new record. Shouldn't it now go to the record specified in rs.FindFirst "InmateId = '" & strInmateID & "'"? How can I get it to do this? Many thanks, Ripper Private Sub Form_Error(DataErr As Integer, Response As Integer) Dim strInmateID As String Const conErrDuplicateKey = 3022 Select Case DataErr Case conErrDuplicateKey MsgBox "This record already exists. ", vbExclamation, "Data Error" strInmateID = Me.InmateID btnUndo_Click Response = acDataErrContinue Dim rs As DAO.Recordset Set rs = Me.RecordsetClone rs.FindFirst "InmateId = '" & strInmateID & "'" If rs.NoMatch Then MsgBox "No entry found.", vbInformation, "Data Not Found" ElseIf Not rs.EOF Then Me.Bookmark = rs.Bookmark End If End Select End Sub
  • 4. Update field in table b from form a
    I'm new to Access and VBA. (I've been working with Pick/Universe forever.) I'm about going nuts trying to figure out something that should be (and most likely is) very simple. I have a form that reads and writes data from/to a table (call it mainTable). It also reads data from a 2nd table (call it secondTable). What I can't figure out how to do is update an item in secondTable from a calculation in the mainTableForm. Specifically, I would like to take a value from secondTable, subtract a value from mainTable and write the new value back on the same field in secondTable. Suggestions?
  • 5. Prediction..?
    Hi all, I have a serie of values like: Month Value 1 100 2 200 3 300 4 400 5 500 6 600 7 700 If I would like to predict the value for month 12. In Excel it's: =PRECICT(predicted X;known y-values;known x-values) = 1200 How can I do the same in Access? Kent J.

autonumber messes up with me.undo

Postby Angi » Wed, 09 Feb 2005 06:38:48 GMT

I have a Coid that's set to autonumber.  The company entry form's
BeforeUpdate event doesn't save the record if certain things aren't
met.  That works fine.  The problem is it still takes the next number
messing the autonumber sequence.  How do I make the Coid 1 + the last
record number rather than using the autonumber?  I don't know how to
reset the autonumber without compacting the db.  If there's a better
way, please let me know.  TIA!

Re: autonumber messes up with me.undo

Postby Dirk Goldgar » Wed, 09 Feb 2005 06:51:38 GMT

If you want to ebsure that your CoIDs are generated in sequence with no
gaps, don't use an autonumber.  Autonumbers exist to generate unique
keys and for no other purpose, so if you care what the number actually
is, you shouldn't use an autonumber.

There are various schemes for generating sequential numbers, from simple
to complex.  In a single-user environment, you can just use code like
this in your form's BeforeInsert event:

    Me!CoID = Nz(DMax("CoID", "tblCompanies"), 0) + 1

In a multi-user database, your best bet is to have a one-record table in
which you store the next number to be assigned.  In the form's
BeforeUpdate event, if it's a new record being saved, you open a
recordset on that table, locking it, get the number from the record,
update the record with the next higher number, and close the recordset
to release the lock.

Dirk Goldgar, MS Access MVP

(please reply to the newsgroup)

Re: autonumber messes up with me.undo

Postby Rick B » Wed, 09 Feb 2005 06:53:05 GMT

Autonumber (as mentioned many mnay times in these groups) is used to create
a unique key for your data.  It should not be used if the numbers are to
have meaning or will be used for any other purpose.  Autonumber is not
designed to keep a list of continuous ordered numbers that never skip.

Please do a search.  Your question is very common and you should find
hundreds of similar questions and responses in the history of these
newsgroups.  I'd probably look in the table design, or getting started

Rick B

Re: autonumber messes up with me.undo

Postby Terry Prontok via » Wed, 09 Feb 2005 14:03:53 GMT

Or, if you wanted to find hundreds of examples of a guy being a jerk, you
could do a search for "Rick B".

Message posted via  http://www.**--****.com/ 

Similar Threads:

1.AutoNumber, messed up sequence, duplicate values.


I have a database that is used by about 20+ users entering about 1000 
records per day. I am noticing that they are having trouble adding records to 
the DB sometimes. When they have trouble, I go into the database table and 
try to add a record manually at the source and sometimes it tells me that the 
Autonumber is creating a duplicate record. As I scrolled through the data in 
the table I noticed the following....

Autonumber type field called "RecordID"

1-1085   looks good

then it jumps to 3741 through 3749

then it jumps to 16864 through 17074

I know some people are hitting the Undo button to back out records that are 
half way done because they changed their minds about creating those records. 
Other people start a record, get about half way and leave the record like 
that for about 30 minutes. Would starting a record and not completing it 
cause problems for everyone else? Btw I am sharing this db over a possible 
inconsistent 1GB WAN connection.

Do you know what is causing the Autonumber to jump around and cause 

Your help is greatly apreciated.


2.Autonumber mess, figure out the number

I am in autonumber hell, it is important for me to get the AutoNumber
even in case of previous deleted records, and I cant get the value
through a 'test' insert and then delete method.  There is no VBA
function that has that value in memory somewhere.  That would be the

For example:

5 *-
6 *---- I need to delete these records
7 *-
8 *
9 * 

so now I have

 --> Next insert

From the example above, I really need to get that value '10' without
performing a 'test' insert,  I was thinking of using a meta-table or
something, other than that I think I have tried everything, any ideas?

3.Messing with autonumber

I recovered an old Access database for new use. I simplified it removing 
unwanted tables. I uaed get external data.
Unfortunately I forgot to set the primary key "ID" to autonumber and I left 
it as "number". Now I have to enter manually a progressive number.
I tried to generate another primary key (with the proper autonumber 
With both primary key the database works (but I still have to enetr manually 
the proghrssive numbers in the original primary key.
If I delete the old primary key, I end up loosing my preexisting one_to_many 
Sergio Biguzzi

4.Autonumber mess

Hi All,

I have a database that is experiencing problems with 2 tables. There's
an AuditMaster table and an AuditResults table, with a one to many

The Key for AuditMaster is AuditID which is an autonumber.

It was index with no duplicates allowed but this has come off
somewhere down the line.

There are now duplicate AuditID's...

The only way I can see to fix this problem is to have the Autonumber
start from 2500, where it is currently at 900~.

Is it possible to have the autonumber commence from 2500 now?

Thank you


5.autonumber and undo

If a user starts a new record and then realizes the record already exists or 
gets interrupted or whatever and clicks on an "Undo" button (me.undo) or 
click the "exit without saving" button (DoCmd.Close acForm, 
"frmSelectCustomer", acSaveNo), the record is not saved but the next new 
record entered is no longer sequential in the autonumber field.

Why is this? Not a big deal, mainly curiosity (I use DMax for required 
sequential fields).

6. AUTONUMBER Field Increments after "UNDO / CLEAR FORM"

7. Undo list: figuring out how many times to undo

8. Undo feature for MS Word - what "exactly" will it "undo"

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 67 guest