Solved Delete of record if subform is blank, and clearing of contents in subform if change on mainform

[ahmad_rmh]

I am trying to delete the records in subform if the user changes mainwarehouse selection on mainform.

The code is as under

Private Sub MainwarehouseFK_AfterUpdate()

If Not IsNull (Me.frmTransactionsSub!TransactionsDetailPK) then
    MsgBox "Change of warehouse will clear the items, Are you sure you want to continue", vbYesNoCancel

But i don't know how to complete so that I could fulfill my requirement. Kindly suggest.

 

[theDBguy]

You would do something like:

If MsgBox("Are you sure you want to continue?", vbQuestion+vbYesNo,"Confirm") = vbYes Then
    'delete record
End If

 

[ahmad_rmh]

You would do something like:

If MsgBox("Are you sure you want to continue?", vbQuestion+vbYesNo,"Confirm") = vbYes Then
    'delete record
End If

Thanks @theDBguy , how to make code for delete the records.

 

[theDBguy]

Thanks @theDBguy , how to make code for delete the records.

You could use a delete query. For example:

CurrentDb.Execute "DELETE FROM TableName WHERE ID=" & Me.ID, dbFailOnError

 

[LarryE]

Thanks @theDBguy , how to make code for delete the records.

Use the command:
DoCmd.RunCommand acCmdDeleteRecord
to delete the record

 

[Pat Hartman]

What is the business process that you are trying to model? The others have told you how to do what you asked but I don't understand what business event would cause you to have to do this. Are you trying to move items from one warehouse to another? If so, it should be done with transactions so there is a record of the event. You wouldn't just delete the records from "here" and then insert them to "there"

 

[gemma-the-husky]

As @Pat Hartman says, if your users took the time to enter data correctly don't throw it away. Mark it as not required, and/or copy it to where it is required. You need to think about traceability.

 

[ahmad_rmh]

Why the code is not responding properly.

Private Sub MainWarehouseFK_AfterUpdate()
    Me!MainWarehouseFK.RowSource = Me.MainWarehouseFK.RowSource
    Me.Form.Refresh
End Sub

Private Sub MainWarehouseFK_BeforeUpdate(Cancel As Integer)
    Dim delrecord As String
 

If Not IsNull(Me.MainWarehouseFK.OldValue) And Me.MainWarehouseFK.OldValue <> Me.MainWarehouseFK Then
    If Not IsNull(Me.frmTransactionsSub!TransactionsDetailPK) Then
        If MsgBox("Are you sure you want to continue?", vbYesNo) = vbNo Then
            Cancel = True
            Me.MainWarehouseFK.Undo
            Exit Sub
        Else
        If Not IsNull(Me.frmTransactionsSub!TransactionsDetailPK) Then
'        If MsgBox("Are you sure you want to continue?", vbYesNo) = vbYes Then
            DoCmd.SetWarnings False
            delrecord = "DELETE * FROM tblTransactionsDetail WHERE (TransactionsFK= " & Me.TransactionsPK & ")"
            DoCmd.RunSQL delrecord
            DoCmd.SetWarnings True
'        End If
        End If
        End If
    End If
End If

End Sub

Sometimes it works fine with requery but it transfers to 1st record.
ItemsFK is bound with Main Warehouse FK as the selection of Warehouse change it will change the items list. So if the user wants to change the warehouse then the entered items should have to be deleted first.

Which event should have to be triggered to achieve the required results and what are the charges required.

kindly suggest.

 

[ahmad_rmh]

Solved:

I would like to share the code.

Private Sub Form_Unload(Cancel As Integer)
    Dim DeleteRecord As String
    If IsNull(Me.frmTransactionsSub!TransactionsDetailPK) Then
            If MsgBox("You cannot record a blank transaction. Do You want to exit", vbYesNo) = vbYes Then
                DoCmd.SetWarnings False
                DeleteRecord = "DELETE * FROM tblTransactions WHERE (TransactionsPK= " & Me.TransactionsPK & ")"
                DoCmd.RunSQL DeleteRecord
                DoCmd.SetWarnings True
            Else
                Cancel = True
                Me.frmTransactionsSub!ItemsFK.SetFocus
                Exit Sub
            End If
    End If
End Sub

Private Sub MainWarehouseFK_AfterUpdate()
    Me!frmTransactionsSub!ItemsFK.Requery
End Sub

Private Sub MainWarehouseFK_BeforeUpdate(Cancel As Integer)
    Dim delrecord As String
   
    If Not IsNull(Me.MainWarehouseFK.OldValue) And Me.MainWarehouseFK.OldValue <> Me.MainWarehouseFK Then
            If MsgBox("Do you want to change the Warehouse? It will clear the contents.", vbYesNo) = vbNo Then
                Cancel = True
                Me.MainWarehouseFK.Undo
                Exit Sub
            Else
            If Not IsNull(Me.frmTransactionsSub!TransactionsDetailPK) Then
                DoCmd.SetWarnings False
                delrecord = "DELETE * FROM tblTransactionsDetail WHERE (TransactionsFK= " & Me.TransactionsPK & ")"
                DoCmd.RunSQL delrecord
                DoCmd.SetWarnings True
            End If
            End If
    End If
End Sub

Suggest the best options. thanks