Go to specific row in Subform datasheet

[Todd H]

I have a main form with several subforms, one being a datasheet that
lists monthly billing periods with cost information. I am trying to
write a statement that will start the datasheet at the row that is 3
months prior to the current month so we dont have to scroll down to
get there each time (I currently have a 69 month spread, and we are on
about month 30; the form displays about 15 at one time). I need all
the months to show up, so a filter won't accomplish what I am looking
for.

I have created a SQL statement that returns the BillingPeriodNumber,
and I want the datasheet to 'goto' the corresponding
BillingPeriodNumber in the datasheet. I can't seem to get past the
SQL statement to finish the process, plus I am not sure that I can
have the SQL return an integer value

Here is the code:

Dim intCurPer as integer

intCurPer = SELECT (BillingPeriodNumber)FROM tblBillingPeriod_
WHERE (((tblBillingPeriod.BillingPeriodEndDate) Between_
DateAdd("m",-4,Date()) And DateAdd("m",-3,Date())));

Me!frmPaymentSubform.SetFocus
DoCmd.GoToRecord , , acGoTo,intCurPer

I think I am at the limit of my meager knowledge :-\

 

[Vincent Verheul]

Hi Todd,

The subform has a recordset that you can access. Just use this recorsdset to
position the subform on the record you want. Like this:

<your code to find a value for intCurPer>
Dim SubRS as DAO.Recordset
Set SubRS =Me!frmPaymentSubform.Recordset
Call SubRS.FindFirst("CurPer=" & intCurPer) ' Assuming the field in the
table is called "CurPer"
Set SubRS =Nothing

Do not use SubRS.Close at the end, because it would close the active
recordset of your subform.

Good luck!
Vincent