Need To Generate AutoNumbers

[Guest_imported]

Hi,
There was a suggestion to generate autonumbers in the dbase (Access) itself, by setting the field datatype to autnumber. But that gives several problems when records are deleted / added.
So is there any way to generate numbers (serially form 1 to 9999) through VB coding itself.
Thanx

 

[CodeHammer]

A few things you might think about.
1) Any numbering system will end up with gaps in it if you allow records to be removed unless you are willing to renumber the entire table each time a record is added or deleted. In that case the record numbering system has little or no value.
2) You can use a "Max" statement in a SQL string to return the highest value currently in the database/table/field, simply add 1 and you have your next record number.
3) My preference is to keep a counter table in the database that allows me to store the last used record number for each table that I wish to number.

 

[Guest_imported]

Ok I will try to use the max statement (In my access dbase).
Let's say: I have

SerialNumber <Integer>
Name <text>
..
..
Count < ? >

What would be the datatype here, autonumber or integer.
If it is autonumber there is the above problwm (My first post) else ho to handle the first record that is going to be added.
thanks

 

[elmi]

The datatype should be integer. Because you want to get the Max value yourself. For the first record in your table you can handle the problem like this:
..
rs.Open &quot;Select Max(Count) From Table1&quot; ,cn1

If isnull(rs(0)) then
NewNumber = 1
Else
NewNumber = rs(0)+1
End If