Update one field based on change to another in same table

[Carissathorne]

Hi I'm a newbie at Access and I've come across a problem I can't easily Google the answer to.

I have a table of client data. One field is the Spouse ID, which stores the record ID number of the client who is the spouse of said client. I have a form that I am using to edit the information, and when I update the Spouse ID in that form for one client I would like it to also update the Spouse ID of the client's spouse to the Record ID of the current client, so they always match up with each other. What's the easiest way to do this? VBA? Thanks for any help!

[ranman256]

an update query
after the 'save' or afterupdate event

docmd.openquery "quUpdSpouseID"

the sql would use the current spouseID as the ClientID then update its spouseID from ClientID

[June7]

And if there is not already a client record for the spouse would need to instead open a form to enter new record. Then possibly also have to update records to remove spouse ID, assuming client can't have two spouses. I suppose it would be rare for your clients to switch spouses with each other, but not impossible.

[Carissathorne]

Thanks I'm not sure how to reference the field in the query...haven't done much other than basic lookup queries before. How do I tell it to update that field in another record if it matches a particular field in the current record? I'm missing how to do the syntax for referencing different records but same field.

[June7]

I would run this in VBA:

CurrentDb.Execute "UPDATE Clients SET SpouseID = " & Me.ClientID & " WHERE ClientID = " & Me.SpouseID

[Carissathorne]

Yep works perfect... thanks! So Me.xxxx is referencing the current record is that right?

Oh forgot to add that I don't think I'll need a separate form for entering a spouse that doesn't exist yet... its a drop down box where you can only choose someone who is already in the database. So if it's a new client the first one will go in with no spouse then the second one I will be able to choose the spouse that I already entered. Should work fine for my purposes.

Another question....is there a way to have it open a specific folder in file explorer with a button click? We store correspondence etc. in a shared folder specific to each client. I would have to add a field with the folder path for each client which I would have to enter manually for each one...but it would be worth it in the end.

[June7]

Options:

1. hyperlink type field, no VBA code needed

2. text field stores path and code opens the folder, research FollowHyperlink method, review http://allenbrowne.com/func-GoHyperlink.html

[Carissathorne]

One issue... if I add a spouse and then delete the spouse, I'm getting an error message. Run-time error '3075': Syntax error (missing operator) in query expression 'ID ='

here's the code I have

CurrentDb.Execute "UPDATE DataClients SET SpouseID = " & Me.ID & " WHERE ID = " & Me.SpouseID

[Carissathorne]

Options:

1. hyperlink type field, no VBA code needed

2. text field stores path and code opens the folder, research FollowHyperlink method, review http://allenbrowne.com/func-GoHyperlink.html

Looks like that will work great thanks for the info!

[June7]

You mean you are just deleting value from SpouseID field, not deleting record?

If IsNull(Me.SpouseID) Then
CurrentDb.Execute "UPDATE DataClients SET SpouseID = Null WHERE ID = " & Me.SpouseID.OldValue
Else
CurrentDb.Execute "UPDATE DataClients SET SpouseID = " & Me.ID & " WHERE ID = " & Me.SpouseID
End If

What event are you using?

[Carissathorne]

You mean you are just deleting value from SpouseID field, not deleting record?

If IsNull(Me.SpouseID) Then
CurrentDb.Execute "UPDATE DataClients SET SpouseID = Null WHERE ID = " & Me.SpouseID.OldValue
Else
CurrentDb.Execute "UPDATE DataClients SET SpouseID = " & Me.ID & " WHERE ID = " & Me.SpouseID
End If

What event are you using?

I am using AfterUpdate event on the combobox for the data entry. When I add the code above I'm getting:

Method or data member not found
and it highlights .OldValue

Here's the full code I have there now:

Private Sub Combo138_AfterUpdate()




CurrentDb.Execute "UPDATE DataClients SET SpouseID = " & Me.ID & " WHERE ID = " & Me.SpouseID


If IsNull(Me.SpouseID) Then
CurrentDb.Execute "UPDATE DataClients SET SpouseID = Null WHERE ID = " & Me.SpouseID.OldValue
Else
CurrentDb.Execute "UPDATE DataClients SET SpouseID = " & Me.ID & " WHERE ID = " & Me.SpouseID
End If


End Sub


Without adding the code to set the record to null, it does actually work when I delete the value, if I save it, go to another record, then go back, it is deleted. It just gives me that syntax error box when I actually delete it.

[June7]

Remove the first Execute line that is not within the If Then structure.

Well, the OldValue property works for me. The combobox is named SpouseID?

[Carissathorne]

Remove the first Execute line that is not within the If Then structure.

Well, the OldValue property works for me. The combobox is named SpouseID?

Got it!

Deleted the first line (duh should have noticed that)

The combobox itself is named Combo138. I put Combo138 in place of SpouseID and it seems to be working now, no errors! Thanks so much for the help.

[June7]

Advise giving controls meaningful names. I always name different from field, such as: cbxSpouse.