Auto generate number

[Chris]

Ok, I have tried some info here, but am still having trouble realizing my
goal lol. So i would like to post what i want and what track i am currently
on, i am having troubles getting the code to work.
Ok i want to display auto generated numbers on records in the format of
yy-000, where yy is the 2 digit current year, and 000 is a number adding up
+1 each time. i started by having 2 fields, number and year. i am storing
the 2 digit year, i know you can work with them, but i figure i have to store
the current year because i want to use it for comparison. I dont have to
display the 2 fields on the form, after code i can combine the yy and the 000
on one display box, i dont have to store it in the format yy-000 just display
it as such. Now, i want to store the number as 000 format, so i want 001,
002, so on (having problems with that too?? ). I also want this number to
reset to 001 on the beginning of a new year. So, i was thinking using
something like a nested IF, first, see if the number field is null (i dont
want to change the number that might already be there if i am just scrolling,
i just want to on adding a new record, generate a number), then if is null,
then i use the record with the highest "year" and "number"field and compare
that 2 digit "year" value with the current year using format yy. if it is
true, then something like year=format yy and number= the dmax number +1 of
the highest yy and number recrods. now if the above boolean was false, (as
in the stored year of the highest number and current year do not match) then
number would = 001 and year would = current year, using the format yy
feature. then once those are stored on a new record, i could use an unbound
text box to combine the year-number. my theory is that as long as current
year is the same as stored, the number will just keep going up, but the first
time you add a record on a new year, they wont match, and it will reset
number to 001 and store the current year which will now be higher than the
previous year, and then the number will continue to go up. Sorry for the
long post, trying to figure this out some using my limited knowledge as well,
but am having difficulties with the vb code. Thanks for reading and any
info.

Chris

 

[Marshall Barton]

Ok, I have tried some info here, but am still having trouble realizing my
goal lol. So i would like to post what i want and what track i am currently
on, i am having troubles getting the code to work.
Ok i want to display auto generated numbers on records in the format of
yy-000, where yy is the 2 digit current year, and 000 is a number adding up
+1 each time. i started by having 2 fields, number and year. i am storing
the 2 digit year, i know you can work with them, but i figure i have to store
the current year because i want to use it for comparison. I dont have to
display the 2 fields on the form, after code i can combine the yy and the 000
on one display box, i dont have to store it in the format yy-000 just display
it as such. Now, i want to store the number as 000 format, so i want 001,
002, so on (having problems with that too?? ). I also want this number to
reset to 001 on the beginning of a new year. So, i was thinking using
something like a nested IF, first, see if the number field is null (i dont
want to change the number that might already be there if i am just scrolling,
i just want to on adding a new record, generate a number), then if is null,
then i use the record with the highest "year" and "number"field and compare
that 2 digit "year" value with the current year using format yy. if it is
true, then something like year=format yy and number= the dmax number +1 of
the highest yy and number recrods. now if the above boolean was false, (as
in the stored year of the highest number and current year do not match) then
number would = 001 and year would = current year, using the format yy
feature. then once those are stored on a new record, i could use an unbound
text box to combine the year-number. my theory is that as long as current
year is the same as stored, the number will just keep going up, but the first
time you add a record on a new year, they wont match, and it will reset
number to 001 and store the current year which will now be higher than the
previous year, and then the number will continue to go up. Sorry for the
long post, trying to figure this out some using my limited knowledge as well,
but am having difficulties with the vb code. Thanks for reading and any
info.

Man, is that difficult to read, don't know why I waded
through the whole thing.

You really should follow the standard rules of English for
capitalizing words at the beginning of sentences and proper
nouns. Separating different thoughts into individual
paragraphs also makes it easier to follow your explanation.

Anyway, I think maybe you're looking for the logic to manage
the numbers for each year. If so, then use code something
like this air code in the form's BeforeUpdate event
procedure:

If Me.NewRecord Then
Me.yearfield = Format(Date, "yy")
Me.numfield = Nz(DMax("numfield", "thetable", _
"yearfield =" & Me.yearfield), 0) + 1
End If

Note that you should not use names like Year and Number
since they are reserved words.

 

[Tim Ferguson]

Man, is that difficult to read, don't know why I waded
through the whole thing.

You really should follow the standard rules of English for
capitalizing words at the beginning of sentences and proper
nouns. Separating different thoughts into individual
paragraphs also makes it easier to follow your explanation.

It's also been answered already the first time (s)he posted it. Can't
write, can't read..?


Tim F