Auto Number

[Majic]

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

 

[MikeR]

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

 

[Dale Fye]

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:

=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.

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

 

[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 storeit.
If the key will always contain the "C-", then don't store that, add it tothe
report/display with a format statement.

Mike

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

Majic

 

[Majic]

To expand on the other response, as the Default Value of a text box boundto
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.

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

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

 

[Majic]

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:

=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.

- Show quoted text -

Thank so much I will try it

 

[Majic]

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=...

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 fieldvalue.

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

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 withthe
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

Thank you for the reference