Using Recalc in Forms 1

[HenriM]

I have Forms which allow Users to amend data in certain controls in subforms; which is then totalled using:
'If me.Dirty Then
M.Recalc
Me.Dirty = False
End If'

This works fine except that the Recalc Method then sends the focus back to the First Control in the First Record. If there are a lot of records this pushes the current record right off the screen and the user has to find it again.


Using
'Me.[ControlName].SetFocus '

sends the focus to the selected control but still in the first Record.

'Me.CurrentRecord.[ControlName].SetFocus' is not recognised.

So for the moment all I can do is either settle for this rogue Focus on the First Record or scrap the Recalc altogether and tell my users to exit the Form and then re-enter it to allow the usual Sum([ControlName]) or a Function do the calculation on re-entry.

Neither choice is particularly satisfactory and I am sure there must be a way, after a Recalc, of resetting the focus to the next control in the current record.

I am equally sure that, somewhere in the Office Help screen I have seen the syntax which does this but can I find it when I want it?..... No!!

I have tried every combination which I can think of without success.

This seems so elementary that I am almost ashamed to ask and when, hopefully, I do get the answer I am sure it will be something blindingly obvious, but right now ...

Can somebody please help??

 

[kewo99]

Try using a Bookmark. Set it before the recalc and then return to it after.
Lookup Bookmark in the help file.

Ken

 

[spikebears]

did you try the GoToControl command?
syntax:
DoCmd.GoToControl "your form name"

 

[HenriM]

kewo99 - Brilliant!!! I wouldn't have thought of this in a thousand years - and it works perfectly.

spikebears - Thank you, I had tried GotoControl and this did have the same effect as Me.[ControlName].SetFocus, but as I said in my request the problem was that it went to the first record in the Recordset whereas I needed to go to the current record.

 

[HenriM]

HenriM

Having now used the Bookmark option successfully for a couple of weeks I thought I would reproduce my code for the benefit of others.

' Dim BM as Variant
Dim rst as Recordset
set rst = Me.RecordsetClone
If rst.RecordCount >0 then
Me.Recalc
Me.Bookmark = BM
Me.(next Control).SetFocus
Else
Me.Recalc
Me.(next Control).SetFocus
End If
It is important to check the RecordCount because if you are inputting the first Record, Bookmark will give you an
ErrMsg, "No Record".
Also, if this is the case, Recalc will default to that Record anyway, so you don't need a Bookmark.

Hope this helps someone.