Results 1 to 3 of 3

Thread: Delete Query

  1. #1
    Join Date
    Sep 2003

    Unanswered: Delete Query

    I have two tables which have related records ie, 3 fields.
    My form has a tab control with subforms on each of the pages.
    sbfEstimateDetails : tblEstimateDetails
    sbfPartsDetails: tblParts

    There are no joins, or primary keys.

    I need to create a delete query, which when run from sbfEstimateDetails, will delete the related record or row from tblParts.
    I'm not sure whether to use the Before Delete property of the subform or the On Delete propery.

    The related fields are :

    EstimateNo : Number, Long Integer
    Supp : Number , Long Integer
    Code : Text

    Any Idea's on the syntax would be really appreciated.
    Heres what I have so far, which doesn't work.

    Private Sub Form_Delete(Response As Integer)
    Response = acDataErrContinue ' Display custom dialog box.
    If MsgBox("Delete this record?", vbOKCancel) = vbOK Then
    Dim strSQL As String
    strSQL = "Delete tblParts.EstimateNo, tblParts.Supp, tblParts.Code FROM tblParts WHERE (((tblParts.EstimateNo)='forms!sbfEstimateDetails! EstimateNo' And (tblParts.EstimateNo)='forms!sbfEstimateDetails!co de') AND ((tblParts.Supp)='forms!sbfEstimateDetails!supp') AND ((tblParts.Code)='forms!sbfEstimateDetails!code')) ;"
    DoCmd.RunSQL strSQL
    'cancel = True
    End If
    End Sub

  2. #2
    Join Date
    Jul 2003
    Change your sql statement to:

    strSQL = "Delete * FROM tblParts
    WHERE (((tblParts.EstimateNo)='" & forms!sbfEstimateDetails!EstimateNo & "' And ((tblParts.Supp)='" & forms!sbfEstimateDetails!supp & "') AND ((tblParts.Code)='" & forms!sbfEstimateDetails!code & "'));"
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Thanks Red, I had just found a solution as you posted your reply.

    Ok, Here's how I did it:
    Any further advice or comments would be appreciated.

    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    Response = acDataErrContinue
    End Sub

    Private Sub Form_Delete(Cancel As Integer)

    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String

    strMsgPrompt = "You Are About To Delete An Item," & vbCrLf & "This Will Also Be Deleted From Parts," & vbCrLf & " Do You Want To Continue ? "
    strMsgTitle = "Delete"

    intButType = vbYesNo + vbCritical + vbDefaultButton2
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)

    If intButSelected = vbYes Then
    ' YES button code...
    Dim strSQL As String
    strSQL = "DELETE tblParts.EstimateNo, tblParts.Supp, tblParts.Code, * FROM tblParts WHERE (((tblParts.EstimateNo)=[tblEstimateDetails].[estimateno]) AND ((tblParts.Supp)=[tblEstimateDetails].[supp]) AND ((tblParts.Code)=[tblEstimateDetails].[code]));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    ' NO button code...
    Cancel = True
    End If
    End Sub

    Private Sub Form_AfterDelConfirm(Status As Integer)
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts