Scroll to record in continuous subform

[JB]

This code is running in the Form_Load event of my mainform. It should
select the first subform record that is greater than or equal to
today's date. The subform is read only and all controls are locked.
lngCount gets set to 4 which is correct in my test example but the
record does not scoll into view inside the subform. The subform
contains five records and is large enough to view two at a time. This
code is an adapted sample so I am wondering if I am missing something?


Dim lngCount As Long
Dim rs As DAO.Recordset

Set rs = Me.frmReservationListDetail.Form.RecordsetClone
rs.MoveFirst
rs.FindFirst "[Loan_Date] >= Date()"
If rs.NoMatch Then
DoCmd.GoToRecord , , acLast
Else
lngCount = rs.AbsolutePosition
DoCmd.GoToRecord , , lngCount
End If

 

[Albert D. Kallal]

Set rs = Me.frmReservationListDetail.Form.RecordsetClone

Don't use a "clone" of the reocrdset...use the actual reocrdset...

eg:

 

[JB]

Thank you for the response.

With your line of code or mine, I get a "Run Time Error 2455 - You
entered an expression with an invalid reference to the Form/Report."

I do not know how to trace the problem. It fails on the Set rs =
Me.frmReservationListDetail.Form.RecordsetClone line or the Set rs =
Me.frmReservationListDetail.Form.Recordset line.

My code sample that I submitted had been working before but now I can
not get past the error. I don't know why it started since I did not
change anything. What else should I be looking at? The recordset
declaration does not seem to be occurring and then nothing will work
now because of that.

 

[Albert D. Kallal]

This could very well be a dao reference problem.


You can go:

dim rst as dao.RecordSet


set rst = me.FrmRes.Form.Recorset

now, just sue rst for the moving...

If you move rst record pointer, then the form will mvoe with it...

However, we might as well use recordsetClone...

The reason why recordsetclone don't move, is because a clone can be used and
moved independently of the form. However, their bookmarks are =, so:

Dim lngCount As Long
Dim rs As DAO.Recordset

Set rs = Me.frmReservationListDetail.Form.RecordsetClone
rs.MoveFirst
rs.FindFirst "[Loan_Date] >= Date()"
If rs.NoMatch Then
me.frmReservationList.bookMark =
me.frmReservationlistDetails.Form.ReocrdSetClone.Bookmark.
Else
lngCount = rs.AbsolutePosition
DoCmd.GoToRecord , , lngCount
End If

 

[JB]

Albert:

I appreciate your time on this matter. I tried to add your changes but
the Bookmark line would not compile as typed. I adjusted it to be
accepted by Access while trying to meet your intended idea. Below is
the current updated sample of code.

The form will load without error but the triangle record indicator on
the subform is on the first record and should be on the fifth. If I
trace the code in debug mode, I still get an error 2455 as previously
mentioned on the Set rs statement. Since no recordset declaration is
made, none of the other code will work at that point. As I also
mentioned prior, I used this exact code before and the set rs line
worked fine when traced so I don't understand why the error is
happening now. When I originally submitted this post, it was only to
resolve the GoTo record issue at the end of the code.

Set rs = Me.frmReservationListDetail.Form.RecordsetClone

If Not rs.EOF Then
rs.MoveFirst
rs.FindFirst "[Loan_Date] >= Date()"
If rs.NoMatch Then
Me.frmReservationListDetail.Form.Bookmark =
Me.frmReservationListDetail.Form.RecordsetClone.Bookmark
Else
lngCount = rs.AbsolutePosition
DoCmd.GoToRecord , , lngCount
End If
End If

 

[Albert D. Kallal]

Set rs = Me.frmReservationListDetail.Form.RecordsetClone

if we going to use the above rs...then we want to use the bookmark from
above..

hence:

Set rs = Me.frmReservationListDetail.Form.RecordsetClone

If Not rs.EOF Then
rs.MoveFirst
rs.FindFirst "[Loan_Date] >= Date()"
If rs.NoMatch Then
Me.frmReservationListDetail.Form.Bookmark
= rs.bookMark
Else
lngCount = rs.AbsolutePosition
Me.frmReservationListDetail.Form.
RecordSet.AbsolutePosition = lngCount
End If
End If

The above should work....

Both of the "me" above are wrapped becuase of the newsreader..and should be
on one line..

 

[Albert D. Kallal]

Also, your findfirst looks wrong..

rs.FindFirst "[Loan_Date] >= Date()"

The above should be:

rst.findFirst "Loan_Date] >= #" & format(date(),"mm/dd/yyyy") & "#"


eg:

rs.FindFirst "[Loan_Date] >= Zoo"

Is teh above to check loan date > zoo, or a variable/expression called zoo??

You need:

rs.FindFirst "[Loan_Date] >= " & date()

However, due to your date/time settings, teh above might cause problems..and
you as general rule need to cast the date format into mm/dd/yyyy

So, that is what I done with:

rst.findFirst "Loan_Date] >= #" & format(date(),"mm/dd/yyyy") & "#"