The following works but it is inefficient. i am close.
This involves a MainForm and a subform. the subform may have 3 or 4 records. two of the fields are Unit Raw Materials - Original and New.
I made a command button which Updates the 4 records for JUST the screen that i am on. The main and sub forms are connected via 3 fields, soldtoID2, shiptoID, formulaID (i call this a 3combo).
I am properly prompted twice to enter the 2 unit costs.
Here is the rub. I am prompted to enter a soldtoID2, shiptoID2 and formulaID.
The form and query do not seem automatically in sync to grab my 3combo code. The first record in the subform gets refreshed fine. Then i have to HIT the "Records, Refresh" from the toolbar. And then the 4 subform records are all fine with new costs.
How can i get this "On Click" vba code to grab the 3combo fields as a match, and then ask me just 2 questions, and then automatically Refresh all 4 subform records?
Here is the Qry and the Command Button On Click that is on my MainForm.
===== MY QRY IN SQL MODE ===============
UPDATE (tblPriceDetail LEFT JOIN tblProduct ON tblPriceDetail.FormulaID = tblProduct.ProductID) LEFT JOIN tblProductClass ON tblProduct.ProductClassID = tblProductClass.ProductClassID SET tblPriceDetail.URawOrig = [Enter URaw Orig for All PLevels], tblPriceDetail.URawNew = [Enter URaw Revised for All PLevels]
WHERE (((tblPriceDetail.SoldtoID2)=[forms]![sfrmCostDetail]![SoldtoId2]) AND ((tblPriceDetail.ShiptoID2)=[forms]![sfrmCostDetail]![ShiptoId2]) AND ((tblPriceDetail.FormulaID)=[forms]![sfrmCostDetail]![FormulaID]));
======================================
=== my ON CLICK stuff inside the button ===============
Private Sub Update_All_Uraws_Click()
On Error GoTo Err_Update_All_Uraws_Click
Dim stDocName As String
stDocName = "QryUpdate UnitRaw by PriceLevel"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Update_All_Uraws_Click:
Exit Sub
Err_Update_All_Uraws_Click:
MsgBox Err.Description
Resume Exit_Update_All_Uraws_Click
Me.Refresh
End Sub
==============================================
maybe this involves a Setfocus kind of command??
Thank you.
This involves a MainForm and a subform. the subform may have 3 or 4 records. two of the fields are Unit Raw Materials - Original and New.
I made a command button which Updates the 4 records for JUST the screen that i am on. The main and sub forms are connected via 3 fields, soldtoID2, shiptoID, formulaID (i call this a 3combo).
I am properly prompted twice to enter the 2 unit costs.
Here is the rub. I am prompted to enter a soldtoID2, shiptoID2 and formulaID.
The form and query do not seem automatically in sync to grab my 3combo code. The first record in the subform gets refreshed fine. Then i have to HIT the "Records, Refresh" from the toolbar. And then the 4 subform records are all fine with new costs.
How can i get this "On Click" vba code to grab the 3combo fields as a match, and then ask me just 2 questions, and then automatically Refresh all 4 subform records?
Here is the Qry and the Command Button On Click that is on my MainForm.
===== MY QRY IN SQL MODE ===============
UPDATE (tblPriceDetail LEFT JOIN tblProduct ON tblPriceDetail.FormulaID = tblProduct.ProductID) LEFT JOIN tblProductClass ON tblProduct.ProductClassID = tblProductClass.ProductClassID SET tblPriceDetail.URawOrig = [Enter URaw Orig for All PLevels], tblPriceDetail.URawNew = [Enter URaw Revised for All PLevels]
WHERE (((tblPriceDetail.SoldtoID2)=[forms]![sfrmCostDetail]![SoldtoId2]) AND ((tblPriceDetail.ShiptoID2)=[forms]![sfrmCostDetail]![ShiptoId2]) AND ((tblPriceDetail.FormulaID)=[forms]![sfrmCostDetail]![FormulaID]));
======================================
=== my ON CLICK stuff inside the button ===============
Private Sub Update_All_Uraws_Click()
On Error GoTo Err_Update_All_Uraws_Click
Dim stDocName As String
stDocName = "QryUpdate UnitRaw by PriceLevel"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Update_All_Uraws_Click:
Exit Sub
Err_Update_All_Uraws_Click:
MsgBox Err.Description
Resume Exit_Update_All_Uraws_Click
Me.Refresh
End Sub
==============================================
maybe this involves a Setfocus kind of command??
Thank you.
