Auto numbering based on selection

[brichard]

Hello

I would like to ask your help with auto-numbering.
My database contains project details.
A unique number is assigned to each project.
When adding a new project through a form, I would need an automatically entered project number based on which partner is selected.
The project number needs to be *last (highest) project number for the selected partner*+1.
If it's a new partner and no previous number is found, it should return the value "0".
Partner names and project numbers are only stored in the table what I am writing with the form.

partner
project number

partnerA 2010000001
partnerA 2010000002
partnerA 2010000003

partnerB 2020000001
partnerB 2020000002

In this example
if selecting partnerA from the drop-down, 2010000004 should be the automatic project number
if selecting partnerB from the drop-down, 2020000003 should be the automatic project number

Could you help with this?

Thank you

 

[John Big Booty]

Perhaps the sample posted here will give you some pointers.

 

[brichard]

Thank you John

This looks promising, but the queries containing references to tables I can't see in the database.
I can see it is working, but not getting the concept behind.
Something like "we are looking for the Dmax value of the numbers filtered based on the selection and adding one to that value".

 

[John Big Booty]

Sorry that Sample was built out of my sandbox DB and the queries are artefacts from that and are not relevant to the principal being demonstrated. The important part is the code in the After Update event of the Combo.