1. You don't need the If….Else….EndIf construct. You can simplify it to a
single line with:
Me.[IDNum] = Nz(DMax("[IDNum]", "[YourTableName]"),0) + 1
2. The statement:
"Number" fields that aren't used for math really should be defined as Text
doesn't really have any sound rationale. An attribute whose values are of
an ordinal or cardinal nature would be appropriately modelled by a column of
number data type without being involved in any mathematical operations.
Values of number data type will sort correctly of course, whereas text values
won't, e.g. "12" sorts before "2". I think what you have in mind are
'numbers' which are really coding systems; telephone numbers or zip codes are
a case in point. For these a text data type is appropriate.
3. Its worth mentioning that if the DMax function is used to compute the
next number in a sequence conflicts can arise in a multi-user environment if
two or more users are inserting a record simultaneously. The first user to
save their record will succeed, the other(s) will experience a data error in
view of the key violation. This should either be handled in the form's Error
event procedure, or the error can be avoided in the first place by ensuring
user's cannot simultaneously obtain the same number. One way of doing this,
which also includes a means of setting the next number at which a sequence
will continue, can be found at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps
Ken Sheridan
Stafford, England
Linq Adams via AccessMonster.com said:
I'm glad you have your problem of an unwanted record being generated solved,
but as far as an AutoNumber being "wasted" and not being available to be
"used again," it really shouldn't matter! AutoNumbers should never be
seen/used by the end users! AutoNumbers are intended to be used for one
purpose and only one purpose, to provide a unique identifier for each record.
Here's a post I've archived from a gentleman named John Vinson, MVP,
explaining how AutoNumbers work:
******************************************
"When using Autonumber, do be aware that there will be gaps in the numbering -
any record that's deleted will leave a gap; hitting <Esc> after starting a
record will leave a gap; adding records using an Append query may leave a
gap, often a huge one; replicating the database will make your invoice
numbers random.
In short... it's best NOT to use Autonumbers for human consumption, and
particularly not for consumption by accountants and auditors. Invoice
sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such
people get very nervous."
*****************************************
Yo use an ID type number, you need to use an auto-incrementing number hack
such as one of these.
The first code here would be for an IDNumber that is defined in the table as
Text datatype. "Number" fields that aren't used for math really should be
defined as Text.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = "1"
Else
Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
End If
End If
End Sub
If you insist on using a Numerical field:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1
Else
Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
End If
End If
End Sub