Parent Item / Child Item Not Saving Correctly Together

[Evilferret]

I have two tables called Shipping and ShippingParts, with a one-to-many relationship. (One shipping entry to many shippingparts entries)



My main form allows for data input for Shipping, with ShippingParts as a subform.

On the main form I have a Save Button (Code below). When the save button is clicked, it should save the data for the Shipping record into the Shipping table. And the ShippingParts records associated with that record into a temp table. It should then build the ShippingParts records from the records in the temp table. Instead it's saving the Shipping record, but the ShippingParts records are being associated with the previously entered Shipping record. For instance, if my current shipping record has ShippingID 100, the ShippingParts entries are being associated with ShippingID 99.

Any ideas on why this is occuring?

Private Sub AddNewShipping_Click()

    ' Save the Temp_Shipping data, and the Shipping data
    If Forms(callingfrm).Dirty Then
        Forms(callingfrm).Dirty = False
    End If
    
    ' Create records in ShippingParts from Temp_Shipping data
    CurrentDb.Execute "INSERT INTO ShippingParts (ShipmentID, PartID, PartQuantity, PartState) SELECT (SELECT TOP 1 ShipmentID FROM Shipping ORDER BY ShipmentID DESC), Temp_Shipping.PartID, Temp_Shipping.Quantity, iif ([Anodized] = Yes, 'Anodized', 'Raw') FROM Temp_Shipping WHERE Temp_Shipping.Quantity>0;"

    ' Check if print checkbox is checked
    If CheckPrint.Value = True Then
    ' Print 2 packing slips
        DoCmd.OpenReport "PackingSlip", acViewNormal, , "[Shipping.ShipmentID] = " & Me.[ShipmentID]
        DoCmd.OpenReport "PackingSlip", acViewNormal, , "[Shipping.ShipmentID] = " & Me.[ShipmentID]
    End If
    
    ' Close the form
    DoCmd.Close acForm, "AddShippingEntry", acSaveNo

End Sub

EDIT: It seems like it works fine if I use the main form first followed by the subform. If I enter data into the subform first, then input data into the main form, I encounter the save glitch... Somehow the .Dirty save for the main form isn't taking effect until after the SQL query has executed.

EDIT2: The problem is only occurring when a combobox on the main form is changed after the quantities are entered in the subform. Here's the code behind the combobox:

Private Sub ShipmentTypeCombo_Change()
    
    If ShipmentTypeCombo.Value = "To Hupe" Or ShipmentTypeCombo.Value = "To Kam Valley" Then
    ' The parts are being sent for anodizing - Default to not anodized.
    CurrentDb.Execute "UPDATE Temp_Shipping SET Temp_Shipping.Anodized=No;"
    Else
    ' The parts are being sent somewhere else - Default to the standard for the part.
    CurrentDb.Execute "UPDATE Temp_Shipping INNER JOIN Parts ON Parts.PartID=Temp_Shipping.PartID SET Temp_Shipping.Anodized=Parts.GetsAnodized;"
    End If

    If ShipmentTypeCombo.Value Like "To*" Then
        ' We're sending a shipment out - Print packing slips
        CheckPrint.Value = True
    Else
        ' We're receiving a shipment - Don't print packing slips
        CheckPrint.Value = False
    End If

    'Refresh the parts list
    Me!AddShippingEntry_PartsList.Requery
End Sub

[June7]

Are you using bound forms?

Yes, there should be a record on main form before entering records into subform, if Master/Child links properties are set or table relationships established to enforce this.

Want to provide db for analysis? Follow instructions at bottom of my post.

[Evilferret]

Are you using bound forms?

Yes, there should be a record on main form before entering records into subform, if Master/Child links properties are set or table relationships established to enforce this.

Want to provide db for analysis? Follow instructions at bottom of my post.

The child form is bound to the temp table. The parent form is bound to shipping and opens in data entry mode.

Here's the database (With lots of info removed).

The form I'm working on is called EditShipping. The subform is EditShippingList. The form/subform work correctly when the combobox on the form is filled out prior to the textboxes on the subform. They do not work correctly if the subform textboxes are filled out first and then the combobox on the main form is filled out.

InventoryDatabase.zip

[Missinglinq]

...They do not work correctly if the subform textboxes are filled out first and then the combobox on the main form is filled out...

As June7 said:

...there should be a record on main form before entering records into subform

Simply put, you don't create Child Records before Parent Records!

Linq ;0)>

[Evilferret]

As June7 said:
Simply put, you don't create Child Records before Parent Records!
Linq ;0)>

The child records are not associated with a parent record until after the save button is clicked (By which point the parent record should already exist thanks to the .Dirty save).

The temp table does not contain any reference to the parent item. Instead, records are built from the temp table once the save button is clicked.

[Evilferret]

Everything was correct as far as the logic of the form was concerned.

The trouble was I was using this code for some reason:

    If Forms(callingfrm).Dirty Then         
      Forms(callingfrm).Dirty = False     
End If

Access didn't catch it as an error, but Forms(Callingfrm) isn't a valid reference to anything.
So in cases where the main form was dirty, nothing was happening at all in terms of saving the parent record until the form was actually closed down.

I corrected my code to read:

    If Me.Dirty Then
        Me.Dirty = False
    End If

Thankyou everyone for your suggestions/help

[June7]

Glad you figured it out. Was just about to take a closer look at your code. I suspect you found the Dirty code from an example that was designed to be a generic procedure that could be called from other procedures. 'callingform' is a variable that would be used as an argument of the generic procedure. Since you placed the Dirty code within a procedure specific to a form it necessarily had to be modified.

This might have been detected much sooner if you used Option Explicit in the header section of code module. That line would require variables to be declared with Dim statement. The Compile would have errored because the variable was not declared. Also, step debug would have shown that the variable was not populated. Review link at bottom of my post for debugging techniques.