REcordset Error when added referential integrity and cascading delete. (1 Viewer)

chrisjames25 · Oct 10, 2018

Hi.

I have an invoice form with a invoice detail section and a subform with a list of prodcuts associated to he the invoice. See image of my form.

The subform is not the actual invoice_product details table but in fact a neater query.

The subform is locked and the products are added to the invoice_product details table by cycling through a product details section in the main table and on clicking add product the following code runs

Code:

Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_TempInvoiceDetail")

rs.AddNew
rs.Fields("Variety_ID") = Me.Lst_SearchResults.Column(6)
rs.Fields("Batch_ID") = Me.Lst_SearchResults.Column(0)
rs.Fields("TrayQty_ID") = Me.Lst_SearchResults.Column(7)
rs.Fields("NoOfTrays") = Me.Txt_HeadNoOfTrays
rs.Fields("AdditionalPlants") = Me.Txt_HeadAdditionalPlants
rs.Fields("Invoice_ID") = Me.Txt_Invoice_ID
rs.Fields("TotalPlants") = Me.Txt_HeadTotalPlants

rs.Update
rs.Close
Set rs = Nothing
db.Close

Me.Frm_ChildInvoiceForm.Requery
'Me.Frm_ChildInvoiceForm.SetFocus
'DoCmd.GoToRecord , , acLast
Me.Frm_ChildInvoiceForm.Form.Recordset.MoveLast

This worked great till i started coding my cancel invoice button and changed the relationship between the two tables to enforce cascading deletes. When i now try to enter a new product to an invoice i get the following message:" Run time error 3201 - You cannot add or change a record because a related record is required in table 'tbl_invoices".

Within the form it is clear that the current record has gone from new to populating an auto number in invoice_ID but has it not actually updated that record yet and if not how do i get it to update the record at the start of the above code.

Many thanks for your help on this one.

pbaldy · Oct 10, 2018

Try

If Me.Dirty Then Me.Dirty = False

chrisjames25 · Oct 11, 2018

THanks for the tip.

That worked a treat and now deletes both sets of records as desired when i press the cancel button using following code which was from a macro conversion:

Code:

Private Sub Cmd_Close_Click()
'On Error GoTo cmd_close_Click_Err

On Error Resume Next
 
    If (Not Form.NewRecord) Then
        DoCmd.RunCommand acCmdDeleteRecord
            With Me.Cbo_Customer
            .Value = ""
            .SetFocus
        End With
        Me.Txt_OrderNo.Value = ""
        Me.Txt_OrderDate = ""
        Me.Txt_SearchFor = ""
        Me.Txt_SrchText = ""
        Me.Lst_SearchResults.Requery
        Me.Txt_HeadPlugsPerTray = ""
        Me.Txt_HeadNoOfTrays = ""
        Me.Txt_HeadAdditionalPlants = 0
        Me.Txt_HeadTotalPlants = ""
        Me.Lst_SearchResults = Me.Lst_SearchResults.ItemData(0)
        Me.Lst_SearchResults = Null
    End If
    If (Form.NewRecord And Not Form.Dirty) Then
        Beep
    End If
    If (Form.NewRecord And Form.Dirty) Then
       ' DoCmd.RunCommand acCmdUndo
        Me.Undo
        With Me.Cbo_Customer
            .Value = ""
            .SetFocus
        End With
        Me.Txt_OrderNo.Value = ""
        Me.Txt_OrderDate = ""
        Me.Txt_SearchFor = ""
        Me.Txt_SrchText = ""
        Me.Lst_SearchResults.Requery
        Me.Txt_HeadPlugsPerTray = ""
        Me.Txt_HeadNoOfTrays = ""
        Me.Txt_HeadAdditionalPlants = 0
        Me.Txt_HeadTotalPlants = ""
        Me.Lst_SearchResults = Me.Lst_SearchResults.ItemData(0)
        Me.Lst_SearchResults = Null
        
    End If

End Sub

Problem i am now having is if i then right click the title br to close the form it says No Current Record !.

THis only occurs if i have cancelled the invoice once i have input data into the sub table. ANy idea why?

REcordset Error when added referential integrity and cascading delete. (1 Viewer)

chrisjames25

Registered User.

Attachments

pbaldy

Wino Moderator

chrisjames25

Registered User.

Similar threads

Users who are viewing this thread