default value +1 by default

[aw]

Could anyone can help :

I have a table & contain Field “Invoice number†and I want to start from
number 507000 & it can automatically add +1 for new record enter. How can I
set the expression for this purpose?

I try expression =max(“[Invoice number]â€)+1 and input into the "default
value" but got error.

Thanks a lot in advance.

 

[Allen Browne]

Could you use a table with an AutoNumber field? That would automatically
assign a unique (but not necessarily sequential) number.

If you can't do that, use the BeforeInsert event procedure of your form to
drop the number in. Use DMax() to get the highest number in your table. Use
Nz() to convert to zero (if there are none.) Then add 1.

Below the function I use for this kind of thing. Paste it into a Standard
module. Then set the Before Insert property of your form to:
=NextNumber("[Invoice Table]", "[Invoice number]")
At the moment when you start entering a new record, it will drop the next
invoice number in.

Please note that if you have multiple users entering new records at the same
time, this approach might give them both the same invoice number, so the
record won't save (assuming you have a unique index on this field.)

Public Function NextNumber(strTable As String, strField As String, _
Optional strCriteria As String) As Long
On Error Resume Next
Dim lngMax As Long

lngMax = Nz(DMax(strField, strTable, strCriteria), 0&)
NextNumber = lngMax + 1&
End Function

 

[Dirk Goldgar]

Could you use a table with an AutoNumber field? That would automatically
assign a unique (but not necessarily sequential) number.

If you can't do that, use the BeforeInsert event procedure of your form to
drop the number in. Use DMax() to get the highest number in your table.
Use Nz() to convert to zero (if there are none.) Then add 1.

Below the function I use for this kind of thing. Paste it into a Standard
module. Then set the Before Insert property of your form to:
=NextNumber("[Invoice Table]", "[Invoice number]")
At the moment when you start entering a new record, it will drop the next
invoice number in.

Please note that if you have multiple users entering new records at the
same time, this approach might give them both the same invoice number, so
the record won't save (assuming you have a unique index on this field.)

Public Function NextNumber(strTable As String, strField As String, _
Optional strCriteria As String) As Long
On Error Resume Next
Dim lngMax As Long

lngMax = Nz(DMax(strField, strTable, strCriteria), 0&)
NextNumber = lngMax + 1&
End Function

Allen, am I missing something? I don't see where setting the BeforeInsert
property to that function expression will actually set the control on the
form to anything.

 

[Allen Browne]

You're right Dirk. Thanks.

1. Set the Before Insert property of the form to:
[Event Procedure]

2. Click the Build button beside this.
Access opens the code window.

3. Between the "Private Sub..." and "End Sub" lines, enter:
Me.[Invoice number] = NextNumber("[Invoice Table]", "[Invoice number]")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Could you use a table with an AutoNumber field? That would automatically
assign a unique (but not necessarily sequential) number.

If you can't do that, use the BeforeInsert event procedure of your form
to drop the number in. Use DMax() to get the highest number in your
table. Use Nz() to convert to zero (if there are none.) Then add 1.

Below the function I use for this kind of thing. Paste it into a Standard
module. Then set the Before Insert property of your form to:
=NextNumber("[Invoice Table]", "[Invoice number]")
At the moment when you start entering a new record, it will drop the next
invoice number in.

Please note that if you have multiple users entering new records at the
same time, this approach might give them both the same invoice number, so
the record won't save (assuming you have a unique index on this field.)

Public Function NextNumber(strTable As String, strField As String, _
Optional strCriteria As String) As Long
On Error Resume Next
Dim lngMax As Long

lngMax = Nz(DMax(strField, strTable, strCriteria), 0&)
NextNumber = lngMax + 1&
End Function

Allen, am I missing something? I don't see where setting the BeforeInsert
property to that function expression will actually set the control on the
form to anything.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)