Q: How to open subform to particular record

[John]

(acc2002)

I have a command button (cmdView) on a form (frmOverview)that needs to
open a form (frmFilmProjectAgreements) and its two subforms (control
names: fsubAgreements and fsubPayments) based on parameters on the
original form (frmOverview). The problem is navigating the subforms to
specific records.

Here's my code (frmOverview.cmdView_Click)thus far:

Dim frm1 As Form
Dim frm2 As Form
Dim rst1 As Recordset
Dim rst2 As Recordset

DoCmd.OpenForm "frmFilmProjectAgreements", _
WhereCondition:="[lngFilmprojectID] = " & Me.lngFilmProjectID

Set frm1 = Forms!frmFilmProjectAgreements!fsubAgreements.Form
Set frm2 = Forms!frmFilmProjectAgreements!fsubPayments.Form
Set rst1 = frm1.RecordsetClone
Set rst2 = frm2.RecordsetClone

With rst1
.FindFirst "lngFilmAgreementID = " & Me.txtFilmAgreementID
If .NoMatch Then
MsgBox "agreement not found"
Else
frm1.Bookmark = rst1.Bookmark
End If
End With

With rst2
.FindFirst "lngFilmAgreementPaymentID = " &
Me.lngFilmAgreementPaymentID
If .NoMatch Then
MsgBox "project not found"
Else
frm2.Bookmark = rst2.Bookmark
End If
End With
What happens:
frmFilmProjectAgreements opens to the correct lngFilmProjectID. The
highlighted tips in code "me.txtFilmAgreementID" and
"Me.lngFilmAgreementPaymentID" display the correct IDs, but
fsubPayments does not navigate to the right ID
(lngFilmAgreementPaymentID)...sometimes it even displays "project not
found", even though there is as its tool tip shows.

Any suggestions?
PS: the tool tip thing i keep referring to is the floating tip in VBA
when the code execution is broken.

[Pavel Romashkin]

I think the problem you are having is caused by your using
RecordsetClone for the subforms. Clone recordsets are detached from the
GUI and are used for behind-the-scenes actions. You, in contrast, want
to position your GUIs accordingly. Use Recordset properties:

Set rst1 = frm1.Recordset
Set rst2 = frm2.Recordset

and everything should work fine. When you move inside Form.Recordset,
your subform will navigate too.
As for the ""project not found" cases, step through the code to see why.

Good luck,
Pavel

John wrote:

(acc2002)

I have a command button (cmdView) on a form (frmOverview)that needs to
open a form (frmFilmProjectAgreements) and its two subforms (control
names: fsubAgreements and fsubPayments) based on parameters on the
original form (frmOverview). The problem is navigating the subforms to
specific records.

Here's my code (frmOverview.cmdView_Click)thus far:

Dim frm1 As Form
Dim frm2 As Form
Dim rst1 As Recordset
Dim rst2 As Recordset

DoCmd.OpenForm "frmFilmProjectAgreements", _
WhereCondition:="[lngFilmprojectID] = " & Me.lngFilmProjectID

Set frm1 = Forms!frmFilmProjectAgreements!fsubAgreements.Form
Set frm2 = Forms!frmFilmProjectAgreements!fsubPayments.Form
Set rst1 = frm1.RecordsetClone
Set rst2 = frm2.RecordsetClone

With rst1
.FindFirst "lngFilmAgreementID = " & Me.txtFilmAgreementID
If .NoMatch Then
MsgBox "agreement not found"
Else
frm1.Bookmark = rst1.Bookmark
End If
End With

With rst2
.FindFirst "lngFilmAgreementPaymentID = " &
Me.lngFilmAgreementPaymentID
If .NoMatch Then
MsgBox "project not found"
Else
frm2.Bookmark = rst2.Bookmark
End If
End With

What happens:
frmFilmProjectAgreements opens to the correct lngFilmProjectID. The
highlighted tips in code "me.txtFilmAgreementID" and
"Me.lngFilmAgreementPaymentID" display the correct IDs, but
fsubPayments does not navigate to the right ID
(lngFilmAgreementPaymentID)...sometimes it even displays "project not
found", even though there is as its tool tip shows.

Any suggestions?
PS: the tool tip thing i keep referring to is the floating tip in VBA
when the code execution is broken.