Autonumber Get then next number prior to writting the record

[Jimgarry]

Hi, thanks in advance

I am working with a table that has autonumber Increment as the key filed. I am writting the records from VB.

I would like to get the next Autonumber to be written prior to the record being written. now using a qry
slelect max(autoField) from table XX provides me with the current auto number so I can add 1 BUT !!!
what about when the table is blank. the next number is not always 1 how do I determin the next autonumber in this case?

Thanks

Jim

 

[KenReay]

Hi

A very risky way to get the next number if you have multiple users.

I think you need to write your own routine to derive next available number

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[LNBruno]

Sounds like you need to use the Nz function:

iNextNum = Nz([autonumfield],1)


< M!ke >

 

[LNBruno]

I agree w/ Ken - normally, I'd get the record id AFTER the insert...

< M!ke >

 

[Jimgarry]

true I agree that it would be better to get the next number after it is written.

This will be used in a single user. The program is multi user but this is for a special import which only one user will be on the system at a time.

Thanks for your assistance. I think I am going to write the record first then grab the autonumber it may take longer but I do agree it is safer. What if the manufacture changes from Inc to random Yes I have seen it happen

thanks again

Jim

 

[Golom]

You don't ... and in fact, your query that returns MAX(AutoNumField) doesn't work either (it only appears to some times.)

In the absence of any messing with it like starting at a value other than 1, Access assigns autonumbers as the number of records that have been added to the table since it was created ... NOT adjusted for deletions. If you were to

[li]Create a new table with an autonumber field[/li]
[li]Add 20 records[/li]
[li]Delete the last record added[/li]
[li]Run the "Select Max..." query[/li]

It would return "19" and you would conclude that the next number will be "20" but in fact, it will be "21". Even if you deleted all 20 records (i.e. the table is now empty), the next record added will still be "21".

The end result of all this is that the only way to be certain what autonumber will be assigned is to add the record. Any other method carries no guarantees.

 

[LNBruno]

Come to think of it, why are you wanting to get/add to an autonumber field anyway?

< M!ke >

 

[Jimgarry]

Good question, I am writing the data to another table in the same database that uses the autonumber as the key and link between the tabels. I wanted to get the number prior to writing the first record so that I could write the second. Its been a long day.