John,
In this context, my sample db named AppendSequence might also be of interest to you. It is in access 2000 file format and is available at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
The db demonstrates two modes (i.e. (i) Batch mode for bulk appendment from source to target table, and (ii) Data entry mode - one record at a time) - for automatic insertion of following types of sequential values:
(a) Pure sequential numbers
(b) Sequential number strings with date prefix (numeric portion starts afresh for each date).
(c) Similar to (b) above, but with YearMonth prefix (numeric portion starts afresh for each YearMonth).
(d) Similar to (b) above, but with Year prefix (numeric portion starts afresh for each Year).
(e) Sequential strings with alpha-numeric prefix (caters for multiple parallel series).
(f) Linked sub-sequential values
In data entry mode, if the record having existing highest sequential value is deleted, the value gets re-used on insertion of next record, thus preventing any holes.
Best wishes,
A.D. Tejpal
------------
----- Original Message -----
From: John Viescas
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, November 27, 2011 03:34
Subject: RE: [MS_AccessPros] Re: Increment Number
John-
Then assign the number in the BeforeUpdate event of the form when saving a new
record. That's actually better than AutoNumber because the numbers will be in
sequence with none skipped. With AutoNumber, the user can start a new record,
then bail out of it without saving, but the number is "used up."
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John S Moss
Sent: Saturday, November 26, 2011 9:34 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Increment Number
I want to use it for numbering boxes. I'm a contract employee and need
to ship 6000 items by next October. I figure if there's ~50 per items
container, we'll only use 120 out of the 1000. That leaves room for them
to continue with this sequence of shipping numbers even after we leave
next year. I highly doubt this Library has many more than 6000 items
that they own the copyright for.
On 11/26/2011 9:24 AM, John Viescas wrote:
>
> John-
>
> If you already have an AutoNumber PKey, then you'll have to maintain this
> "sequence" field in code. You can use DMax in the BeforeUpdate event
> of the
> form when on a new record (Me.NewRecord = True) to find out the
> previous high
> number and add 1.
>
> Why only up to 1000? Do you start over again after 1000?
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>] On Behalf Of John
> S Moss
> Sent: Saturday, November 26, 2011 3:08 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Re: Increment Number
>
> Yes, the auto-number is the Primary Key.
>
> All I need to to auto-increment a number field from 1 up to 1000. My
> client wants a sequential Box Number for each box shipped. If there's a
> better or easier way to achieve the same result, please let me know.
>
> Thanks.
>
> On 11/26/2011 1:07 AM, John Viescas wrote:
> >
> > John-
> >
> > The COUNTER data type is the equivalent of the Access AutoNumber. Do you
> > already have an AutoNumber in the table? If so, what is its purpose?
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>] On Behalf Of
> yshopper
> > Sent: Friday, November 25, 2011 11:59 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Re: Increment Number
> >
> > I tried using an AutoNumber but the error says "Access allows only one
> > autonumber field per table"
> >
> > I tried using Text data type, Number data type, nothing works.
> >
> > Any other ideas?
> >
> > Thanks for any help.
> >
> > John
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>, "Clive"
> > <zctek@...> wrote:
> > >
> > > Hi John,
> > >
> > > The FieldType must be of Type: AutoNumber which you
> > > can select from the DropDown in the 2nd column of the
> > > table in DesignView.
> > > It will create FieldSize of LongInteger.
> > >
> > > If you link to an AutoNumber field then the linking
> > > field must also be a LongInteger.
> > >
> > > Regards, Clive.
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>, "yshopper"
> > <jlinux@> wrote:
> > > >
> > > > I am trying to change a field type so that the Number increments 1
> > for each
> > new record. After I go to Query Design, Close Table box, Click Data
> > Definition
> > and type in
> > > >
> > > > "Alter TABLE tbl_Items Alter Column BoxNumber COUNTER (1000,1)"
> > > >
> > > > I get the error "Invalid Field Data type"
> > > >
> > > > How can I fix this? I've tried changing the Data Type using Number
> > and Text,
> > but both result in the same error.
> > > >
> > > > Thanks,
> > > > John
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar