Auto Number

On Sep 1, 2:56 pm, "BruceM via AccessMonster.com" <u54429@uwe> wrote:
> 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 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 viahttp://www.accessmonster.com- Hide quoted text -
>
> - Show quoted text -

Thank you for the reference