DMax

[Aurora]

I am using Access 2000
I am trying to create a Db that will automatically number,
in increments of 1, a record such as a box number within
the record. Up to this point I have always used the
autonumber feature for the field (box number) i wanted to
increment. But I have been told on many occasions that
this is not a good idea.

It has been suggested that I use the DMax function, but I
am not sure of where to put the Dmax function. For ex:
In DbOne, I have a field that is labeled [boxnumber] and
is an autonumber. Now I want to created a field
[realboxno] that takes the largest autonumber from
[boxnumber] and adds one to it. The way I understand it
the autonumber field will run in the background while the
[realboxno] will show up on the forms and reports. This
is how I think the formula goes, but it is not working.
DMax("[boxnumber]","DbOne")+1 I must not be putting it
in the right property of [realboxno]. Can anyone help me
or at least point me in the right direction?

Aurora

 

[Gerald Stanley]

Unfortunately, DMax is not a function that the JET engine
recognises. To achieve the result that you wish, you would
need to include a textbox for the boxNumber column on the
table's data entry form, then in the form's BeforeUpdate
event handler, you would put in the call to DMax e.g.
If txtBoxNumber.Value = "" Then
txtBoxNumber.Value = DMax("[boxnumber]","DbOne")+1
End If

Hope This Helps
Gerald Stanley MCSD

 

[Bob]

Using the DMax function in the control source of the
control will display the next largest number available
when added to an autonumber field. Such as:

=DMax("BoxNumber","DBOne")+1

This will however not save the data to a table because it
is bound to the calculation rather than a table.
Personally I don't see the difference using this formula
or the autonumber field itself. You still have the same
situation...just one number higher doing it like this. If
you want to save this calculated number in a table you can
use the OnCurrent property to set RealBoxNumber to the
calculated value. Such as:

Forms!MyForm!RealBoxNumber = DMax("BoxNumber","DBOne")+1

 

[Pat Hartman]

You have been misinformed. The autonumber works relilably
and is preferred over self-generated sequence numbers
because it doesn't generate duplicates. In a multi-user
environment, you run the risk of generating duplicate
numbers when using the suggested DMax() method. To ensure
robustness in your application you need to add error
trapping to deal with a duplicate assignment so you can
try again. If you do not include error trapping, then the
custom solution is FAR WORSE than the autonumber solution.

 

[Ken Snell]

Whether to use autonumber or a code-calculated sequential number depends
upon many things:

(1) Can you tolerate gaps in the numbers? If no, use code-generated.
Autonumber will develop gaps and can become negative.

(2) Will you expose the number to the user? If yes, use code-generated.
Autonumbers should have *no* meaning to a user and are meant to just ensure
an almost unique key value.

(3) Will you have multiple users "getting" the next number? If yes, and if
the answer to (1) is yes and the answer to (2) is no, then autonumber is
best. If no, then you can either start and immediately save a record with
the code-generated value, or use a more fancy setup of a table with the
"unused" values in it, and have the code "check out" the next number (that
prevents, if coded correctly, two people getting the same number when
starting a record at the exact same time). I use the immediate save method
myself.


--

Ken Snell
<MS ACCESS MVP>

You have been misinformed. The autonumber works relilably
and is preferred over self-generated sequence numbers
because it doesn't generate duplicates. In a multi-user
environment, you run the risk of generating duplicate
numbers when using the suggested DMax() method. To ensure
robustness in your application you need to add error
trapping to deal with a duplicate assignment so you can
try again. If you do not include error trapping, then the
custom solution is FAR WORSE than the autonumber solution.

-----Original Message-----
I am using Access 2000
I am trying to create a Db that will automatically number,
in increments of 1, a record such as a box number within
the record. Up to this point I have always used the
autonumber feature for the field (box number) i wanted to
increment. But I have been told on many occasions that
this is not a good idea.

It has been suggested that I use the DMax function, but I
am not sure of where to put the Dmax function. For ex:
In DbOne, I have a field that is labeled [boxnumber] and
is an autonumber. Now I want to created a field
[realboxno] that takes the largest autonumber from
[boxnumber] and adds one to it. The way I understand it
the autonumber field will run in the background while the
[realboxno] will show up on the forms and reports. This
is how I think the formula goes, but it is not working.
DMax("[boxnumber]","DbOne")+1 I must not be putting it
in the right property of [realboxno]. Can anyone help me
or at least point me in the right direction?

Aurora
.

 

[Ted]

would it be ok if i interrupted....

in my application i have a parent-child pair of forms
which are related by a common field, let's call
it 'linker'. for simplicity, let's say that the control on
the parent i want sequentially incrementing is
called 'record' and that what i'm trying to get is the
value of 'record' to initiate it with the number '1' every
time you go to a new value of 'linker' and
increment 'record' by 1 each time you add another record.

if that could work, then the next step would be to allow
things to dynamically adjust the numbering in the event
any one of the records of the 'children' were deleted for
any given 'parent'

-----Original Message-----
Using the DMax function in the control source of the
control will display the next largest number available
when added to an autonumber field. Such as:

=DMax("BoxNumber","DBOne")+1

This will however not save the data to a table because it
is bound to the calculation rather than a table.
Personally I don't see the difference using this formula
or the autonumber field itself. You still have the same
situation...just one number higher doing it like this. If
you want to save this calculated number in a table you can
use the OnCurrent property to set RealBoxNumber to the
calculated value. Such as:

Forms!MyForm!RealBoxNumber = DMax("BoxNumber","DBOne")+1

-----Original Message-----
I am using Access 2000
I am trying to create a Db that will automatically number,
in increments of 1, a record such as a box number within
the record. Up to this point I have always used the
autonumber feature for the field (box number) i wanted to
increment. But I have been told on many occasions that
this is not a good idea.

It has been suggested that I use the DMax function, but I
am not sure of where to put the Dmax function. For ex:
In DbOne, I have a field that is labeled [boxnumber] and
is an autonumber. Now I want to created a field
[realboxno] that takes the largest autonumber from
[boxnumber] and adds one to it. The way I understand it
the autonumber field will run in the background while the
[realboxno] will show up on the forms and reports. This
is how I think the formula goes, but it is not working.
DMax("[boxnumber]","DbOne")+1 I must not be putting it
in the right property of [realboxno]. Can anyone help me
or at least point me in the right direction?

Aurora
.

.

 

[Roger Carlson]

On my website (see sig below) I have a small sample database called
"FormSubform.mdb" which should answer your questions (assuming I understand
it correctly, that is.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

would it be ok if i interrupted....

in my application i have a parent-child pair of forms
which are related by a common field, let's call
it 'linker'. for simplicity, let's say that the control on
the parent i want sequentially incrementing is
called 'record' and that what i'm trying to get is the
value of 'record' to initiate it with the number '1' every
time you go to a new value of 'linker' and
increment 'record' by 1 each time you add another record.

if that could work, then the next step would be to allow
things to dynamically adjust the numbering in the event
any one of the records of the 'children' were deleted for
any given 'parent'

-----Original Message-----
Using the DMax function in the control source of the
control will display the next largest number available
when added to an autonumber field. Such as:

=DMax("BoxNumber","DBOne")+1

This will however not save the data to a table because it
is bound to the calculation rather than a table.
Personally I don't see the difference using this formula
or the autonumber field itself. You still have the same
situation...just one number higher doing it like this. If
you want to save this calculated number in a table you can
use the OnCurrent property to set RealBoxNumber to the
calculated value. Such as:

Forms!MyForm!RealBoxNumber = DMax("BoxNumber","DBOne")+1

-----Original Message-----
I am using Access 2000
I am trying to create a Db that will automatically number,
in increments of 1, a record such as a box number within
the record. Up to this point I have always used the
autonumber feature for the field (box number) i wanted to
increment. But I have been told on many occasions that
this is not a good idea.

It has been suggested that I use the DMax function, but I
am not sure of where to put the Dmax function. For ex:
In DbOne, I have a field that is labeled [boxnumber] and
is an autonumber. Now I want to created a field
[realboxno] that takes the largest autonumber from
[boxnumber] and adds one to it. The way I understand it
the autonumber field will run in the background while the
[realboxno] will show up on the forms and reports. This
is how I think the formula goes, but it is not working.
DMax("[boxnumber]","DbOne")+1 I must not be putting it
in the right property of [realboxno]. Can anyone help me
or at least point me in the right direction?

Aurora
.

.

 

[Ted]

i hope this is along the same lines as your thread....

i too'd like to use an autonumbering-like functionality
w/o using the ms a2k autonumber property.

here's the layout so far. two forms bound to two tables.
parent form is called 'master' has one field call 'linker'.
'master' has a sub-form called 'child' that has two bound
fields called 'linker' and 'counter' and as you would have
guessed they are connected to each other via 'linker'.

the objective is this: whenever the viewer is using the
data entry forms, (s)he would not have to input a value in
the 'child' control called 'counter'; it'd automatically
be set by 'something' running in the background that
would 'know' the first 'child' record's value
for 'counter' were the numeral one (1) and that for that
linker's value to increment 'counter' ad infinitum with
each new record. when moving to another record having a
different value of 'linker', the same functionality would
incrementally add values to this collection of sub-records
and so forth.

ideas?

-----Original Message-----
Unfortunately, DMax is not a function that the JET engine
recognises. To achieve the result that you wish, you would
need to include a textbox for the boxNumber column on the
table's data entry form, then in the form's BeforeUpdate
event handler, you would put in the call to DMax e.g.
If txtBoxNumber.Value = "" Then
txtBoxNumber.Value = DMax("[boxnumber]","DbOne")+1
End If

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I am using Access 2000
I am trying to create a Db that will automatically number,
in increments of 1, a record such as a box number within
the record. Up to this point I have always used the
autonumber feature for the field (box number) i wanted to
increment. But I have been told on many occasions that
this is not a good idea.

It has been suggested that I use the DMax function, but I
am not sure of where to put the Dmax function. For ex:
In DbOne, I have a field that is labeled [boxnumber] and
is an autonumber. Now I want to created a field
[realboxno] that takes the largest autonumber from
[boxnumber] and adds one to it. The way I understand it
the autonumber field will run in the background while the
[realboxno] will show up on the forms and reports. This
is how I think the formula goes, but it is not working.
DMax("[boxnumber]","DbOne")+1 I must not be putting it
in the right property of [realboxno]. Can anyone help me
or at least point me in the right direction?

Aurora
.

.