Need help Using Max Value

[Tom]

With a New Record how do I return an InvoiceNumber that is one higher than
the maximum number already used in the table?
Win XP, Office XP
Table name is LoadDetails and Field is InvoiceNumber
I currently make the InvoiceNumber default value to equal the last entry
plus 1 through a control click event which works as long as all the records
are put in in one sitting or the user remembers to go to the last record
before creating a NewRecord BUT that is not good code. The user has proven
that to me twice in one month.
I'm pretty sure that Max is the function to use but am not sure how to use
it in the code Help is appreciated.
Tom T

 

[Sandra Daigle]

The easiest way to create a custom counter is using the Dmax function where
you grab the highest existing value from the table and then increment it.
For example: You have a table tblInvoice and you want to increment
Invoiceid. The following will get the current max value of Invoiceid,
increment it by 1 and then put the new value into the control me.Invoiceid:

me.Invoiceid=nz(dmax("Invoiceid","tblInvoice"),0)+1

Put this code in either the BeforeInsert or BeforeUpdate event of the form -
BeforeUpdate ensures that the number is assigned immediately before the
update occurs.

 

[Tom]

Thanks Sandra,
I hope you MVPs realize how helpful you all are to us minor leaguers.
I'll give this a try in the morning. Just one question and if it's apparent
in the code forgive me,
but what prevents the Invoice Number from changing the next time I enter
that form/record?
I'm trying to learn and at 54 it's tougher than it used to be.

The easiest way to create a custom counter is using the Dmax function where
you grab the highest existing value from the table and then increment it.
For example: You have a table tblInvoice and you want to increment
Invoiceid. The following will get the current max value of Invoiceid,
increment it by 1 and then put the new value into the control me.Invoiceid:

me.Invoiceid=nz(dmax("Invoiceid","tblInvoice"),0)+1

Put this code in either the BeforeInsert or BeforeUpdate event of the form -
BeforeUpdate ensures that the number is assigned immediately before the
update occurs.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

With a New Record how do I return an InvoiceNumber that is one higher than
the maximum number already used in the table?
Win XP, Office XP
Table name is LoadDetails and Field is InvoiceNumber
I currently make the InvoiceNumber default value to equal the last entry
plus 1 through a control click event which works as long as all the
records are put in in one sitting or the user remembers to go to the last
record before creating a NewRecord BUT that is not good code. The user
has proven that to me twice in one month.
I'm pretty sure that Max is the function to use but am not sure how to use
it in the code Help is appreciated.
Tom T

 

[Sandra Daigle]

Hi Tom,

I'm glad to help - we've all been there before and most of us agree that the
newsgroups were an extremely valuable resource while we were learning -
actually they still are for me!

Good question (I should have included this part) - If you put the code in
the BeforeInsert event it will only run when a new record is being created.
If you put it in the BeforeUpdate event you should put it within an if
statement that tests whether the record is a new record - one way to do this
is as follows:

if me.newrecord then
me.Invoiceid=nz(dmax("Invoiceid","tblInvoice"),0)+1
endif

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thanks Sandra,
I hope you MVPs realize how helpful you all are to us minor leaguers.
I'll give this a try in the morning. Just one question and if it's
apparent in the code forgive me,
but what prevents the Invoice Number from changing the next time I enter
that form/record?
I'm trying to learn and at 54 it's tougher than it used to be.

The easiest way to create a custom counter is using the Dmax function
where you grab the highest existing value from the table and then
increment it. For example: You have a table tblInvoice and you want to
increment Invoiceid. The following will get the current max value of
Invoiceid, increment it by 1 and then put the new value into the control
me.Invoiceid:

me.Invoiceid=nz(dmax("Invoiceid","tblInvoice"),0)+1

Put this code in either the BeforeInsert or BeforeUpdate event of the form -
BeforeUpdate ensures that the number is assigned immediately before the
update occurs.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

With a New Record how do I return an InvoiceNumber that is one higher
than the maximum number already used in the table?
Win XP, Office XP
Table name is LoadDetails and Field is InvoiceNumber
I currently make the InvoiceNumber default value to equal the last entry
plus 1 through a control click event which works as long as all the
records are put in in one sitting or the user remembers to go to the
last record before creating a NewRecord BUT that is not good code. The
user has proven that to me twice in one month.
I'm pretty sure that Max is the function to use but am not sure how to
use it in the code Help is appreciated.
Tom T

 

[Tom]

I was hoping it wasn't a stupid question.
Thanks again, Tom

Hi Tom,

I'm glad to help - we've all been there before and most of us agree that the
newsgroups were an extremely valuable resource while we were learning -
actually they still are for me!

Good question (I should have included this part) - If you put the code in
the BeforeInsert event it will only run when a new record is being created.
If you put it in the BeforeUpdate event you should put it within an if
statement that tests whether the record is a new record - one way to do this
is as follows:

if me.newrecord then
me.Invoiceid=nz(dmax("Invoiceid","tblInvoice"),0)+1
endif

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thanks Sandra,
I hope you MVPs realize how helpful you all are to us minor leaguers.
I'll give this a try in the morning. Just one question and if it's
apparent in the code forgive me,
but what prevents the Invoice Number from changing the next time I enter
that form/record?
I'm trying to learn and at 54 it's tougher than it used to be.

The easiest way to create a custom counter is using the Dmax function
where you grab the highest existing value from the table and then
increment it. For example: You have a table tblInvoice and you want to
increment Invoiceid. The following will get the current max value of
Invoiceid, increment it by 1 and then put the new value into the control
me.Invoiceid:

me.Invoiceid=nz(dmax("Invoiceid","tblInvoice"),0)+1

Put this code in either the BeforeInsert or BeforeUpdate event of the form -
BeforeUpdate ensures that the number is assigned immediately before the
update occurs.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Tom wrote:
With a New Record how do I return an InvoiceNumber that is one higher
than the maximum number already used in the table?
Win XP, Office XP
Table name is LoadDetails and Field is InvoiceNumber
I currently make the InvoiceNumber default value to equal the last entry
plus 1 through a control click event which works as long as all the
records are put in in one sitting or the user remembers to go to the
last record before creating a NewRecord BUT that is not good code. The
user has proven that to me twice in one month.
I'm pretty sure that Max is the function to use but am not sure how to
use it in the code Help is appreciated.
Tom T