count number in field

I use an unbound text box on my form to count the number of records there
are, then add 1 to the total with. =Count(*)+1

The user then enters this number in a field called Log No which is bound to
the table.



Is there a way to add this number automatically to my field (Log No) when
the form loads. I tried the auto number but it keeps going out of sink and
missing numbers out



Something on the lines of



Log no = number of last record+1



So if the last log number used was 200, when a new record was opened the log
no field would auto fill with 201



Using Access 97



Thanks

Steve Goodrich wrote:
I use an unbound text box on my form to count the number of records there
are, then add 1 to the total with. =Count(*)+1

The user then enters this number in a field called Log No which is bound to
the table.



Is there a way to add this number automatically to my field (Log No) when
the form loads. I tried the auto number but it keeps going out of sink and
missing numbers out



Something on the lines of



Log no = number of last record+1



So if the last log number used was 200, when a new record was opened the log
no field would auto fill with 201



Using Access 97



Thanks




Here's a fragment of code which I use to determine what "Invoice Number"
has just been generated as an Autonumber:

Note, to avoid clutter, I haven't included all the code. This is part
of a function that builds an INSERT statement which creates a new
Invoice record. That table has an Autonumber as a primary key, and I
need to use elsewhere the number just created. So, after the stSQL is
executed, I use the line:
Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY")
... to access that Autonumber generated a split second before. Note that
not everyone thinks it's a good idea to use the _value_ of an Autonumber
this way (I print them on the Invoices as an identifier) but it works
for me. Here's the code:
================================================== =================

stSQL = "INSERT INTO tbl_Invoice (... blah blah...)"
Set db = CurrentDb()
'Insert new Invoice record:
CurrentDb.Execute stSQL
'return last Autonumber generated:
Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY")
If rs.EOF Then
MsgBox "No Autonumber Generated:" & vbCrLf & "Cannot
continue", vbCritical, "in cmd_report_Click()"
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Else
'store ID of inserted Invoice record
lngInvoiceID = rs.Fields(0)
rs.Close
Set rs = Nothing
Set db = Nothing
End If
================================================== =================

Hope that's useful.

Phil, London

To increment the last log no value by 1 when a new record is inserted you can
either put the following in the form's Current event procedu

Me.[Log no].DefaultValue = _
"""" & Nz(DMax("[Log no]", "[YourTable]"), 0) + 1 & """"

or the following in the form's BeforeInsert event procedu

Me.[Log no] = _
Nz(DMax("[Log no]", "[YourTable]"), 0) + 1

The former has the advantage of showing the new number as soon as a user
navigates to the blank new record, whereas the latter inserts the value only
when the user starts to add other data into a new record. You might be
wondering why in the first example the value is wrapped in literal quotes
characters even though a number. This is because the DefaultValue property
is always a string expression, regardless of the data type of the field to
which the control is bound. In the above case it wouldn't actually matter if
the quotes were omitted, but they can be essential in some situations, dates
being a case in point, so its best to include them in any case.

While the above will work fine in a single user environment, in a multi-user
environment on a network it could give rise to a conflict if two or more
users are entering a new record simultaneously, in which case the first one
would be able to save their record , but the other(s) would experience an
error due to the violation of the unique key on the log no field. There are
various ways of avoiding this. Roger Carlson has a simple solution at:


http://www.rogersaccesslibrary.com/f...19 89c53d7ffb


while mine at the following link is more complex, but includes provision for
re-setting the value at which the sequence will start when the next record is
inserted:


http://community.netscape.com/n/pfx/...g=ws-msdevapps



Ken Sheridan
Stafford, England

"Steve Goodrich" wrote:

I use an unbound text box on my form to count the number of records there
are, then add 1 to the total with. =Count(*)+1

The user then enters this number in a field called Log No which is bound to
the table.



Is there a way to add this number automatically to my field (Log No) when
the form loads. I tried the auto number but it keeps going out of sink and
missing numbers out



Something on the lines of



Log no = number of last record+1



So if the last log number used was 200, when a new record was opened the log
no field would auto fill with 201



Using Access 97



Thanks

Steve,
Try this as a Default Value for your field LogNo...
=NZ(DCount("*","tblYourTable"),0) +1
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"Steve Goodrich" wrote in message
. ..
I use an unbound text box on my form to count the number of records there
are, then add 1 to the total with. =Count(*)+1

The user then enters this number in a field called Log No which is bound
to the table.



Is there a way to add this number automatically to my field (Log No) when
the form loads. I tried the auto number but it keeps going out of sink
and missing numbers out



Something on the lines of



Log no = number of last record+1



So if the last log number used was 200, when a new record was opened the
log no field would auto fill with 201



Using Access 97



Thanks

Note that setting the Default Value of the field like this carries the same
risk in a multi-user environment has been noted by Ken.

While placing the code in the Form_Before event carries a small risk is of
this in the multi-user environment, by assigning it at the last minute cuts
the odds down considerably. I've used it for years without ever having had
two users save a record at the precise same instant.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

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

Thanks guys, works great
"Steve Goodrich" wrote in message
. ..
I use an unbound text box on my form to count the number of records there
are, then add 1 to the total with. =Count(*)+1

The user then enters this number in a field called Log No which is bound
to the table.



Is there a way to add this number automatically to my field (Log No) when
the form loads. I tried the auto number but it keeps going out of sink
and missing numbers out



Something on the lines of



Log no = number of last record+1



So if the last log number used was 200, when a new record was opened the
log no field would auto fill with 201



Using Access 97



Thanks

Linq,
Thanks for that info.
I've used my method many times in the past with no problems, but... that
doesn't mean it can't be improved.
(My environments, to date, have not involved multiple users on the same
form)

I'll take a look at Roger's and Ken's solutions, as soon as I have a
chance.

Thanks,
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"Linq Adams via AccessMonster.com" u28780@uwe wrote in message
news:9110f62845e28@uwe...
Note that setting the Default Value of the field like this carries the
same
risk in a multi-user environment has been noted by Ken.

While placing the code in the Form_Before event carries a small risk is of
this in the multi-user environment, by assigning it at the last minute
cuts
the odds down considerably. I've used it for years without ever having had
two users save a record at the precise same instant.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

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