CODEWITHSUNDEEP
ms-accesssql-insertautonumber
samiksha
samiksha

Reputation: 73

How to insert values into an Access table's AutoNumber field?

I'm working on a project using jsp and Microsoft Access.

There are three tables in which a same field is used as Receipt_No.
This can get from Recp table, which has a only one field: Receipt_No (AutoNumber, Primary Key).

Now my question is, how can I insert in this AutoNumber field with every generation of receipt?.

Upvotes: 0

Views: 4798

Answers (2)

Banjoe
Banjoe

Reputation: 1768

WARNING This is ghetto. (But then again I'm thinking a table with ONLY an autonumber field is kinda ghetto too, so oh well.)

INSERT INTO yourTable (your_autonumber_field) SELECT max(your_autonumber_field) + 1 FROM yourTable

Not elegant, but it works. That will create a new record in your Receipt table. It defeats the whole purpose of having an autonumber field but I don't see another way to use SQL to create a record in a table with only an autonumber field. You can then retrieve the newly created receipt ID with a SELECT max(your_autonumber_field) FROM yourTable for use in your FK fields in the other tables.

Upvotes: 2

user909058
user909058

Reputation: 188

The autonumber field has the property that it gets numbered accordingly as the records are entered into the table. its smthng like autoincrement

Upvotes: 1

Related Questions