Delete record from subform, but not from table, and clear _fk text

[steve21nj]

I have a form with a subform where I review records via a relationship. The subform records belong to a table [tblPropertyDetails] and the main form table [tblPropertyManifest].

The records are pulled from an after update / dlookup code.

I would like to delete the record from the subform, but not from the table.

Is there a "remove" code instead of a "delete"? If I remove this particular record from the subform, I would need to update and clear out the fk somehow.


I was using this code on my non-review form with no issues because I needed to delete the record at that point. But now, I need to just remove it from the subform but keep it in the table for later use.

Any suggestions or is this possible?

Private Sub Check265_Click()
 
RunCommand acCmdSaveRecord
If MsgBox("Delete selected Property?", vbQuestion + vbYesNo, "Delete Selected Records") = vbNo Then Exit Sub
CurrentDb.Execute "DELETE * FROM tblPropertyDetails WHERE DeleteRow = True"
Forms!frmPropertyTransferReview!SubfrmPropertyTransferReview.Form.Requery
 
End Sub

 

[MarkK]

You need to edit the record in tblPropertyDetails so that the data that links it to tblPropertyManifest is removed or modified. Being more specific about how those records are selected for the subform could help, so maybe you need to elaborate on ...

The records are pulled from an after update / dlookup code.

If the fields that define the link to the parent exist in the subform, you can edit those fields in the subform, and then requery it. You can also execute an update query.
Hope this helps,

 

[steve21nj]

Thank you for the response. I came up with the below as a quick fix. The problem I am facing is that the subform is a continuous form. If I click on the check 265, it removes the top record, not the current selected. Other than that, the code does what I need.

Any suggestions on the ability to remove the current record selected in the subform rather than the top?

Private Sub Check265_Click()
 
RunCommand acCmdSaveRecord
If MsgBox("Remove selected Property?", vbQuestion + vbYesNo, "Remove Selected Records") = vbNo Then Exit Sub
 
Me.Text272 = ""
Me.TurnoverTo = ""
Me.DateOut = ""
Me.PropertyStatus = 2
Me.BagNum.SetFocus
 
Forms!frmPropertyTransferReview1!SubfrmPropertyTransferReview2.Form.Requery
 
End Sub

 

[steve21nj]

All,

I created a screen shot of what is happening.

For this example, my subform has three records. I am attempting to "" the _fk values. When I click to run my code, instead changing the values of the last record, it changes the values of the top record. The code does what I want, just not for the selected record.

What is needed to make sure only the record select is changed?

Also, I am not trying to delete the record, just null the values of the foreign key to make it available again.

Private Sub Check265_Click()
 
If MsgBox("Remove selected Property?", vbQuestion + vbYesNo, "Remove Selected Records") = vbNo Then Exit Sub
 
Me.Text272 = ""
Me.TurnoverTo = ""
Me.DateOut = ""
Me.PropertyStatus = 2
Me.Check265 = ""
Me.BagNum.SetFocus
 
Forms!frmPropertyTransferReview1!SubfrmPropertyTransferReview2.Form.Requery
 
End Sub

 

[MSAccessRookie]

What do you think about adding another Field that starts out as False, but is set to True when the record needs to be removed. The Query could then have a constraint such as "WHERE RecordRemoved=False". Note that once RecordRemoved is True, the record will no longer show up in a Requery.

 

[steve21nj]

I'd like to set the values back to false after the update so i can reuse that record.

One thing I just tried that is working is putting the same code I have above in the On_Current event of the subform as well as the On_Click. It executes the specific actions but it gives me the msg box warning each time I load the form. Is there a way around that?

 

[steve21nj]

My Solution:

Private Sub Check265_Click()
If MsgBox("Remove selected Property?", vbQuestion + vbYesNo, "Remove Selected Records") = vbNo Then
Me.Check265 = ""
Else
If Me.Check265 = -1 Then
Me.Text272 = ""
Me.TurnoverTo = ""
Me.DateOut = ""
Me.PropertyStatus = 2
Me.Check265 = ""
Me.BagNum.SetFocus
Else
    MsgBox ("Nothing to delete")
End If
End If
 
Forms!frmPropertyTransferReview1!SubfrmPropertyTransferReview2.Form.Requery
End Sub