Auto Number

To expand on the other response, as the Default Value of a text box bound to
the key field:

=Nz(DMax("[KeyFieldName]","[tblName]"),0) + 1

The Nz is necessary only for the first record. If you already have values in
that field you will not need it, in which case it would be:

=DMax("[KeyFieldName]","[tblName]") + 1

Also, if you do use the expression with Nz you can use a number other than 0.
If your first record is to be numbered 202000, substitute 201999 for the 0.
Again, this is only if there are no records with a value in that field when
you begin.

You can hide that text box, then set Control Source of an unbound text box:

="C-" & [KeyFieldName]

If the "C-" prefix is not a constant, please provide details.

Majic wrote:
>Hey guys,
>
>I have a key number that I need to create a sequential number based on
>that number.
>For example: C-202743 and when you enter new record I wanted to go to
>C-202744 and so forth.
>Could we do that? I know the autonumber goes 1, 2 , 3,....etc. Could
>we format that?
>
>If you know how to do it, I will be very greatful
>
>Thank you all
>
>Majic

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200908/1

The down side of this approach is that if you are working in an environment
where more than one person could be using the database at the same time, then
you could end up with duplicates. The reason for this is that the
[KeyFieldName] field will not actually get updated until you save the current
record, so if one person creates a new record, and is working on it but has
not saved it, and another person creates a record, they will end up with the
same #.

One way to handle this is to not assign the number until the user saves the
record (in the forms BeforeUpdate event).

Another method is to store the number in a table (I use tbl_db_Parameters
which only contains 1 record) and then create a function that opens that
table, gets the current value from the appropriate field in that table, and
increments the value in the table. Something like:

Public Function fnNextKey(KeyName as string) as long

Dim strSQL as string
Dim rs as DAO.Recordset

strSQL = "SELECT [" & KeyName & "] FROM tbl_db_Parameters"
set rs = currentdb.OpenRecordset(strsql,,dbFailOnError)

with rs
.edit
fnNextKey = rs(KeyName)
rs(KeyName) = rs(KeyName) + 1
.update
end with

rs.close
set rs = nothing

End Function
----
HTH
Dale



"BruceM via AccessMonster.com" wrote:

> To expand on the other response, as the Default Value of a text box bound to
> the key field:
>
> =Nz(DMax("[KeyFieldName]","[tblName]"),0) + 1
>
> The Nz is necessary only for the first record. If you already have values in
> that field you will not need it, in which case it would be:
>
> =DMax("[KeyFieldName]","[tblName]") + 1
>
> Also, if you do use the expression with Nz you can use a number other than 0.
> If your first record is to be numbered 202000, substitute 201999 for the 0.
> Again, this is only if there are no records with a value in that field when
> you begin.
>
> You can hide that text box, then set Control Source of an unbound text box:
>
> ="C-" & [KeyFieldName]
>
> If the "C-" prefix is not a constant, please provide details.
>
> Majic wrote:
> >Hey guys,
> >
> >I have a key number that I need to create a sequential number based on
> >that number.
> >For example: C-202743 and when you enter new record I wanted to go to
> >C-202744 and so forth.
> >Could we do that? I know the autonumber goes 1, 2 , 3,....etc. Could
> >we format that?
> >
> >If you know how to do it, I will be very greatful
> >
> >Thank you all
> >
> >Majic
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200908/1
>
>

Roger Carlson demonstrates another possibility, which is to use the form's
Error event to generate a new number. He demonstrates this in a sample
database (link all on one line):
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395&SID=5...ze35817


Alternatively, go to the main site:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

Look for the AutoNumberProblem database.

The Error event may be used in conjunction with assigning the number in VBA
rather than as the Default Value of a text box bound to the field.

I have also used a function to assign the number initially. In the form's
Before Update event I run the function again and compare the result to the
original value. If they are different, the new value becomes the field value.


I'm not arguing against your approaches, but rather pointing out some other
options.


Dale Fye wrote:
>The down side of this approach is that if you are working in an environment
>where more than one person could be using the database at the same time, then
>you could end up with duplicates. The reason for this is that the
>[KeyFieldName] field will not actually get updated until you save the current
>record, so if one person creates a new record, and is working on it but has
>not saved it, and another person creates a record, they will end up with the
>same #.
>
>One way to handle this is to not assign the number until the user saves the
>record (in the forms BeforeUpdate event).
>
>Another method is to store the number in a table (I use tbl_db_Parameters
>which only contains 1 record) and then create a function that opens that
>table, gets the current value from the appropriate field in that table, and
>increments the value in the table. Something like:
>
>Public Function fnNextKey(KeyName as string) as long
>
> Dim strSQL as string
> Dim rs as DAO.Recordset
>
> strSQL = "SELECT [" & KeyName & "] FROM tbl_db_Parameters"
> set rs = currentdb.OpenRecordset(strsql,,dbFailOnError)
>
> with rs
> .edit
> fnNextKey = rs(KeyName)
> rs(KeyName) = rs(KeyName) + 1
> .update
> end with
>
> rs.close
> set rs = nothing
>
>End Function
>----
>HTH
>Dale
>
>> To expand on the other response, as the Default Value of a text box bound to
>> the key field:
>[quoted text clipped - 31 lines]
>> >
>> >Majic

--
Message posted via http://www.accessmonster.com

On Aug 30, 5:39 am, MikeR <nf4lNoS....RemoveThis@pobox.com> wrote:
> Majic wrote:
> > Hey guys,
>
> > I have a key number that I need to create a sequential number based on
> > that number.
> > For example: C-202743 and when you enter new record I wanted to go to
> > C-202744 and so forth.
> > Could we do that?  I know the autonumber goes 1, 2 , 3,....etc.  Could
> > we format that?
>
> > If you know how to do it, I will be very greatful
>
> > Thank you all
>
> > Majic
>
> Autonumber is not guaranteed to be sequential. It is not a suitable choice here. It
> is a not-for-human-consumption number that is only guaranteed to be unique.
>
> Use an integer, and the DMAX function to return it, add 1 to it and store it.
> If the key will always contain the "C-", then don't store that, add it to the
> report/display with a format statement.
>
> Mike

Thanks Mike I will try it in few and let you know

Majic

On Sep 1, 8:47 am, "BruceM via AccessMonster.com" <u54429@uwe> wrote:
> To expand on the other response, as the Default Value of a text box bound to
> the key field:
>
> =Nz(DMax("[KeyFieldName]","[tblName]"),0) + 1
>
> The Nz is necessary only for the first record.  If you already have values in
> that field you will not need it, in which case it would be:
>
> =DMax("[KeyFieldName]","[tblName]") + 1
>
> Also, if you do use the expression with Nz you can use a number other than 0.
> If your first record is to be numbered 202000, substitute 201999 for the 0.
> Again, this is only if there are no records with a value in that field when
> you begin.
>
> You can hide that text box, then set Control Source of an unbound text box:
>
> ="C-" & [KeyFieldName]
>
> If the "C-" prefix is not a constant, please provide details.
>
> Majic wrote:
> >Hey guys,
>
> >I have a key number that I need to create a sequential number based on
> >that number.
> >For example: C-202743 and when you enter new record I wanted to go to
> >C-202744 and so forth.
> >Could we do that?  I know the autonumber goes 1, 2 , 3,....etc.  Could
> >we format that?
>
> >If you know how to do it, I will be very greatful
>
> >Thank you all
>
> >Majic
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

Thank you so very much I will try it and let you know

On Sep 1, 1:48 pm, Dale Fye <dale.... RemoveThis @nospam.com> wrote:
> The down side of this approach is that if you are working in an environment
> where more than one person could be using the database at the same time, then
> you could end up with duplicates.  The reason for this is that the
> [KeyFieldName] field will not actually get updated until you save the current
> record, so if one person creates a new record, and is working on it but has
> not saved it, and another person creates a record, they will end up with the
> same #.
>
> One way to handle this is to not assign the number until the user saves the
> record (in the forms BeforeUpdate event).
>
> Another method is to store the number in a table (I use tbl_db_Parameters
> which only contains 1 record) and then create a function that opens that
> table, gets the current value from the appropriate field in that table, and
> increments the value in the table.  Something like:
>
> Public Function fnNextKey(KeyName as string) as long
>
>     Dim strSQL as string
>     Dim rs as DAO.Recordset
>
>     strSQL = "SELECT [" & KeyName & "] FROM tbl_db_Parameters"
>     set rs = currentdb.OpenRecordset(strsql,,dbFailOnError)
>
>     with rs
>         .edit
>         fnNextKey = rs(KeyName)
>         rs(KeyName) = rs(KeyName) + 1
>         .update
>     end with
>
>     rs.close
>     set rs = nothing
>
> End Function
> ----
> HTH
> Dale
>
>
>
> "BruceM via AccessMonster.com" wrote:
> > To expand on the other response, as the Default Value of a text box bound to
> > the key field:
>
> > =Nz(DMax("[KeyFieldName]","[tblName]"),0) + 1
>
> > The Nz is necessary only for the first record.  If you already have values in
> > that field you will not need it, in which case it would be:
>
> > =DMax("[KeyFieldName]","[tblName]") + 1
>
> > Also, if you do use the expression with Nz you can use a number other than 0.
> > If your first record is to be numbered 202000, substitute 201999 for the 0.
> > Again, this is only if there are no records with a value in that field when
> > you begin.
>
> > You can hide that text box, then set Control Source of an unbound text box:
>
> > ="C-" & [KeyFieldName]
>
> > If the "C-" prefix is not a constant, please provide details.
>
> > Majic wrote:
> > >Hey guys,
>
> > >I have a key number that I need to create a sequential number based on
> > >that number.
> > >For example: C-202743 and when you enter new record I wanted to go to
> > >C-202744 and so forth.
> > >Could we do that?  I know the autonumber goes 1, 2 , 3,....etc.  Could
> > >we format that?
>
> > >If you know how to do it, I will be very greatful
>
> > >Thank you all
>
> > >Majic
>
> > --
> > Message posted via AccessMonster.com
> >http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...- Hide quoted text -
>
> - Show quoted text -

Thank so much I will try it