In my database, I have a table called "Rules." The Rules table holds all rules for the organization across an undetermined number of departments. The unique identifier in each of my tables is specified in a field I call IDNo which is generated as an autonumber. In my Rules Table, there are a number of additional fields, two of which are "Department" (which references the IDNo from the "Departments" table and is pulled using the Lookup feature) and "Rule_Number" which is a field of datatype number. The Rule_Number field does not contain a unique value; however, when used together the Department and Rule_Number fields will comprise unique values. That is, there will be exactly 1 Rule_Number 1 for each department, 1 Rule_Number 2 for each department, etc.
I want to automatically populate the Rule_Number field with a calculated default value using the next number in order when I run a parameterized query of the following form:
Code:
SELECT 1 + Max([Rules].Rule_Number) AS MaxOfRule_Number
FROM [Rules]
HAVING (((Max([Rules].Department))=[Department?])
My question is how do I use the Default Value parameter in my Rules Table to reference this code? I cannot seem to do it using the Expression Builder.
Thank you in advance for any guidance.