Getting a subroutine called from AfterUpdate and LostFocus to work

[SCaryCS]

I'm having trouble getting the AfterUpdate and LostFocus events of a text box on a subform to work properly (at least to work as I expect).

The textbox in question is one called Compare_Ratio in the Child_Node_Compare_Datasheet subform within the Nodes_In_This_Project form: [Forms]![Nodes_In_This_Project]![Child_Node_Compare_Datasheet].[Form]![Child_Node_2_SK]. When I update this field, I want to call a subroutine (called UpdtCompareRatioInverse) in the AfterUpdate event of the Compare_Ratio text field. UpdtCompareRatioInverse should update the value in the Compare_Ratio field in a different, but related record with the reciprocal of the just updated value of the Compare_Ratio field.

I expect that AfterUpdate event to be triggered when I change the value in the Compare_Ratio field and then either
1) move the cursor to another field in the same record on the subform,
2) hit ENTER,
3) move the cursor to the Compare_Ratio field in another record of the subform, or
4) click a botton on the parent form (Nodes_In_This_Project) that calls another subroutine that is dependent on UpdtCompareRatioInverse having completed successfully.

I get different results, depending on whether I a) program UpdtCompareRatioInverse to be called from Compare_Ratio_AfterUpdate() or Compare_Ratio_LostFocus() and b) whether I do 1) or 2) above vs 3) or 4). The results are as follows:

When I program UpdtCompareRatioInverse to be called from Sub Compare_Ratio_AfterUpdate() and do either 1) or 2):
-- the AfterUpdate event occurs, but the value of the Compare_Ratio field in the record that I tried to change remains unchanged and
-- UpdtCompareRatioInverse updates the related record with the reciprocal of the unchanged value.

When I program UpdtCompareRatioInverse to be called from Sub Compare_Ratio_AfterUpdate() and do either 3) or 4):
-- the AfterUpdate event occurs, and the value of the Compare_Ratio field in the record that I tried to change remains changes, but
-- UpdtCompareRatioInverse updates the related record with the reciprocal of the previous/unchanged value.

When I program UpdtCompareRatioInverse to be called from Sub Compare_Ratio_LostFocus() and do either 1) or 2):
-- the AfterUpdate event occurs, and the value of the Compare_Ratio field in the record that I tried to change remains changes, but
-- UpdtCompareRatioInverse updates the related record with the reciprocal of the previous/unchanged value.

When I program UpdtCompareRatioInverse to be called from Sub Compare_Ratio_LostFocus() and do either 3) or 4), things work as they should:
-- the AfterUpdate event occurs, and the value of the Compare_Ratio field in the record that I tried to change remains changes, but
-- UpdtCompareRatioInverse updates the related record with the reciprocal of the new value.

Can you help me understand:
1) Why calling UpdtCompareRatioInverse from Compare_Ratio_AfterUpdate() does not work at all, but calling it from Compare_Ratio_LostFocus() does work for 3) or 4).
2) Why doing 1) or 2) causes UpdtCompareRatioInverse not to work (when called from Compare_Ratio_AfterUpdate()), but 3) or 4) do cause UpdtCompareRatioInverse to work under the same circumstances.


The code for UpdtCompareRatioInverse and the two event subroutines is as follows:

Private Sub UpdtCompareRatioInverse()
Dim wrkSpace As Workspace
Dim dBase As Database
Dim RecSet As Recordset
Dim Source_Ratio As Single
Dim intProject_SK, intParent_Node_SK, intChild_Node_1_SK, intChild_Node_2_SK As Integer

On Error GoTo TransactionFailed

intProject_SK = [Forms]![Nodes_In_This_Project]![Child_Node_Compare_Datasheet].[Form]![Project_SK]
intParent_Node_SK = [Forms]![Nodes_In_This_Project]![Child_Node_Compare_Datasheet].[Form]![Parent_Node_SK]
intChild_Node_1_SK = [Forms]![Nodes_In_This_Project]![Child_Node_Compare_Datasheet].[Form]![Child_Node_1_SK]
intChild_Node_2_SK = [Forms]![Nodes_In_This_Project]![Child_Node_Compare_Datasheet].[Form]![Child_Node_2_SK]

Set wrkSpace = DBEngine.Workspaces(0)
Set dBase = wrkSpace.Databases(0)
Set RecSet = dBase.OpenRecordset("SELECT Child_Node_Compare.Compare_Ratio FROM Child_Node_Compare " & _
" WHERE (((Child_Node_Compare.Project_SK)= " & intProject_SK & ") " & _
" AND ((Child_Node_Compare.Parent_Node_SK)= " & intParent_Node_SK & ") " & _
" AND ((Child_Node_Compare.Child_Node_1_SK)= " & intChild_Node_1_SK & ") " & _
" AND ((Child_Node_Compare.Child_Node_2_SK)= " & intChild_Node_2_SK & "))")
Source_Ratio = RecSet![Compare_Ratio]
Source_Ratio = 1 / Source_Ratio

MsgBox "Compare Ratio Inverse: " & Source_Ratio

dBase.Execute "UPDATE Child_Node_Compare SET Child_Node_Compare.Compare_Ratio = " & Source_Ratio & " " & _
"WHERE (((Child_Node_Compare.Project_SK)= " & intProject_SK & ") " & _
"AND ((Child_Node_Compare.Parent_Node_SK)= " & intParent_Node_SK & ") " & _
"AND ((Child_Node_Compare.Child_Node_2_SK)= " & intChild_Node_1_SK & ") " & _
"AND ((Child_Node_Compare.Child_Node_1_SK)= " & intChild_Node_2_SK & "))"


RecSet.Close
Exit Sub

TransactionFailed:
MsgBox "Error #: " & Err & ": " & Error(Err)
wrkSpace.Rollback 'Rollback if any execute fails
Exit Sub
End Sub



Private Sub Compare_Ratio_AfterUpdate()
MsgBox ("AfterUpdate Started")
'UpdtCompareRatioInverse
End Sub


Private Sub Compare_Ratio_LostFocus()
MsgBox ("LostFocus Started ")
UpdtCompareRatioInverse
End Sub

At any given time, I only call UpdtCompareRatioInverse from Compare_Ratio_AfterUpdate() or Compare_Ratio_LostFocus(), but not from both.

Your thoughts?

 

[MajP]

That is a little hard to swallow without seeing what is happening. You did not mention any attempted debugging. If you have not traced the flow, then here are some debugging things that could make understanding what is happening a little easier.

Why not run some different cases and post the different debug statements?

Private Sub UpdtCompareRatioInverse()
  Dim wrkSpace As Workspace
  Dim dBase As Database
  Dim RecSet As Recordset
  Dim Source_Ratio As Single
  dim frm as acces.form
  dim strSql as string
  dim strDebug
  Dim intProject_SK, intParent_Node_SK, intChild_Node_1_SK, intChild_Node_2_SK As Integer
  
  On Error GoTo TransactionFailed
  'Maybe do events if there is a timing issue.
  Doevents 
  set frm = [Forms]![Nodes_In_This_Project]![Child_Node_Compare_Datasheet].[Form] 
  intProject_SK = frm![Project_SK]
  intParent_Node_SK = frm![Parent_Node_SK]
  intChild_Node_1_SK = frm![Child_Node_1_SK]
  intChild_Node_2_SK = frm![Child_Node_2_SK]

  Set wrkSpace = DBEngine.Workspaces(0)
  Set dBase = wrkSpace.Databases(0)
  
  strSql = "SELECT Child_Node_Compare.Compare_Ratio FROM Child_Node_Compare " & _
    " WHERE (((Child_Node_Compare.Project_SK)= " & intProject_SK & ") " & _
    " AND ((Child_Node_Compare.Parent_Node_SK)= " & intParent_Node_SK & ") " & _
    " AND ((Child_Node_Compare.Child_Node_1_SK)= " & intChild_Node_1_SK & ") " & _
    " AND ((Child_Node_Compare.Child_Node_2_SK)= " & intChild_Node_2_SK & "))"
  
  Set RecSet = dBase.OpenRecordset(strSql)
  Source_Ratio = RecSet![Compare_Ratio]
  Source_Ratio = 1 / Source_Ratio
  
  
  strDebug = "Recordset Sql String: " & strSql & " Source Ratio: " & source_Ration
  
  debug.print strDebug
  'msgbox strDebug
  MsgBox "Compare Ratio Inverse: " & Source_Ratio
  
  strSql = "UPDATE Child_Node_Compare SET Child_Node_Compare.Compare_Ratio = " & Source_Ratio & " " & _
      "WHERE (((Child_Node_Compare.Project_SK)= " & intProject_SK & ") " & _
      "AND ((Child_Node_Compare.Parent_Node_SK)= " & intParent_Node_SK & ") " & _
      "AND ((Child_Node_Compare.Child_Node_2_SK)= " & intChild_Node_1_SK & ") " & _
      "AND ((Child_Node_Compare.Child_Node_1_SK)= " & intChild_Node_2_SK & "))"
  
  strDebug = "Update String: " & updateString
  
  debug.print strDebug
  'msgbox strDebug
  dBase.Execute strSql
  

  RecSet.Close
  Exit Sub
  
TransactionFailed:
    MsgBox "Error #: " & Err & ": " & Error(Err)
    wrkSpace.Rollback    'Rollback if any execute fails
  Exit Sub
End Sub

 

[SCaryCS]

MajP,

I implemented your suggested code above with a few enhancements from the LostFocus event of the Compare_Ratio field of the subform.

When I hit ENTER or moved to (clicked on) another field in the same record, the value of Compare_Ratio was not updated in that record by the time that any of the debugging checkpoints fired. Since Compare_Ratio was not updated, this subroutine used the unupdated/previous value for calculating 1 / Compare_Ration and updating that value to the related record.

As before, when I move to (Click on) another record or click on a command button on the main form, Compare_Ratio updates properly and the subroutine works as I had hoped.

I've experimented with different values for Compare_Ratio in different rows of the sub-form with the same results.

I also implemented the time delay loop both with and without the nested For loop, and the results were the same.

Any other thoughts or suggestions? The code that I implemented is below:

Private Sub UpdtCompareRatioInverse()
Dim wrkSpace As Workspace
Dim dBase As Database
Dim RecSet As Recordset
Dim Source_Ratio As Single
Dim frm As Access.Form
Dim strSql As String
Dim DummyVar As Integer
Dim strDebug
Dim intProject_SK, intParent_Node_SK, intChild_Node_1_SK, intChild_Node_2_SK As Integer


On Error GoTo TransactionFailed

'Maybe do events if there is a timing issue.
Dim PauseTime, Start
PauseTime = 2 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
For intCtr = 1 To 1000
DummyVar = 1000
Next intCtr
DoEvents ' Yield to other processes.
Loop


Set frm = [Forms]![Nodes_In_This_Project]![Child_Node_Compare_Datasheet].[Form]
intProject_SK = frm![Project_SK]
intParent_Node_SK = frm![Parent_Node_SK]
intChild_Node_1_SK = frm![Child_Node_1_SK]
intChild_Node_2_SK = frm![Child_Node_2_SK]

Set wrkSpace = DBEngine.Workspaces(0)
Set dBase = wrkSpace.Databases(0)

strSql = "SELECT Child_Node_Compare.Compare_Ratio FROM Child_Node_Compare " & _
" WHERE (((Child_Node_Compare.Project_SK)= " & intProject_SK & ") " & _
" AND ((Child_Node_Compare.Parent_Node_SK)= " & intParent_Node_SK & ") " & _
" AND ((Child_Node_Compare.Child_Node_1_SK)= " & intChild_Node_1_SK & ") " & _
" AND ((Child_Node_Compare.Child_Node_2_SK)= " & intChild_Node_2_SK & "))"

Set RecSet = dBase.OpenRecordset(strSql)
Source_Ratio = RecSet![Compare_Ratio]

'CHECK POINT #1
strDebug = "Recordset Sql String: " & strSql & " Source Ratio: " & Source_Ratio
Debug.Print strDebug
MsgBox strDebug

Source_Ratio = 1 / Source_Ratio

'CHECK POINT #2
strDebug = "Recordset Sql String: " & strSql & " 1/Source Ratio: " & Source_Ratio
Debug.Print strDebug
MsgBox strDebug

'MsgBox "Compare Ratio Inverse: " & Source_Ratio
strSql = "UPDATE Child_Node_Compare SET Child_Node_Compare.Compare_Ratio = " & Source_Ratio & " " & _
"WHERE (((Child_Node_Compare.Project_SK)= " & intProject_SK & ") " & _
"AND ((Child_Node_Compare.Parent_Node_SK)= " & intParent_Node_SK & ") " & _
"AND ((Child_Node_Compare.Child_Node_2_SK)= " & intChild_Node_1_SK & ") " & _
"AND ((Child_Node_Compare.Child_Node_1_SK)= " & intChild_Node_2_SK & "))"

'CHECK POINT #3
strDebug = "Update String: " & strSql
Debug.Print strDebug
MsgBox strDebug
dBase.Execute strSql
RecSet.Close
Exit Sub

TransactionFailed:
MsgBox "Error #: " & Err & ": " & Error(Err)
'wrkSpace.Rollback 'Rollback if any execute fails
Exit Sub
End Sub

 

[MajP]

These timing things are hard to debug without seeing them. It sounds like your order of events is correct so not sure. If you can post a stripped down version (applicable form and tables and code) I could take a look. I use 4shared.com to post to tek-tips, but there are other free sites.

 

[MajP]

So if I understand correctly it works when moving between records, but not when moving within a record.

In the cases where Compare_Ratio was not updated

Are these values correct:
intParent_Node_SK
intChild_Node_1_SK
intChild_Node_2_SK

Does this come out correct:
Source_Ratio = RecSet![Compare_Ratio]

 

[SCaryCS]

MAJ P,

Thanks for sticking with me on this. Developing this Access application is not my day job. My responses to your questions are in CAPS below:

MAJP:So if I understand correctly it works when moving between records, but not when moving within a record. SCARYCS: THAT'S CORRECT. IT WORKS CONSISTENTLY WHEN I MOVE BETWEEN RECORDS ON THE SUBFORM, BUT NOT BETWEEN FIELDS IN THE SAME RECORD.

IT ALSO NOW WORKS ONLY PART OF THE TIME WHEN I CHANGE THE VALUE ON THE SUBFORM AND THEN CLICK IMMEDIATELY ON A COMMAND BUTTON ON THE PARENT FORM.


MAJP:In the cases where Compare_Ratio was not updated, are these values correct:
intParent_Node_SK
intChild_Node_1_SK
intChild_Node_2_SK
SCARYCS:YES

MAJPDoes this come out correct:
Source_Ratio = RecSet![Compare_Ratio]

SCARYCS: NO. IN ALL THE CASES THAT I'VE DESCRIBED, WHEN THE SUBROUTINE AS A WHOLE DOES NOT WORK, THE VALUE OF SOURCE_RATIO IS THE UNUPDATED VALUE.

APPRENT ACCESS IS NOT ACTUALLY UPDATING THE RECORD IN WHICH I'VE MADE THE MANUAL CHANGE TO COMPARE_RATIO BEFORE IT FIRES THE LOSTFOCUS EVENT (FROM WHICH THIS SUBROUTINE IS NOW BEING CALLED). AS I'VE MENTIONED BEFORE, IT ALSO DOES NOT WORK WHEN I CALL THE SUBROUTINE FROM THE AFTERUPDATE EVENT.

IS MY DIAGNOSIS CORRECT? IF NOT, WHAT ELSE MIGHT BE HAPPENING? IF SO, WHY WOULD IT BE HAPPENING?

 

[MajP]

I am guessing but.
Replace:
Set wrkSpace = DBEngine.Workspaces(0)
Set dBase = wrkSpace.Databases(0)
with this
Set dBase = currentDB


Possibly why:

http://www.experts-exchange.com/Mic...kspaces-0-Databases-0-and-an-alternative.html

CurrentDB() is an Access function and is processed by the UI layer, which returns a new reference to the current database. As a result, it's always up to date with current information (i.e. it would include a form that was just added) in its collections. Using dbEngine(0)(0) on the other hand is getting a pointer to the DAO (Data Access Object) reference maintained by JET. In using this reference you are bypassing the UI layer, which means that you won't know if something has been done in the UI unless you do a refresh of a collection. That refresh can be quite expensive in terms of performance

 

[MajP]

If I understand correctly, then the problem is has to be here.
Set RecSet = dBase.OpenRecordset(strSql)

All the values in the select query are correct, but the query returns the old value for compare_ratio.

So the only way that could happen in my mind is that the new compare_Ratio is not committed to the underlying data.

I would add a me.dirty = false somewhere to ensure any change on the form has been committed.

 

[SCaryCS]

MajP,

Thanks very much. When I inserted the "Me.dirty = false" executable statement in the subroutine, it worked in all the scenarios we've discussed. It worked both when I called it from AfterUpdate and LostFocus.

Do you have any thoughts about why the new Compare_Ratio values were not being committed before the AfterUpdate event was fired (as the MS Access documentation indicates that it should?

Thanks again?

 

[MajP]

I am going to have to read up on that one or ask on Tek-Tips, because I would have thought it was not required.

I thought once the afterupdate of the field occurs that the most current value of the control is saved. But there are certain things that happens once the record saves. I just do not know everything that happens. The following discusses some issues that do not occur until the record is saved.

http://msdn.microsoft.com/en-us/library/bb238523(office.12).aspx

I am thinking that this behavior is expected. Do you have a reference that shows otherwise?

Bottom line there are some additional things that happen between the value of the field being committed and the entire record being committed.

Lesson learned is that if modifying a fields data in a record and then retrieving that updated from a recordset, you need to save the record first. Ensuring the form is not dirty will do that.

 

[MajP]

I apologize, I should have been able to provide an answer quicker and not lead you on a wild goose chase. Also I kind of explained things wrong. After thinking about this for a few minutes it became obvious to me.

When you edit a record on a form the record becomes dirty. By definition that means that changes at the form level are buffered and not written to the underlying table. Changes are not written to the table until the record is saved. So the behavior is completely what is expected. There are many ways to save the record such as clicking save or doing it in code. However, the obvious one is when you move to a different record.

 

[SCaryCS]

MajP,

Thanks again very much.

After I added "me.dirty = false" as the first executable line, the subroutine worked as I had intended in all the scenarios that I needed and described to you earlier.

Using CurrentDB did not work, but thanks for giving me another option.