Deleting entire record in table, including primary key

[jweber]

Hello! I have two tables, one is Emp (basic employee information) and the other is EmpAudit (results from auditing file). They are linked using the EmpID, and EmpId is the primary key in both tables. I've been trying to create a re-hire button that eliminates the record in the EmpAudit table, and only deletes a few fields in the Emp table. I have used the help directory, and I think I should be using DeleteObject, but I can't figure out how to associate that one record. My code is below. This was originally done through a query, but it corrupted the record associated with the employee ID in the EmpAudit table. This is why I am trying to do it programmatically. Thanks!

If TeamNumber = 99 Then
strMessage = "Are you sure you want to re-hire and clear pertinent data? If you select ok, your action CAN NOT be undone!"
intOptions = vbQuestion + vbOKCancel
bytChoice = MsgBox(strMessage, intOptions)

If bytChoice = vbCancel Then
LastName.SetFocus
Cancel = True

Else
Cancel = False
stDocName = "MakeNull"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.DeleteObject , acRecord, [EmpID]
End If
End If

 

[jimmythegeek]

You can't delete a few fields from a record using "Delete". The Delete action or method only deletes one or more rows. It sounds to me like you want to delete the records from the EmpAudit table:

CurrentDb.Execute "Delete * from EmpAudit where EmpID = " & Me.EmpID

And then set certain fields in the Emp table to null:

Dim strSQL As String
Dim rs As Recordset

strSQL = "Select * from Emp where EmpID = " & Me.EmpID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Not rs.EOF Then
With rs
!Field1 = Null
!Field2 = Null
!Field3 = Null
.Update
End With
End If
rs.CLOSE
Set rs = Nothing
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jweber]

Thank you for your response. I haven't tried it yet (I only work half days). I will definitely let you know if it works. I thought you could do it programmatically! Thanks so much!

 

[jimmythegeek]

Sorry, forgot the .edit after the With rs:

If Not rs.EOF Then
With rs
.edit
!Field1 = Null
!Field2 = Null
!Field3 = Null
.Update
End With
End If
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jweber]

Hello! I was working on the code, and it doesn't like the
.edit. It tries to tell you to use EditMode, but that doesn't work either it says "Compile error - Invalid use of property".

This is my code as it stands now.

Private Sub Command113_Click()
On Error GoTo Err_Command113_Click

Dim strSQL As String
Dim rs As Recordset
Dim strMessage As String
Dim intOptions As Integer
Dim bytChoice As Byte

If TeamNumber = 99 Then
strMessage = "Are you sure you want to re-hire and clear pertinent data? If you select ok, your action CAN NOT be undone!"
intOptions = vbQuestion + vbOKCancel
bytChoice = MsgBox(strMessage, intOptions)

If bytChoice = vbCancel Then
LastName.SetFocus
Cancel = True

Else
CurrentDb.Execute "Delete * from EmpAudit where EmpID = " & Me.EmpID
strSQL = "Select * from Emp where EmpID = " & Me.EmpID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Not rs.EOF Then
With rs
.EditMode
!Termination = Null
!TeamNumber = Null
!HireDate = Null
.Update
End With
End If
rs.Close
Set rs = Nothing
End If
End If

Exit_Command113_Click:
Exit Sub

Err_Command113_Click:
MsgBox ("Re-hire canceled. No data has been changed.&quot
Resume Exit_Command113_Click

End Sub

 

[jimmythegeek]

Are you using Access 2000? Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jweber]

Yes, I am...

 

[jimmythegeek]

1) You need to make sure you have a reference set to the "Microsoft DAO 3.51 Object Library". If you do not know how to do this, go to the design of any module. Select Tools | References from the menu, find and check the the reference listed.

2) Change this line:
Dim rs As Recordset
To:
Dim rs As DAO.Recordset

Let me know if that works.

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jweber]

Yep! That worked! Thanks...I have some other problem in my code where if I chose to delete the information, it cancels my action. I know it has to be in the if statement somewhere....

Anyways, thanks for your generous help!

 

[jweber]

FORGOT TO POST CODE...

Private Sub Command113_Click()
On Error GoTo Err_Command113_Click

Dim strSQL As String
Dim rs As DAO.Recordset
Dim strMessage As String
Dim intOptions As Integer
Dim bytChoice As Byte

If TeamNumber = 99 Then
strMessage = "Are you sure you want to re-hire and clear pertinent data? If you select ok, your action CAN NOT be undone!"
intOptions = vbQuestion + vbOKCancel
bytChoice = MsgBox(strMessage, intOptions)

If bytChoice = vbCancel Then
LastName.SetFocus
Cancel = True

Else
CurrentDb.Execute "Delete * from EmpAudit where EmpID = " & Me.EmpID
strSQL = "Select * from Emp where EmpID = " & Me.EmpID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Not rs.EOF Then
With rs
.Edit
!Termination = Null
!TeamNumber = Null
!HireDate = Null
.Update
End With
End If
rs.Close
Set rs = Nothing
Cancel = False
End If
End If

Exit_Command113_Click:
Exit Sub

Err_Command113_Click:
MsgBox ("Re-hire canceled. No data has been changed.&quot
Resume Exit_Command113_Click

End Sub