Repost-multiple id numbers

[hsdaguilar]

Hello,

I have two tables/forms, Projects and Entitlements, that are linked by id.
The Entitlements table consists of a entitlement type field and an
entitlement id# field. The thing is the entitlment id# increments based on
the entitlement type. I need to find a way to have it automatically insert
the next entitlment id# based on the selected entitlement type.
Example:
Project 1 has entitlement CUP 800 and PDP 1700.
So, when I input Project 2 and I select CUP for the entitlment type, I
would like it to automatically insert 801 in the entitlment id# (and not
1701, etc, etc.)

I appreciate the assistance.
Please let me know if you need more detail.
Thank you.

 

[Steve Schapel]

Hsdaguilar,

On the Before Insert or Before Update event of the entitlement subform,
try code something like this...
Me.entitlement_id = DMax("[entitlement
id]","Entitlements","[entitlement type]='" & Me.entitlement_type & "'")+1

By the way, as asn aside, it is not a good idea to use a # as part of
the name of a field or control.

 

[hsdaguilar]

Sorry, I took so long to post, but Thank you! It worked perfectly.

Hsdaguilar,

On the Before Insert or Before Update event of the entitlement subform,
try code something like this...
Me.entitlement_id = DMax("[entitlement
id]","Entitlements","[entitlement type]='" & Me.entitlement_type & "'")+1

By the way, as asn aside, it is not a good idea to use a # as part of
the name of a field or control.

--
Steve Schapel, Microsoft Access MVP

Hello,

I have two tables/forms, Projects and Entitlements, that are linked by id.
The Entitlements table consists of a entitlement type field and an
entitlement id# field. The thing is the entitlment id# increments based on
the entitlement type. I need to find a way to have it automatically insert
the next entitlment id# based on the selected entitlement type.
Example:
Project 1 has entitlement CUP 800 and PDP 1700.
So, when I input Project 2 and I select CUP for the entitlment type, I
would like it to automatically insert 801 in the entitlment id# (and not
1701, etc, etc.)

I appreciate the assistance.
Please let me know if you need more detail.
Thank you.