Counter Advance By One

[burrina]

I have a module and a table that I am trying to use to advance a number by 1
Module is named ModEvenIDCounter and table is named EventCounterTable
table EventCounterTable has one field named NextCustomerEventIDCounter which is a primary key
It is Long Integer and set to no duplicates

I think my code on the module is off because when I try and increment a value into my field, all it does is add a 0


The number in the field nextCustomEventIDCounter is 999 right now
I am missing something simple I'm sure. Here is the code for the module.

Function Next_CustomEventID_Counter()


On Error GoTo Next_Custom_Counter_Err

Dim rs As ADODB.Recordset
Dim NextCounter As Long

Set rs = New ADODB.Recordset

rs.Open "EventCounterTable", CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'Open the ADO recordset.

NextCounter = rs!NextCustomEventIDCounter 'Get the next counter.

rs!NextEventIDCounter = NextCounter + 1
NextCounter = rs!NextEventIDCounter
rs.Update

'MsgBox "Next available counter value is " & Str(NextCounter)
rs.Close

Set rs = Nothing

[alcapps]

I would turn off the autonumber field... none primary key..
if you are controlling the table and field. get the current number and + 1 save. If this is one row to keep the counter..
another suggestion is create a function that gets the next number by getting the max number from the table and add 1 and return that as your seq number instead of getting it from a field.
From what I have seen.. getting the max number from a table is better than having a default counter in a table.

so if you table has a max integer number of 322 then you + 1 it and add the record. if there was a problem. and you back out the record then the next number is still 322 + 1.. if you delete the record later your going to have gaps.. then you can have processes that squeeze the Id's if you need to do that. That is something you can't do with autonumbers.

hope this helps..

[burrina]

Thank You, that will work!