Position to record on subform 2

[BitZero]

I have a subform that has a table for the record source that has a key of master_sid_k and budget_year. The master_sid_k links the subform to the main form, and the subform is displayed in Form View (one record at a time). When the main form is displayed, I want the record showing in the subform to be the record where budget_year = 2009. If there is no record for 2009, then display the latest year available. In other words, how can you control what record shows initially in a subform?

 

[Remou]

In this case, you could sort by year descending.

http://lessthandot.com

 

[BitZero]

It's possible that there could be records for 2007, 2008, 2009, 2010 and 2011. How to position to 2009?

 

[Remou]

You can use the recordsetclone to find the relevant record and the bookmark property to move to the record.

Set rs=Me.RecordsetClone
rs.MoveLast
rs.FindPrevious "budget_year<=2009"
If rs.NoMatch Then
    'oops
Else
    Me.Bookmark=rs.Bookmark
End If

Or there aboouts.

http://lessthandot.com

 

[BitZero]

Thanks Remou. Where does this code go? In the on load event for the form used in the subform?

 

[PHV]

In the current event procedure of the main form (typed, untested):
With Me![name of subform control].Form
.OrderBy = "budget_year"
.OrderByOn = True
.Recordset.FindLast "budget_year<=" & Year(Now())
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BitZero]

PHV - thanks - that worked.

Remou - thanks also for your help. I learned about recordsets today.