Auto numbering

[jeff1]

I know that there are many threads concerning this same question, I'v read many many many of them w/ no resolution.

I have a table that has a field that I need to increase by one number based on the max number in the table. After each update(new record added) to the table I would like a way to auto perform such a function. For the life of me I cannot get any of the solutions offered in other threads to work.

Thank you in advance for your time.

 

[willir]

jeff1

Basically, I gather you do NOT want to use the autonumber feature.

Okay, here is how I would do this...
Create a public module in VBA.

There are variations in this, but something like this should work...

[blue]
public sub FindMax (tblAny as string, lngCount as long)

dim dbs as DAO.database, rst as DAO.recordset

set dbs = currentdb()
set rst = dbs.openrecordset(tblAny)

rst.movelast
lngCount = rst.recordcount

'since you want the next number
lngCount = lngCount + 1

end sub
[/blue]

Then from the form in question...
[blue]
Dim tblAny As String, lngCount As Long

tblAny = "YourTableName"
FinMax tblAny, lngCount

'lngCount will have the number records plus one
'You can display it on the form...
'Me.MyTableCountFieldName = lngCount
[/blue]

Hope this is what you are looking for.
Richard