Changing another Record in same Table

[L.A. Lawyer]

I want to change the value of another record in the same table in Access
2007.

I am using a SpouseID to connect a contact to his or her spouse's ContactID
field. I have already assigned the value to the Spouse ID field and now I
want Access to automatically assign the value to the spouse's record too.

 

[Damon Heron]

What you said makes no sense. If you have already assigned the spouseID to
all the fields, then there is nothing left to do...??

Damon

 

[KARL DEWEY]

Is this what you have or want to have --
IndividualID - primary key
FName
LName
Spouse - foreign key - IndividualID of another
Addr1
Addr2
City
State
Zip
Contact - foreign key - IndividualID of another
etc.

Set up a one-to-many relationships from Individual table to two copies of
Individual table. The relationship from IndividualID (one) to Spouse (many)
and from IndividualID (one) to Contact (many).

Use a combo to select Individual for Spouse and another combo to select
Contact.

 

[LA Lawyer]

Obviously, I was not clear.
I was to update/change a field in another record in the same table.

This is simply to say that I want to use the value of a field in a current record to update the value of another field in another record, which happens to be in the same table.

I will call the current record "the current contact" and the other record the "the spouse's record".

I am easily able to assign the record ID number of the "spouse's record" into the contact's contact's record. That is no problem at all.

However, I want, when I exit that field in the current record having assigned the value of the spouse's record ID, now I want to assign the value of the current record ID to the spouse's record.


How is that done?

 

[KARL DEWEY]

I did not follow that. Can you post example of records showing what you mean?

 

[LA Lawyer]

All I really need to know is how to change the value of a field on another
record in the same table; I think that I made my request too complicated. I
can figure out the rest.

 

[John W. Vinson]

All I really need to know is how to change the value of a field on another
record in the same table; I think that I made my request too complicated. I
can figure out the rest.

You'll need to either open a Form with that record selected, or run an Update
Query with a criterion selecting that record.

 

[John W. Vinson]

I want to change the value of another record in the same table in Access
2007.

I am using a SpouseID to connect a contact to his or her spouse's ContactID
field. I have already assigned the value to the Spouse ID field and now I
want Access to automatically assign the value to the spouse's record too.

The need to do this suggests that your table design is not properly
normalized! A value in a record should depend ONLY on that record's primary
key, not the value of some other record's primary key.

What you'll need to do is open a Recordset based on the table using the
spouseID as a criterion, and use the Update method on that recordset. Perhaps
if you could post the actual fieldnames of your table and an example of what
you want to "assign... to the spouse's record" it would help. It's not at all
clear from your post what you want to assign to what!