After Insert Event does not work - Microsoft Access/VBA

Question

The code worked in MS Access 2003 and stopped working when we upgraded to MS Access 2010. I have a main form with a field paid_in_full_date which i need to set as the current date if the subform where the payment checks are entered inserts a new record.  If i set a breakpoint and step thur it the code below works. But will not work if i don't set the breakpoint and step thru it. The code always does the first DoCmd.OpenQuery and updates the table with the payment amount BUT it will not do the next DoCmd.OpenQuery and update the paid_in_full_date to the system date if the form is run normally, if i set a breakpoint and step thru it works.

Private Sub Form_AfterInsert()

totalpayment (Me!invno) 'totals the payment amount

DoCmd.SetWarnings False

DoCmd.OpenQuery "updatepartial_payment" 'updates table with the payment amount

If Forms!frmupdateinvoice2!Total <= 0 Then

  DoCmd.OpenQuery "qryupdatepaidinfulldate" 'updates table with sysdate

End If

DoCmd.SetWarnings True

End Sub

Answer 1

DJSteele, MVP wrote in

news:*** Email address is removed for privacy ***

m:

In addition to what the others have told you, OpenQuery really

isn't an appropriate way to run action queries.

Instead, try:

Private Sub Form_AfterInsert()

  Dim db As DAO.Database

  totalpayment (Me!invno) 'totals the payment amount

  Set db = CurrentDb

  db.QueryDefs(("updatepartial_payment").Execute dbFailOnError

  If Forms!frmupdateinvoice2!Total <= 0 Then

     db.QueryDefs("qryupdatepaidinfulldate").Execute dbFailOnError

  End If

  Set db = Nothing

End Sub

I"ve never really understood why anyone would execute queries via

the QueryDefs collection, instead of just directly. This is going to

work just fine:

  CurrentDB.Execute("updatepartial_payment", dbFailOnError)

I also don't understand why there is a need in the code above to

assign a database variable. It would be a correct thing to do if the

second use of it were something like this:

  Debug.Print db.OpenRecordset("SELECT @@IDENTITY")(0)

...but there is no necessary dependency between the two uses, so

CurrentDB should be just fine. The queries are going to take a lot

longer to run than it will take to initialize CurrentDB a second

time.

---

David W. Fenton                  http://www.dfenton.com/ contact via website only     http://www.dfenton.com/DFA/

Answer 2

In addition to what the others have told you, OpenQuery really isn't an appropriate way to run action queries.

Instead, try:

Private Sub Form_AfterInsert()

Dim db As DAO.Database

  totalpayment (Me!invno) 'totals the payment amount

 Set db = CurrentDb

 db.QueryDefs(("updatepartial_payment").Execute dbFailOnError

 If Forms!frmupdateinvoice2!Total <= 0 Then

   db.QueryDefs("qryupdatepaidinfulldate").Execute dbFailOnError

 End If

 Set db = Nothing

End Sub

(I do agree with Scott, though, that you should not be storing calculated values...)

 "bird56" wrote in message news:*** Email address is removed for privacy ***...

The code worked in MS Access 2003 and stopped working when we upgraded to MS Access 2010. I have a main form with a field paid_in_full_date which i need to set as the current date if the subform where the payment checks are entered inserts a new record.  If i set a breakpoint and step thur it the code below works. But will not work if i don't set the breakpoint and step thru it. The code always does the first DoCmd.OpenQuery and updates the table with the payment amount BUT it will not do the next DoCmd.OpenQuery and update the paid_in_full_date to the system date if the form is run normally, if i set a breakpoint and step thru it works.

Private Sub Form_AfterInsert()

totalpayment (Me!invno) 'totals the payment amount

DoCmd.SetWarnings False

DoCmd.OpenQuery "updatepartial_payment" 'updates table with the payment amount

If Forms!frmupdateinvoice2!Total <= 0 Then

  DoCmd.OpenQuery "qryupdatepaidinfulldate" 'updates table with sysdate

End If

DoCmd.SetWarnings True

End Sub

---

Doug Steele, Microsoft Access MVP

http://www.AccessMVP.com/djsteele (no e-mails, please!)

Answer 3

It sounds like what you need a pause so the first query will run.  Try putting a...

DoEvents

...between the two.

---

--

Gina Whipp

2010 Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Answer 4

Part of the problem here appears to be that you are storing calculated values. If you have the payments amounts and dates in the transactions no need to store them in the parent table.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries