How to generate a auto number or increment number

[MacrossHunter77 via AccessMonster.com]

Hello everyone I am kind of new on using Access so here is m thing I need
too create a tracking number that start like this "CUPR0800001" ok I need to
generate this tracking# for the table but I want that everytime I open the
form automatically will generate the next number for example "CUPR0800002",
"CUPR0800003" and so on ok first CUPR is the title of the program 08 is the
current year and 00000 this five number are the one that I want to create
automatically ok now for example let's say today is January 1, 2009 then when
I open the form it will create this new tracking "CUPR0900001" and so on now
anybody can give a idea on how to create the table and the form to make it
happen I would really appreciate if anybody can help me on this thanks

 

[strive4peace]

here is code you can put behind the form to make your number

'~~~~~~~~~~~
dim mStr as string _
, mNextNumber as Long

mStr = "CUPR" & format(Date(),"yy")

mNextNumber = nz( dMax("right([fieldname],5)" _
, "[Tablename]" _
, "Left([fieldname],6) ='" & mStr & "'"),0) + 1

me.controlname = mStr & format(mNextNumber, "00000")
'~~~~~~~~~~~~~

WHERE
fieldname is the name of the field
Tablename is the name of the table
controlname is the name of the control on the form you want to write the
value to

As for telling you how to set up your tables, read this:

Access Basics (on Allen Browne's site)
http://www.allenbrowne.com/casu-22.html
8-part free tutorial that covers essentials in Access

Allen has a wealth of information on his site; after you get to the
bottom of this link, click on 'Index of Tips'

~~~~~~~~~~~~~~~~

once you get done reading Access Basics, if you have any questions on
the code above, please specify each line you do not understand and we
will help again


Warm Regards,
Crystal

*
have an awesome day
*

 

[MacrossHunter77 via AccessMonster.com]

WHOAA THANK YOUSO MUCH it works like a charm Thanks again you rule!!!!

here is code you can put behind the form to make your number

'~~~~~~~~~~~
dim mStr as string _
, mNextNumber as Long

mStr = "CUPR" & format(Date(),"yy")

mNextNumber = nz( dMax("right([fieldname],5)" _
, "[Tablename]" _
, "Left([fieldname],6) ='" & mStr & "'"),0) + 1

me.controlname = mStr & format(mNextNumber, "00000")
'~~~~~~~~~~~~~

WHERE
fieldname is the name of the field
Tablename is the name of the table
controlname is the name of the control on the form you want to write the
value to

As for telling you how to set up your tables, read this:

Access Basics (on Allen Browne's site)
http://www.allenbrowne.com/casu-22.html
8-part free tutorial that covers essentials in Access

Allen has a wealth of information on his site; after you get to the
bottom of this link, click on 'Index of Tips'

~~~~~~~~~~~~~~~~

once you get done reading Access Basics, if you have any questions on
the code above, please specify each line you do not understand and we
will help again

Warm Regards,
Crystal

*
have an awesome day
*

Hello everyone I am kind of new on using Access so here is m thing I need
too create a tracking number that start like this "CUPR0800001" ok I need to

[quoted text clipped - 6 lines]

anybody can give a idea on how to create the table and the form to make it
happen I would really appreciate if anybody can help me on this thanks

 

[strive4peace]

you're welcome happy to help

Warm Regards,
Crystal

*
have an awesome day
*

WHOAA THANK YOUSO MUCH it works like a charm Thanks again you rule!!!!

here is code you can put behind the form to make your number

'~~~~~~~~~~~
dim mStr as string _
, mNextNumber as Long

mStr = "CUPR" & format(Date(),"yy")

mNextNumber = nz( dMax("right([fieldname],5)" _
, "[Tablename]" _
, "Left([fieldname],6) ='" & mStr & "'"),0) + 1

me.controlname = mStr & format(mNextNumber, "00000")
'~~~~~~~~~~~~~

WHERE
fieldname is the name of the field
Tablename is the name of the table
controlname is the name of the control on the form you want to write the
value to

As for telling you how to set up your tables, read this:

Access Basics (on Allen Browne's site)
http://www.allenbrowne.com/casu-22.html
8-part free tutorial that covers essentials in Access

Allen has a wealth of information on his site; after you get to the
bottom of this link, click on 'Index of Tips'

~~~~~~~~~~~~~~~~

once you get done reading Access Basics, if you have any questions on
the code above, please specify each line you do not understand and we
will help again

Warm Regards,
Crystal

*
have an awesome day
*

Hello everyone I am kind of new on using Access so here is m thing I need
too create a tracking number that start like this "CUPR0800001" ok I need to

[quoted text clipped - 6 lines]

anybody can give a idea on how to create the table and the form to make it
happen I would really appreciate if anybody can help me on this thanks