Re: Sequential number

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

"[MVP] S.Clark" wrote:

Klatuu,

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


"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3C40B3AE-68B0-4760-803A-251D58568326@xxxxxxxxxxxxxxxx
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.

"[MVP] S.Clark" wrote:

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

"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FD0207D8-9F26-438A-A00D-C54BA6AEB7B7@xxxxxxxxxxxxxxxx
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


"Scott" wrote:

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

"[MVP] S.Clark" <steve.clark_NOSPAM@xxxxxxxxxx> wrote in message
news:e3JRsHjYGHA.500@xxxxxxxxxxxxxxxxxxxxxxx
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

"Scott" <NoSpam-Scott.Xe@xxxxxxxxx> wrote in message
news:%23uRxUOdYGHA.460@xxxxxxxxxxxxxxxxxxxxxxx
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














.

Relevant Pages

  • Re: Sequential number
    ... duplicates in a multiuser environment. ... additional proctection from duplicates. ... I would create a table with an AutoNumber ... mulitple users can generate these part numbers for the same report, ...
    (microsoft.public.access.queries)
  • Re: Combining Data from multiple fields
    ... Steve, why does Access occasionally skip numbers [AutoNumber]? ... there may be occasional gaps in the sequence of the numbers. ... the form or report is based on, or in the Control Source of an unbound ...
    (microsoft.public.access.forms)
  • RE: Ken Sheridan!!!
    ... autonumber can be Null, i.e. have their Required property False. ... The report shouldn't be difficult. ... query; Jobs to Assignments on JobID, ...
    (microsoft.public.access.gettingstarted)
  • Re: Build index for report... plug index into data base
    ... In my case I need to know which page each records' account number prints on. ... is an autonumber field... ... How to Create a Table of Contents or Index for a Report ... Microsoft Office/Access MVP ...
    (microsoft.public.access.reports)
  • Re: Running Sum in a Query?
    ... on a report to =1, and set the running sum ... Record number 2 is deleted and a new record is added, using autonumber I ... You're running sum is out the window. ... an append query EVERY time a new record is added. ...
    (microsoft.public.access.formscoding)