UserDefCounter

Dim db as DataBase
Dim rs As RecordSet
Dim t%
Dim SQL$
Const tbnaam$ = [yourtablename]
Const DBNaam$ = [yourdatabasename]

Set db = OpenDatabase(DBNaam)
         t% = 1
        SQL$ = "SELECT * FROM " & tbnaam & " ORDER BY number"
        Set rs = db.OpenRecordset(SQL$)
        Do While Not rs.EOF
            If rs.Fields(0).Value <> t% Then
                'show number that's missing and do what you want
	   MsgBox Str(rs.Fields(0).Value + 1)
                Exit Do
            End If
            rs.MoveNext
            t% = t% + 1
        Loop
 
'no number-gap so use t% as new counternumber

'You see.. it's possible but when your database is getting very large 
'then it will cost some time to search for a number-gap.
 I want to have my records counted. So every new record must have a new
identical number. But when i remove a record the recordnumber must stay
the same, so that the next record that i add checks if their is a gap
between the recordnumbers fils a empty recordnumber or just adds the
next recordnumber in line.

I hope you understand what i mean, and i hope it is possible.

Starting with your last question: it's always possible. Only you must 
think about the work you have to do to make it happen.

So first.. why don't you use a counter? I don't know if you use a 
database but with MS Access you can implement a automatic counter. 
Of course you get a gap if you delete one or more records BUT the most 
essential thing about a counter is that it stays unique! (A counter is 
NOT for counting how many records you have in a database!).

If you really want to use your way of working then you are in need of 
a routine wich checks if a number is missing. If so then use that 
number; if not then expand with a new one.

One way to do this is like this:

1. open recordset sorted by the counterfield
2. start a loop beginning with the value of the first counterfield
3. in this loop check if the value of the counterfield is the same 
    as the value of the loop
4. if this is not the case then you have found a gap: use that number
5. if you reach the end of the recordset then there is no gap: 
use a new number

Dim db as DataBase
Dim rs As RecordSet
Dim t%
Dim SQL$
Const tbnaam$ = [yourtablename]
Const DBNaam$ = [yourdatabasename]

Set db = OpenDatabase(DBNaam)
t% = 1
SQL$ = "SELECT * FROM " & tbnaam & " ORDER BY number"
Set rs = db.OpenRecordset(SQL$)
Do While Not rs.EOF
If rs.Fields(0).Value <> t% Then
'show number that's missing and do what you want

MsgBox Str(rs.Fields(0).Value + 1)
Exit Do
End If
rs.MoveNext
t% = t% + 1
Loop

'no number-gap so use t% as new counternumber

'You see.. it's possible but when your database is getting very large

'then it will cost some time to search for a number-gap.

I want to have my records counted. So every new record must have a new
identical number. But when i remove a record the recordnumber must stay
the same, so that the next record that i add checks if their is a gap
between the recordnumbers fils a empty recordnumber or just adds the
next recordnumber in line.

I hope you understand what i mean, and i hope it is possible.

Starting with your last question: it's always possible. Only you must
think about the work you have to do to make it happen.

So first.. why don't you use a counter? I don't know if you use a
database but with MS Access you can implement a automatic counter.
Of course you get a gap if you delete one or more records BUT the most
essential thing about a counter is that it stays unique! (A counter is
NOT for counting how many records you have in a database!).

If you really want to use your way of working then you are in need of
a routine wich checks if a number is missing. If so then use that
number; if not then expand with a new one.

One way to do this is like this:

1. open recordset sorted by the counterfield
2. start a loop beginning with the value of the first counterfield
3. in this loop check if the value of the counterfield is the same
as the value of the loop
4. if this is not the case then you have found a gap: use that number
5. if you reach the end of the recordset then there is no gap:
use a new number