Assign new record number

[PAUL]

Hi,
On my Access 2000 application, I have a form to maintain
a reference table: Class No., on the form there is a
button : New, to add new Class No. When the user click the
button they can add new record and system will assign a
new Class No.
The Class No is an primary key , text field size 4(I
converted this table from another application), the
current number could be 1000 or 1500 or 2000. You can see
gap in between two number. How to find a new number in
between?
Thanks in advance.

 

[Marshall Barton]

On my Access 2000 application, I have a form to maintain
a reference table: Class No., on the form there is a
button : New, to add new Class No. When the user click the
button they can add new record and system will assign a
new Class No.
The Class No is an primary key , text field size 4(I
converted this table from another application), the
current number could be 1000 or 1500 or 2000. You can see
gap in between two number. How to find a new number in
between?

You'll have to query the table to find a gap:

SELECT Min([ClassNo]) + 1 AS Available
FROM [tblClassNo] LEFT JOIN [tblClassNo] AS X
ON [tblClassNo].[ClassNo] + 1 = X.[ClassNo]
WHERE X.[ClassNo] Is Null