DMax function

[jamccarley]

I have a form, with a sub form. The main forms [SVR tracking form] ID feild
is [NumberID]. This form contains all of the claims that we receive from our
customer. The subform [SVR C/M] is the countermeasure form. It is linked to
the main form thru [NumberID]. The numberID field increases by one for every
new record thru the DMax function in its default field. I understand how to
do that, what I am having trouble doing is increasing the countermeasure
number per [NumberID] by one. Each claim on the main form can have multiple
countermeasures. If I set up the default like the main form it works, except
it increases by one over all of the countermeasures in the database. I need
it to increase by one only in the record the c/m pertains to. So if record
"57001" has 2 c/m's the next one will be 3. And if record 57002 has 8
countermeasures the next one will be 9. I hope I gave enough information.

Thanks
Josh

 

[Ken Snell MVP]

Generically, you would add a criterion to your DMax function that will limit
the returned value to be based on the "NumberID" of interest.

So add NumberID to your DMax function in the criterion area:

=DMax("FieldName", "TableName", "[NumberID]=" & [NumberID])

 

[jamccarley]

For some reason this is returning an ERROR#. This is exactly what I have
typed

=DMax("Confirmation_number","Gage_confirmation","[GageID]=" & [GageID])

Generically, you would add a criterion to your DMax function that will limit
the returned value to be based on the "NumberID" of interest.

So add NumberID to your DMax function in the criterion area:

=DMax("FieldName", "TableName", "[NumberID]=" & [NumberID])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have a form, with a sub form. The main forms [SVR tracking form] ID feild
is [NumberID]. This form contains all of the claims that we receive from
our
customer. The subform [SVR C/M] is the countermeasure form. It is linked
to
the main form thru [NumberID]. The numberID field increases by one for
every
new record thru the DMax function in its default field. I understand how
to
do that, what I am having trouble doing is increasing the countermeasure
number per [NumberID] by one. Each claim on the main form can have
multiple
countermeasures. If I set up the default like the main form it works,
except
it increases by one over all of the countermeasures in the database. I
need
it to increase by one only in the record the c/m pertains to. So if record
"57001" has 2 c/m's the next one will be 3. And if record 57002 has 8
countermeasures the next one will be 9. I hope I gave enough information.

Thanks
Josh

 

[jamccarley]

I created a query that brings back the highest confirmation number for each
unique GageID. Is there any way to have the form use that query and just + 1
onto that?
My query fields are "GageID" and "Confirmation_number"

For some reason this is returning an ERROR#. This is exactly what I have
typed

=DMax("Confirmation_number","Gage_confirmation","[GageID]=" & [GageID])

Generically, you would add a criterion to your DMax function that will limit
the returned value to be based on the "NumberID" of interest.

So add NumberID to your DMax function in the criterion area:

=DMax("FieldName", "TableName", "[NumberID]=" & [NumberID])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have a form, with a sub form. The main forms [SVR tracking form] ID feild
is [NumberID]. This form contains all of the claims that we receive from
our
customer. The subform [SVR C/M] is the countermeasure form. It is linked
to
the main form thru [NumberID]. The numberID field increases by one for
every
new record thru the DMax function in its default field. I understand how
to
do that, what I am having trouble doing is increasing the countermeasure
number per [NumberID] by one. Each claim on the main form can have
multiple
countermeasures. If I set up the default like the main form it works,
except
it increases by one over all of the countermeasures in the database. I
need
it to increase by one only in the record the c/m pertains to. So if record
"57001" has 2 c/m's the next one will be 3. And if record 57002 has 8
countermeasures the next one will be 9. I hope I gave enough information.

Thanks
Josh

 

[John Spencer MVP]

What type of field is GageID? Is it a text field? If so you need to add some
quote marks, you can use quotes or apostrophes. If GageId never has an
apostrophe then you can use the following.

=DMax("Confirmation_number","Gage_confirmation","[GageID]='" & [GageID] & "'")

If GageID can have an apostrophe, then replace the apostrophes above with TWO
quote marks.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

For some reason this is returning an ERROR#. This is exactly what I have
typed

=DMax("Confirmation_number","Gage_confirmation","[GageID]=" & [GageID])

Generically, you would add a criterion to your DMax function that will limit
the returned value to be based on the "NumberID" of interest.

So add NumberID to your DMax function in the criterion area:

=DMax("FieldName", "TableName", "[NumberID]=" & [NumberID])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have a form, with a sub form. The main forms [SVR tracking form] ID feild
is [NumberID]. This form contains all of the claims that we receive from
our
customer. The subform [SVR C/M] is the countermeasure form. It is linked
to
the main form thru [NumberID]. The numberID field increases by one for
every
new record thru the DMax function in its default field. I understand how
to
do that, what I am having trouble doing is increasing the countermeasure
number per [NumberID] by one. Each claim on the main form can have
multiple
countermeasures. If I set up the default like the main form it works,
except
it increases by one over all of the countermeasures in the database. I
need
it to increase by one only in the record the c/m pertains to. So if record
"57001" has 2 c/m's the next one will be 3. And if record 57002 has 8
countermeasures the next one will be 9. I hope I gave enough information.

Thanks
Josh

 

[Ken Snell MVP]

Yes, DLookup function will bring you back one vaue from your query. You then
add 1 to the result:

=DMax("Confirmation_number","NameOfQuery","[GageID]=" & [GageID])

As for #Error result, make sure that the name of the control that is getting
the value of this expression is not named the same as GageID.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I created a query that brings back the highest confirmation number for each
unique GageID. Is there any way to have the form use that query and just +
1
onto that?
My query fields are "GageID" and "Confirmation_number"

For some reason this is returning an ERROR#. This is exactly what I have
typed

=DMax("Confirmation_number","Gage_confirmation","[GageID]=" & [GageID])

Generically, you would add a criterion to your DMax function that will
limit
the returned value to be based on the "NumberID" of interest.

So add NumberID to your DMax function in the criterion area:

=DMax("FieldName", "TableName", "[NumberID]=" & [NumberID])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a form, with a sub form. The main forms [SVR tracking form] ID
feild
is [NumberID]. This form contains all of the claims that we receive
from
our
customer. The subform [SVR C/M] is the countermeasure form. It is
linked
to
the main form thru [NumberID]. The numberID field increases by one
for
every
new record thru the DMax function in its default field. I understand
how
to
do that, what I am having trouble doing is increasing the
countermeasure
number per [NumberID] by one. Each claim on the main form can have
multiple
countermeasures. If I set up the default like the main form it works,
except
it increases by one over all of the countermeasures in the database.
I
need
it to increase by one only in the record the c/m pertains to. So if
record
"57001" has 2 c/m's the next one will be 3. And if record 57002 has 8
countermeasures the next one will be 9. I hope I gave enough
information.

Thanks
Josh