Help With Code for Update Query

EternalMyrtle

I'm still alive
Local time
Yesterday, 16:53
Joined
May 10, 2013
Messages
533
Here is my code:

Code:
Private Sub btnRemoveSpouse_Click()
Dim PriorValue As Integer
PriorValue = Me.Spouse.OldValue

Dim dbs As dao.Database
Set dbs = CurrentDb
dbs.Execute _
   "UPDATE tblContacts " & _
   "SET Spouse = " & Me.ContactID & " " & _
   "WHERE ContactID = " & PriorValue
  
 Me.Spouse = ""
End Sub

This is for a button to remove a contact's spouse association. I want the information to be updated in the spouse's record too. Since the Spouse (control for FK SpouseID) will be removed, I am trying to use a variable (PriorValue) instead of the control name to store the old value but it is not working :(

Can someone please point me in the right direction??
 
I think I see the problem. I need to change the SET part. Will post back if I still cannot get it working.

Thanks!
 
Okay got it working. Sorry to have bothered. Took a lunch break and saw the light :)

Here is the code for reference:

Code:
Private Sub btnRemoveSpouse_Click()
Dim PriorValue As Integer
PriorValue = Me.Spouse.OldValue

Dim dbs As dao.Database
Set dbs = CurrentDb
dbs.Execute _
   "UPDATE tblContacts " & _
   "SET Spouse = Null " & _
   "WHERE ContactID = " & PriorValue
   Me.Spouse = ""
End Sub
 

Users who are viewing this thread

Back
Top Bottom