Re: Sequential number

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.


"Scott" <NoSpam-Scott.Xe@xxxxxxxxx> wrote in message
news:OOKqNjwYGHA.4836@xxxxxxxxxxxxxxxxxxxxxxx
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

"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: race on multi-processor solaris
    ... > I think in my case, I don't want locks (just want to atomically ... Yes I'd agree that if all you want to do is perform atomic increment ... That's a kernel private header file, you are not able to use ... If you want low featured locking ...
    (comp.unix.solaris)
  • Re: Perforated report 3 per page
    ... create a "spacer" between the records on the page so that each one fits in ... record per person in the report. ... etc) increment by increment to no avail. ... have tried will give me 3 matching sections on the template. ...
    (microsoft.public.access.reports)
  • Re: Increment custom number format ie. 080100, 080101 (yy####)
    ... Private Sub Form_BeforeInsert ... Dim strCriteria As String ... If you need something to increment a number you can try this in the Before ... report and then be able to retrieve any specific report by its datestamp. ...
    (microsoft.public.access.gettingstarted)
  • Re: Conditional Counter field ..
    ... Actualy I was going through the new features of VFP8 Pro and was amused to ... read that you can have multiple autoinc fields in a table .. ... Actualy I am using a SP to increment the field when data in certain fields ... > conditionally over the life of the report or within a group on a report. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: locking an int
    ... Locking on value types is a bad idea. ... boxed instance of the value type, but you get a brand new boxed instance ... class serves your need. ... variable based on the outcome of the increment. ...
    (microsoft.public.dotnet.languages.csharp)
Loading