Problem with AutoNumber

[Gina]

Hi all.

I have an application holding data about customers, cars and work done on it
in a main form with some linked subforms.

After coding various additions I can't think of a part which has to do with
the following fault...

I found out that when trying to add a new record the autonumber field of the
main form (containing all the subforms) does not create a fresh new number
(the highest one) it rather takes any not already existing ......

lets say if it normally would create '300' as customerID (as the last record
is at 299) it creates 15 (which is not existing as I must have deleted it at
some point) ... the whole linking of the subforms seems to go wild ....

my questions:
what (in my programming?) could cause such a fault ?
has anyone else had such a problem ? - how was it solved ?
how (apart from taking an earlier version) could I correct this version ?

I would be very grateful for your help !

Gina

 

[Nikos Yannacopoulos]

Hi Gina,

Is this a real autonumber field? The behaviour you describe sounds funny
for an autonumber, even with its New Values property set to random, it
should not be reassigning already used (and dropped) values. Therefore
my guess is the field is not really an autonumber (check the table
design); you must be assigning the values either in code or with a
macro, or simply with an expression in the form control's default value
property. Try to locate it, and post back with more details of what
you're doing, if you don't manage to solve it.

HTH,
Nikos

 

[Gina]

Hi Nikos.

Thanks for your answer !!
Yes it is a real auto number field which is the primary key ... long
integer, without duplicates
I didn't do any 'new value settings to random'
neither with a macro nor in code

I the form when creating a new record ... BEFORE I insert any value in
customerSurname the customerID shows ' autonumber ' ( as it is visible)
and after typing a single letter into the customerSurname control the
autonumber turns to any low number

Neither the form's controls - autonumber field, nor the customerSurname have
a default value

This is very annoying !! as I do not know where to start .... it doesn't
make sense

by the way .... I am very happy about your 'AfterImport_Nikos' Function ;-)

Gina

 

[Gina]

me again ....

I decided to use a rather radical 'solution'

Thanks to our co-prodution: import function! ...

I deleted all the records in the db ... compressed and repaired it and
imported the data again ... and it works
it accepts new records and the autonum generates a valid new ID

anyway I would be very interested to know what could cause such a problem
...... to avoid it in future !!

Gina

 

[Guest]

Gina,

It looks like your pesky AutoNumber problem is due simple setting in the
underlying table. Of course, as with a lot of other settings, it's not
obvious to the user!

Go back to the underlying table. Switch to design view. Click on the primary
key field that is set to AutoNumber. Look below under Field Properties. The
General tab will show a property called New Values. If this is set to
"Random", Microsoft Access will randomly assign an autonumber. I prefer to
use "Increment", although this leaves gaps after one deletes records.

Unfortunately, you can't change an autonumber from Random to Increment just
like that. Access will display a warning saying it's not possible to change
the setting. To change the AutoNumber to Increment would require deleting the
primary key altogether, and then creating a new primary key that's an
AutoNumber set to Increment. This of course creates a big problem, especially
if other records in other tables refer to the AutoNumber. Check with your
database administrator.

 

[Nikos Yannacopoulos]

Beats me! If it's autonumber with its New Values property not set to
random, I can't think of any possible reason why this should happen...
except maybe corruption? If that's the case, then what you did took care
of it.

Regards,
Nikos

 

[Gina]

Nikos,

I checked the underlying table but forgot to mention that the autonum field
was set to 'new values - increment'

and the strange thing was, that it worked over the past months ...

I interpreted this behavior as 'some kind of corruption' ....
question here to Access: WHERE and HOW ?:-(
a compress & repair with the data in it didn't do any changes to the better
..... and that really gave me a headache
from posts here my idea was to go the radical way ... which was good

Thanks for your help!!

Gina

 

[Gina]

rupertsland,

Thanks for your answer ...
Yes .... I checked the underlying table and all looked as it should ....
I forgot to mention that the autonum field was actually set to 'new values -
increment'

and the strange thing was, that it worked over the past months ...

deary me, question in my head ... can things like this always happen without
a warning ??

I am so glad I solved it !!!!

Cheers,
Gina