Question Add Plus One Field Value To Form

[NickyThorne]

Hello,

What i need to do:
For the value in my primary key field to increase by 1 when a new record is started.

Currently, when a new record is started, i have to manually enter the "Card Number" each time (Which is +1 than the previous record). I need vb code which can do this for me. (Please tell me where i place the code)

The Problem:
I can't use the Autonumber data type because i have some what 2000 records already in the table, and it wont allow me to change it which is why im asking for vb code.

img7.imageshack.us/img7/5351/proofgc0.png

In Summary:
Currently i have a form which uses a text field, linked to a primary key called "Card Number".

When i start a new record, i have to enter a card number eg, 1001239
(the previous records had the card numbers: 1001237, 1001238)

Can anyone give a code or solution as to how i achieve this?

Thank you in advance,
Nicky Thorne.

 

[KenHigg]

Can you re-create the table less the data, set the field to autonumber then append the current records to it?

 

[MSAccessRookie]

Hello,

What i need to do:
For the value in my primary key field to increase by 1 when a new record is started.

Currently, when a new record is started, i have to manually enter the "Card Number" each time (Which is +1 than the previous record). I need vb code which can do this for me. (Please tell me where i place the code)

The Problem:
I can't use the Autonumber data type because i have some what 2000 records already in the table, and it wont allow me to change it which is why im asking for vb code.

img7.imageshack.us/img7/5351/proofgc0.png

In Summary:
Currently i have a form which uses a text field, linked to a primary key called "Card Number".

When i start a new record, i have to enter a card number eg, 1001239
(the previous records had the card numbers: 1001237, 1001238)

Can anyone give a code or solution as to how i achieve this?

Thank you in advance,
Nicky Thorne.

You could try inserting an AutoNumber Field into the table. For each existing record, the value of the AutoNumber Field will be the record position in the table. All new records will act as you wish.

If there are no gaps in the numbers that have been entered so far (AutoNumber = CurrentPK for each record), then you can rename the old column (to save it in case you need it), and rename the new column to have the same name as the old one did, giving you the AutoNumber that you are looking for.

If there are gaps in the numbers that have been entered so far, then you will need to find another way (perhaps copying the table to a temp table that has an additional column for the AutoNumber), to get you the AutoNumber that you are looking for.

 

[Husan]

u can set the default value to lookup the maximum value and add 1 ie:

default value = DMax("[Card Number]","Table1")

if card number is a text, u may need to convert to a integer:

default value = DMax("int([Card Number])","Table1")

 

[Husan]

sorry forgot to add 1:

default value = DMax("[Card Number]","Table1") + 1

 

[NickyThorne]

sorry forgot to add 1:

default value = DMax("[Card Number]","Table1") + 1

Hello Husan!

Thank you for your reply,

I tried placing the code as the default value, and it worked!

I thank you greatly for your time and expertees,

All the best in the future,
Nicky.