Previous Value + 1 in a field

[RabidMilkMan]

I am using Microsoft Access 2003. I have a tabular form for data entry with a
number field named "Volume". These volume numbers the user types are often
five or more digits long, and usually sequential. When a new record is
entered, I would like this field to be automatically filled with a value
equal to the value of the same field in the previous record, plus one.
Basically, if one record is volume "10000", when a new record is made I would
like the volume to automatically be "10001", then "10002" and so on. Kind of
a "Ctrl + ' " trick, only adding one to the value rather than duplicating the
value.

Any tips?

Thanks!

 

[Jeff Boyce]

Look into using the DMax() function to find the "current largest value" and
add 1.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

So what is previous record? There is no reliable way to know what the
previous record in a table was. Now, if you want to increment a value in a
control, you can use a technique to determine the highest current number and
add 1 to that. For this you have to define the criteria for the numbering.
For example, let's say you want to number transactions to a specific account
by year.

Me.txtTransNumber = Nz(DLookup("TransNumber", "tblTransactions",
"[AccountNo] = " & Me.txtAccount & " AND Year(TransDate]) = " &
Year(Me.txtAcctPeriod),0) + 1

 

[BruceM]

For an example of how to set up an incrementing number, see:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
Note in particular that in a multi-user environment you need to guard
against duplicates.
You say that the number is "usually" sequential. Note that if the user
changes the number, the DMax function will increment from the new number
(assuming it is higher than any other number in that field). It will not
fill in skipped values.

 

[BruceM]

Maybe there's something I'm missing here, but I thought DLookup returned the
first instance of a record that matches the criteria. I assume "first" is
according to how the records are arranged in the recordset. If so, I don't
see how DLookup will locate the correct record unless there is a descending
ORDER BY clause or something like that. Wouldn't DMax be the function of
choice?
I hesitate to toss this out there since I am going to be leaving in a half
hour or so, and will not return for a week, but there it is anyhow.

So what is previous record? There is no reliable way to know what the
previous record in a table was. Now, if you want to increment a value in
a
control, you can use a technique to determine the highest current number
and
add 1 to that. For this you have to define the criteria for the
numbering.
For example, let's say you want to number transactions to a specific
account
by year.

Me.txtTransNumber = Nz(DLookup("TransNumber", "tblTransactions",
"[AccountNo] = " & Me.txtAccount & " AND Year(TransDate]) = " &
Year(Me.txtAcctPeriod),0) + 1


--
Dave Hargis, Microsoft Access MVP

I am using Microsoft Access 2003. I have a tabular form for data entry
with a
number field named "Volume". These volume numbers the user types are
often
five or more digits long, and usually sequential. When a new record is
entered, I would like this field to be automatically filled with a value
equal to the value of the same field in the previous record, plus one.
Basically, if one record is volume "10000", when a new record is made I
would
like the volume to automatically be "10001", then "10002" and so on. Kind
of
a "Ctrl + ' " trick, only adding one to the value rather than duplicating
the
value.

Any tips?

Thanks!

 

[Klatuu]

Thanks for the catch, Bruce. It should be DMax. My apologies.
--
Dave Hargis, Microsoft Access MVP

Maybe there's something I'm missing here, but I thought DLookup returned the
first instance of a record that matches the criteria. I assume "first" is
according to how the records are arranged in the recordset. If so, I don't
see how DLookup will locate the correct record unless there is a descending
ORDER BY clause or something like that. Wouldn't DMax be the function of
choice?
I hesitate to toss this out there since I am going to be leaving in a half
hour or so, and will not return for a week, but there it is anyhow.

So what is previous record? There is no reliable way to know what the
previous record in a table was. Now, if you want to increment a value in
a
control, you can use a technique to determine the highest current number
and
add 1 to that. For this you have to define the criteria for the
numbering.
For example, let's say you want to number transactions to a specific
account
by year.

Me.txtTransNumber = Nz(DLookup("TransNumber", "tblTransactions",
"[AccountNo] = " & Me.txtAccount & " AND Year(TransDate]) = " &
Year(Me.txtAcctPeriod),0) + 1


--
Dave Hargis, Microsoft Access MVP

I am using Microsoft Access 2003. I have a tabular form for data entry
with a
number field named "Volume". These volume numbers the user types are
often
five or more digits long, and usually sequential. When a new record is
entered, I would like this field to be automatically filled with a value
equal to the value of the same field in the previous record, plus one.
Basically, if one record is volume "10000", when a new record is made I
would
like the volume to automatically be "10001", then "10002" and so on. Kind
of
a "Ctrl + ' " trick, only adding one to the value rather than duplicating
the
value.

Any tips?

Thanks!

 

[RabidMilkMan]

I tried using the DMax method suggested. However, when I would begin to enter
the data into the record (let's call it record A1), and the new record would
appear (we'll call this one A2), the "Volume" field in the new record (A2)
would be blank. Then as I went on to enter data into the new record (A2), as
the next record would appear (A3), THAT one would take the default DMax value
that I intended for the record prior to it (A2; in essence, A3's default
value was A1 +1). Subsequent records would do the same, basically giving me
kind of a leapfrog effect. I'd love for A3 to be A2+1, instead of A1+1. Does
that make any sense?

Again, I really appreciate your help.

 

[Linq Adams via AccessMonster.com]

"These volume numbers the user types are often five or more digits long, and
usually sequential. "

Am I correct in thinking that these "Volume" numbers might be

10000
10001
10002

one time and then the user could start another group with, say

400011
400012
400013

and then maybe

20110
20111
20112

and like that? Sequential for a session or part of a session, but not
necessarily sequential from one session to the next? If so you could use the
Default Value of the field with code like this:

Private Sub Volume_AfterUpdate()
If Not IsNull(Me.Volume) Then
Volume.DefaultValue = Me.Volume + 1
End If
End Sub

This would add 1 to the previously entered Volume value until you either
manually change the number, at which time it'll start adding 1 to the newly
entered number or you close the form.

 

[Rick Brandt]

I tried using the DMax method suggested. However, when I would begin
to enter the data into the record (let's call it record A1), and the
new record would appear (we'll call this one A2), the "Volume" field
in the new record (A2) would be blank. Then as I went on to enter
data into the new record (A2), as the next record would appear (A3),
THAT one would take the default DMax value that I intended for the
record prior to it (A2; in essence, A3's default value was A1 +1).
Subsequent records would do the same, basically giving me kind of a
leapfrog effect. I'd love for A3 to be A2+1, instead of A1+1. Does
that make any sense?

Again, I really appreciate your help.

DMax()+1 does NOT work as a default value in a continuous form nor in some of
the events you might try to use. It will work in the BeforeUpdate event.

 

[RabidMilkMan]

Linq,

That seemed to just about work! It puts the value I want in the right record
and everything.

Just a follow up question-- if I like the number it gives me in the new
"Volume" field, and I just tab through it, it gives me that same value in the
next record (because I did not update the one that was correct).

Sorry if this seems nitpicky, but is there a way for it to add one to the
previous record's volume field even if I don't update the field?

Hope that makes sense. Thanks so much!