I have a form with a subform datasheet to help users write recipes. There are certain pieces of information that can be pre-filled by a table in the db. I have set after update events for two fields. The events were firing just fine until Access crashed on me and rebooted. Now it will not fire and I'm wondering why. Here is the code that I'm using in the after update event:
Code:
Private Sub RawMaterial_AfterUpdate()
Call RawMarterial_AfterUpdate
End Sub
Sub RawMarterial_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.Save
DoCmd.RunSQL "UPDATE tmp_Formula INNER JOIN tbl_RawMaterial ON tmp_Formula.RawMaterial = tbl_RawMaterial.RawMaterial " _
& "SET tmp_Formula.MiscInfo = [tbl_RawMaterial].[MiscInfo], tmp_Formula.Potency = [tbl_RawMaterial].[Potency], " _
& "tmp_Formula.PUoM = [tbl_RawMaterial].[PUoM], tmp_Formula.CUoM = [tbl_RawMaterial].[ClaimUoM], " _
& "tmp_Formula.Cost = [tbl_RawMaterial].[Cost], tmp_Formula.CostUoM = [Tbl_RawMaterial].[CostUoM];"
DoCmd.SetWarnings True
End Sub
I used the call function because having the SQL directly in the after update event led to previous records being updated but not the record being typed in. Changing it to this method allowed for the event to fire on information being typed in.
Here is the code I'm using on the second field:
Code:
Private Sub Claim_AfterUpdate()DoCmd.SetWarnings False
DoCmd.Save
DoCmd.RunSQL "UPDATE tmp_Formula SET tmp_Formula.[Input] = [Claim]*(1+[Overage])/([Potency]/100)/1000 " _
& "WHERE (((tmp_Formula.CUoM)='MCG'));"
DoCmd.RunSQL "UPDATE tmp_Formula SET tmp_Formula.[Input] = ([Claim]/([Potency]/100))*(1+[Overage]) " _
& "WHERE (((tmp_Formula.CUoM)='MG'));"
DoCmd.Save
DoCmd.RunSQL "UPDATE tmp_Formula SET tmp_Formula.InputWeight = [Input]/DLookUp('[SumOfInput]','qry_CurrentFormula_Pt0'), " _
& "tmp_Formula.Quantity = [Input]*[BatchSize]/1000, tmp_Formula.UoM = 'KG';"
DoCmd.RunSQL "UPDATE tmp_Formula INNER JOIN tbl_RawMaterial ON tmp_Formula.RawMaterial = tbl_RawMaterial.RawMaterial " _
& "SET tmp_Formula.DV = Round(([claim]/[ADV])*'100',2) " _
& "WHERE (((tbl_RawMaterial.ADV) Is Not Null));"
DoCmd.RunSQL "UPDATE tmp_Formula INNER JOIN tbl_RawMaterial ON tmp_Formula.RawMaterial = tbl_RawMaterial.RawMaterial " _
& "SET tmp_Formula.BulkCost = [InputWeight]*[tbl_RawMaterial].[Cost];"
DoCmd.SetWarnings True
End Sub
Update: I've noticed that the phrase "[Event Procedure]" that usually shows in the properties section of the event is cleared. I reassociated it with the VBA I wrote for the second event (Claim_AfterUpdate()). I tried to fire the event again and Access crashed. I have tried doing this 3x and it has crashed every time.