Autofill a textbox

[JezzaHyde]

Hey folks,

I have a feeling that the solution to my problem is rather simple...but i can't seem to figure it out.

I have a form that is used to enter new records in to a table.

On the load of this form, i want to set the value of one particular textbox, to the maximum value contained within a field in the source table plus one.

That is, if TABLEA.FieldA contains various values and the maximum is 247, then i want the textbox to automatically fill with "248".

So far i have tried setting the default value to a query that finds the max value...but no luck there.

I think this is one of those problems that needs the perspective of a fresh pair of eyes.

Any help is much appreciated!!

Cheers

-Jezza

 

[JeremyNYC]

Make a recordset that returns the max value in that field. Add one to that value. Put the new value in your control.

put this in the Current event of your form

dim db as dao.database
dim rst as dao.recordset
dim strSql as string

set db = currentdb
strsql = "SELECT max(FieldName) as MaxFieldName" _
& " FROM tblName"
set rst = db.openrecordset(strsql, dbopensnapshot)
me!ControlName = rst!MaxFieldName + 1




Note that this will fail if there are no records with values in that field, and really should have some check in there for that, but it should get you started.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[JezzaHyde]

Hey Jeremy,

Thanks heaps for that buddy. Works like a charm.

Keep up the good work

-Jezza