If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
custom numbering which resets when new year is entered
As Rick suggested, use the form's Before Update event to reduce the
possibility that two users will attempt to grab the same number at the same time. You may want to test the number, just to guard againt the coincidence of two users creating a record at the same instant. One thing I have done in such cases is to create a custom function to create the number. This would be in the form's code module: Public Function CreateNumber () Dim lngNum as Long lngNum = Nz(DMax("[MyField]","[MyTable]", _ "Year([DateField]) = " & Year(Date()),0) + 1 Me.MyField = lngNum End Function This way you can create the MyField value at something like the form's Before Insert event so it is visible to the user from the start of creating the record. Note that you are not using DefaultValue in this case: Private Sub Form_BeforeInsert(Cancel As Integer) CreateNumber End Sub Then in the form's Before Update event: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim lngTest as Long lngTest = DCount("[MyField]","[MyTable]", _ "Year([DateField]) = " & Year(Date()),0) If lngTest 0 Then CreateNumber End If End Sub Again, this is a way of letting the user see the number when the record is first created. If that is not needed, just use the form's Before Update event to create the number. You could try testing by having two users in sight of each other attempt to save a new record at the same time ("one, two, three, SAVE") to see if you can create a duplicate value in MyField. If you are reasonably satisfied this will not occur, the Before Update event will be OK for your purposes. If it is possible to create a duplicate number you may want to use the form's After Update event to check again. If it is a duplicate, return to the record, clear the value from MyField, and instruct the user to save the record again. Another thing you could do is to create an incremented primary key as shown in the link. It does not need to be seen, so you can set its visible property to No. It would be a separte field from MyField. Use the same error handling as shown in the sample for a myulti-user database, except add a call to the function CreateNumber. I have to admit I have not used this exact technique, but I think it would work, and it would eliminate the need to check and recheck the MyField value. "jaworski_m" wrote in message ... 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" wrote in message ... 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 he http://www.rogersaccesslibrary.com/f...c2 eb2a8z6c2z |
|
Thread Tools | |
Display Modes | |
|
|