By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Autonumber code

P: n/a
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.OpenRecordset("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
Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
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:bl**********@sparta.btinternet.com...
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.OpenRecordset("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

Nov 12 '05 #2

P: n/a
"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@sparta.btinternet.com...
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.OpenRecordset("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
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
Nov 12 '05 #3

P: n/a
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" <fl****@home.com> wrote in message news:<bl**********@hercules.btinternet.com>...
"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@sparta.btinternet.com...
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.OpenRecordset("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
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

Nov 12 '05 #4

P: n/a
"Heather" <he*********@optusnet.com.au> wrote in message
news:9a**************************@posting.google.c om...
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

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.However, you may not want to actually allocate the new number until
the record is finally being saved:

Private Sub Form_BeforeUpdate(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
Nov 12 '05 #5

P: n/a
"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@sparta.btinternet.com...
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.OpenRecordset("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


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
Nov 12 '05 #6

P: n/a
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" <fl****@home.com> wrote in message
news:bl**********@hercules.btinternet.com...
"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@sparta.btinternet.com...
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.OpenRecordset("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


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

Nov 12 '05 #7

P: n/a
"Tony Williams" <tw@tcp.com> wrote in message
news:bl*********@hercules.btinternet.com...
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


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.OrderNo (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
Nov 12 '05 #8

P: n/a
Thanks Fletcher. I have been warned off using Autonumber by reading some of
the posts on here. I read that, and I paraphrase here,"Autonumber 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" <fl****@home.com> wrote in message
news:bl**********@titan.btinternet.com...
"Tony Williams" <tw@tcp.com> wrote in message
news:bl*********@hercules.btinternet.com...
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
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.OrderNo (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

Nov 12 '05 #9

P: n/a
"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@hercules.btinternet.com...
Thanks Fletcher. I have been warned off using Autonumber by reading some of the posts on here. I read that, and I paraphrase here,"Autonumber 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


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.
Nov 12 '05 #10

P: n/a
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" <fl****@home.com> wrote in message
news:bl**********@titan.btinternet.com...
"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@hercules.btinternet.com...
Thanks Fletcher. I have been warned off using Autonumber by reading some

of
the posts on here. I read that, and I paraphrase here,"Autonumber 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


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.

Nov 12 '05 #11

P: n/a
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" <fl****@home.com> wrote in message
news:bl**********@titan.btinternet.com...
"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@hercules.btinternet.com...
Thanks Fletcher. I have been warned off using Autonumber by reading some

of
the posts on here. I read that, and I paraphrase here,"Autonumber 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


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.

Nov 12 '05 #12

P: n/a

"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@titan.btinternet.com...
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


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.OpenRecordset("tblOrders", dbOpenDynaset, dbAppendOnly,
dbOptimistic)

lngOrdNo = NewNbr()
dteOrdDate = Date

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

Me.Requery

With Me.RecordsetClone
.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

Nov 12 '05 #13

P: n/a
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" <fl****@home.com> wrote in message
news:bl**********@sparta.btinternet.com...

"Tony Williams" <tw@tcp.com> wrote in message
news:bl**********@titan.btinternet.com...
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


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.OpenRecordset("tblOrders", dbOpenDynaset, dbAppendOnly,
dbOptimistic)

lngOrdNo = NewNbr()
dteOrdDate = Date

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

Me.Requery

With Me.RecordsetClone
.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

Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.