Discussion:
How to generate a auto number or increment number
(too old to reply)
MacrossHunter77 via AccessMonster.com
2008-04-08 20:56:42 UTC
Permalink
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
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200804/1
strive4peace
2008-04-09 05:08:46 UTC
Permalink
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 :)
*
Post by 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
MacrossHunter77 via AccessMonster.com
2008-04-16 17:21:59 UTC
Permalink
WHOAA THANK YOUSO MUCH it works like a charm Thanks again you rule!!!!
Post by 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
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 :)
*
Post by 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
[quoted text clipped - 6 lines]
Post by MacrossHunter77 via AccessMonster.com
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
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200804/1
strive4peace
2008-04-19 21:20:12 UTC
Permalink
you're welcome ;) happy to help

Warm Regards,
Crystal

*
(: have an awesome day :)
*
Post by MacrossHunter77 via AccessMonster.com
WHOAA THANK YOUSO MUCH it works like a charm Thanks again you rule!!!!
Post by 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
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 :)
*
Post by 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
[quoted text clipped - 6 lines]
Post by MacrossHunter77 via AccessMonster.com
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
j***@gmail.com
2015-09-30 20:01:26 UTC
Permalink
Post by strive4peace
you're welcome ;) happy to help
Warm Regards,
Crystal
*
(: have an awesome day :)
*
Post by MacrossHunter77 via AccessMonster.com
WHOAA THANK YOUSO MUCH it works like a charm Thanks again you rule!!!!
Post by 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
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 :)
*
Post by 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
[quoted text clipped - 6 lines]
Post by MacrossHunter77 via AccessMonster.com
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
Hi I am new to access and Need help on the following
I need to create a work order number automatically (the format is 001) and it is by the product ID. Eg. select the Product ID and get the next work order number for that product, if there is not work order number for that product, the work order number should be 001.
c***@gmail.com
2016-01-14 16:58:16 UTC
Permalink
please help tried your code in access 2010 but got an compiler error message user not defined

thanks.

Loading...