|
(Q) I have a field which contains records in the format
"REC-1", "REC-2", "REC-3" etc. For new records, how can I
automatically determine the value of this field for a new record such that the numerical
part of the field gets incremented by 1.
(A) Use the Right and DMax function to return the numeric part of the
highest value of the field "FOO" and add one to it, concatenating the result in
the end to "REC-". You can either use the following example as the DefaultValue
of the field or assign it manually on the form using AfterUpdate event of another control.
For example, if the field name in the above example is "FOO" and the tableName
FOOTable, then the expression would be
="REC-" & right(DMax("FOO", "FOOTable"), _
Len(DMax("FOO", "FOOTable")) - _
InStr(1, DMax("FOO", "FOOTable"), "-")) + 1
Note: As the multiple calls to DMax function can slow down this operation on a large
table, I'd suggest against using such an expression as DefaultValue. Instead, assign this
new value to a hidden control on your form which is bound to field FOO. This way you only
have to use DMax once. For example,
Private Sub SomeField_AfterUpdate()
Dim strMax as string
strMax =DMax("FOO", "FOOTable")
me!HiddenFooCtl = "REC-" & right(strMax, _
len(strMax) - _
Instr(1,strMax, "-")) +1
End Sub
|
