I have a parent table with several child tables all related by DeviceID. I would like to enforce that for each parent there is only one child record in each table. For example if I have Device001 in the parent, then the child will only have Device001.
Before you refer me to create a unique index on the child...which I can do...my issue is that it is not very user friendly. I don't want the user to ever get an error stating that they tried to enter a record that already exists. I would prefer to only allow the Layout to perform the following logic at the layout level:
When the layout opens...
IF a record exists for the DeviceID show that record and allow the user to modify it but not delete it.
IF a record does not exist for the Device ID - then allow the user to enter a new record.
The user should never browse the records or even know that there are other records for other Device ID.
I have been playing with the "Layout_OnEnter" trigger - but can't seem to figure out how to get the DeviceID from the parent table and then enforce the above logic.
Hopefully this made sense and I appreciate any guidance on how-to make this happen or pointers to samples of this working.