Sequential number

[Scott]

I need to have a field to generate the report number with the following
format:-

The first part consists of four digits for each year. It could be obtained
from the date field entered manually.

The second part consists of four digits for the report number of the year.
It should be generated automatically when a record is created. The number
must be incremented by 1 for each new record and not repeated in the year
but can be repeated each year.

The third part consists of a single digit for each part of report number,
ie, -1, -2, -3. It should be generated automatically when a record is
created. The number must be incremented by 1 for each new record and not be
repeated in a report number but can be repeated in other report numbers.

Can someone share the experience in generate such report no for reference.

Thanks,

Scott

 

[[MVP] S.Clark]

The first part, you have handled.

For the second part, I would create a table with an AutoNumber field. When
you need a number, you write a record to the table(which increments the
number), and use that as the basis for the 4-digit number. This will work
around any multiuser locking issues that you might have. (I don't know what
you'll do when you get to 9999, but maybe that's not an issue) Also store
things like date created, user id, etc.

The Third part would be a child table to the second part table. If mulitple
users can generate these part numbers for the same report, then you'll need
to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it altogether.

 

[Scott]

Steve,

Thanks for your suggestion. For the second part, each year will not go
beyond 9999 - far below 9999 at the moment. It requires to reset to 0001
each year automatically. Both the second part and the third part may be
duplicate and need to number sequentially. I am unsure how to control them
automatically. Any idea?

Thanks,

Scott

 

[Guest]

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1

 

[[MVP] S.Clark]

This may cause dups in a multiuser environment. Use with caution.

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1

Steve,

Thanks for your suggestion. For the second part, each year will not go
beyond 9999 - far below 9999 at the moment. It requires to reset to 0001
each year automatically. Both the second part and the third part may be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

 

[Guest]

Maybe I should state that this is a simplistic explanation to provide a
conceptual idea of how it can be done. Using tables that take up space for
very little value and have to be maintained on an annual basis provides no
additional proctection from duplicates.

This may cause dups in a multiuser environment. Use with caution.

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1

Steve,

Thanks for your suggestion. For the second part, each year will not go
beyond 9999 - far below 9999 at the moment. It requires to reset to 0001
each year automatically. Both the second part and the third part may be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

The first part, you have handled.

For the second part, I would create a table with an AutoNumber field.
When you need a number, you write a record to the table(which
increments
the number), and use that as the basis for the 4-digit number. This
will
work around any multiuser locking issues that you might have. (I don't
know what you'll do when you get to 9999, but maybe that's not an
issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report,
then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it
altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I need to have a field to generate the report number with the following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of the
year. It should be generated automatically when a record is created.
The
number must be incremented by 1 for each new record and not repeated
in
the year but can be repeated each year.

The third part consists of a single digit for each part of report
number,
ie, -1, -2, -3. It should be generated automatically when a record is
created. The number must be incremented by 1 for each new record and
not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[Scott]

Klatuu,

Thanks for your code. Could you please advise how to use this code to get
the result.

Scott

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1

Steve,

Thanks for your suggestion. For the second part, each year will not go
beyond 9999 - far below 9999 at the moment. It requires to reset to 0001
each year automatically. Both the second part and the third part may be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

 

[Scott]

Dear Klatuu & S Clark,

Many thanks for your kind advice. Could you please tell me more about the
caution I have to take in multiuser environment in where I am going to use.

Scott

Maybe I should state that this is a simplistic explanation to provide a
conceptual idea of how it can be done. Using tables that take up space
for
very little value and have to be maintained on an annual basis provides no
additional proctection from duplicates.

This may cause dups in a multiuser environment. Use with caution.

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = "
_
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1


:

Steve,

Thanks for your suggestion. For the second part, each year will not
go
beyond 9999 - far below 9999 at the moment. It requires to reset to
0001
each year automatically. Both the second part and the third part may
be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

The first part, you have handled.

For the second part, I would create a table with an AutoNumber
field.
When you need a number, you write a record to the table(which
increments
the number), and use that as the basis for the 4-digit number. This
will
work around any multiuser locking issues that you might have. (I
don't
know what you'll do when you get to 9999, but maybe that's not an
issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report,
then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it
altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I need to have a field to generate the report number with the
following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of
the
year. It should be generated automatically when a record is
created.
The
number must be incremented by 1 for each new record and not
repeated
in
the year but can be repeated each year.

The third part consists of a single digit for each part of report
number,
ie, -1, -2, -3. It should be generated automatically when a record
is
created. The number must be incremented by 1 for each new record
and
not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[Guest]

Well, without knowing how your form works, I can't give an exact answer. You
would use it at whatever point you want to assign an new number. If you need
a better answer, tell me more about how the form works.

As to the multi-user issue. It is possible, depending on when you assign a
new number, that two users could both request a new number. One would create
the record with the new number. When the other tries to create a new record
with the same number, what happens next depends on a number of things. For
example, If you have an Index or your primary key defined as unique and
another user has added the combination of values that makes it unique, then
you will get an error.
For your purposes, all 3 fields should be included in your primary key or an
index that is unique.

The timing that is least likely to create a conflict is in the Before Insert
event of your form. If you can create your number at that time, chances of
getting an error are negligable and, should it occur, you can cancel the
event and try again with the next number or you can create a loop to keep
trying until you get the next number.

When that approach doesn't work is when the user has to see the number
before the insert can happen. One way to handle this situation is to
immediately create the new number and insert it into the table so the number
is saved. Then you can continue data entry on the record and update it when
you are done.

Klatuu,

Thanks for your code. Could you please advise how to use this code to get
the result.

Scott

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1

Steve,

Thanks for your suggestion. For the second part, each year will not go
beyond 9999 - far below 9999 at the moment. It requires to reset to 0001
each year automatically. Both the second part and the third part may be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

The first part, you have handled.

For the second part, I would create a table with an AutoNumber field.
When you need a number, you write a record to the table(which
increments
the number), and use that as the basis for the 4-digit number. This
will
work around any multiuser locking issues that you might have. (I don't
know what you'll do when you get to 9999, but maybe that's not an
issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report,
then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it
altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I need to have a field to generate the report number with the following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of the
year. It should be generated automatically when a record is created.
The
number must be incremented by 1 for each new record and not repeated
in
the year but can be repeated each year.

The third part consists of a single digit for each part of report
number,
ie, -1, -2, -3. It should be generated automatically when a record is
created. The number must be incremented by 1 for each new record and
not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[Rick Brandt]

Well, without knowing how your form works, I can't give an exact
answer. You would use it at whatever point you want to assign an new
number. If you need a better answer, tell me more about how the form
works.

As to the multi-user issue. It is possible, depending on when you
assign a new number, that two users could both request a new number.
One would create the record with the new number. When the other
tries to create a new record with the same number, what happens next
depends on a number of things. For example, If you have an Index or
your primary key defined as unique and another user has added the
combination of values that makes it unique, then you will get an
error.
For your purposes, all 3 fields should be included in your primary
key or an index that is unique.

The timing that is least likely to create a conflict is in the Before
Insert event of your form. [snip]

Sorry, but that is incorrect. BeforeInsert fires upon the first keystroke to
dirty a new record. The user could then stop and not finish saving that record
for seconds, minutes, or hours, all the while leaving another user certain to
grab that same value.

The event with the least chance of collisions is BeforeUpdate which fires and
*completes* just before the record is commited. The only caveate is that being
an event that can fire multiple times per record you need an If-Then block to
make sure the number is only assigned on the very first save.

 

[Scott]

Klatuu,

I do not have final form structure but have following idea.

In a switchboard screen, apart from other details, there are two On-click
buttons: one for new report and one for new version.

On new report screen/form, there are three boxes: Date box, report number
box and version box. The Date box requires to enter a date manually. Once
a date is entered, the program generates a new report number in Report
number box and version box is defaulted to 1.

On new version screen/form, there are also three boxes as above. Date and
report number box are Synchronized combo boxes to select the year and report
number. Once they are selected, the system will generate the new version
number.

I appreciate your explanation about the shortcomings for mulituser
environment and will try to avoid it.

Scott

Well, without knowing how your form works, I can't give an exact answer.
You
would use it at whatever point you want to assign an new number. If you
need
a better answer, tell me more about how the form works.

As to the multi-user issue. It is possible, depending on when you assign
a
new number, that two users could both request a new number. One would
create
the record with the new number. When the other tries to create a new
record
with the same number, what happens next depends on a number of things.
For
example, If you have an Index or your primary key defined as unique and
another user has added the combination of values that makes it unique,
then
you will get an error.
For your purposes, all 3 fields should be included in your primary key or
an
index that is unique.

The timing that is least likely to create a conflict is in the Before
Insert
event of your form. If you can create your number at that time, chances
of
getting an error are negligable and, should it occur, you can cancel the
event and try again with the next number or you can create a loop to keep
trying until you get the next number.

When that approach doesn't work is when the user has to see the number
before the insert can happen. One way to handle this situation is to
immediately create the new number and insert it into the table so the
number
is saved. Then you can continue data entry on the record and update it
when
you are done.

Klatuu,

Thanks for your code. Could you please advise how to use this code to
get
the result.

Scott

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = "
_
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1


:

Steve,

Thanks for your suggestion. For the second part, each year will not
go
beyond 9999 - far below 9999 at the moment. It requires to reset to
0001
each year automatically. Both the second part and the third part may
be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

The first part, you have handled.

For the second part, I would create a table with an AutoNumber
field.
When you need a number, you write a record to the table(which
increments
the number), and use that as the basis for the 4-digit number. This
will
work around any multiuser locking issues that you might have. (I
don't
know what you'll do when you get to 9999, but maybe that's not an
issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report,
then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it
altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I need to have a field to generate the report number with the
following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of
the
year. It should be generated automatically when a record is
created.
The
number must be incremented by 1 for each new record and not
repeated
in
the year but can be repeated each year.

The third part consists of a single digit for each part of report
number,
ie, -1, -2, -3. It should be generated automatically when a record
is
created. The number must be incremented by 1 for each new record
and
not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[Guest]

You are correct, Rick.

Well, without knowing how your form works, I can't give an exact
answer. You would use it at whatever point you want to assign an new
number. If you need a better answer, tell me more about how the form
works.

As to the multi-user issue. It is possible, depending on when you
assign a new number, that two users could both request a new number.
One would create the record with the new number. When the other
tries to create a new record with the same number, what happens next
depends on a number of things. For example, If you have an Index or
your primary key defined as unique and another user has added the
combination of values that makes it unique, then you will get an
error.
For your purposes, all 3 fields should be included in your primary
key or an index that is unique.

The timing that is least likely to create a conflict is in the Before
Insert event of your form. [snip]

Sorry, but that is incorrect. BeforeInsert fires upon the first keystroke to
dirty a new record. The user could then stop and not finish saving that record
for seconds, minutes, or hours, all the while leaving another user certain to
grab that same value.

The event with the least chance of collisions is BeforeUpdate which fires and
*completes* just before the record is commited. The only caveate is that being
an event that can fire multiple times per record you need an If-Then block to
make sure the number is only assigned on the very first save.

 

[[MVP] S.Clark]

Klatuu,

Allowing a number to be generated by the autonumber is a simple way to avoid
duplicates in a multiuser environment.

Maybe I should state that this is a simplistic explanation to provide a
conceptual idea of how it can be done. Using tables that take up space
for
very little value and have to be maintained on an annual basis provides no
additional proctection from duplicates.

This may cause dups in a multiuser environment. Use with caution.

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = "
_
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1


:

Steve,

Thanks for your suggestion. For the second part, each year will not
go
beyond 9999 - far below 9999 at the moment. It requires to reset to
0001
each year automatically. Both the second part and the third part may
be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

The first part, you have handled.

For the second part, I would create a table with an AutoNumber
field.
When you need a number, you write a record to the table(which
increments
the number), and use that as the basis for the 4-digit number. This
will
work around any multiuser locking issues that you might have. (I
don't
know what you'll do when you get to 9999, but maybe that's not an
issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report,
then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it
altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I need to have a field to generate the report number with the
following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of
the
year. It should be generated automatically when a record is
created.
The
number must be incremented by 1 for each new record and not
repeated
in
the year but can be repeated each year.

The third part consists of a single digit for each part of report
number,
ie, -1, -2, -3. It should be generated automatically when a record
is
created. The number must be incremented by 1 for each new record
and
not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[[MVP] S.Clark]

In a multiuser environment, you must ensure that no two(or more) users
retrieve a value simultaneously, and increment it, too..

Using DMax() has no safeguard to prevent this, as two users may
simultaneously retrieve the current value, then both increment it to the
next value. This is bad.

My suggestion of using a table with an autonumber is based on the fact that
Access takes care of all of the multiuser and locking issues for you. I do
think that after I made my suggestion, you added the fact that the values
had to restart annually. Since this is the case, the autonumber option
looses some of it value.

I continue to recommend that your best option is still to maintain the used
numbers in a table. You will have to use your own locking scheme to access
the records, and perform the increments. DMax() is a not an option in this
scenario. Since it is annual, I would store the year and the value. When
you open the recordset, use the proper locking to prevent another user from
opening the table. Retrieve the value, increment the value, then remove the
lock.

Dear Klatuu & S Clark,

Many thanks for your kind advice. Could you please tell me more about the
caution I have to take in multiuser environment in where I am going to
use.

Scott

Maybe I should state that this is a simplistic explanation to provide a
conceptual idea of how it can be done. Using tables that take up space
for
very little value and have to be maintained on an annual basis provides
no
additional proctection from duplicates.

This may cause dups in a multiuser environment. Use with caution.

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR =
" _
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1


:

Steve,

Thanks for your suggestion. For the second part, each year will not
go
beyond 9999 - far below 9999 at the moment. It requires to reset to
0001
each year automatically. Both the second part and the third part may
be
duplicate and need to number sequentially. I am unsure how to
control
them
automatically. Any idea?

Thanks,

Scott

The first part, you have handled.

For the second part, I would create a table with an AutoNumber
field.
When you need a number, you write a record to the table(which
increments
the number), and use that as the basis for the 4-digit number.
This
will
work around any multiuser locking issues that you might have. (I
don't
know what you'll do when you get to 9999, but maybe that's not an
issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report,
then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it
altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I need to have a field to generate the report number with the
following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of
the
year. It should be generated automatically when a record is
created.
The
number must be incremented by 1 for each new record and not
repeated
in
the year but can be repeated each year.

The third part consists of a single digit for each part of report
number,
ie, -1, -2, -3. It should be generated automatically when a
record is
created. The number must be incremented by 1 for each new record
and
not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[Guest]

If you read the requirement, the sequential number starts at 1 for each date,
report. An autonumber will not work for this business requirement.

Klatuu,

Allowing a number to be generated by the autonumber is a simple way to avoid
duplicates in a multiuser environment.

Maybe I should state that this is a simplistic explanation to provide a
conceptual idea of how it can be done. Using tables that take up space
for
very little value and have to be maintained on an annual basis provides no
additional proctection from duplicates.

This may cause dups in a multiuser environment. Use with caution.

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = "
_
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1


:

Steve,

Thanks for your suggestion. For the second part, each year will not
go
beyond 9999 - far below 9999 at the moment. It requires to reset to
0001
each year automatically. Both the second part and the third part may
be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

The first part, you have handled.

For the second part, I would create a table with an AutoNumber
field.
When you need a number, you write a record to the table(which
increments
the number), and use that as the basis for the 4-digit number. This
will
work around any multiuser locking issues that you might have. (I
don't
know what you'll do when you get to 9999, but maybe that's not an
issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report,
then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it
altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I need to have a field to generate the report number with the
following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of
the
year. It should be generated automatically when a record is
created.
The
number must be incremented by 1 for each new record and not
repeated
in
the year but can be repeated each year.

The third part consists of a single digit for each part of report
number,
ie, -1, -2, -3. It should be generated automatically when a record
is
created. The number must be incremented by 1 for each new record
and
not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[Guest]

In either case, you are creating a new record that has yet to be updated to
the database. In this case, using the After Update event of the version
number, I would force an update and use the Before Update event to do a
dlookup to ensure the number is still available. If the number has been
used, the cancel the update, increment the number by 1, and alert the user.
Allow the user to try again or cancel. If they try again, go through the
force update process again. If they cancel, undo the form and start over.

One additional thing you have to keep track of. That is, if the user
decides not to complete the form, you now have an incomplete record in your
table. You can delete it, but then you could end up with a gap in version
numbering. Your user's business rules may have to dictate what you do in
this case.

Klatuu,

I do not have final form structure but have following idea.

In a switchboard screen, apart from other details, there are two On-click
buttons: one for new report and one for new version.

On new report screen/form, there are three boxes: Date box, report number
box and version box. The Date box requires to enter a date manually. Once
a date is entered, the program generates a new report number in Report
number box and version box is defaulted to 1.

On new version screen/form, there are also three boxes as above. Date and
report number box are Synchronized combo boxes to select the year and report
number. Once they are selected, the system will generate the new version
number.

I appreciate your explanation about the shortcomings for mulituser
environment and will try to avoid it.

Scott

Well, without knowing how your form works, I can't give an exact answer.
You
would use it at whatever point you want to assign an new number. If you
need
a better answer, tell me more about how the form works.

As to the multi-user issue. It is possible, depending on when you assign
a
new number, that two users could both request a new number. One would
create
the record with the new number. When the other tries to create a new
record
with the same number, what happens next depends on a number of things.
For
example, If you have an Index or your primary key defined as unique and
another user has added the combination of values that makes it unique,
then
you will get an error.
For your purposes, all 3 fields should be included in your primary key or
an
index that is unique.

The timing that is least likely to create a conflict is in the Before
Insert
event of your form. If you can create your number at that time, chances
of
getting an error are negligable and, should it occur, you can cancel the
event and try again with the next number or you can create a loop to keep
trying until you get the next number.

When that approach doesn't work is when the user has to see the number
before the insert can happen. One way to handle this situation is to
immediately create the new number and insert it into the table so the
number
is saved. Then you can continue data entry on the record and update it
when
you are done.

Klatuu,

Thanks for your code. Could you please advise how to use this code to
get
the result.

Scott

Rather than having to maintain tables, this can be done using the DMax
function.

lngTheYear = Year(Date)
lngSecondPart = Nz(DMax("[SECOND_PART]", "ReportsTable", "[RPT_YEAR = "
_
& lngTheYear),0) + 1
lngLastBit = Nz(DMax("[LAST_BIT]", "ReportsTable", "[RPT_YEAR = " _
& lngTheYear & " AND [SECOND_PART] = " & lngSecondPart),0) + 1


:

Steve,

Thanks for your suggestion. For the second part, each year will not
go
beyond 9999 - far below 9999 at the moment. It requires to reset to
0001
each year automatically. Both the second part and the third part may
be
duplicate and need to number sequentially. I am unsure how to control
them
automatically. Any idea?

Thanks,

Scott

The first part, you have handled.

For the second part, I would create a table with an AutoNumber
field.
When you need a number, you write a record to the table(which
increments
the number), and use that as the basis for the 4-digit number. This
will
work around any multiuser locking issues that you might have. (I
don't
know what you'll do when you get to 9999, but maybe that's not an
issue)
Also store things like date created, user id, etc.

The Third part would be a child table to the second part table. If
mulitple users can generate these part numbers for the same report,
then
you'll need to worry about locking issues to prevent duplicates.

These tables, along with a smattering of VBA code will tie it
altogether.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I need to have a field to generate the report number with the
following
format:-

The first part consists of four digits for each year. It could be
obtained from the date field entered manually.

The second part consists of four digits for the report number of
the
year. It should be generated automatically when a record is
created.
The
number must be incremented by 1 for each new record and not
repeated
in
the year but can be repeated each year.

The third part consists of a single digit for each part of report
number,
ie, -1, -2, -3. It should be generated automatically when a record
is
created. The number must be incremented by 1 for each new record
and
not
be repeated in a report number but can be repeated in other report
numbers.

Can someone share the experience in generate such report no for
reference.

Thanks,

Scott

 

[gdt]

Hey, Scott.

I need to have a field to generate the report number with the following
format:-

The first part consists of four digits for each year. It could be obtained
from the date field entered manually.

The second part consists of four digits for the report number of the year.
It should be generated automatically when a record is created. The number
must be incremented by 1 for each new record and not repeated in the year
but can be repeated each year.

The third part consists of a single digit for each part of report number,
ie, -1, -2, -3. It should be generated automatically when a record is
created. The number must be incremented by 1 for each new record and not be
repeated in a report number but can be repeated in other report numbers.

Here's how I would handle the first two parts. (The third part would
simply be a logical extension of this.)

The table [ParentTable] has an AutoNumber column called [ID] as its
primary key. The form has a hidden text box called [txtID] bound to it.


The [ReportSeq] column is Text(9), e.g. "2006-0001". It has a unique
index on it ("Indexed: Yes (No Duplicates)"). Note that such an index
is required to make this work.

The code behind the form is:

Option Compare Database
Option Explicit

Dim lngID As Long

Private Sub Form_AfterInsert()
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim strPart1 As String, intNextPart2 As Integer
Dim strReportSeq As String
Dim blnUpdated As Boolean

Set cdb = CurrentDb

strPart1 = Format(Date, "yyyy") ' or whatever

' get last ReportSeq value
Set rst = cdb.OpenRecordset( _
"SELECT MAX(ReportSeq) AS LastSeq " & _
"FROM ParentTable " & _
"WHERE ReportSeq LIKE '" & strPart1 & "*'", _
dbOpenSnapshot)

If IsNull(rst!LastSeq) Then
' no matching records exist
intNextPart2 = 1
Else
intNextPart2 = Val(Right(rst!LastSeq, 4)) + 1
End If
rst.Close
Set rst = Nothing

blnUpdated = False
Do While Not blnUpdated
strReportSeq = strPart1 & "-" & Format(intNextPart2, "0000")
On Error Resume Next
cdb.Execute _
"UPDATE ParentTable SET " & _
"ReportSeq='" & strReportSeq & "' " & _
"WHERE ID=" & lngID, _
dbFailOnError
If Err.Number = 0 Then
blnUpdated = True
Else
If Err.Number = 3022 Then
' duplicate index error on unique index (ReportSeq)
' somebody else just took that Part2 value,
' so try the next one
intNextPart2 = intNextPart2 + 1
Else
Err.Raise Err.Number, Err.Source, Err.Description
End If
End If
Loop
On Error GoTo 0

Set cdb = Nothing
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
lngID = Val(Me.txtID.Value)
End Sub