Return to record and subform after requery

[matt beamish]

I have been trying to write code to effect the return to the last edited record after a requery of the main form following data update on the sub. I am not making any progress.

I have chased various threads that have examples of similar things but nothing seems to do what I need.

Would any kind person have a look at my database and advise me on what to do?

Thanks

Matt

 

[RuralGuy]

Before the Requery do you save the PK field and then do a FindFirst after the Requery?

 

[matt beamish]

I have been trying to do this, but clearly not getting the syntax correct. This is all a bit new to me, so I am not surprised by me getting it wrong!
Underlying my problems are perhaps the fact that my understanding of VBA is poor and so I am really jumping in at the deep end with little understanding of
what some of the terms and statements mean, copying things from elsewhere and by trial and error finding out what works and what doesn't.


My MainForm is called "F_Daily_Overall_Time_Sheet"
My Subform is called "SF_Daily_Job_Time_Sheet"

My PKfield on the MainForm is called "T_ConHoursRecordID".
My PKfield on the SubForm is called "Daily_Record_ID"

The fields on which the AfterUpdate Event is set on the SubForm are:
"SF_Daily_Job_Time_Sheet.Start_time" and "SF_Daily_Job_Time_Sheet.End_time"

My first problem was prompting a requery of a main form following a data update on a subform.
Despite searching all the forums I could find for code to get this to work, I ended up asking a colleague who gave me the code

Private Sub Start_time_AfterUpdate()
Forms("F_Daily_Overall_Time_Sheet").Requery
End Sub

This works, which none of the other things I tried did, but following the requery displays the most recently entered main form record, and puts the focus on the first field of that form which happens to be the date field. I want the form to return to the record current at the time of the requery, and ideally to put the cursor in the next field according to the TabOrder of the form.

For me to understand the code I need to write, I need to understand the processes. I think they are like this:
1.An event triggers the requery of the main form from the subform.
2.The PK of the subform is captured.
3.The PK of the mainform is captured.
4.The form is requeried
5.The previously current record of the mainform is returned to by returning to the captured PK.
6.The focus then needs to be returned to the subform.
7.The previously current subform record is returned to by returning to the captured PK.
8.The focus within the subform is then returned to the field I want

Is this correct?

Thanks alot

 

[LPurvis]

Here's something for you to try.
Assumes the button click to requery in on the parent form.

On Error Resume Next
 
    Const cParentPK = "YourParentPK"
    Const cChildPK = "YourChildPK"
 
    Dim lngParentID As Long
    Dim lngChildID As Long
    Dim lngTop As Long
 
    lngParentID = Me(cParentPK)
    With Me.sfmChild.Form
        lngChildID = .Recordset.Fields(cChildPK)
        lngTop = .SelTop
    End With
 
    Me.Painting = False
    Me.Requery
    Me.Recordset.FindFirst cParentPK & " = " & lngParentID
    With Me.sfmChild.Form
        .Recordset.MoveLast
        .Recordset.FindFirst cChildPK & " = " & lngChildID
        .SelTop = lngTop
    End With
    Me.Painting = True

 

[RuralGuy]

Matt,
I might ask why do you feel it necessary to requery the MainForm? What control/field on the main form needs to be updated?

 

[matt beamish]

2 sums on 2 other Subforms, a DSum, A DLookup, and an IIf.
Matt

 

[RuralGuy]

Have you tried a Refresh or a Recalc to see if they will do what you want? Neither one moves the record pointer.

 

[matt beamish]

Leigh,
thanks very much; having set this to a control on a text field on the mainform it works.
How can I now set this to operate from an after update on a control on the subform?
Thanks again
Matt

 

[matt beamish]

Have you tried a Refresh or a Recalc to see if they will do what you want? Neither one moves the record pointer.

I had tried the Refresh several days ago, but it did not work. But this may have been because my syntax was wrong rather than the Function would not work!

Perhaps Ill try again!

 

[matt beamish]

on an AfterUpdate on a Subform control

Private Sub Start_time_AfterUpdate()
Forms("F_Daily_Overall_Time_Sheet").Recalc
End Sub

Works like a dream. Thanks RG.

 

[RuralGuy]

That's great! You can also use Me.Parent.Recalc.

 

[cdoyle]

I'm trying to update a txt field on my main form, with the after update of a combo on a subform.

I've tried these in the after update
Forms("formmain").Recalc << Doesn't seem to do anything
Me.Parent.Recalc << Doesn't do anything
Forms("Formmain").Form.Requery <<Requeries the whole form, and takes you off the current record.

I have to be getting close right?
I just want to either update the whole form, but stay on the current record.
Or just update the 1 txtbox on the main form is all I really need.

Thanks,

 

[RuralGuy]

Try:
Me.Parent.ControlYouWantToRecalc.Recalc

 

[cdoyle]

Try:
Me.Parent.ControlYouWantToRecalc.Recalc

This gives me a
Run Time Error'438'
Object doesn't support this property or method'

 

[RuralGuy]

How about:
Me.Parent.ControlYouWantToRecalc.Refresh
or
Me.Parent.ControlYouWantToRecalc.Requery