DLookup() compromises form "Relationship"???

[Sheila.deJesus]

My db has 4 tables and forms
Table/Form 1 contains customer information: ID, Name, Address and Contact Numbers
Table/Form 2 contains vehicle information: ID, Vehicle Model, Plate Number, Chassis and Engine Numbers
Table/Form 3 contains insurance coverage data: ID, Plate Number, Policy Number and details of insurance coverages
And Table/Form 4 contains premium collection data: ID, Policy Number and details of payment

The underlined fields are the respective primary keys and relationship of each table.

I want the Plate Number on Table 3 to automatically change, once the user edits the same field on Table 2.
My concern is, if the customer has 2 or more vehicles or plate numbers, the DLookup function only takes up one plate number. How do I use this function without compromising the relationship of the form???

My other concern is the “=Right([PlateNumber],1)” function I used on my “Ending Plate Number” field. Data appears on the Form but if I look on the Table, data is missing.

Can’t figure these out! Please help..


Thank you!

Sheila

 

[Adeptus]

If you're changing the plate number in Table 2, and plate number is the key in Table 2, then I'd say do a lookup of the plate number in Table 3, not the (customer) ID.

Also - if you set up "Enforced Referential Integrity" in the Relationships window, then it should make the changes for you.

 

[Sheila.deJesus]

Thanks very much for the inputs!!

 

[Sheila.deJesus]

Hi!

I made some modifications on my tables based on your inputs.

It works. Except that, now i'm facing another problem.

The field name "Plate Number" in Table2 is being edited a month after encoding. Once the user edit such field, Table3 adapts the changes but all the data previously encoded on Table3 were missing.

Thanks.

 

[Sheila.deJesus]

Also, i tried to enforce referential integrity on the "Plate Number" but there's an error message saying "No unique index found......."

Thanks again for your inputs!