Next autonumber (non consecutive data)

[vrk1219]

HI,
I have a table with a field as autonumber and is a primary key.
But some of the records were deleted due to some reasons.
If I try to insert data into it, I want to know what will be the autonumber for the record to be inserted.

This code is not working
DMax("resourcemasterid", "resourcemaster")

The above code is verymuch giving me the max(resourcemasterid)+1.

But If I delete last 2 records I'm not getting the right number.


Please help.


Regards
RK Veluvali

 

[namliam]

Why want the next auto number? It should be generated... automaticaly...

Anyhow at the moment you have a new record it already has the Autonumber, so you should just be able to get it of the form?
Me.resourcemasterid

 

[vrk1219]

Hi,
I want to insert data from a temp table to main table.
if ther are any differences, while inserting the data into the main table, integrity will be lost.

 

[stopher]

Hi,
I want to insert data from a temp table to main table.
if ther are any differences, while inserting the data into the main table, integrity will be lost.

Maybe you could explain more about what you are trying to do. The idea of having the same data in two places is wrong. If you are saying that temp table is just that - a temporary table for importing data then why are you interested in the integrity?

Chris

 

[vrk1219]

Hi Chris,
The point here is I have a form, from where the data gets into the temp table.
This table does not have any sequntial data (auto numbers), so I'll be givng the numbers as 0,1,2,3... etc
But I have a parent child relation in both temp and main tables.
So, before I insert the data in my main table I must know the next record's auto number, so that I can make sure that the data entered into the main table is consistent.
As there is a auto number field in the main table, I can't insert any value to that field. So, to reproduce the same relationships in my temp table, I need the next number to be produced by the table.

Regards
RK Veluvali

 

[gemma-the-husky]

an autonumber holds the next number seed somewhere other than in the table itself. so if you add autonumbers 24 and 25, trhen delete them, the next autonumber added will be 26. it will reset to 24 with a compact/repair.

now, note

a) firstly you CAN force any (new) number to be inserted into an autonumber field, with an append query
b) secondly, you shouldnt be trying to use an autonumber as a control reference
c) thirdly, you will realy struggle trying to infill any sequence, particularly an autonumber

an autonumber is really just there there to provide a unique link between this table, and other tables. if you need a meaningful reference number in the table, you ought to provide in a different way, generally with an appropriately typed field. (number or string)

management of this field can be achieved in 2 ways.

either
a) generate a new number by finding the highest number in the table, and adding 1 or
b) (better in my opinion - as you get more manageability) store the next number to be used in a separate table, and read it from there.

in any event, you shouldnt really have a situation where users can delete previously entered sequential records. Much better that you just set an appropriate donotuse flag of some sort. then you never get gaps in sequences.

 

[namliam]

What the Husky is in the basses saying... Generate your PK at the point of data entry (your temp table) not later when you need to do data manipulation.