Results 1 to 11 of 11
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    AfterUpdate Event Not Firing (subform)

    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.
    Last edited by lccrews; 05-17-2018 at 12:40 PM. Reason: New information

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, that minor spelling difference threw me for a second. At first looked like procedure was calling itself. Suggest more distinctive naming.

    Why are you using DoCmd.Save? This does not save record, it saves object. Review https://msdn.microsoft.com/en-us/vba...-method-access
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Hi June. I did not know that. I removed the DoCmd.Save and Access still crashes everytime. I think it has to do with the second after update event, but like I said, I removed the DoCmd.Save.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Confirm where the crash occurs - set a breakpoint and step debug or comment lines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Maybe try this method of executing the queries:
    Code:
    Private Sub Claim_AfterUpdate()
    	Dim sSQL as string
    	DoCmd.SetWarnings False
    
    
    	sSQL = "UPDATE tmp_Formula SET tmp_Formula.[Input] = [Claim]*(1+[Overage])/([Potency]/100)/1000 " _
            	& "WHERE (((tmp_Formula.CUoM)='MCG'));"
    	Currentdb.Execute sSQL, dbFailOnError
    
    
    	sSQL = "UPDATE tmp_Formula SET tmp_Formula.[Input] = ([Claim]/([Potency]/100))*(1+[Overage]) " _
            	& "WHERE (((tmp_Formula.CUoM)='MG'));"
    	Currentdb.Execute sSQL, dbFailOnError
    	
    	sSQL = "UPDATE tmp_Formula SET tmp_Formula.InputWeight = [Input]/DLookUp('[SumOfInput]','qry_CurrentFormula_Pt0'), " _
            	& "tmp_Formula.Quantity = [Input]*[BatchSize]/1000, tmp_Formula.UoM = 'KG';"
    	Currentdb.Execute sSQL, dbFailOnError
    
    
    	sSQL = "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));"
    	Currentdb.Execute sSQL, dbFailOnError
    
    
    	sSQL = "UPDATE tmp_Formula INNER JOIN tbl_RawMaterial ON tmp_Formula.RawMaterial = tbl_RawMaterial.RawMaterial " _
            	& "SET tmp_Formula.BulkCost = [InputWeight]*[tbl_RawMaterial].[Cost];"
    	Currentdb.Execute sSQL, dbFailOnError
    	
    	DoCmd.SetWarnings True
    End Sub

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SetWarnings not needed when using CurrentDb.Execute.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    This may be easier to show than to explain. If you click "Formulation" from the main menu and then "Find Formula" it will bring you to the form in question. [tmp_Formula subform] is where the headache is at. Try changing the claim of an ingredient and pressing tab. You should get the same errors I do (posted below). The idea of this whole measure is to populate fields as the user types. I've tried setting break-points at different steps in the second event, but nothing is failing or acting inappropriately. It just works until you put them together.
    Click image for larger version. 

Name:	1.png 
Views:	14 
Size:	25.6 KB 
ID:	34078Click image for larger version. 

Name:	2.png 
Views:	14 
Size:	27.8 KB 
ID:	34079
    Attached Files Attached Files

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Well, OK. Give this a try:
    Code:
    Private Sub Claim_AfterUpdate()
        Dim sSQL As String
    
    
        sSQL = "UPDATE tmp_Formula SET tmp_Formula.[Input] = [Claim]*(1+[Overage])/([Potency]/100)/1000 " _
                & "WHERE (((tmp_Formula.CUoM)='MCG'));"
        CurrentDb.Execute sSQL, dbFailOnError
        CurrentDb.TableDefs.Refresh
        Me.Refresh
        
        sSQL = "UPDATE tmp_Formula SET tmp_Formula.[Input] = ([Claim]/([Potency]/100))*(1+[Overage]) " _
                & "WHERE (((tmp_Formula.CUoM)='MG'));"
        CurrentDb.Execute sSQL, dbFailOnError
        CurrentDb.TableDefs.Refresh
        Me.Refresh
        
        sSQL = "UPDATE tmp_Formula SET tmp_Formula.InputWeight = [Input]/DLookUp('[SumOfInput]','qry_CurrentFormula_Pt0'), " _
                & "tmp_Formula.Quantity = [Input]*[BatchSize]/1000, tmp_Formula.UoM = 'KG';"
        CurrentDb.Execute sSQL, dbFailOnError
        CurrentDb.TableDefs.Refresh
        Me.Refresh
        
        sSQL = "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));"
        CurrentDb.Execute sSQL, dbFailOnError
        CurrentDb.TableDefs.Refresh
        Me.Refresh
        
        sSQL = "UPDATE tmp_Formula INNER JOIN tbl_RawMaterial ON tmp_Formula.RawMaterial = tbl_RawMaterial.RawMaterial " _
                & "SET tmp_Formula.BulkCost = [InputWeight]*[tbl_RawMaterial].[Cost];"
        CurrentDb.Execute sSQL, dbFailOnError
        CurrentDb.TableDefs.Refresh
        Me.Refresh
        
    'DoCmd.RefreshRecord
        
    End Sub

  9. #9
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I don't believe it. That worked. Why?

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I don't believe it. That worked. Why?
    I think it was because of the complex interactions of the update queries themselves using tables and queries running in sequence.
    The .refreshes forced the results of the preceding update to complete before the next update started.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by davegri View Post
    I think it was because of the complex interactions of the update queries themselves using tables and queries running in sequence.
    The .refreshes forced the results of the preceding update to complete before the next update started.
    If that does explain it, the single line
    Code:
    DoEvents
    Could be used in place of both of these
    Code:
    CurrentDB.TableDefs.Refresh
    Me.Refresh
    Worth trying at least...

    UPDATE.
    Tested. It works for me.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Navigation subform event firing
    By Frank2306 in forum Forms
    Replies: 4
    Last Post: 05-11-2017, 07:10 AM
  2. AfterUpdate not firing
    By GraeagleBill in forum Forms
    Replies: 7
    Last Post: 04-24-2016, 11:42 PM
  3. Replies: 2
    Last Post: 03-26-2014, 07:30 AM
  4. AfterUpdate not firing
    By newvb in forum Forms
    Replies: 4
    Last Post: 09-29-2011, 04:55 AM
  5. AfterUpdate event won't refresh subform!
    By Remster in forum Forms
    Replies: 16
    Last Post: 11-26-2010, 10:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums