On 30 Nov 2003 14:36:21 -0800,
da*******@yahoo.com (Katie M) wrote:
I would create a new table:
YearForBlock
BlockBeginNo
BlockEndNo
BlockCurrentNo
I would add the data for 2003 and 2004.
Then write a public function that based on the current year
(=Year(Date)) would either return an error because the BlockEndNo has
been exceeded or YearForBlock doesn't exist, or return a new number
and increment BlockCurrentNo. I would do this in a transaction, so
reading and updating both happen, or neither happens. If it's a
multiuser app, I would also add error handling to deal with a locked
table (likely meaning someone else was just getting a number; I would
sleep (Sleep(500)) a bit and try again a few times.
Then in your code call this function when you need a new number. You
can also call this function from an Append query, if that's the way
you add a new record.
If your number field currently is an Autonumber, convert it to Long
Integer.
-Tom.
I am working on a license program in Access 2000. Every year, the county
assigns a block of numbers to be used. When someone applies for a license,
they are assigned from that group. For example, the block of number for
2002 was 11451 to 11800. The starting number was 11451, so the first person
who applied for the license got that number. The next person got 11452.
Each year, the block of number changes. So this year, the numbers start
with 19001. When the person enters in the information on a form, the next
License number will be assigned. How do I do this?
Thank you for your help
Katie