Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships (A2k)

    I have two tables:

    tblEstimateDetails
    tblParts

    Both tables have related records:

    EstimateNo
    Supp
    Code

    I have set these as a multi Primary Key using the control key because I don't want duplicates on EstimateNo,Supp,Code.
    Given the fact that they are set up this way, I tried to create a relationship between all three PK,s using cascading update & delete.

    Here's where I am stuck.
    tblEstimateDetails can hold information that is "New", "Repair" etc, where tblParts can only hold information for "New" items.

    If I'm correct, using Cascade Update records should, if a "New" item is enterred into tblEstimateDetails, update tblParts with the "New" record.

    &

    If I'm correct, using Cascade Delete records should, if a "New" item is deleted from tblEstimateDetails, delete the record from tblParts.

    Am I correct ?

    I use some VBA and SQL statements to update tbl Parts which works ok, (without creating a relatiionship)but get errors when relationship is used.
    Here is the code behind the "New" field behind tblEstimateDetails which errors out on the DoCmd.RunCommand.....SaveRecord.

    What Am I doing wrong and would it be easier to create a delete query to delete the record from tblParts if it is deleted from tblEstimateDetails.
    ================================================== =============================================

    Private Sub New_AfterUpdate()
    If Me.New = 0.01 Then
    Me.NewYesNo = True
    Me.New = 0
    Me.id = "N"
    DoCmd.RunCommand acCmdSaveRecord
    Forms!frmEstimateDetails!sbfPartsDetails.Requery
    Forms!frmEstimateDetails!sbfOtherDetails.Requery
    End If
    If Me.New > 0.01 Then
    Me.NewYesNo = True
    End If

    DoCmd.RunCommand acCmdSaveRecord
    Dim strSQL As String
    strSQL = "INSERT INTO tblParts ( EstimateNo, Supp, Code, Item )SELECT tblEstimateDetails.EstimateNo, tblEstimateDetails.Supp, tblEstimateDetails.Code, tblEstimateDetails.Item FROM tblEstimateDetails WHERE (((tblEstimateDetails.NewYesNo)=True));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
    Me.NewYesNo = False
    Me.id = "N"
    DoCmd.RunCommand acCmdSaveRecord
    Forms!frmEstimateDetails!sbfPartsDetails.Requery
    Forms!frmEstimateDetails!sbfOtherDetails.Requery

    End Sub

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2k)

    On this same subject, only a different approach.
    I've tried to delete the record with the following code.

    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    Response = acDataErrContinue ' Display custom dialog box.
    If MsgBox("Delete this record?", vbOKCancel) = vbCancel Then
    Cancel = True
    End If
    End Sub

    Private Sub Form_AfterDelConfirm(Status As Integer)
    Dim strSQL As String
    strSQL = "DELETE tblParts.EstimateNo, tblParts.Supp, tblParts.Code, tblParts.Item FROM tblParts WHERE (((tblParts.EstimateNo)=[tblEstimateDetails]![estimateno]) AND ((tblParts.Supp)=[tblEstimateDetails]![supp]) AND ((tblParts.Code)=[tblEstimateDetails]![code]) AND ((tblParts.Item)=[tblEstimateDetails]![item]));"
    DoCmd.RunSQL strSQL
    End Sub

    If I do not apply the SetWarnings and the AcDataErrContinue suppressors, then the messages I get back are:

    "You are about to Delete 1 record"...............
    "You are about to delete (0) rows"................

    So the Before delete works, but the After Delete Confirm doesn't.

    Can anyone see the problem in my code, why does the record from tblParts not delete ?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Relationships (A2k)

    You wrote:
    >>If I'm correct, using Cascade Update records should, if a "New" item is enterred into tblEstimateDetails, update tblParts with the "New" record.<<


    NO! A cascade update automatically updates the related fields in child records when one of the connecting fields in the parent is changed. It doesn't automatically add records.

    The cascade delete will automatically delete the record in tblParts, provided it is on the "many" side of an enforced relationship with tblEstimateDetails.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2k)

    Thanks Mark.
    This is what I didn't understand.
    Although, to keep things a little simpler, my second post is really the way i would like to go.
    Do you have any idea's as to what the problem may be.?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relationships (A2k)

    Since you have two tables in the delete query, you're going to need the DISTINCTROW keyword in there, or the query will not run.
    Charlotte

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2k)

    Post deleted by D Willett

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2k)

    Post deleted by D Willett

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 23-Sep-03 13:22. Other posts deleted because they were confusing the thread)</P>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
    Else
    ' NO button code...
    Cancel = True
    End If
    End Sub

    Private Sub Form_AfterDelConfirm(Status As Integer)
    Forms!frmEstimateDetails!sbfPartsDetails.Requery
    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
  •