how to make the new Autonumber in Access follow the last Autonumber

0
votes

I have a little school project where I create an exam of 30 questions, I display these questions out of a database table that includes 70 questions (which I created in advance). I use an array of random unique integers between 1 and the MaxID (in the questions table) to take these questions using their ID(autonumber, also primary key) and display them. My teacher told me to create a page where the admin can add a question which I did.

The problem starts when I add a question(ID==71) and then remove the question and add another one(ID==72) if The last ID is 72(for example) and before that is 70, then if the number 71 is included in the array of the random unique integers that is gonna make a problem cause there's no row that includes 71 in the Access Table.

My question is: Can I make ID's in an autonumber column always be +1 than the last ID in the table?

I know it's a bit long but I'm really bothered by this, if something is unclear, please tell me so I can make it clear.

3
sqlms-access
not specifically related to your question: using random function (1-70 +1) will yield duplicate results. Have you thought about this? Ideally your array should only contain existing question ids and those not already selected. – Krish
I already solved that. – Daniel
great then the part: initializing only exiting Ids should also be covered which will eliminate your current problem as well as giving your admins full rights to add/delete questions – Krish

3 Answers

0
votes

No, autonumber will be +1 of the last used. The only way to accomplish this would be to assign your own value to the ID, but you'll still have a similar issue if you delete a record in the middle.

0
votes

As a workaround you can use INSERT SQL for adding new record. You cannot control numbering in Autonumber field when you use form or table data editor, but direct SQL allows insert desired value to Autonumber field. After this Access as next number will generate number = inserted value +1. So, if you added and then removed #71, you can still insert desired data using insert into Table1 (id,data) select 71,"text".

But in your case I would recommend to use different principle for selecting random questions. For instance add new column with question index and re-generate consequent numbers in this field every time when you made changes in the questions list

0
votes

Your problem is that you misuse an autonumber - it is for identifying the single record only. It is not nor should it be meaningful in any case.

So, when you have created the questions, have another field (QuestionId) you fill with no. 1 to n. Then select randomly from this number.