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

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/


"jamccarley" <(E-Mail Removed)> wrote in message
news:963EDE61-E5A8-4EC4-8A35-(E-Mail Removed)...
>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

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

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




"Ken Snell MVP" wrote:

> 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/
>
>
> "jamccarley" <(E-Mail Removed)> wrote in message
> news:963EDE61-E5A8-4EC4-8A35-(E-Mail Removed)...
> >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
>
>
>

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"

"jamccarley" wrote:

>
> For some reason this is returning an ERROR#. This is exactly what I have
> typed
>
> =DMax("Confirmation_number","Gage_confirmation","[GageID]=" & [GageID])
>
>
>
>
> "Ken Snell MVP" wrote:
>
> > 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/
> >
> >
> > "jamccarley" <(E-Mail Removed)> wrote in message
> > news:963EDE61-E5A8-4EC4-8A35-(E-Mail Removed)...
> > >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
> >
> >
> >

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

jamccarley wrote:
> For some reason this is returning an ERROR#. This is exactly what I have
> typed
>
> =DMax("Confirmation_number","Gage_confirmation","[GageID]=" & [GageID])
>
>
>
>
> "Ken Snell MVP" wrote:
>
>> 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/
>>
>>
>> "jamccarley" <(E-Mail Removed)> wrote in message
>> news:963EDE61-E5A8-4EC4-8A35-(E-Mail Removed)...
>>> 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
>>
>>

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/



"jamccarley" <(E-Mail Removed)> wrote in message
news:34920D06-9A26-4EA4-AE6A-(E-Mail Removed)...
>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"
>
> "jamccarley" wrote:
>
>>
>> For some reason this is returning an ERROR#. This is exactly what I have
>> typed
>>
>> =DMax("Confirmation_number","Gage_confirmation","[GageID]=" & [GageID])
>>
>>
>>
>>
>> "Ken Snell MVP" wrote:
>>
>> > 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/
>> >
>> >
>> > "jamccarley" <(E-Mail Removed)> wrote in message
>> > news:963EDE61-E5A8-4EC4-8A35-(E-Mail Removed)...
>> > >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
>> >
>> >
>> >