Stop focus from resetting after requery

[goodfu]

I have a main form with a datasheet subform on it. The datasheet has a total of the amounts column in its footer. Due to the fact that the footer is not visible, I have a textbox on the main form which echoes the total so it can be seen. This works fine except that in order to see the current total on the main form when an entry is made in the amounts column I have to requery the total:

Me.Parent.txtAmounttotal.Requery

This is in the After Update event of the amount on the datasheet. The problem is after you type in a new amount, the cursor jumps to the first column of the first record on the data sheet. How can I prevent this? I tried

Dim bm As String
bm = Me.Bookmark
Me.Parent.txtContractamount.Requery
Me.Bookmark = bm

but it didn't work.

 

[MarkK]

Hang on. This problem was recently covered in extreme detail. I'll find the thread...
Check here.

 

[Dairy Farmer]

I have a main form with a datasheet subform on it. The datasheet has a total of the amounts column in its footer. Due to the fact that the footer is not visible, I have a textbox on the main form which echoes the total so it can be seen.

Why have the subform hidden? Rather have it showing and you won't need the extra text box. The totals will update automatically when the requery is run.

Private Sub Field1_AfterUpdate()
    Me.Requery
    DoCmd.GoToControl "Field2"
End Sub

The last field should be

Private Sub Field99_AfterUpdate()
    Me.Requery
End Sub

or

Private Sub Field99_AfterUpdate()
    Me.Requery
    DoCmd.GoToControl "Field1"
End Sub

 

[goodfu]

I've been trying to get this to work. This seems to make sense to me but I get "Compile Error: Invalid Use of Propery" on rs = Me.RecordsetClone

Private Sub amount_AfterUpdate()
Dim bm As String, rs As DAO.Recordset
rs = Me.RecordsetClone
bm = rs.Bookmark
Me.Parent.txtAmount.Requery
rs.Bookmark = bm
End Sub

 

[goodfu]

Sorry, disregard the previous post.

 

[goodfu]

Dairy Farmer, the subform is not hidden. Nothing is hidden on my part but the total in the footer doesn't show. So I put a total on the main form. Everything works except I can't get rid of the jump when the total is recalculated.

 

[vbaInet]

Why do you need to requery? Is the Amounts control in the subform or main form?

 

[goodfu]

It's still not working. This doesn't work:

Private Sub amount_AfterUpdate()
Dim bm As String, rs As DAO.Recordset
Set rs = Me.RecordsetClone
bm = rs.Bookmark
Me.Parent.txtAmount.Requery
rs.Bookmark = bm
End Sub

This doesn't work:

Dim crec As Long
crec = Me.CurrentRecord
Me.Parent.txtAmount.Requery
Me.Move crec

 

[goodfu]

Amounts is in the subform. txtAmounts is the total on the main form. When the form opens, the total of amounts shows in txtAmounts. If an amount is entered or changed on the subform, the total does not update unless it is requeried. I tried recalc and that didn't work.

 

[vbaInet]

On the AfterUpdate of txtAmounts, save the record:

Docmd.runcommand accmdsaverecord

 

[goodfu]

Didn't work.

 

[vbaInet]

I think I know what's going on there. Are you counting using Count(*) ?

Change it to =Count([ID field])

 

[goodfu]

No, I'm using Sum(amount) on the subform.

 

[vbaInet]

Ok. So when you add a new record and move to another record does it update?

In what scenario does it not update? I suspect you mean when you change the value in a field without moving to another record.

 

[goodfu]

I requery the textbox on the main form in the after update event of the amount on the subform. The requery statement causes the cursor to jump up to record 1 column 1. I don't want it to do that. I want it to stay where it is.

 

[vbaInet]

The answer to this is -->

Ok. So when you add a new record and move to another record does it update?

 

[goodfu]

What?

Something very strange. I just made a mock up to post here and the mock up isn't jumping like that.

 

[vbaInet]

Let's still see the mockup. You requery method is just an overkill.

 

[goodfu]

okay, here it is.

 

[goodfu]

I went back and checked again. It is still doing this jump to the first record. It occurs when the main form is opened with openform and a where condition. It does not happen on acNewrec even if you go back and change an amount.