DMax- various incrementing id numbers

[hsdaguilar]

Hello,

I have two tables. 1) Project Table- has a Project Id (autonumber) and holds
records on various projects. 2) Entitlement Table- has Project Id (linking to
Project TAble), Entitlement Type and Entitlement Id.
There are various Entitlement types and each one has it's own set of records.
Example: PDP (entitlement) has 1700 records, while CUP (entitlment) has 800.
Various Entitlments can be associated with each project.

My question is this. I have a form, with both tables, the Entitlement being
a subform. I would like to be able to select the Entitlment type from a drop
down box and then the Entitlement Id automatically updates with the next
record number for the selected entitlement type. So if I select CUP the id
number should update with 801 and not 1701, etc.

I thought the dmax button could come in handy, but do not know how to make
it look up the Entitlement type first. Dlookup? Your help would be greatly
appreciated.

Thank you.

 

[Rick Brandt]

Hello,

I have two tables. 1) Project Table- has a Project Id (autonumber)
and holds records on various projects. 2) Entitlement Table- has
Project Id (linking to Project TAble), Entitlement Type and
Entitlement Id.
There are various Entitlement types and each one has it's own set of
records. Example: PDP (entitlement) has 1700 records, while CUP
(entitlment) has 800. Various Entitlments can be associated with each
project.

My question is this. I have a form, with both tables, the Entitlement
being a subform. I would like to be able to select the Entitlment
type from a drop down box and then the Entitlement Id automatically
updates with the next record number for the selected entitlement
type. So if I select CUP the id number should update with 801 and not
1701, etc.

I thought the dmax button could come in handy, but do not know how to
make it look up the Entitlement type first. Dlookup? Your help would
be greatly appreciated.

Thank you.

Nz(DMax("[Entitlement Id]", "[Entitlement Table]", "[Entitlement Type] = '" &
Me![Entitlement Type] & "'"), 0) + 1

 

[hsdaguilar]

I placed the code in the default value...
It says error until I inpute the entitlment type, then it goes to 0 and
stays that way...

Hello,

I have two tables. 1) Project Table- has a Project Id (autonumber)
and holds records on various projects. 2) Entitlement Table- has
Project Id (linking to Project TAble), Entitlement Type and
Entitlement Id.
There are various Entitlement types and each one has it's own set of
records. Example: PDP (entitlement) has 1700 records, while CUP
(entitlment) has 800. Various Entitlments can be associated with each
project.

My question is this. I have a form, with both tables, the Entitlement
being a subform. I would like to be able to select the Entitlment
type from a drop down box and then the Entitlement Id automatically
updates with the next record number for the selected entitlement
type. So if I select CUP the id number should update with 801 and not
1701, etc.

I thought the dmax button could come in handy, but do not know how to
make it look up the Entitlement type first. Dlookup? Your help would
be greatly appreciated.

Thank you.

Nz(DMax("[Entitlement Id]", "[Entitlement Table]", "[Entitlement Type] = '" &
Me![Entitlement Type] & "'"), 0) + 1

 

[Rick Brandt]

I placed the code in the default value...
It says error until I inpute the entitlment type, then it goes to 0
and stays that way...

Can't be used there because the default value is set as soon as you navigate to
the new reocrd position and you haven't yet entered a value for [Entitlement
Type]. That wouldn't work in a multi-user or continuous form anyway so it's
best avoided.

Apply the value using code in the Form's BeforeUpdate event...

If Nz(Me![Entitlement Id]) = True Then
Me![Entitlement Id] = Nz(DMax("[Entitlement Id]", "[Entitlement Table]",
"[Entitlement Type] = '" & Me![Entitlement Type] & "'"), 0) + 1
End If

Hello,

I have two tables. 1) Project Table- has a Project Id (autonumber)
and holds records on various projects. 2) Entitlement Table- has
Project Id (linking to Project TAble), Entitlement Type and
Entitlement Id.
There are various Entitlement types and each one has it's own set
of records. Example: PDP (entitlement) has 1700 records, while CUP
(entitlment) has 800. Various Entitlments can be associated with
each project.

My question is this. I have a form, with both tables, the
Entitlement being a subform. I would like to be able to select
the Entitlment type from a drop down box and then the Entitlement
Id automatically updates with the next record number for the
selected entitlement type. So if I select CUP the id number
should update with 801 and not 1701, etc.

I thought the dmax button could come in handy, but do not know
how to make it look up the Entitlement type first. Dlookup? Your
help would be greatly appreciated.

Thank you.

Nz(DMax("[Entitlement Id]", "[Entitlement Table]", "[Entitlement
Type] = '" & Me![Entitlement Type] & "'"), 0) + 1

 

[hsdaguilar]

I'm sorry, I'm not very good with code. I placed this in the forms before
update in VB code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(Me![Entitlement Id]) = True Then Me![Entitlement Id] =
Nz(DMax("[Entitlement Id]", "[Entitlement Table]", "[Entitlement Type] = '" &
Me![Entitlement Type] & "'"), 0) + 1
End Sub

But I keep getting 0 as the Entitlement Id after I select the Entitlement
Type.

I placed the code in the default value...
It says error until I inpute the entitlment type, then it goes to 0
and stays that way...

Can't be used there because the default value is set as soon as you navigate to
the new reocrd position and you haven't yet entered a value for [Entitlement
Type]. That wouldn't work in a multi-user or continuous form anyway so it's
best avoided.

Apply the value using code in the Form's BeforeUpdate event...

If Nz(Me![Entitlement Id]) = True Then
Me![Entitlement Id] = Nz(DMax("[Entitlement Id]", "[Entitlement Table]",
"[Entitlement Type] = '" & Me![Entitlement Type] & "'"), 0) + 1
End If

hsdaguilar wrote:
Hello,

I have two tables. 1) Project Table- has a Project Id (autonumber)
and holds records on various projects. 2) Entitlement Table- has
Project Id (linking to Project TAble), Entitlement Type and
Entitlement Id.
There are various Entitlement types and each one has it's own set
of records. Example: PDP (entitlement) has 1700 records, while CUP
(entitlment) has 800. Various Entitlments can be associated with
each project.

My question is this. I have a form, with both tables, the
Entitlement being a subform. I would like to be able to select
the Entitlment type from a drop down box and then the Entitlement
Id automatically updates with the next record number for the
selected entitlement type. So if I select CUP the id number
should update with 801 and not 1701, etc.

I thought the dmax button could come in handy, but do not know
how to make it look up the Entitlement type first. Dlookup? Your
help would be greatly appreciated.

Thank you.

Nz(DMax("[Entitlement Id]", "[Entitlement Table]", "[Entitlement
Type] = '" & Me![Entitlement Type] & "'"), 0) + 1