Autonumber that resets at 10,000??

[natemclain]

Basically what Im looking for is code I can use that will count from 1-10,000 and reset to 1 again. And just increment by 1 each time.
Can anyone explain to me how to program this with VBA??

 

[tlbroadbent]

I'd do this.[ol][li]Create a table with two columns TblName, RowID.
Example: TblName="MyTable" RowID=0

[li]Create a public function in a module to find the next RowID value.

Public Function GetRowID () As Integer

Dim sSQL As String, iRowID As Integer

sSQL = "Update RowIDTable Set RowID=RowID+1 Where TblName='MyTable'"
DoCmd.RunSQL(sSQL)

iRowID = DMax("[RowID]","RowIDTable","[TblName]='MyTable'&quot
If iRowID > 10000 Then
sSQL = "Update RowIDTable Set RowID=1 Where TblName='MyTable'"
DoCmd.RunSQL(sSQL)
iRowCnt = 1
Endif

GetRowID = iRowID

End Function

[li]You can call the function from another VB module or from a query.[/ol] Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[natemclain]

Terry,

I get a Runtime Error 3078 when I do this.
Do you know why I would get this error with my VBA code?
When I hit debug it goes to this line:
DoCmd.RunSQL(sSQL)

 

[tlbroadbent]

The error indicates the table name is incorrect. Jet Engine could not find the table name you supplied in the sSQL string. Make sure you use the correct names. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous