Set value of a form control in a different record (1 Viewer)

[EternalMyrtle]

I am a little bit lost here. I have a SpouseID field in my Contacts table to link spouses.

When a spouse is selected in one contact's record, I want to be able to automatically update the other contact's SpouseID control.

I have created a calculated control called txtSpouseID that uses DLookup to find the ContactID for the other spouse:

=Nz(DLookUp("[ContactID]","tblContacts","[Spouse] =" & [ContactID]))

And I have entered this code into the form's OnCurrent event:

If Not IsNull(Me.txtSpouseID) Then
Me.Spouse = Me.txtSpouseID
End If

However, this obviously only works if the user navigates to the spouses record. If s/he doesn't then the value does not get entered into the table.

Is there some way to do this that I don't know of??

 

[MarkK]

Yeah, you can update data in tables without using forms at all. In this case you know this spouse's ID and the other spouse's ID, you can do this . . .

dim dbs as dao.database
set dbs = currentdb
dbs.execute _
   "UPDATE tContact " & _
   "SET SpouseID = " & ThisSpouseID & " " & _
   "WHERE ContactID = " & OtherSpouseID

. . . so see how that finds the record belonging to the other spouse, and updates a field using data from this spouse?
Cheers,

 

[EternalMyrtle]

This is great! I learned something new and I don't need that DLookup anymore. Thanks