Access Autonumber

[Vntalk]

Hi guys, i have some troubles with my database, i really need your help.

Let say i have a table with 10 records in it with an autonumber from 1 to 10. Now i want to insert a new record and i want make that record the second line.

So the point is increase record from 10 to 11, 9 to 10... and so on.. cause i don't want duplicate.

I already done this with VB6. It works however for some reason sometimes it doesn't.

My question is "Is there any way to do it automatically with Access". Increase auto from the insert record.

Thanks

[si_the_geek]

If it is actually an AutoNumber, it should be done automatically within Access already.

So, what is it that you are currently doing? (and what data type is the field)

[Vntalk]

yes, it's a text field. The autonumber works however the new insert record will be 11 in this case (after the 10 ligne already there). This is not what i want, i want that the new record will be appear as 2 and the existing records from 2 will increase to 3 + and so on.

I am able to do it with VB6 without the autonumber of access, but it's not stable.

[si_the_geek]

Why is it a Text field?

Being a Number would make sense, but even that would still not be ideal.

I am able to do it with VB6 without the autonumber of access, but it's not stable.

It will probably never be stable (or safe), especially if it is going to be used by multiple people.

Why are you avoiding the built-in method (which has already been designed to cover the issues you are having and more, and is fully tested)?

[Vntalk]

sorry for my draw talent.

[si_the_geek]

I'm afraid I have no idea what you meant by that - please try to explain in words what is 'wrong' with using an AutoNumber.

[Vntalk]

If i try

.Execute ("Insert Into * Table (Field) VALUES ('" & mytext & "')")

Then to autonumber will increase from the last record.


This is not what i want. i want to the new insert ligne have the number 2. and the existing record increase one by one. Hope it's clear enought. Thnaks for your help

[si_the_geek]

It still isn't clear I'm afraid.. after reading that a few times, I think you might mean that you want to add a new record in the "middle" of the existing values, and increment all of the existing ones (eg: [1, 2, 3, 4] would become [1, new, 2+1 =3, 3+1=4, 4+1=5] ).

Is that correct?

And if so, why do you want to do that?

[Vntalk]

yes, that's correct.

[salvelinus]

You'd have to do that manually, something along the lines of

UPDATE yourTable SET yourNumberField = yourNumberField + 1
WHERE yourNumberField >= newNumberToInsert

You'd have to do this before inserting the new record, because otherwise you won't update the previous existing number.