Code to find max value of a field

[BroncoMarc]

I need to figure out how to automatically insert the next record number when someone hits New in a form to add a record. Easy enough.. Autonumber, right? Nope.. the field they're using in this database is setup as Text and there are a couple of records that have letters in the field. I don't want to mess with the design of the table to do this.

I tried exprimenting with a query. I made a query with one column that says: MaxProjNum: Max(Int(Val([ProjNum]))) The query works great.. It returns 1 record with the highest value of ProjNum.

I planned on refering to that and adding 1 to create the next ProjNum. Then I reaized I had no idea how to refer to the results of that query from VB code.

How do I code txtProjNum = (value from qryMaxProjNum) + 1 ?

Or, Is there a way to skip the query and figure out the max value all from VB?

Thanks..
- Marc

 

[Pauldohert]

txtProjNum = DMax("fieldname", "tablename") + 1

 

[BroncoMarc]

Thanks!! I changed it to the following and it worked.

txtProjNum = DMax("val(ProjNum)", "tablename") + 1

- Marc