creating a an automatically incrementing, formatted reference number

[coldfire]

Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a lab
number column that i want to increment to the next unused number, with
the click of a command button on the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the last
two digits of the year, and the other 4 digits incrementing with the
click of this button. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?

Thank you in advance,
coldfire

 

[Steve]

You don't need a command button to do this; it can be done automatically!

In the table the form is based on, add a field named MyPrimaryKey and make
it autonumber and add MyPrimaryKey to your form. On your for, set the
default value of LabNumber to the following expression:
Format(Year(Date()),"YY") & "-" & Format([MyPrimaryKey],"0000")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[coldfire]

You don't need a command button to do this; it can be done automatically!

In the table the form is based on, add a field named MyPrimaryKey and make
it autonumber and add MyPrimaryKey to your form. On your for, set the
default value of LabNumber to the following expression:
Format(Year(Date()),"YY") & "-" & Format([MyPrimaryKey],"0000")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a lab
number column that i want to increment to the next unused number, with
the click of a command button on the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the last
two digits of the year, and the other 4 digits incrementing with the
click of this button. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?

Thank you in advance,
coldfire

The reason that I want a button to formulate the new number is so that
i can enter older records into the database via this form, and also so
i can add duplicates with an additional rn number in another field.
Also is there anyway to format the manually input numbers so they show
up in the database how i want, not just make them look that way like
with an input mask.

 

[Steve]

You still don't need a command button. If you don'tike the default
LabNumber, just type over it.

On the data entry form, you can put code in the AfterUpdate event of a field
that formats what you just entered to appear the way you want. Note that it
will still be the way you entered it in the table. So in other forms and
reports, you need to use an unbound textbox that formats a value in a hidden
bound field in its control source.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

You don't need a command button to do this; it can be done automatically!

In the table the form is based on, add a field named MyPrimaryKey and
make
it autonumber and add MyPrimaryKey to your form. On your for, set the
default value of LabNumber to the following expression:
Format(Year(Date()),"YY") & "-" & Format([MyPrimaryKey],"0000")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a lab
number column that i want to increment to the next unused number, with
the click of a command button on the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the last
two digits of the year, and the other 4 digits incrementing with the
click of this button. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?

Thank you in advance,
coldfire

The reason that I want a button to formulate the new number is so that
i can enter older records into the database via this form, and also so
i can add duplicates with an additional rn number in another field.
Also is there anyway to format the manually input numbers so they show
up in the database how i want, not just make them look that way like
with an input mask.

 

[coldfire]

You still don't need a commandbutton. If you don'tike the default
LabNumber, just type over it.

On the data entry form, you can put code in the AfterUpdate event of a field
that formats what you just entered to appear the way you want. Note that it
will still be the way you entered it in the table. So in other forms and
reports, you need to use an unbound textbox that formats a value in a hidden
bound field in its control source.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

You don't need a commandbuttonto do this; it can be done automatically!
In the table the form is based on, add a field named MyPrimaryKey and
make
it autonumber and add MyPrimaryKey to your form. On your for, set the
default value of LabNumber to the following expression:
Format(Year(Date()),"YY") & "-" & Format([MyPrimaryKey],"0000")
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a lab
number column that i want to increment to the next unused number, with
the click of a commandbuttonon the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the last
two digits of the year, and the other 4 digitsincrementingwith the
click of thisbutton. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?
Thank you in advance,
coldfire

The reason that I want abuttonto formulate the new number is so that
i can enter older records into the database via this form, and also so
i can add duplicates with an additional rn number in another field.
Also is there anyway to format the manually inputnumbersso they show
up in the database how i want, not just make them look that way like
with an input mask.- Hide quoted text -

- Show quoted text -

This would work accept for the fact that if i entered old records in
the mitsd of entering new records the primary key would have gaps in
the new records, which is why i wanted to do it with a button, and
with code instead of basing it upon the autoincrement feature. Could
you please help in the development of some code to solve this problem.

 

[Steve]

Put the following code in the click event of your button:
Dim MaxLastFourDigitLabNumber As String
Dim NextLastFourDigitLabNumber As String
MaxLastFourDigitLabNumber = DMax("Right([LabNumber], 4)",
"NameOfTableContainingLabNumber", "Left([LabNumber],2)= Format(Date(),
'yy')")
NextLastFourDigitLabNumber = MaxLastFourDigitLabNumber + 1
Me!LabNumber = Format(Date, "yy") & "-" & Format(NextLastFourDigitLabNumber,
"0000")

Should be 5 lines of code. Watch for word wrap in MaxLast..... line and
Me!LabNumber line.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

You still don't need a commandbutton. If you don'tike the default
LabNumber, just type over it.

On the data entry form, you can put code in the AfterUpdate event of a
field
that formats what you just entered to appear the way you want. Note that
it
will still be the way you entered it in the table. So in other forms and
reports, you need to use an unbound textbox that formats a value in a
hidden
bound field in its control source.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

You don't need a commandbuttonto do this; it can be done
automatically!

In the table the form is based on, add a field named MyPrimaryKey and
make
it autonumber and add MyPrimaryKey to your form. On your for, set the
default value of LabNumber to the following expression:
Format(Year(Date()),"YY") & "-" & Format([MyPrimaryKey],"0000")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a
lab
number column that i want to increment to the next unused number,
with
the click of a commandbuttonon the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the
last
two digits of the year, and the other 4 digitsincrementingwith the
click of thisbutton. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?

Thank you in advance,
coldfire

The reason that I want abuttonto formulate the new number is so that
i can enter older records into the database via this form, and also so
i can add duplicates with an additional rn number in another field.
Also is there anyway to format the manually inputnumbersso they show
up in the database how i want, not just make them look that way like
with an input mask.- Hide quoted text -

- Show quoted text -

This would work accept for the fact that if i entered old records in
the mitsd of entering new records the primary key would have gaps in
the new records, which is why i wanted to do it with a button, and
with code instead of basing it upon the autoincrement feature. Could
you please help in the development of some code to solve this problem.

 

[coldfire]

Put the following code in the click event of your button:
Dim MaxLastFourDigitLabNumber As String
Dim NextLastFourDigitLabNumber As String
MaxLastFourDigitLabNumber = DMax("Right([LabNumber], 4)",
"NameOfTableContainingLabNumber", "Left([LabNumber],2)= Format(Date(),
'yy')")
NextLastFourDigitLabNumber = MaxLastFourDigitLabNumber + 1
Me!LabNumber = Format(Date, "yy") & "-" & Format(NextLastFourDigitLabNumber,
"0000")

Should be 5 lines of code. Watch for word wrap in MaxLast..... line and
Me!LabNumber line.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

You still don't need a commandbutton. If you don'tike the default
LabNumber, just type over it.
On the data entry form, you can put code in the AfterUpdate event of a
field
that formats what you just entered to appear the way you want. Note that
it
will still be the way you entered it in the table. So in other forms and
reports, you need to use an unbound textbox that formats a value in a
hidden
bound field in its control source.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

You don't need a commandbuttonto do this; it can be done
automatically!
In the table the form is based on, add a field named MyPrimaryKey and
make
it autonumber and add MyPrimaryKey to your form. On your for, set the
default value of LabNumber to the following expression:
Format(Year(Date()),"YY") & "-" & Format([MyPrimaryKey],"0000")
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a
lab
number column that i want to increment to the next unused number,
with
the click of a commandbuttonon the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the
last
two digits of the year, and the other 4 digitsincrementingwith the
click of thisbutton. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?
Thank you in advance,
coldfire
The reason that I want abuttonto formulate the new number is so that
i can enter older records into the database via this form, and also so
i can add duplicates with an additional rn number in another field.
Also is there anyway to format the manually inputnumbersso they show
up in the database how i want, not just make them look that way like
with an input mask.- Hide quoted text -
- Show quoted text -

This would work accept for the fact that if i entered old records in
the mitsd of entering new records the primary key would have gaps in
the new records, which is why i wanted to do it with a button, and
with code instead of basing it upon the autoincrement feature. Could
you please help in the development of some code to solve this problem.

I couldn't get the button to work there weren't any compiling errors,
however it did nothing when clicked.

 

[Guest]

This thread started a couple weeks ago and then just petered out. Did you
get your problem solved? If not, here's a question about your question:
what about the rollover factor? I.e., if you create 2993 records in 2007, is
the first one you create in 2008 going to be 08-2994, or 08-0001? The answer
to that greatly affects the answer to your question.

 

[coldfire]

This thread started a couple weeks ago and then just petered out. Did you
get your problem solved? If not, here's a question about your question:
what about the rollover factor? I.e., if you create 2993 records in 2007, is
the first one you create in 2008 going to be 08-2994, or 08-0001? The answer
to that greatly affects the answer to your question.






- Show quoted text -

No i'm still struggling with the problem and to answer your question
there is no particular rollover number, however when a new year starts
i would like the number to start over ie: 08-0001.

 

[coldfire]

This thread started a couple weeks ago and then just petered out. Did you
get your problem solved? If not, here's a question about your question:
what about the rollover factor? I.e., if you create 2993 records in 2007, is
the first one you create in 2008 going to be 08-2994, or 08-0001? The answer
to that greatly affects the answer to your question.






- Show quoted text -

No my problem has still not been resolved unfortunately. And as for
the rollover number i just want the counter to start at 1 when the new
year hits, Ie: 08-0001

 

[Guest]

Okay then, there's probably more than one way to do this, but I'd suggest you
create a table in your database with two fields -- the year part of the last
number assigned, and the sequence number part of the last number assigned.
You can then write code for your button that shows you those two pieces of
information and asks you "Assign next number, or start a new year?"
Depending on your choice, it'll either use the same year and the next number
in the sequence, or will go with the next year and 0001. After generating
the new number, it will finish by posting whatever number was assigned back
into the "last used" table as the starting point for your next transaction.

I've intentionally refrained from including any actual code, as I first want
to get your reaction to this approach.

 

[coldfire]

Okay then, there's probably more than one way to do this, but I'd suggest you
create a table in your database with two fields -- the year part of the last
number assigned, and the sequence number part of the last number assigned.
You can then write code for your button that shows you those two pieces of
information and asks you "Assign next number, or start a new year?"
Depending on your choice, it'll either use the same year and the next number
in the sequence, or will go with the next year and 0001. After generating
the new number, it will finish by posting whatever number was assigned back
into the "last used" table as the starting point for your next transaction.

I've intentionally refrained from including any actual code, as I first want
to get your reaction to this approach.

I see it as being feasible, better then trying to screw around with
the autonumber function and default value properties like i have been.
I'll give it a try.

 

[coldfire]

Okay then, there's probably more than one way to do this, but I'd suggest you
create a table in your database with two fields -- the year part of the last
number assigned, and the sequence number part of the last number assigned.
You can then write code for your button that shows you those two pieces of
information and asks you "Assign next number, or start a new year?"
Depending on your choice, it'll either use the same year and the next number
in the sequence, or will go with the next year and 0001. After generating
the new number, it will finish by posting whatever number was assigned back
into the "last used" table as the starting point for your next transaction.

I've intentionally refrained from including any actual code, as I first want
to get your reaction to this approach.

sounds good and more viable then the gapping of auto number. lets give
it a try.

 

[Guest]

Okay, so you first set up tblLatestNumbers with two text fields, YearValue
and SeqNo. There’ll never be more than one record in this table.

Then you set up a popup form that opens when you click your button. It has
two text controls to display the latest YearValue and SeqNo from your table
for the user’s information. In your Load event, set the value of those two
controls using a DLookup function, e.g., =DMax("[YearValue]",
"tblLatestNumbers"). (The DMax is of course meaningless since there’s only
one record – DMin would work just as well.) Along with that you have either
an option box with two radio button choices, or two buttons. One option or
button runs the "assign the next number" code, the other one runs the "start
a new year" code. Make the “assign next number†choice the default, since
it’s the one you’ll be doing most of the time.

In either case you need a variable, strYearValue, initialized to =
Me!txtYearValue from your popup form.

If you’re assigning the next number,

strMyField = strYearValue & "-" & Format((CLng(strSeqNo + 1),"0000"))
DoCmd.RunSQL “UPDATE tblLatestNumbers SET SeqNo = ‘†& Format((CLng(strSeqNo
+ 1),"0000")) & “’â€
(Don’t overlook those single quotes!!)

If you’re starting a new year (my assumption here is that you’re going to go
with the year you’re currently in),

strYearValue = Right(CStr(DatePart("yyyy",Now)),2)
strMyField = strYearValue & "-0001"
DoCmd.RunSQL “UPDATE tblLatestNumbers SET YearValue = ‘†& strYearValue &
“’, SeqNo = ‘0001’â€

I didn’t actually run this, just wrote it off the top of my head and tested
a couple of the nested functions in the VBA immediate window, but it should
work.

BTW, one could make this even more sexy by making the CODE figure out
whether you’re now in a year later than the latest one in tblLatestNumber,
and if so automatically start a new year without asking the user to make the
decision. I’ll leave that to you, why should I have all the fun?

 

[coldfire]

Okay, so you first set up tblLatestNumbers with two text fields, YearValue
and SeqNo. There'll never be more than one record in this table.

Then you set up a popup form that opens when you click your button. It has
two text controls to display the latest YearValue and SeqNo from your table
for the user's information. In your Load event, set the value of those two
controls using a DLookup function, e.g., =DMax("[YearValue]",
"tblLatestNumbers"). (The DMax is of course meaningless since there's only
one record - DMin would work just as well.) Along with that you have either
an option box with two radio button choices, or two buttons. One option or
button runs the "assign the next number" code, the other one runs the "start
a new year" code. Make the "assign next number" choice the default, since
it's the one you'll be doing most of the time.

In either case you need a variable, strYearValue, initialized to =
Me!txtYearValue from your popup form.

If you're assigning the next number,

strMyField = strYearValue & "-" & Format((CLng(strSeqNo + 1),"0000"))
DoCmd.RunSQL "UPDATE tblLatestNumbers SET SeqNo = '" & Format((CLng(strSeqNo
+ 1),"0000")) & "'"
(Don't overlook those single quotes!!)

If you're starting a new year (my assumption here is that you're going to go
with the year you're currently in),

strYearValue = Right(CStr(DatePart("yyyy",Now)),2)
strMyField = strYearValue & "-0001"
DoCmd.RunSQL "UPDATE tblLatestNumbers SET YearValue = '" & strYearValue &
"', SeqNo = '0001'"

I didn't actually run this, just wrote it off the top of my head and tested
a couple of the nested functions in the VBA immediate window, but it should
work.

BTW, one could make this even more sexy by making the CODE figure out
whether you're now in a year later than the latest one in tblLatestNumber,
and if so automatically start a new year without asking the user to make the
decision. I'll leave that to you, why should I have all the fun?

sounds good and more viable then the gapping of auto number. lets give
it a try.

I finally got some time to try the code and i'm getting compiling
errors on the do cmd line and on the whole assign number statement.
Could you take a look at it please.

 

[Guest]

Post your code here or E-mail it to me at (e-mail address removed).

Okay, so you first set up tblLatestNumbers with two text fields, YearValue
and SeqNo. There'll never be more than one record in this table.

Then you set up a popup form that opens when you click your button. It has
two text controls to display the latest YearValue and SeqNo from your table
for the user's information. In your Load event, set the value of those two
controls using a DLookup function, e.g., =DMax("[YearValue]",
"tblLatestNumbers"). (The DMax is of course meaningless since there's only
one record - DMin would work just as well.) Along with that you have either
an option box with two radio button choices, or two buttons. One option or
button runs the "assign the next number" code, the other one runs the "start
a new year" code. Make the "assign next number" choice the default, since
it's the one you'll be doing most of the time.

In either case you need a variable, strYearValue, initialized to =
Me!txtYearValue from your popup form.

If you're assigning the next number,

strMyField = strYearValue & "-" & Format((CLng(strSeqNo + 1),"0000"))
DoCmd.RunSQL "UPDATE tblLatestNumbers SET SeqNo = '" & Format((CLng(strSeqNo
+ 1),"0000")) & "'"
(Don't overlook those single quotes!!)

If you're starting a new year (my assumption here is that you're going to go
with the year you're currently in),

strYearValue = Right(CStr(DatePart("yyyy",Now)),2)
strMyField = strYearValue & "-0001"
DoCmd.RunSQL "UPDATE tblLatestNumbers SET YearValue = '" & strYearValue &
"', SeqNo = '0001'"

I didn't actually run this, just wrote it off the top of my head and tested
a couple of the nested functions in the VBA immediate window, but it should
work.

BTW, one could make this even more sexy by making the CODE figure out
whether you're now in a year later than the latest one in tblLatestNumber,
and if so automatically start a new year without asking the user to make the
decision. I'll leave that to you, why should I have all the fun?

Okay then, there's probably more than one way to do this, but I'd suggest you
create a table in your database with two fields -- the year part of the last
number assigned, and the sequence number part of the last number assigned.
You can then write code for your button that shows you those two pieces of
information and asks you "Assign next number, or start a new year?"
Depending on your choice, it'll either use the same year and the next number
in the sequence, or will go with the next year and 0001. After generating
the new number, it will finish by posting whatever number was assigned back
into the "last used" table as the starting point for your next transaction.

I've intentionally refrained from including any actual code, as I first want
to get your reaction to this approach.

coldfire" wrote:
This thread started a couple weeks ago and then just petered out. Did you
get your problem solved? If not, here's a question about your question:
what about the rollover factor? I.e., if you create 2993 records in 2007, is
the first one you create in 2008 going to be 08-2994, or 08-0001? The answer
to that greatly affects the answer to your question.

:
Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a lab
number column that i want to increment to the next unused number, with
the click of a command button on the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the last
two digits of the year, and the other 4 digitsincrementingwith the
click of this button. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?

Thank you in advance,
coldfire- Hide quoted text -

- Show quoted text -

No i'm still struggling with the problem and to answer your question
there is no particular rollover number, however when a new year starts
i would like the number to start over ie: 08-0001.

sounds good and more viable then the gapping of auto number. lets give
it a try.

I finally got some time to try the code and i'm getting compiling
errors on the do cmd line and on the whole assign number statement.
Could you take a look at it please.

 

[coldfire]

Post your code here or E-mail it to me at (e-mail address removed).

Okay, so you first set up tblLatestNumbers with two text fields, YearValue
and SeqNo. There'll never be more than one record in this table.
Then you set up a popup form that opens when you click your button. It has
two text controls to display the latest YearValue and SeqNo from your table
for the user's information. In your Load event, set the value of those two
controls using a DLookup function, e.g., =DMax("[YearValue]",
"tblLatestNumbers"). (The DMax is of course meaningless since there's only
one record - DMin would work just as well.) Along with that you have either
an option box with two radio button choices, or two buttons. One option or
button runs the "assign the next number" code, the other one runs the "start
a new year" code. Make the "assign next number" choice the default, since
it's the one you'll be doing most of the time.
In either case you need a variable, strYearValue, initialized to =
Me!txtYearValue from your popup form.
If you're assigning the next number,
strMyField = strYearValue & "-" & Format((CLng(strSeqNo + 1),"0000"))
DoCmd.RunSQL "UPDATE tblLatestNumbers SET SeqNo = '" & Format((CLng(strSeqNo
+ 1),"0000")) & "'"
(Don't overlook those single quotes!!)
If you're starting a new year (my assumption here is that you're going to go
with the year you're currently in),
strYearValue = Right(CStr(DatePart("yyyy",Now)),2)
strMyField = strYearValue & "-0001"
DoCmd.RunSQL "UPDATE tblLatestNumbers SET YearValue = '" & strYearValue &
"', SeqNo = '0001'"
I didn't actually run this, just wrote it off the top of my head and tested
a couple of the nested functions in the VBA immediate window, but it should
work.
BTW, one could make this even more sexy by making the CODE figure out
whether you're now in a year later than the latest one in tblLatestNumber,
and if so automatically start a new year without asking the user to make the
decision. I'll leave that to you, why should I have all the fun?
:
Okay then, there's probably more than one way to do this, but I'd suggest you
create a table in your database with two fields -- the year part of the last
number assigned, and the sequence number part of the last number assigned.
You can then write code for your button that shows you those two pieces of
information and asks you "Assign next number, or start a new year?"
Depending on your choice, it'll either use the same year and the next number
in the sequence, or will go with the next year and 0001. After generating
the new number, it will finish by posting whatever number was assigned back
into the "last used" table as the starting point for your next transaction.
I've intentionally refrained from including any actual code, as I first want
to get your reaction to this approach.
coldfire" wrote:
This thread started a couple weeks ago and then just petered out. Did you
get your problem solved? If not, here's a question about your question:
what about the rollover factor? I.e., if you create 2993 records in 2007, is
the first one you create in 2008 going to be 08-2994, or 08-0001? The answer
to that greatly affects the answer to your question.
:
Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a lab
number column that i want to increment to the next unused number, with
the click of a command button on the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the last
two digits of the year, and the other 4 digitsincrementingwith the
click of this button. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?
Thank you in advance,
coldfire- Hide quoted text -
- Show quoted text -
No i'm still struggling with the problem and to answer your question
there is no particular rollover number, however when a new year starts
i would like the number to start over ie: 08-0001.
sounds good and more viable then the gapping of auto number. lets give
it a try.

I finally got some time to try the code and i'm getting compiling
errors on the do cmd line and on the whole assign number statement.
Could you take a look at it please.

its the code that you gave me, for the get next number and start new
year options

 

[coldfire]

Post your code here or E-mail it to me at (e-mail address removed).

Okay, so you first set up tblLatestNumbers with two text fields, YearValue
and SeqNo. There'll never be more than one record in this table.
Then you set up a popup form that opens when you click your button. It has
two text controls to display the latest YearValue and SeqNo from your table
for the user's information. In your Load event, set the value of those two
controls using a DLookup function, e.g., =DMax("[YearValue]",
"tblLatestNumbers"). (The DMax is of course meaningless since there's only
one record - DMin would work just as well.) Along with that you have either
an option box with two radio button choices, or two buttons. One option or
button runs the "assign the next number" code, the other one runs the "start
a new year" code. Make the "assign next number" choice the default, since
it's the one you'll be doing most of the time.
In either case you need a variable, strYearValue, initialized to =
Me!txtYearValue from your popup form.
If you're assigning the next number,
strMyField = strYearValue & "-" & Format((CLng(strSeqNo + 1),"0000"))
DoCmd.RunSQL "UPDATE tblLatestNumbers SET SeqNo = '" & Format((CLng(strSeqNo
+ 1),"0000")) & "'"
(Don't overlook those single quotes!!)
If you're starting a new year (my assumption here is that you're going to go
with the year you're currently in),
strYearValue = Right(CStr(DatePart("yyyy",Now)),2)
strMyField = strYearValue & "-0001"
DoCmd.RunSQL "UPDATE tblLatestNumbers SET YearValue = '" & strYearValue &
"', SeqNo = '0001'"
I didn't actually run this, just wrote it off the top of my head and tested
a couple of the nested functions in the VBA immediate window, but it should
work.
BTW, one could make this even more sexy by making the CODE figure out
whether you're now in a year later than the latest one in tblLatestNumber,
and if so automatically start a new year without asking the user to make the
decision. I'll leave that to you, why should I have all the fun?
:
Okay then, there's probably more than one way to do this, but I'd suggest you
create a table in your database with two fields -- the year part of the last
number assigned, and the sequence number part of the last number assigned.
You can then write code for your button that shows you those two pieces of
information and asks you "Assign next number, or start a new year?"
Depending on your choice, it'll either use the same year and the next number
in the sequence, or will go with the next year and 0001. After generating
the new number, it will finish by posting whatever number was assigned back
into the "last used" table as the starting point for your next transaction.
I've intentionally refrained from including any actual code, as I first want
to get your reaction to this approach.
coldfire" wrote:
This thread started a couple weeks ago and then just petered out. Did you
get your problem solved? If not, here's a question about your question:
what about the rollover factor? I.e., if you create 2993 records in 2007, is
the first one you create in 2008 going to be 08-2994, or 08-0001? The answer
to that greatly affects the answer to your question.
:
Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a lab
number column that i want to increment to the next unused number, with
the click of a command button on the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the last
two digits of the year, and the other 4 digitsincrementingwith the
click of this button. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?
Thank you in advance,
coldfire- Hide quoted text -
- Show quoted text -
No i'm still struggling with the problem and to answer your question
there is no particular rollover number, however when a new year starts
i would like the number to start over ie: 08-0001.
sounds good and more viable then the gapping of auto number. lets give
it a try.

I finally got some time to try the code and i'm getting compiling
errors on the do cmd line and on the whole assign number statement.
Could you take a look at it please.

Also as far as making the code sexier, i'm not sure exactly how to do
this since i don't know vb, but wouldn't it work just to compare the
system date against the year number in the table and if it is not
equal set the count back to one. I'm used to java programing so i
don't know if this is applicable here or not, just an idea.

 

[Guest]

Okay, let's regroup. Assumptions:

-- You have a table, tblLatestNumbers, with two numeric fields, YearValue
and SeqNo. This table will always have only one record, showing the last
year and seq no used.

-- You have a form with two text fields, txtYearValue and txtSeqNo, that are
populated from the values in tblLatestNumbers, using the DLookup function.

-- On the form you also have a button, cmdProcess, that runs the code below.
For purposes of texting this code builds the next sequence number, then
appends it as a new record in tblMyData; you'll have to decide what you
really want to do with the number, and replace that part of the following
with your own code.


Private Sub cmdProcess_Click()
Dim strMyField As String, intWhichAction As Integer
DoCmd.SetWarnings False
If Year(Now) = Me!txtYearValue Then
intWhichAction = 1
Else
intWhichAction = 2
End If

Select Case intWhichAction

Case 1:
' If you’re assigning the next number,
strMyField = Right(Me!txtYearValue, 2) & "-" &
Format(Me!txtSeqNo + 1, "0000")
' This "bumps" the sequence value in tblLatestNumbers
DoCmd.RunSQL "UPDATE tblLatestNumbers SET tblLatestNumbers.SeqNo
= " & Me!txtSeqNo + 1 & ";"

Case 2:
' If you’re starting a new year (assumption: the year you’re
currently in),
strMyField = Right(Year(Now), 2) & "-0001"
'This "bumps" the year value in tblLatestNumbers, and sets the
sequence number to 1
DoCmd.RunSQL "UPDATE tblLatestNumbers SET
tblLatestNumbers.YearValue = " & Year(Now()) & ", tblLatestNumbers.SeqNo = 1;"

End Select

'Whichever option you selected, the following line inserts the sequence
number you generated as a new record in tblMyData.
'Depending on what you actually want to do with the sequence number,
you'll have to write your own code for it, replacing the line below.
DoCmd.RunSQL "INSERT INTO tblMyData (LogNo) VALUES ('" & strMyField &
"');"

DoCmd.SetWarnings True
Me!frmPickAction = Null
Me.Refresh
End Sub

Post your code here or E-mail it to me at (e-mail address removed).

Okay, so you first set up tblLatestNumbers with two text fields, YearValue
and SeqNo. There'll never be more than one record in this table.

Then you set up a popup form that opens when you click your button. It has
two text controls to display the latest YearValue and SeqNo from your table
for the user's information. In your Load event, set the value of those two
controls using a DLookup function, e.g., =DMax("[YearValue]",
"tblLatestNumbers"). (The DMax is of course meaningless since there's only
one record - DMin would work just as well.) Along with that you have either
an option box with two radio button choices, or two buttons. One option or
button runs the "assign the next number" code, the other one runs the "start
a new year" code. Make the "assign next number" choice the default, since
it's the one you'll be doing most of the time.

In either case you need a variable, strYearValue, initialized to =
Me!txtYearValue from your popup form.

If you're assigning the next number,

strMyField = strYearValue & "-" & Format((CLng(strSeqNo + 1),"0000"))
DoCmd.RunSQL "UPDATE tblLatestNumbers SET SeqNo = '" & Format((CLng(strSeqNo
+ 1),"0000")) & "'"
(Don't overlook those single quotes!!)

If you're starting a new year (my assumption here is that you're going to go
with the year you're currently in),

strYearValue = Right(CStr(DatePart("yyyy",Now)),2)
strMyField = strYearValue & "-0001"
DoCmd.RunSQL "UPDATE tblLatestNumbers SET YearValue = '" & strYearValue &
"', SeqNo = '0001'"

I didn't actually run this, just wrote it off the top of my head and tested
a couple of the nested functions in the VBA immediate window, but it should
work.

BTW, one could make this even more sexy by making the CODE figure out
whether you're now in a year later than the latest one in tblLatestNumber,
and if so automatically start a new year without asking the user to make the
decision. I'll leave that to you, why should I have all the fun?

:
Okay then, there's probably more than one way to do this, but I'd suggest you
create a table in your database with two fields -- the year part of the last
number assigned, and the sequence number part of the last number assigned.
You can then write code for your button that shows you those two pieces of
information and asks you "Assign next number, or start a new year?"
Depending on your choice, it'll either use the same year and the next number
in the sequence, or will go with the next year and 0001. After generating
the new number, it will finish by posting whatever number was assigned back
into the "last used" table as the starting point for your next transaction.

I've intentionally refrained from including any actual code, as I first want
to get your reaction to this approach.

coldfire" wrote:
This thread started a couple weeks ago and then just petered out. Did you
get your problem solved? If not, here's a question about your question:
what about the rollover factor? I.e., if you create 2993 records in 2007, is
the first one you create in 2008 going to be 08-2994, or 08-0001? The answer
to that greatly affects the answer to your question.

:
Hello, I'm new to access so please bare with me. I have created a
database which records information about cases, in this form is a lab
number column that i want to increment to the next unused number, with
the click of a command button on the form. The format that I want to
use for the lab number is 00-0000 the first two digits being the last
two digits of the year, and the other 4 digitsincrementingwith the
click of this button. I have tried vigorously to write code to do
this, however to no avail. Could someone please help me with this?

Thank you in advance,
coldfire- Hide quoted text -

- Show quoted text -

No i'm still struggling with the problem and to answer your question
there is no particular rollover number, however when a new year starts
i would like the number to start over ie: 08-0001.

sounds good and more viable then the gapping of auto number. lets give
it a try.

I finally got some time to try the code and i'm getting compiling
errors on the do cmd line and on the whole assign number statement.
Could you take a look at it please.

Also as far as making the code sexier, i'm not sure exactly how to do
this since i don't know vb, but wouldn't it work just to compare the
system date against the year number in the table and if it is not
equal set the count back to one. I'm used to java programing so i
don't know if this is applicable here or not, just an idea.