Autonumber code

[Tony Williams]

I have a table called tblseries with a field NextNumber
I also have a form called form1 based on table1 On the form is a control
NNum which I've called NNumtxt with the following code in the BeforeUpdate
Property =NewNbr()
NewNbr is a function in Module1 the code for the function is this

Function NewNbr() As Integer
Dim rst As DAO.Recordset, db As DAO.Database
Dim lngNextNumber As Long
Set db = CurrentDb

Set rst = db.OpenRecordse t("tblSeries" , , dbDenyRead)

With rst
..MoveFirst
..Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
..Update
End With
rst.Close
Set db = Nothing

End Function

I have seeded the tblseries with the number 1 but when I open my form for a
new record nothing happens just a 0 appears.

Can anyone help here, why doesn't this work? I am using Access2000
Tony
PS With thanks to Hugh O'Neill who posted the original code for another user

[Douglas J. Steele]

Re: Autonumber code

You're not returning the value you retrieved from the table. Just before the
End Function line, you need

NewNbr = lngNextNumber

And, assuming that the NextNumber field in tblSeries is a Long Integer, I'd
change the declaration of the function from Integer to Long.

If you go into tblSeries, you should see that the value of NextNumber is
considerably different than 1, depending on how many times you've tried
running your code.

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

"Tony Williams" <tw@tcp.com> wrote in message
news:blpl2v$r9b $1@sparta.btint ernet.com...[color=blue]
> I have a table called tblseries with a field NextNumber
> I also have a form called form1 based on table1 On the form is a control
> NNum which I've called NNumtxt with the following code in the BeforeUpdate
> Property =NewNbr()
> NewNbr is a function in Module1 the code for the function is this
>
> Function NewNbr() As Integer
> Dim rst As DAO.Recordset, db As DAO.Database
> Dim lngNextNumber As Long
> Set db = CurrentDb
>
> Set rst = db.OpenRecordse t("tblSeries" , , dbDenyRead)
>
> With rst
> .MoveFirst
> .Edit
> lngNextNumber = ![NextNumber]
> ![NextNumber] = lngNextNumber + 1
> .Update
> End With
> rst.Close
> Set db = Nothing
>
> End Function
>
> I have seeded the tblseries with the number 1 but when I open my form for[/color]
a[color=blue]
> new record nothing happens just a 0 appears.
>
> Can anyone help here, why doesn't this work? I am using Access2000
> Tony
> PS With thanks to Hugh O'Neill who posted the original code for another[/color]
user[color=blue]
>
>[/color]

[Fletcher Arnold]

Re: Autonumber code

"Tony Williams" <tw@tcp.com> wrote in message
news:blpl2v$r9b $1@sparta.btint ernet.com...[color=blue]
> I have a table called tblseries with a field NextNumber
> I also have a form called form1 based on table1 On the form is a control
> NNum which I've called NNumtxt with the following code in the BeforeUpdate
> Property =NewNbr()
> NewNbr is a function in Module1 the code for the function is this
>
> Function NewNbr() As Integer
> Dim rst As DAO.Recordset, db As DAO.Database
> Dim lngNextNumber As Long
> Set db = CurrentDb
>
> Set rst = db.OpenRecordse t("tblSeries" , , dbDenyRead)
>
> With rst
> .MoveFirst
> .Edit
> lngNextNumber = ![NextNumber]
> ![NextNumber] = lngNextNumber + 1
> .Update
> End With
> rst.Close
> Set db = Nothing
>
> End Function
>
> I have seeded the tblseries with the number 1 but when I open my form for[/color]
a[color=blue]
> new record nothing happens just a 0 appears.
>
> Can anyone help here, why doesn't this work? I am using Access2000
> Tony
> PS With thanks to Hugh O'Neill who posted the original code for another[/color]
user


Hi Tony
At a guess, you are trying to get some sort of number allocated to a new
record - presumably because you found that the built-in autonumber does not
quite do what you need.

Firstly, your function does not return any value. For example, add the
following line to the end of the function:
NewNbr = lngNextNumber

You might also think about when to actually allocate the new number. For
example you could get rid of the standard 'new record button' (star on the
record selectors) and replace this with a 'new' button on your form to
create a saved record. Otherwise, you could move to a new record, update
the table but later decide to abandon the new record leaving you with
missing numbers in the series.

Fletcher

[Heather]

Re: Autonumber code

Hello, I hope I am not intruding. I have also been trying to achieve
this process of 'Autonumber' and have used the original message from
Hugh ONeill. I have also followed instructions below such as NewNbr
= lngNextNumber and changed the declaration to Long but have still
been unsuccessful in updating my 'Original' form which requires the
new number.

I would be grateful for any other suggestions.
regards
Heather


"Fletcher Arnold" <fletch@home.co m> wrote in message news:<blptsp$44 o$1@hercules.bt internet.com>.. .[color=blue]
> "Tony Williams" <tw@tcp.com> wrote in message
> news:blpl2v$r9b $1@sparta.btint ernet.com...[color=green]
> > I have a table called tblseries with a field NextNumber
> > I also have a form called form1 based on table1 On the form is a control
> > NNum which I've called NNumtxt with the following code in the BeforeUpdate
> > Property =NewNbr()
> > NewNbr is a function in Module1 the code for the function is this
> >
> > Function NewNbr() As Integer
> > Dim rst As DAO.Recordset, db As DAO.Database
> > Dim lngNextNumber As Long
> > Set db = CurrentDb
> >
> > Set rst = db.OpenRecordse t("tblSeries" , , dbDenyRead)
> >
> > With rst
> > .MoveFirst
> > .Edit
> > lngNextNumber = ![NextNumber]
> > ![NextNumber] = lngNextNumber + 1
> > .Update
> > End With
> > rst.Close
> > Set db = Nothing
> >
> > End Function
> >
> > I have seeded the tblseries with the number 1 but when I open my form for[/color]
> a[color=green]
> > new record nothing happens just a 0 appears.
> >
> > Can anyone help here, why doesn't this work? I am using Access2000
> > Tony
> > PS With thanks to Hugh O'Neill who posted the original code for another[/color]
> user
>
>
> Hi Tony
> At a guess, you are trying to get some sort of number allocated to a new
> record - presumably because you found that the built-in autonumber does not
> quite do what you need.
>
> Firstly, your function does not return any value. For example, add the
> following line to the end of the function:
> NewNbr = lngNextNumber
>
> You might also think about when to actually allocate the new number. For
> example you could get rid of the standard 'new record button' (star on the
> record selectors) and replace this with a 'new' button on your form to
> create a saved record. Otherwise, you could move to a new record, update
> the table but later decide to abandon the new record leaving you with
> missing numbers in the series.
>
> Fletcher[/color]

[Fletcher Arnold]

Re: Autonumber code

"Heather" <heather.pfc@op tusnet.com.au> wrote in message
news:9a472727.0 310052042.3ea06 0a2@posting.goo gle.com...[color=blue]
> Hello, I hope I am not intruding. I have also been trying to achieve
> this process of 'Autonumber' and have used the original message from
> Hugh ONeill. I have also followed instructions below such as NewNbr
> = lngNextNumber and changed the declaration to Long but have still
> been unsuccessful in updating my 'Original' form which requires the
> new number.
>
> I would be grateful for any other suggestions.
> regards
> Heather[/color]


You just need to decide at which point you want to allocate the new number.
You could use the following code in the form's current event:

Private Sub Form_Current()

If Me.NewRecord Then
Me!MyNumber = NewNbr()
End If

End Sub

This would have the advantage of immediately making the new number visible
on the form - as soon as you begin the process of taking out a new order or
whatever.Howeve r, you may not want to actually allocate the new number until
the record is finally being saved:

Private Sub Form_BeforeUpda te(Cancel As Integer)
If Me.NewRecord Then
Me!MyNumber = NewNbr()
End If
End Sub

This has some advantages, but you don't get to see the new number until the
new order is saved. My personal preference is to have a 'New Order' button
which opens an unbound form with buttons 'Cancel' and 'OK'. The form
collects the minimum info needed for a new order and pressing OK gets a new
number, creates the new record and takes you to it for further editing.

HTH

Fletcher

[Fletcher Arnold]

Re: Autonumber code

"Tony Williams" <tw@tcp.com> wrote in message
news:blpl2v$r9b $1@sparta.btint ernet.com...[color=blue]
> I have a table called tblseries with a field NextNumber
> I also have a form called form1 based on table1 On the form is a control
> NNum which I've called NNumtxt with the following code in the BeforeUpdate
> Property =NewNbr()
> NewNbr is a function in Module1 the code for the function is this
>
> Function NewNbr() As Integer
> Dim rst As DAO.Recordset, db As DAO.Database
> Dim lngNextNumber As Long
> Set db = CurrentDb
>
> Set rst = db.OpenRecordse t("tblSeries" , , dbDenyRead)
>
> With rst
> .MoveFirst
> .Edit
> lngNextNumber = ![NextNumber]
> ![NextNumber] = lngNextNumber + 1
> .Update
> End With
> rst.Close
> Set db = Nothing
>
> End Function
>
> I have seeded the tblseries with the number 1 but when I open my form for[/color]
a[color=blue]
> new record nothing happens just a 0 appears.
>
> Can anyone help here, why doesn't this work? I am using Access2000
> Tony
> PS With thanks to Hugh O'Neill who posted the original code for another[/color]
user




Have just been prompted by Heather's post:

Your function returns an integer which is often not big enough for this sort
of thing. In a lot of databases, you could have an order number of 93675
but that number would be too large for an integer which can only store
values from -32,768 to 32,767.

Fletcher




Fletcher

[Tony Williams]

Re: Autonumber code

Thanks for all your advice here Fletcher. Sorry I didn't get back to you
earlier but I gave up last night after I posted the message (I'm in the UK
and it was getting late in the day!) I will study your notes and take on
board what you recommend. I believe there is a possibility that my numbers
will exceed the limit you mentioned, how do I overcome that? (PS I am a
newbie, be gentle with me)
Thanks
Tony
"Fletcher Arnold" <fletch@home.co m> wrote in message
news:blr2u6$n6n $1@hercules.bti nternet.com...[color=blue]
> "Tony Williams" <tw@tcp.com> wrote in message
> news:blpl2v$r9b $1@sparta.btint ernet.com...[color=green]
> > I have a table called tblseries with a field NextNumber
> > I also have a form called form1 based on table1 On the form is a control
> > NNum which I've called NNumtxt with the following code in the[/color][/color]
BeforeUpdate[color=blue][color=green]
> > Property =NewNbr()
> > NewNbr is a function in Module1 the code for the function is this
> >
> > Function NewNbr() As Integer
> > Dim rst As DAO.Recordset, db As DAO.Database
> > Dim lngNextNumber As Long
> > Set db = CurrentDb
> >
> > Set rst = db.OpenRecordse t("tblSeries" , , dbDenyRead)
> >
> > With rst
> > .MoveFirst
> > .Edit
> > lngNextNumber = ![NextNumber]
> > ![NextNumber] = lngNextNumber + 1
> > .Update
> > End With
> > rst.Close
> > Set db = Nothing
> >
> > End Function
> >
> > I have seeded the tblseries with the number 1 but when I open my form[/color][/color]
for[color=blue]
> a[color=green]
> > new record nothing happens just a 0 appears.
> >
> > Can anyone help here, why doesn't this work? I am using Access2000
> > Tony
> > PS With thanks to Hugh O'Neill who posted the original code for another[/color]
> user
>
>
>
>
> Have just been prompted by Heather's post:
>
> Your function returns an integer which is often not big enough for this[/color]
sort[color=blue]
> of thing. In a lot of databases, you could have an order number of 93675
> but that number would be too large for an integer which can only store
> values from -32,768 to 32,767.
>
> Fletcher
>
>
>
>
> Fletcher
>
>[/color]

[Fletcher Arnold]

Re: Autonumber code

"Tony Williams" <tw@tcp.com> wrote in message
news:blr7mn$gu$ 1@hercules.btin ternet.com...[color=blue]
> Thanks for all your advice here Fletcher. Sorry I didn't get back to you
> earlier but I gave up last night after I posted the message (I'm in the UK
> and it was getting late in the day!) I will study your notes and take on
> board what you recommend. I believe there is a possibility that my numbers
> will exceed the limit you mentioned, how do I overcome that? (PS I am a
> newbie, be gentle with me)
> Thanks
> Tony[/color]

Hi Tony
No objection to newbie questions - they're easier! To allow bigger numbers
use the long integer type. First change the design of the table so
tblOrders.Order No (or whatever it is) is a long integer - see the field size
property. Then simply change "Function NewNbr() As Integer" to "Function
NewNbr() As Long"

Out of curiosity - what is this new number and why didn't you want an
autonumber?

Fletcher

[Tony Williams]

Re: Autonumber code

Thanks Fletcher. I have been warned off using Autonumber by reading some of
the posts on here. I read that, and I paraphrase here,"Autonumbe r is
designed only to provide a unique identifier for records and its values
should never be exposed to users because they develop gaps, become random
and go negative"
So I looked to find another way. Was this the right thing to do?
Tony
"Fletcher Arnold" <fletch@home.co m> wrote in message
news:blr8pm$ejh $1@titan.btinte rnet.com...[color=blue]
> "Tony Williams" <tw@tcp.com> wrote in message
> news:blr7mn$gu$ 1@hercules.btin ternet.com...[color=green]
> > Thanks for all your advice here Fletcher. Sorry I didn't get back to you
> > earlier but I gave up last night after I posted the message (I'm in the[/color][/color]
UK[color=blue][color=green]
> > and it was getting late in the day!) I will study your notes and take on
> > board what you recommend. I believe there is a possibility that my[/color][/color]
numbers[color=blue][color=green]
> > will exceed the limit you mentioned, how do I overcome that? (PS I am a
> > newbie, be gentle with me)
> > Thanks
> > Tony[/color]
>
> Hi Tony
> No objection to newbie questions - they're easier! To allow bigger[/color]
numbers[color=blue]
> use the long integer type. First change the design of the table so
> tblOrders.Order No (or whatever it is) is a long integer - see the field[/color]
size[color=blue]
> property. Then simply change "Function NewNbr() As Integer" to "Function
> NewNbr() As Long"
>
> Out of curiosity - what is this new number and why didn't you want an
> autonumber?
>
> Fletcher
>
>[/color]

[Fletcher Arnold]

Re: Autonumber code

"Tony Williams" <tw@tcp.com> wrote in message
news:blrbjd$7ja $1@hercules.bti nternet.com...[color=blue]
> Thanks Fletcher. I have been warned off using Autonumber by reading some[/color]
of[color=blue]
> the posts on here. I read that, and I paraphrase here,"Autonumbe r is
> designed only to provide a unique identifier for records and its values
> should never be exposed to users because they develop gaps, become random
> and go negative"
> So I looked to find another way. Was this the right thing to do?
> Tony[/color]

Yes - that's reasonable advice, although you could still use an autonumber
in addition. For example:

tblOrders:
OrdID = primary key autonumber
OrdNo = long integer, required, unique index
OrdDate = date
etc

But unless you have found a reason to do this, perhaps 1 key (OrdNo) would
be simpler.

[Tony Williams]

Re: Autonumber code

Thanks again, I'll now try all that.
But now I'm off to a meeting discussing the changes to a Pension Scheme with
the Trustees, why are my days so hectic now that I have retired????
I'll be back later to try this so if I may come back to you later today??
Tony
"Fletcher Arnold" <fletch@home.co m> wrote in message
news:blrcfm$liq $1@titan.btinte rnet.com...[color=blue]
> "Tony Williams" <tw@tcp.com> wrote in message
> news:blrbjd$7ja $1@hercules.bti nternet.com...[color=green]
> > Thanks Fletcher. I have been warned off using Autonumber by reading some[/color]
> of[color=green]
> > the posts on here. I read that, and I paraphrase here,"Autonumbe r is
> > designed only to provide a unique identifier for records and its values
> > should never be exposed to users because they develop gaps, become[/color][/color]
random[color=blue][color=green]
> > and go negative"
> > So I looked to find another way. Was this the right thing to do?
> > Tony[/color]
>
> Yes - that's reasonable advice, although you could still use an autonumber
> in addition. For example:
>
> tblOrders:
> OrdID = primary key autonumber
> OrdNo = long integer, required, unique index
> OrdDate = date
> etc
>
> But unless you have found a reason to do this, perhaps 1 key (OrdNo) would
> be simpler.
>
>[/color]

[Tony Williams]

Re: Autonumber code

Back again! Everything seems to work Ok. I took heed of your point about
when to create the number. My users want the number before they move off the
new record so what I'm think of doing is putting the code behind a button
that says "Get Number and Save" but doesn't close the form there is a
separate button to close the form. This way they can edit the record if they
have made a mistake without closing and reopening the form. Does that seem
sound?
Tony
"Fletcher Arnold" <fletch@home.co m> wrote in message
news:blrcfm$liq $1@titan.btinte rnet.com...[color=blue]
> "Tony Williams" <tw@tcp.com> wrote in message
> news:blrbjd$7ja $1@hercules.bti nternet.com...[color=green]
> > Thanks Fletcher. I have been warned off using Autonumber by reading some[/color]
> of[color=green]
> > the posts on here. I read that, and I paraphrase here,"Autonumbe r is
> > designed only to provide a unique identifier for records and its values
> > should never be exposed to users because they develop gaps, become[/color][/color]
random[color=blue][color=green]
> > and go negative"
> > So I looked to find another way. Was this the right thing to do?
> > Tony[/color]
>
> Yes - that's reasonable advice, although you could still use an autonumber
> in addition. For example:
>
> tblOrders:
> OrdID = primary key autonumber
> OrdNo = long integer, required, unique index
> OrdDate = date
> etc
>
> But unless you have found a reason to do this, perhaps 1 key (OrdNo) would
> be simpler.
>
>[/color]

[Fletcher Arnold]

Re: Autonumber code


"Tony Williams" <tw@tcp.com> wrote in message
news:bls3gd$71i $1@titan.btinte rnet.com...[color=blue]
> Back again! Everything seems to work Ok. I took heed of your point about
> when to create the number. My users want the number before they move off[/color]
the[color=blue]
> new record so what I'm think of doing is putting the code behind a button
> that says "Get Number and Save" but doesn't close the form there is a
> separate button to close the form. This way they can edit the record if[/color]
they[color=blue]
> have made a mistake without closing and reopening the form. Does that seem
> sound?
> Tony[/color]

Yes that sounds good. You could have a button 'cmdNew' with code similar to
the following which is based on an orders table. Hope you can adapt it to
meet your needs:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler

Dim lngOrdNo As Long
Dim dteOrdDate As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

If MsgBox("New Order?", vbInformation Or vbYesNoCancel) <> vbYes Then
Exit Sub
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecords et("tblOrders" , dbOpenDynaset, dbAppendOnly,
dbOptimistic)

lngOrdNo = NewNbr()
dteOrdDate = Date

rst.AddNew
rst!OrdNo = lngOrdNo
rst!OrdDate = dteOrdDate
rst.Update

Me.Requery

With Me.RecordsetClo ne
.FindFirst "OrdNo=" & lngOrdNo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Exit_Handler:

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Tony Williams]

Re: Autonumber code

Thanks a lot Fletcher that looks really good. I know some people misuse this
sort of group but I find I learn a lot more from asking questions and
getting sample code from you guys to work with than ploughing thru my text
books. Being new to this at the ripe old age of 58 I can understand things
better if I see them in context, thank goodness for guys like you who are
prepared to spend time helping us out!!!!!
Tony
"Fletcher Arnold" <fletch@home.co m> wrote in message
news:bltt78$d5t $1@sparta.btint ernet.com...[color=blue]
>
> "Tony Williams" <tw@tcp.com> wrote in message
> news:bls3gd$71i $1@titan.btinte rnet.com...[color=green]
> > Back again! Everything seems to work Ok. I took heed of your point about
> > when to create the number. My users want the number before they move off[/color]
> the[color=green]
> > new record so what I'm think of doing is putting the code behind a[/color][/color]
button[color=blue][color=green]
> > that says "Get Number and Save" but doesn't close the form there is a
> > separate button to close the form. This way they can edit the record if[/color]
> they[color=green]
> > have made a mistake without closing and reopening the form. Does that[/color][/color]
seem[color=blue][color=green]
> > sound?
> > Tony[/color]
>
> Yes that sounds good. You could have a button 'cmdNew' with code similar[/color]
to[color=blue]
> the following which is based on an orders table. Hope you can adapt it to
> meet your needs:
>
> Private Sub cmdNew_Click()
>
> On Error GoTo Err_Handler
>
> Dim lngOrdNo As Long
> Dim dteOrdDate As Date
> Dim dbs As DAO.Database
> Dim rst As DAO.Recordset
>
> If MsgBox("New Order?", vbInformation Or vbYesNoCancel) <> vbYes Then
> Exit Sub
> End If
>
> Set dbs = CurrentDb
> Set rst = dbs.OpenRecords et("tblOrders" , dbOpenDynaset, dbAppendOnly,
> dbOptimistic)
>
> lngOrdNo = NewNbr()
> dteOrdDate = Date
>
> rst.AddNew
> rst!OrdNo = lngOrdNo
> rst!OrdDate = dteOrdDate
> rst.Update
>
> Me.Requery
>
> With Me.RecordsetClo ne
> .FindFirst "OrdNo=" & lngOrdNo
> If Not .NoMatch Then
> Me.Bookmark = .Bookmark
> End If
> End With
>
> Exit_Handler:
>
> If Not rst Is Nothing Then
> rst.Close
> Set rst = Nothing
> End If
>
> If Not dbs Is Nothing Then
> Set dbs = Nothing
> End If
>
> Exit Sub
>
> Err_Handler:
> MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
> Resume Exit_Handler
>
> End Sub
>
>
>[/color]