Insert a sequential set of numbers into a a set of record fields

[PDilly]

My user would have normally created a set of records each of which includes the number of key for a lock, but the 250 keys hadn't arrived... I'd have waited!!



typing them in one by one is a task i admit!

his question was can the key numbers, which are sequential, be added to the key number field with the use of simple update query?

I guess the answer is yes but i'm not sure of the best way

The only update criteria wii be that the key number field in every record is currently null

The starting number for keys is 1300 and it doesn't matter who gets which keys but I will sort alphabetically by surname to make it easy

Is there a way of using a row number in the query to increment the key number?
I cant use the primary field as there are gaps in the sequence.

Any help or suggestions welcome
Many thanks
PD

[orange]

Putting the values into the database table is one thing, but giving the physical key to the specific individual is another.
Seems these have to be "in sync".

Just a thought, but KeyNo seems to be a separate concept than KeyX is assigned to PersonQ.
Does assigning KeyNos in Surname sequence make sense--when Employee leaves or is replaced?
Not saying it's a terrible idea, it might be simple for initial distribution of keys. It's when staff changeovers occur that the alphabetic surname to sequence numbers are no longer equivalent. You may have considered that.
How will keys be distributed physically to personnel?

[PDilly]

Putting the values into the database table is one thing, but giving the physical key to the specific individual is another.
Seems these have to be "in sync".

Just a thought, but KeyNo seems to be a separate concept than KeyX is assigned to PersonQ.
Does assigning KeyNos in Surname sequence make sense--when Employee leaves or is replaced?
Not saying it's a terrible idea, it might be simple for initial distribution of keys. It's when staff changeovers occur that the alphabetic surname to sequence numbers are no longer equivalent. You may have considered that.
How will keys be distributed physically to personnel?

hi,
pardon the pun, but it’s a bit more Low key than that.
the keys are for the lock on a fishing lake.
the lock and keys are changed every year and when the permits are sent out the keys are sent with them.
physically, it’s just a case of putting the right key in the right envelope. As the permit has the key number printed on it it’s not too taxing. The permits print in alphabetical order by name.

permits and keys are renewed annually together.

this is really a one off task as normally assigning a key to each person is done as the record is created.

[orange]

Good stuff.
Sometimes when you read, you get a mental picture and you try to make the pieces fit. And sometimes the mental picture is not too accurate.
Go fish....
Good luck with your project.

[pbaldy]

You can open a recordset on the table, ordered as desired. Set a variable to the starting value. Within a loop of the recordset:

rs.Edit
rs!KeyNumber = VariableName
rs.Update
VariableName = VariableName + 1