Update incremental values in one table from fields in multiple tables

[YDYW147]

I have an interesting problem in a new database.
The company uses an incremental number for all received loads such as 16000001. In the details for drums, skids, etc they are also number sequentially 1600002, 1600003 etc.
They then create new inventory using same format but all of the numbers need to be sequential but the fields are different names in different tables. Field names like [ShipmentID], [ShipDetailID], [CreatedID]. They would like all forms to increment a common counter max value by 1 each time and each year automatically change the first to digits to represent the current year. (in example the year is 2016.) So you have to check all 7 digits to check the maximum number as every year you need to reset the firt two to the current year and start the next record a say 1700001 last record in 20016 was 1610978 The reset occurs Jan 1st.

[PhilOfWalton]

I suggest you look at
https://bytes.com/topic/access/answe...crement-number
Yhat is virtually the same question.
Phil

[jforbes]

This could be a complicated requirement. Which version of Access are you using for this? Are you using MS-SQL as a Backend or strictly MS-Access?