> Hello,
> Would you be so kind and advices how below expresion should be structred
> to
> enable proper numbering in multi user enviroment? Unfortunately, sugested
> link does not respond to above question as assumtions taken in that
> example
> are different than in mine case - auto numbering reset each new calendar
> year. The suggested example of numbering in the muti user enviroment based
> on
> the tabel prioperites (unique values feature), which is not relevant tn my
> case.
>
> Regards,
>
>
> "BruceM" wrote:
>
>> As I showed you. The optional third part of the DMax expression is the
>> Where condition:
>> "Year([DateField]) = " & Year(Date())
>>
>> This expression is telling Access to find the largest number in the field
>> MyField in the table MyTable in which the year in DateField is the same
>> as
>> the current year:
>>
>> DMax("[MyField]","[MyTable]", _
>> "Year([DateField]) = " & Year(Date())
>>
>> Use your own table and field names, of course.
>>
>> The Nz function comes into play at the first record each year. At that
>> time
>> no records that match the criteria (Year in DateField is the same as the
>> current year), so the DMax function returns Null as the result. Nz
>> converts
>> Null to 0; otherwise it doesn't touch the value returned by DMax. The
>> expression adds 1 to 0 (first record of the year) or to the largest
>> number
>> in MyField for the year.
>>
>> DefaultValue applies only to new records. If the user backs out of the
>> new
>> record without entering any data, no value is added to MyField.
>>
>> Check VBA Help for more information about these functions:
>> DMax
>> Year
>> Date
>> Nz
>> Also, look up the DefaultValue property.
>>
>> "jaworski_m" <jaworskim[ at ]discussions.microsoft.com> wrote in message
>> news:196662D8-0EA9-4B1B-8BBB-8A77ED96310E[ at ]microsoft.com...
>> > Thank you for reply.
>> >
>> > What is the way to reset sequential numbering implemented with "DMax"
>> > function when year changes (2008->2009)
>> >
>> > 2008/1
>> > 2008/2
>> > 2008/...
>> >
>> > After year change:
>> > 2009/1
>> > 2009/2
>> > 2009/...
>> >
>> > "BruceM" wrote:
>> >
>> >> Assuming that the form is based on the table MyTable or a query that
>> >> includes MyTable, MyField is the field in MyTable containing the
>> >> incremented
>> >> number (without the year), and DateField is a date field in MyTable,
>> >> in
>> >> the
>> >> form's Before Insert event:
>> >>
>> >> Me.MyField.DefaultValue = Nz(DMax("[MyField]","[MyTable]", _
>> >> "Year([DateField]) = " &
>> >> Year(Date()),0) + 1
>> >>
>> >> Note that the underscore is a line break character that is used for
>> >> convenience only. It can be used in VBA, but not in text box
>> >> expressions
>> >> ort query expressions, as I recall.
>> >>
>> >> Concatenate with the year as needed. For instance, in the form's
>> >> Record
>> >> Source query:
>> >>
>> >> FullNumber: Year([DateField]) & "/" & [MyField]
>> >>
>> >> If you want leading zeros for MyField in FullNumber:
>> >>
>> >> FullNumber: Year([DateField]) & "/" & Format([MyField],"0000")
>> >>
>> >> The expression could also be used as the Default Value of a text box
>> >> bound
>> >> to MyField.
>> >>
>> >> In a multi-user environment you will need to take precautions against
>> >> two
>> >> users creating a record at the same time and trying to use the same
>> >> number.
>> >> One way to prevent that most of the time is to wait until the form's
>> >> Before
>> >> Update event to run the code. You could also save the record as soon
>> >> as
>> >> the
>> >> number is created if you use the Before Insert event. Another way to
>> >> handle
>> >> the problem in a multi-user environment is shown here:
>> >>
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395&SID=a41d8z5zz92c1e125z9ec2eb2a8z6c2z>> >
>>
>>