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?
Code:
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:
Code:
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