Record Number

[FrancisC]

I am creating an application using an Access databse and ADO. In one of my table, I need to create a unique record number. I need it to stay up-to-date of course when doing inserts, but also when deleting records.

Does anyone have ideas, tips, examples of how this is done ?

Thanks !

[shragel]

This is the code I use

Dim cdbs As Database, IRST As Recordset
Set cdbs = OpenDatabase("c:\my documents\friedman.mdb")
Set IRST = cdbs.OpenRecordset("SELECT INV FROM CONTRACTS ORDER BY INV")' INV is the record containing the unique invoice number
IRST.MoveLast
IRST.MoveFirst
WW = IRST.RecordCount
For WX = 0 To WW - 1
If ((WX + 1) = IRST.Fields(0)) = False Then 'Checks for the first record number availible starting from 1
IN1 = WX + 1
GoTo Con 'If Found continues after the search with IN1 Having the number 
End If
IRST.MoveNext
Next

feel free to email me

[sanon]

The basic idea of my solution is the program will retrieve the latest id number from the table every time before inserting a new record. However, in case of deleting, we have to let that id number be skipped, e.g. record# 1, 2, 3 and users delete the record# 2; as a result, our database is going to be #1, #3. Well, you may move the next record to replace the one that was just deleted, but I don't think that's a good idea and also, it's not practical.

[FrancisC]

Thanks everyone...

For anyone interested, I found the 'AutoIncrement' column property that does automatically what Sanon was talking about. It still doesn't do exaclty what I want, but it does generate a unique row number.

tbl.Columns("RECORD_NUM").Properties("AutoIncrement") = True

This is the syntax I use right after building the table in ADO and right before appending it to the catalog.

I hope this can help somebody...