Remove a record from a table that is linked to other tables

[julie67]

I know this is probably an easy one, but I am pressed for time.

I have a "Names" table with names in it. It is also linked to other tables such as "Property Names Associations" which indicates the properties each person owns. I also have it linked to Group affliations as well. If a person dies or ceases membership, how can I remove them from the list? I believe that it is good practice to not delete them, but to just filter them out.

It gets tricky since each person can have multiple affliations and own multiple properties. Any suggestions?

thanks,
Julie

 

[VBslammer]

Add a Yes/No field to the main table for "Active" or similar, then filter your reports/records for Active members.

You could also create an archive table and post the dead people to the archive table before deleting them. If you have "Cascade Deletes" enforced in your table relationships, all the child records will be deleted automatically.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[julie67]

The only problem is that they may quit one association but still be a member of multiple other organizations. I can add the deceased field easy, but I don't know about the other one.

 

[VBslammer]

When you want to do selective deletes, you can use a transaction to perform separate action queries, and roll back the transaction if an error occurs so you won't be left with orphaned records. I've worked with many systems that didn't even use related tables, so I had to perform the "cascade" operations manually anyway. With SQL Server, you have the option of using triggers to handle these tasks.

Here's an ADO example that deletes a parent table entry and its linker table entries using a transaction (it could just as easily refer to a person instead of "AirUnitMotor"):

Public Function RemoveAirUnitFanMotor(ByVal motorID As Long) As Boolean
On Error GoTo ErrHandler
  Dim cnn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim lngRecsAffected As Long
  Dim blnInTrans As Boolean
  
  Set cnn = CurrentProject.Connection
  Set cmd = New ADODB.Command
  Set cmd.ActiveConnection = cnn
  cmd.CommandType = adCmdText
  
  cnn.BeginTrans
  blnInTrans = True [green]'=================================[/green]
  
  cmd.CommandText = "DELETE * FROM AirUnitMotor WHERE [MotorNo]=" & motorID
  cmd.Execute lngRecsAffected

  cmd.CommandText = "DELETE * FROM AirUnitSchedule WHERE [MotorNo]=" & motorID
  cmd.Execute lngRecsAffected
  
  cnn.CommitTrans
  blnInTrans = False [green]'================================[/green]
  
  RemoveAirUnitFanMotor = True

ExitHere:
  On Error Resume Next
  Set cmd = Nothing
  Set cnn = Nothing
  Exit Function
ErrHandler:
  If blnInTrans Then
    cnn.RollbackTrans
    MsgBox "Transaction Rolled Back"
  End If
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

"You just have to know which screws to turn." - Professor Bob