VBA AfterUpdate to trigger saved Query requires additional record input before update

[Alhassani]

Good morning!

I have several areas of the database that affect this quetion. The two tables in question are "Audit_Db" and "Audit_Scores", there are two queries called "Grades_Qry" and "Update_Grades_Qry" that are triggered using VBA in the "Audit_Input" form.

The Audit_Input form stores information in the "Audit_Db" table. Anytime an entry is placed in the "DCN" text box on the Audit_Input form, the "Grades_Qry" is ran to add the value of the "[PCH: Sponsor SSN]" combo box "Audit_Scores" table. All combo boxes are auto-filled to pass, so when "Grades_Qry" is ran, all fields added to "Audit_Scores" are set to pass. (THIS WORKS) As the user changes values from "Pass" to whichever defect code is applicable, an AfterUpdate event triggers the "Update_Grades_Qry". This query then puts the correct, and updated value into the appropriate record in "Audit_Scores" table. (THIS WORKS)

This all works almost perfectly. The only issue is that it requires that an additional record be put in for everything to trigger. For example, if there are 13 records in the database and I start a new record on number 14, it is not until after I complete record 15 that record 14 would be added. Then, upon completion of record 16, record 15 would be added and correct. The intention is for this update to apply automatically and not be dependant upon the entry of an additional record to be accurate. Here is the code for each event.

DCN AfterUpdate event code

Private Sub DCN_AfterUpdate()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Grades_Qry"
    DoCmd.SetWarnings True
    
End Sub

ComboBox AfterUpdate event code. I have both the SQL syntax and the DoCmd code saved mainly for reference. (Debug.print also does nothing. )

Private Sub PCH__Sponsor_SSN_AfterUpdate()
Dim strSQL As String
Dim db As Database

Debug.Print strSQL

strSQL = _
    "UPDATE Audit_Db RIGHT JOIN Audit_Scores ON " & _
    "Audit_Db.DCN = Audit_Scores.DCN SET Audit_Scores.Field_Result = Audit_Db.[PCH: Sponsor SSN]"

Set db = CurrentDb
db.Execute (strSQL)

'    DoCmd.SetWarnings False
'    DoCmd.OpenQuery "Update_Grades_Qry"
'    DoCmd.SetWarnings True
    
End Sub

While this is a relatively minor error that I could theoretically live with if I have to, I would prefer not to. The database is attached. Reference the threads below for the full-back story on this database if you are curious. Thanks!

QualityDb.zip

https://www.accessforums.net/program...sql-44214.html
https://www.accessforums.net/access/...gue-44029.html

[pbaldy]

For starters, the Debug does nothing because you have it before the string is built. It is correctly printing the empty string that strSQL is at that point.

As to the problem at hand, would it make sense to use the after update event of the form instead of a control? The record isn't yet saved when you use the after update event of a control on a new record.

[Alhassani]

That makes sense. I'll adjust the position of the debug code.

Also, there was some discussion before as to how to correctly handle the placement of the afterupdate procedure. We generally agreed that placing it on the comboboxes meant for less processing needed to be done in comparison to placing it in a OnSave or OnUnload command due to there having to be 55 copies of that code ran everytime. After reading your point, it may actually make more sense to set it to OnSave (which I know used to exist, but I cannot find in 2010).

[pbaldy]

I didn't review the other threads, but if having it behind the combo is appropriate, you can force the record to save by adding:

If Me.Dirty Then Me.Dirty = False

before running the query/SQL.

[Alhassani]

Ok, I'll give that a try. What does it do?

[pbaldy]

Other than this?

force the record to save

[Alhassani]

I meant more on a technical level. How does it force the record to save?

[Alhassani]

Well, it works on the first set. It still isn't forcing the update on changes in the second set. I tried putting that code into the form's OnDirty command, but it didn't fix it either.

EDIT: Applied a macro to the save button that calls the query and it works now. Not the ideal solution, but it works and thats what matters. Thanks for the help pbaldy!

[pbaldy]

Happy to help!