Default Max value plus one

In my form, I am trying to set up a field so that its default is the
max of that field + 1. I have gone to my form, design view and
clicked on the data tab. From there I clicked on the elipses next to
default value and built an expression. Here is what the expression
builder has given me.

=Max([AssetNumber])+1

It seems to always think that Max([AssetNumber]) is 0. In the table
design view, I have the default value set to 0. I can't seem to
change that to be based upon other values in the field.

Does what I'm asking for make sense?

Thanks for any help you can give,

Matt

Re: Default Max value plus one

"mcolson" wrote in message
news:6dcc1bb5-db39-4697-844f-ca5c39d7a50d@w56g2000hsf.google groups.com...
> In my form, I am trying to set up a field so that its default is the
> max of that field + 1. I have gone to my form, design view and
> clicked on the data tab. From there I clicked on the elipses next to
> default value and built an expression. Here is what the expression
> builder has given me.
>
> =Max([AssetNumber])+1
>
> It seems to always think that Max([AssetNumber]) is 0. In the table
> design view, I have the default value set to 0. I can't seem to
> change that to be based upon other values in the field.
>
> Does what I'm asking for make sense?
>
> Thanks for any help you can give,
>
> Matt

The DMAX() function may work. But it will slowdown paging between records.
Also if this is a multi-user system, more then one user may get the same
default value.

Re: Default Max value plus one

Here are a load of useful functions to replace DLookup, DMax, DLast etc.
Much faster

Not original I'm afraid. Nicked from Allen Browne - Thanks Allen

So to answer your question, Matt

EMax(AssetNumber) + 1 should work

Phil

Function EMin(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMin
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. abrowne1@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMin = Null
Else
EMin = rs(0)
End If
rs.Close

Exit_EMin:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMin:
' MsgBox Err.Description, vbExclamation, "EMin Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMin = CVErr(5) 'Out of range.
Else
EMin = CVErr(Err.Number)
End If
Resume Exit_EMin

End Function

Function EMax(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EMax
'Purpose: Faster and more flexible replacement for Emin()
'Arguments: Same as Emin, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. abrowne1@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & " ORDER BY " & expr & " DESC"
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
EMax = Null
Else
EMax = rs(0)
End If
rs.Close

Exit_EMax:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EMax:
' MsgBox Err.Description, vbExclamation, "EMax Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EMax = CVErr(5) 'Out of range.
Else
EMax = CVErr(Err.Number)
End If
Resume Exit_EMax

End Function
Function ELast(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_ELast
'Purpose: Faster and more flexible replacement for ELast()
'Arguments: Same as ELast, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. abrowne1@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg)
If rs.RecordCount = 0 Then
ELast = Null
Else
rs.MoveLast ' Last record
ELast = rs(0)
End If
rs.Close

Exit_ELast:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELast:
' MsgBox Err.Description, vbExclamation, "ELast Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELast = CVErr(5) 'Out of range.
Else
ELast = CVErr(Err.Number)
End If
Resume Exit_ELast

End Function

Function EFirst(expr As String, domain As String, Optional Criteria)

On Error GoTo Err_EFirst
'Purpose: Faster and more flexible replacement for EFirst()
'Arguments: Same as EFirst, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. abrowne1@bigpond.net.au, Phil S

Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg)
If rs.RecordCount = 0 Then
EFirst = Null
Else
rs.MoveFirst ' First record
EFirst = rs(0)
End If
rs.Close

Exit_EFirst:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_EFirst:
' MsgBox Err.Description, vbExclamation, "EFirst Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
EFirst = CVErr(5) 'Out of range.
Else
EFirst = CVErr(Err.Number)
End If
Resume Exit_EFirst

End Function
Public Function ELookup(expr As String, domain As String, Optional Criteria,
Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for ELookup()
'Arguments: Same as ELookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. abrowne1@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function


"mcolson" wrote in message
news:6dcc1bb5-db39-4697-844f-ca5c39d7a50d@w56g2000hsf.google groups.com...
> In my form, I am trying to set up a field so that its default is the
> max of that field + 1. I have gone to my form, design view and
> clicked on the data tab. From there I clicked on the elipses next to
> default value and built an expression. Here is what the expression
> builder has given me.
>
> =Max([AssetNumber])+1
>
> It seems to always think that Max([AssetNumber]) is 0. In the table
> design view, I have the default value set to 0. I can't seem to
> change that to be based upon other values in the field.
>
> Does what I'm asking for make sense?
>
> Thanks for any help you can give,
>
> Matt