Re: Sequential number

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
    ... The second part consists of four digits for the report number of the year. ... Dim lngID As Long ...
    (microsoft.public.access.queries)
  • Re: Sequential number
    ... The first part consists of four digits for each year. ... The second part consists of four digits for the report number of the ...
    (microsoft.public.access.queries)
  • Re: Sequential number
    ... caution I have to take in multiuser environment in where I am going to use. ... mulitple users can generate these part numbers for the same report, ... The first part consists of four digits for each year. ...
    (microsoft.public.access.queries)
  • Re: Sequential number
    ... This may cause dups in a multiuser environment. ... The first part consists of four digits for each year. ... The second part consists of four digits for the report number of the ...
    (microsoft.public.access.queries)
  • Re: Sequential number
    ... The first part consists of four digits for each year. ... The second part consists of four digits for the report number of the ...
    (microsoft.public.access.queries)
Loading