count # of records

[arage]

Hi,
I’ve been thinking on a new form that I’ll need to make soon and what I’d like to have on it, is a message that tells the user the next available tracking # they can assign to a new record.

Something like, ‘the next available tracking # is 10’

For this message to appear in a control I need to be able to ‘scan’ all the records in a table and then add 1 to it and then reference this number in my message.

How would I get the record count from a table using vba?
Thanks!

 

[BarkerD]

You can do this by creating a recordset object.

Dim rst as DAO.Recordset
Dim db as DAO.Database
Dim intCount as Integer

Set db=CurrentDB
Set rst=db.OpenRecordset("TableName")

'Move to the last record to populate it.
rst.MoveLast
intCount=rst.RecordCount

'You can then inform the user of the next available number.

msgbox$("Your next available tracking # is " & intcount + 1)

'Or you can set your default value

Hope this helps.

Duane Barker