requery to calculate and return? 1

[AppStaff]

Can anyone help me code this feature?

The objects design: Main form with a tab control and multiple tabs. On one tab is a sub form with continous forms type.

Functionality: The user can enter currency values in several fields on the tab specific sub form records. After each value is updated, recalculate an unbound control with an expression that totals all currency values for every record related to that main form record.

Problem: I have this working but unfortunately when I requery on after update the cursor moves to the first record. The user would like to update the total on the main form and then either:
1- goto a new record on the sf if the last field triggered the event
2- go back to the next field in the tab order based on the field and record that triggered the event.


I'm not that experienced with tab references nor with remembering the current record and control and then returning. Any advice and sample code is appreciated!

 

[Trevil]

On the form you are referring to, can the user enter a new record, or only update fields in existing records? If they can add new records, then you will need to save the sort key value of the record being changed to be able to correctly return to the current or next record. If user can only change data, then you can return to the same record by:
intCurrRec = Me.CurrentRecord
xxx.Requery
If intCurrRec > 1 Then
DoCmd.GoToRecord , , , intCurrRec - 1
End If

To recalc some total you could:
Dim Rs as Dao.Recordset
dim dblTotal as Double
dblTotal = 0
set rs = me.recordset.clone
do while not rs.eof
dblTotal = dblTotal + Rs!MyValue
Rs.Movenext
loop
rs.close
set rs = nothing

You would need to have some code to determine which control was updated. You could have a form variable named 'strLastUpdate' then in the 'AfterUpdate' events for the controls you care about, you could save the name. Another option would be to define a 'Control' variable, then set that variable to the control that was just updated.

Your code could also just issue a 'DoCmd.GoToControl xxxx' to go to a random control on your form.

The following thread may be helpful:

http://www.tek-tips.com/viewthread.cfm?qid=849492&page=1

"Hmmm, it worked when I tested it....

 

[AppStaff]

Thanks for your reply. The user can add new records to both the main form and the sub form on the tab page. I'll see if I can get this to work for me. Star awarded for your effort.