On Mon, 26 May 1997 23:28:04 +0200, johan wrote:
>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
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)
t% = t% + 1
'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.