We help IT Professionals succeed at work.

After Requery do not reset Tab Order and stay on current record

jacobtdad
jacobtdad asked
on
I am running this code to requery and not reset the tab order but the data resets to the first record in the database.   This code is on a subform field.  The main Form has a unique id linking to subform and then each subform reocrd has a unique value.  I need this to stay on the current main record and subform record

Private Sub Error_Amt_AfterUpdate()
 Dim lngID As Long
   
    lngID = Audit_ID.Value
   
    'force calculations ...
    Me.Parent.Requery

    'navigate subform to the record you were working on ...
    Me.Recordset.FindFirst "[Audit_ID]=" & lngID

    'now reset the focus ....

Me.Parent.FinancialAccuracy = Me.Text100
End Sub
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Have you tried to Refresh the parent? That may or may not work.

If it doesn't, then you'll have to store the ID value of your records, and then find them after your requery, similar to what you're doing in the code above. Depending on where the code is run, it would look something like this:

lngParentID As Long
lngParentID = Me.Parent.IDField
<other code>
<requery>
Me.Parent.Recordset.FindFirst "YourParentID=" & lngParentID
<now find the subform record>

Author

Commented:
LSM,  This works but it resets the subform record to the first one and that field. Is there a way to caputre the Parent ID and the Subform Record Number
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
It looks as if your code is doing that to some degree.

lngID = Audit_ID.Value
'navigate subform to the record you were working on ...
Me.Recordset.FindFirst "[Audit_ID]=" & lngID

The code above should be resetting the subform to the value captured earlier. Be sure, however to find the record in the Parent form FIRST - moving to a new record in a Parent will trigger a refresh of the Subform, so use the FindFirst after finding the Parent record.

Author

Commented:
The last part of this code is now failing.    Me.Parent.FinancialAccuracy = Me.Text100
Text100 is a calcluated field and I need to copy the value to the Parent field  FinancialAccuracy.  I keep getting the value you entered isn't valid for this field .    Can I copy the value from the calculation to a number formated field?    I think it is because the value won't round, it keeps displaying  .7812345685.  
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
What is the Datatype of the field bound to FinancialAccuracy? It would need to be Double or something of that nature.

What keeps displaying  ".7812345685"? We can't see your screen, so you must be very explicit when you post your comments, and be sure to let us know exactly what is going on.

Author

Commented:
The datatype is set to Double.   The code  Me.Parent.FinancialAccuracy = Me.Text100
 will work fine if I trigger from the next field using the Got Focus but if I add this to the end of the After update code for thefield shown below, I Get error message  "The Value you entered isn't valid for this field" and if I click Debug ,it highligts the string above.

Private Sub Error_Amt_AfterUpdate()
 Dim lngParentID As Long
   
    lngParentID = Me.Parent.ID
    IngID = Me.Detail_ID.Value
   
   
    'force calculations ...
    Me.Parent.Requery

    'navigate subform to the record you were working on ...
    Me.Parent.Recordset.FindFirst "[ID]=" & lngParentID
    Me.Recordset.FindFirst "[Detail_ID]=" & IngID

    'now reset the focus ....


Me.Parent.FinancialAccuracy = Me.Text100
 
End Sub
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
So the "Me.Parent.FinancialAccuracy = Me.Text100" works if you trigger from a GotFocus event, but not from an AfterUpdate event?

If so, then I'd suggest you're getting caught in a refresh/requery problem. What is your goal with the statement? Do you want the FinancialAccuracy value to update when the value in Amt is changed?

What is Text100? What value does it contain, and how does it get that value?

When the code errors out is highlighted, can you determine the value of Me.Text100? You can do this most easily by opening the Immediate window and typing

?Me.Text100

YOu'd do this while the code is in Break mode.

Author

Commented:
The field that this code is triggered after upate is a Currency fileld.  Th Text100 field is a calculation field to give an accuracy %.    Once this Text100 is repopulated it needs to post the equated % to the field "FinancialAccuracy"  on the Main form.  The code that captures the "id"s above is to keep the record on the one I am working on, then it requeries to perform the calculation in Text100 and then needs to copy that result.

Author

Commented:
When the error occurs, I can click Debug and it opens the VB with the code Me.Parent.FinancialAccuracy = Me.Text100
Higligted in YELLOW.   I am unclear where to type the ?Me.Text100 you refer to..

Author

Commented:
Instead of Requery can  nI trigger the calculation in  Text100 after update and then copy?  The formula is     =Format(([Total_Correct_Adj_Amt]-[Total_Error_Amt])/[Total_Correct_Adj_Amt],"Percent")  This give me a Percent return that I need to copy using  

Me.Parent.FinancialAccuracy = Text100
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
< I am unclear where to type the ?Me.Text100 you refer to.. >

In the Immediate window. To open that, press Ctrl+G, or click View - Immediate Window.

<Instead of Requery can  nI trigger the calculation in  Text100 after update and then copy?>

You can if that works for your process. I'm have no knowledge of your process or work flow, so cannot comment as to whether this would be a good choice, but if it renders the correct value, using the correct data, then you can certainly use the AfterUpdate.

<The field that this code is triggered after upate is a Currency fileld>

The Currency datatype is accurate to 4 digits to the RIGHT of the decimal. You mentioned earlier " I think it is because the value won't round, it keeps displaying  .7812345685." That's significantly larger than 4 digits to the right, and could be the cause of your troulbes. Try using CCur, which convert the value to Currency:

Me.Parent.FinancialAccuracy = CCur(Text100 )