Re: Sequential number

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: Linking Tabs
    ... print command that I use to print the report. ... item in the table as the autonumber field is set to allow duplicates. ... FieldName (primary key) ... FieldName ...
    (microsoft.public.access.forms)
  • 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)
Loading