Stop focus from resetting after requery

[boblarson]

Simple change - Don't use REQUERY:

Private Sub amount_AfterUpdate()
   Me.Parent.Recalc
End Sub

end of problem.

 

[goodfu]

That causes the whole record to disappear when you hit the tab key.

 

[boblarson]

That causes the whole record to disappear when you hit the tab key.

Works fine for me in your sample.

 

[boblarson]

What service pack are you on for Office 2007 (I'm assuming 2007 but if it is 2010 let us know).

 

[goodfu]

How do I determine that?

What service pack are you on for Office 2007 (I'm assuming 2007 but if it is 2010 let us know).

 

[boblarson]

How do I determine that?

http://www.btabdevelopment.com/ts/default.aspx?PageId=94

 

[goodfu]

It says SP2. (Access 2007).

 

[boblarson]

It says SP2. (Access 2007).

Same as mine. Don't know why it would clear the form for you when I just simply changed the requery to recalc and it worked fine for me. I made no other changes. It even updates the total for me when tabbing off of the control but leaves the focus on the control until I tab off again.

 

[goodfu]

As I indicated, my own mock up is working just fine although I don't call it from a menu like I do in the other one. And in the other one it only happens when we change existing records, which isn't done that often. So don't worry about it. Maybe in a day or two I will post a stripped down which has the actual problem. Thanks.

 

[Dairy Farmer]

Can I ask why the totals are in a sub form? Is it just because you have the form as a datasheet? You know that you can make a continuous form look just like a datasheet.

I have one situation where my totals are in a sub form in the footer and the main form is a sub form in the detail. Each entry in the main form has upto 3 entries for a date. In the main form I have averages for each coloumn for the entry. These averages are then averaged in the sub form in the footer. I cannot average directly from each entry. It has to be an average of an average.

 

[goodfu]

I not really sure about changing to continuous forms just for this. The mock-up I posted works and it's straightforward enough. For some reason, in my real database there's a jump to record one but only when the amount is modified on a previously-saved record. There must be some way to correct this. I'll post a stripped-down version in a few days.

 

[vbaInet]

As I indicated, my own mock up is working just fine although I don't call it from a menu like I do in the other one. And in the other one it only happens when we change existing records, which isn't done that often. So don't worry about it. Maybe in a day or two I will post a stripped down which has the actual problem. Thanks.

I thought you were going to post the actual problem db?

 

[goodfu]

This is a greatly stripped down database but the 2 forms left definitely show the problem.

Click on one of the lines in the popup which appears when you open the database.

After the form opens, place the cursor on the amount field in the subform on the second line from the top, not the top line.

Backspace out the contents of the field.

Type in a number. Just 1 number will do.

Hit Tab.

The cursor will jump up and to the left.

This will not happen if you remove the requery statement in the after update event of the amount field.

This will not happen if you open the form by itself.

 

[vbaInet]

I don't think you fully understand how the save process works and what you're attempting. For the Sum() function to update the changes made must be committed to the database. The way Access handles committing of records is it commits changes when you move to another record, but what you're doing is forcing it to commit EVERYTIME the amount field is changed regardless of whether the user moves to another record or not. Have you thought about how your engine will cope with multiple users doing this?? Certainly a recipe for disaster.

If you still want to go ahead with your way, then use Me.Refresh

 

[goodfu]

What other way is there? I'm open to ideas.

 

[vbaInet]

To do what exactly?

The Me.Refresh will cause the current record to be saved and the Sum() will get updated.

Maybe in the Before Update event save deduct Old Value of the textbox from the Sum() value and save into a global variable, the After Update event will then add the value in the variable to the new value of the textbox. Convoluted method so you try it out.

 

[goodfu]

Get a total of the amounts and avoid disaster at the same time. I changed it to Me.Refresh and it's working but I don't want a disaster to happen.

Lost focus on the subform?

 

[vbaInet]

Maybe disaster was an exaggeration The point I was making was that what you're doing is constantly querying the db. I'm sure the Engine can handle it nicely but if something can be avoided, it's always best to.

I explained the process in my last post.

Lost Focus - Naturally, when you move to another record or tab out of the subform it will save the record and hence cause the Sum() to update. Also, this doesn't meet your requirement if you think about it.

 

[goodfu]

In the On Exit event of the subform on the main form I put a requery of the total text box and the total figure updates when you go to the next line on the subform or click outside the subform.

 

[vbaInet]

In the On Exit event of the subform on the main form I put a requery of the total text box and the total figure updates when you go to the next line on the subform or click outside the subform.

Yes I know that. I've already seen your db.