Autonumber Problem

[Deano]

Does anyone know why this happens.

For reference below, ID is an Autonumber and record being a product of some
sort (car parts, food etc)

In a form I am part way through creating a record and the ID value is 1001.
I then decide to cancel the entry. Code being used is 'DoCmd.RunCommand
acCmdUndo' . Later on or what ever time period it maybe, I go back to
create a new record (not necessary the same one as before) but the
autonumber being genreated isn't 1001, but 1002. This will inevitable lead
to a lot of gaps (1000, 1002 no 1001) in the number sequence and raise
questions about missing products from people using the system. Is there
anyway that a record can be cancelled and the next record to be created will
be the next in sequence using the autonumber facility.

Thanks in advance.

 

[Niklas Östergren]

No, not as long as you are using a Autonumber field in the tables.

If you want what you´r asking then you have to create your own ID-number for
the parts. If so I sugest that you keep the autonumber field and use that as
a ID for the records. Since then you are sure that it will be unique. And
then create a simple code for creating your own partID.

Something like this:

'============================

Dim rec As DAO.Recordset

'Create a new record in your table tblYourTableName

' Open recordset
Set rec = db.OpenRecordset("tblYourTableName", dbOpenDynaset)

' Creating a new record in your table
With rec
.AddNew
!YourPartIDFieldName = DMax("YourPartIDFieldName ",
"tblYourTableName") + 1
!SomeOtherFieldInYourTable = SomeOtherControleNameInYourForm
.Update
End With

'Clean up
rec.Close
Set rec = Nothing

'======================================================

is the name of the field in your table which you want to control (NOT the

autonumber field).

If you want you can add some other fields in the recordset by just using
!AndFieldNameInTable = SomeControlOrVariableOrValue

The code abow needs to be, the way it is designd right now. Somewhere in
your form´s Code Moduel since it´s refering to a control on the form
<SomeOtherControleNameInYourForm>. But you can create a record from almost
anywhere in your application. But that´s another story.

// Niklas

 

[Deano]

Thanks for quick response.

Unfortunatley, I've done a great deal on my app with the autonumber and it
would mean a complete overhall. It's a bit late at this stage, and with so
many tables, it would be a nightmare to do. I'll stay with the problem
until someone complains and then I will use you code, which you kindly give,
to sort the problem out. It's not a huge problem, but I can see in the
future that people are going to be asking question about the missing gaps.
Maybe I'd better do now and be safe than sorry.

Thanks again

 

[Nikos Yannacopoulos]

Deano,

One way to get rid of gaps in the future is to change the autonumber field
to a Long Integer one, and use the Default Value property of the control
bound to it in the data entry form to assign a new value for each new record
by means of:

=DMax("[IDFieldName]","TableName") + 1

Note: this assumes that new records are only added through the form!

Now, in order to get rid of your existing gaps:
First of all, make a back-up copy of your database before you try anything!
Delete any relationship between tables involving the autonumber field;
In the table's design, change the ID field from autonumber to number (long
integer)
Go back in relationships and restore the realtionship(s) you deleted before,
making sure you enforce referential integrity and cascade-update (at least).
Now, whatever change you make to the ID field in the primary table, will be
cascaded in all related tables. If your primary table is big, one trick is
to copy the ID field column and paste in Excel, edit to sequential numbering
there (piece of cake with autofill), and copy and paste back into the Access
table.

HTH,
Nikos

 

[Niklas Östergren]

OR, you explain to the users how your application works, and document it, so
they understand why some ID´s missing from time to time.

One other way of solving the problem is to disable the posibility to delete
records and next time the user want to create a new record first check if
any record with (for instance) string <Empty> in some field in your tables
exist and if so open that record instead of creating a new record.

If the user realy want to delete a record you could either delete it, which
offcourse will give you the sam problem as you have today, or you could
empty the fields and set string <Empty> in some field.

The problem here then will be that you will have to exclude these records
(with <Emty>-string) if you want to count number of records.

// Niklas

 

[Steve Schapel]

=DMax("[IDFieldName]","TableName") + 1

Note: this assumes that new records are only added through the form!

.... and that it is a single view form.

 

[Deano]

Thanks for advise, will definatley take on board of what is said. Its sound
as if you have come across the problem before and devised this work around.

I'm going to try it on a couple of tables first and see what reation I
get(Access 2003 wise). Just to note, the forms are the only place where new
records are created anywhere within the application. I will post here how I
get on. Might be a couple a days though, bit will go ahead with it.

Thanks again for your help who's contributed to this post.

 

[Niklas Östergren]

Well, I haven´t hade this problem before. And after read through the answer
you have got I´d advice you to follow Nikos´s example, if possible (read
Steve Schapel´s answer).

Good Luck!
// Niklas

 

[Steve Schapel]

Sorry, I should have been a bit more informative in my previous comment,
but I was short of time. If you are using a continuous view form, you
can use code on the Before Insert event of the form to do something
similar to what Nikos suggests...
Me.YourID = DMax("[YourID]","TableName") + 1

--
Steve Schapel, Microsoft Access MVP

=DMax("[IDFieldName]","TableName") + 1

Note: this assumes that new records are only added through the form!

... and that it is a single view form.