Update record on 2 tables??

[ChrisHaynes]

Hi, I have 2 separate forms, both have a field which I have created a relationship between. They both link to records on 2 serparate tables. However, when I rename the field on one form, which I often have to do, this doesn't update the record in the other table, which causes the data to be wrong.

I already have a before update message box on each of the forms which let's the user decide whether to save the changes or not. I was thinking of adding a dlookup function when the user clicks yes, to update the data in the other table which will then syncronise the data.

Does anyone know a code to update the record in the other table??

Cheers,

Chris.

 

[mp9]

In the relationships window, set relationship properties on your relationship between the two tables so that changes are cascaded?

 

[ChrisHaynes]

Thanks for your reply mp9. I was looking at th relationship optons earlier but didn't really know what to select. I have tried selecting the 'Cascade Update Related Fields' option, however when I click OK, i get the error message 'No unique index found for the referenced field of the primary table'??


What's causing this problem? Any ideas?


Thanks again,

Chris.

 

[mp9]

That means you have a value in one of your tables that doesn't have a related record in the other table. Track that down, make sure that all records in one table have a valid related record in the other, and try again.

You can use the Unmatched Query Wizard to easily find records in one table without related records in the other table. Select Queries in the Db window, click the New button in the Db window and one of the wizard choices will be the unmatched one - from there, it's self-explanatory.

 

[ChrisHaynes]

Thanks mp9. I dont have the same records in each table. One of the tables only includes a select few, but if data in the field on the main table which includes all the records is changed, I would like the field on the smaller table to change to (if it exists).

Is this possible?

Thanks again,

Chris.