How to enforce this?

[vientito]

I have table A & B and their relationship is A->B as 1-to-many based on an unique ItemID in A

In fact, I know how many B should be there for 1 record of A

Say, 1 A can have 5 elements of B and each B has a different type ID to define its individual contents

For right now, because of the relationship definition I can only enforce one to many contraint in itemID so I can easily generate another record of B having an unique itemID in A exceeding the its total count of 5 and perhaps has another type ID which has already existed in its repertoire. I do not like this but I cannot find anyway to restrict this in the table definition

Is this the kind of thing that we should enforce in Form entry policy rather than in Table entry?

[ranman256]

Yes, enforce it in Form logic if you only want 5.
You could lock the sub form entry if count=5.

[vientito]

I have found a way

By the use of composite key - if I pair the itemID & the Type in B together as my primary key then I could not create a duplication of that in B

Yet it is not perfect since I still cannot enforce the total number of elements in B. But at least I can enforce the fact that I cannot create a type that I already have created

So that leaves me the only choice of enforcing the total number in Form entry policy

[vientito]

I seem to find another way to restrict the number of elements as well

Instead of using text for element type I could use BYTE as part of the composite key. Then I set its validation rule to be less than or equal to 5. Essentially I could restrict the number of elements and making sure there is no repetition of type element as well.

[ssanfu]

Is this the kind of thing that we should enforce in Form entry policy rather than in Table entry?

If it helps, with the exception of the PK and a few compound indexes, all of my form entry policies are is accomplished using form logic - mostly VBA code.
For me, it is much easier using control/form events (such as before update/ after update events) to ensure the requirements/restrictions are met.