default field value based on a Max expression

S

sm2007

How do you create a default value based on the current max value of that
same field in the table? What i'm trying to do is similar to having an
autonumber but I want it to be editable.

Any ideas?

Many thanks,

SMiller
 
A

Arvin Meyer [MVP]

In the DefaultValue property of a textbox use the expression:

= DMax("FieldName", "TableName") + 1

That will add 1 to the highest value in the table. Be aware that in a
multi-user environment, it is relatively easy for 2 users to grab the same
number so you should save the record asap.
 
K

Ken Sheridan

This sort of thing is generally done at form level. In the Current event
procedure of a form based on the table look up the highest value in the field
and add 1, e.g.

Me.YourField.DefaultValue = """" & DMax("YourField", "YourTable") + 1 & """"

Note that the DefaultValue property is a string expression regardless of the
data type of the field in question, hence the delimiting quotes characters in
the above code. Most of the time omitting them won't matter, but sometimes
they are essential, so its prudent to include them nevertheless.

This works fine in a single user environment, but in a multi user
environment on a network can give rise to conflicts as two or more users
could be adding records simultaneously and get the same number. There are
various ways around this, one of which can be found at the following link,
which also allows the number at which the sequence will start when the next
record is added to be reset manually:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England
 
D

Dale Fye

sm,

The problem with this field being editable on a form is that before you
write the record to your table, you will need to test to make sure that the
number is not already in use.

I have a table (tbl_AutoNum) and a function that I use for this purpose.
The table contains two fields NextRec(Long integer) and GrowBy(Integer), and
only contains one record, which usually starts out at NextRec = 1 and GrowBy
= 1. You can set the GrowBy value to any number you want, but I usually just
leave it at 1.

The function looks like:

Public Function fnAutoNum(Optional SomeValue As Variant) As Long

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tbl_AutoNum")
fnAutoNum = rs("NextRec")
rs.Edit
rs("NextRec") = rs("NextRec") + rs("GrowBy")
rs.Update
rs.Close
set rs = nothing

End Function

Each successive time you call the function, it retrieves the value of
NextRec and then increments it. This process is quick enough that I have
never had a problem with multiple users retrieving data this way.

If, for some reason, you feel the need to use this function in a query that
will return multiple records, you will need to pass it a field name, any
field name should do. Failure to do so, it will return the same number for
each record in the query.

HTH
Dale
 
S

sm2007

Thank you everyone for your help. It worked out simplest for me just to use
the "= DMax("FieldName", "TableName") + 1" function but found I could not
put it into the default value in the table but had to put it into the
default value in the form instead - which works fine.
 
Top