Intersting situation with two tables

[tekaccess]

Hi to all
I have situation here I need help, if i add a recored in a (history table) sub form (master detail) that record should be deleted from the other table(inventory), and if i delete a record from subform (history)that should be added to other table (inventory).

Any sugguestion?

 

[Remou]

Have you considered an Append query in the After Del Confirm event?

 

[tekaccess]

Hi Remou
Can you explain the after del confirm events.
Yes i used append query.

Thank you

 

[Remou]

The best way to do this is to have only one table with a flag to say whether the record is history or inventory. If that is not possible, forms have an After Del Confirm event, which occurs after the user confirms that they wish to delete. For example:

Private Sub Form_Delete(Cancel As Integer)
'Keep a list of deletions
'First, put them in a temporary file until del confirm

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO TempHistory (ID, Code) " _
    & "SELECT Inventory.ID, Inventory.Code " _
    & "FROM Inventory WHERE Inventory.ID = " & Me!ID
DoCmd.SetWarnings True
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
'See Form_Delete
'After delconfirm, put deletions in a deletion file

DoCmd.SetWarnings False
If Status = acDeleteOK Then
    DoCmd.RunSQL "INSERT INTO History (ID, Code) " _
        & "SELECT TempHistory.ID, TempHistory.Code " _
        & "FROM TempHistory"
End If

'Clear temp file, for next time
DoCmd.RunSQL "DELETE TempHistory.* FROM TempHistory"
DoCmd.SetWarnings True
End Sub