count number in field

[Steve Goodrich]

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

 

[Philip Herlihy]

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

 

[Ken Sheridan]

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 procedure:

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

or the following in the form's BeforeInsert event procedure:

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/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb


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...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps



Ken Sheridan
Stafford, England

 

[Al Campagna]

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]

Thanks guys, works great

 

[Al Campagna]

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