Assign new record number

  • Thread starter Thread starter PAUL
  • Start date Start date
P

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.
 
PAUL said:
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?
Click to expand...


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
 
Back
Top