Auto Log Number

[Gary]

Ok, with my DB everything is hinged on the "Log Number†This is the number
that is the relationship between most of the forms/ and sub forms/tables.
This number needs to be automatic, and un-modifiable. The format needs to be
2009-0001, and then the next entry needs to be 2009-0002. I can’t use auto
number because this will be audited for missing numbers. I have tried
everything I can find, and read every thing I can get but I just can not
grasp how to do this. So if you know how please put it in as simple and
noob-proof lingo so someone with little to no knowledge of Access can follow.
I know the subject of auto-numbers has been discussed tirelessly but I have
read most of them and they don’t offer much help.

 

[Steve]

The easiest way to do this is to first add a TransactionDate field to your
table and then on your form add a field named TransactionDate. Also only
record 1, 2,3, etc rather than 0001, 0002, 0003, etc. When you need to
display the Log Number in a form or report, use the following expression:
Year([TransactionDate]) & "-" & Format([LogNumber],"0000")

Steve

 

[John... Visio MVP]

The easiest way to do this is to first add a TransactionDate field to your
table and then on your form add a field named TransactionDate. Also only
record 1, 2,3, etc rather than 0001, 0002, 0003, etc. When you need to
display the Log Number in a form or report, use the following expression:
Year([TransactionDate]) & "-" & Format([LogNumber],"0000")

Steve

Steve, just go away! From your posts earlier today, it is obvious you do not
plan on respecting the FREE nature of the help offered in these newsgroups.

John... Visio MVP

 

[Gina Whipp]

Gary,

If your Log Number is going to always start with Current Year then you could
use Year(Date()) & "-" & Format(Right(Val([YourLogNumber]),4),"0000")+1)
Of course, all of this assumes you have initial Log Numbers. If not then
will need to account for starting value.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gary]

How would i account for the starting value? and how could i "use" this
Year(Date()) & "-" & Format(Right(Val([YourLogNumber]),4),"0000")+1)

Gary,

If your Log Number is going to always start with Current Year then you could
use Year(Date()) & "-" & Format(Right(Val([YourLogNumber]),4),"0000")+1)
Of course, all of this assumes you have initial Log Numbers. If not then
will need to account for starting value.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Ok, with my DB everything is hinged on the "Log Number" This is the number
that is the relationship between most of the forms/ and sub forms/tables.
This number needs to be automatic, and un-modifiable. The format needs to
be
2009-0001, and then the next entry needs to be 2009-0002. I can't use
auto
number because this will be audited for missing numbers. I have tried
everything I can find, and read every thing I can get but I just can not
grasp how to do this. So if you know how please put it in as simple and
noob-proof lingo so someone with little to no knowledge of Access can
follow.
I know the subject of auto-numbers has been discussed tirelessly but I
have
read most of them and they don't offer much help.

 

[Gina Whipp]

Gary,

As I stated IF the first part of your Log Number is based on current year
then Year(Date()) would give you that. As for the second part of the number
what you are doing is turning '0001' into a Value as presently, it must be
being stored in a text field, then adding 1 and then reformatting back into
the 4 digit place holder. HOWEVER, if you have no starting value in your
table then a little VBA code is going to required.

As for where to put this, it actually depends on how you are enterting new
Log Numbers, via a Command Button OR do you want it to happen automatically
OR when you begin to enter a particular field? For it to just assign the
number place the line in the Before_Update event of the form:

Me.txtYourField = Year(Date()) & "-" &
Format(Right(Val([YourLogNumber]),4),"0000")+1)

Note, if you back out of the record without entering anything you will need
some code to back out the Log Number. Also, if this a multi-user
environment then you will want to commit the Log Number by saving so that
two or more users don't end up overwriting each other.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

How would i account for the starting value? and how could i "use" this
Year(Date()) & "-" & Format(Right(Val([YourLogNumber]),4),"0000")+1)

Gary,

If your Log Number is going to always start with Current Year then you
could
use Year(Date()) & "-" & Format(Right(Val([YourLogNumber]),4),"0000")+1)
Of course, all of this assumes you have initial Log Numbers. If not
then
will need to account for starting value.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Ok, with my DB everything is hinged on the "Log Number" This is the
number
that is the relationship between most of the forms/ and sub
forms/tables.
This number needs to be automatic, and un-modifiable. The format needs
to
be
2009-0001, and then the next entry needs to be 2009-0002. I can't use
auto
number because this will be audited for missing numbers. I have tried
everything I can find, and read every thing I can get but I just can
not
grasp how to do this. So if you know how please put it in as simple
and
noob-proof lingo so someone with little to no knowledge of Access can
follow.
I know the subject of auto-numbers has been discussed tirelessly but I
have
read most of them and they don't offer much help.