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

alternative to autonumber

P: n/a
Hi,

I wanted to know what I can use to increment a number in MS ACESS 2000 for a
number field instead of using autonumber.

Thanks for your help.

smriti
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Depends on how you are doing this... There are a couple of options;

a) use DMax() function. But beware... this causes the system to search all
records, for the maximum number of the field you are doing this on. Which
means, in big tables, there could be performance issues
b) use DLast() function. A bit quicker (in the case of big tables), but
could cause problems, if the indexing of the table is not based on the field
(ie: the last record, may not necessarily be the highest number).
c) use a 'Last Numbers' table, so you can see the last number written to
your main table. This again, is not good for performance, as you need to
read the last numbers table, to find the last number written to your main
table, then update the last numbers table, with the last number, if your
main table has records added.
"Smriti Dev" <sm********@utoronto.ca> wrote in message
news:HM********@campus-news-reading.utoronto.ca...
Hi,

I wanted to know what I can use to increment a number in MS ACESS 2000 for a number field instead of using autonumber.

Thanks for your help.

smriti

Nov 12 '05 #2

P: n/a
I often make a table. The code I use is:

Public Function nextinvoice() As Long

Dim myrecs As Recordset

Set myrecs = CurrentDb.OpenRecordset("tblNextInvoice")

nextinvoice = myrecs!nextinvoice

myrecs.Edit
myrecs!nextinvoice = myrecs!nextinvoice + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function

You can then in the before update event use:

if isnull(me.InvoiceNum) = true then
me.InvoiceNum = NextInvoice()
end if

I also thus often build a screen that is bound to the table, and thus lets
the user set what the next invoice number will be.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.