On form close, stay on current record on continuous form

[spartansFC]

Hi

I have a continuous form on a unbound main form and a command button that opens up

frmFamilyCentre

to record based off the continuous form.
the subform

frmChildList2

is bound to

qryChildList

the code to open

Private Sub UpdateFamily_cmdbutton_Click()
On Error GoTo Err_UpdateFamily_cmdbutton_lest_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmFamilyCentre"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!frmChildList2.Form!lngPeopleID

Exit_UpdateFamily_cmdbutton_lest_Click:
    Exit Sub

Err_UpdateFamily_cmdbutton_lest_Click:
    MsgBox Err.Description
    Resume Exit_UpdateFamily_cmdbutton_lest_Click

End Sub

the on open event of frmFamilyCentre is

Private Sub Form_Open(Cancel As Integer)
    MsgBox Me.OpenArgs
    If Len(Me.OpenArgs) > 0 Then
        Dim rs As Object
        Set rs = Me.frmFamilyCentreSub1AddNewFamily.Form.Recordset.Clone
        rs.FindFirst "[lngPeopleID]=" & Me.OpenArgs
        If Not rs.EOF Then Me.frmFamilyCentreSub1AddNewFamily.Form.Bookmark = rs.Bookmark
    End If
End Sub

the problem is the frmFamilyCentre only opens up to the first record, i'm not sure why it's not opening up to the correct record as the openargs

MsgBox Me.OpenArgs

is showing the correct lngPeopleID.

Anyone have any ideas

Michael

 

[MajP]

Try converting the arg to long. Clng

 

[spartansFC]

Did you mean to put it here

 rs.FindFirst "[lngPeopleID]=" & (CLng(Me.OpenArgs))

 

[MajP]

Yes. You may also want to move it to the onload event. In the on open event the form is open but no data is loaded, so you may not be able to move to it yet.

 

[spartansFC]

The onload event idea didn't work. I'm still not sure why it's not passing the value over
as when the onload event starts it shows the correct [lngPeopleID] via the MsgBox command.

I am referencing the correct subform with this:

Set rs = Me.frmFamilyCentreSub1AddNewFamily.Form.Recordset.Clone

it's looping through the continuous subform until EOF.

I'm stuck

 

[MajP]

I am not really seeing the problem. You are using recordset.clone instead of recordsetclone which could cause an issue with the BK. Try this, sometimes a rewrite can solve something you do not see.

Private Sub Form_Open(Cancel As Integer)
    dim RS as dao.recordset
    MsgBox Me.OpenArgs
    If Len(Me.OpenArgs) > 0 Then
      msgbox "Length greater than 0"
      Set rs = Me.frmFamilyCentreSub1AddNewFamily.Form.Recordset
      rs.FindFirst "[lngPeopleID]= " & clng(Me.OpenArgs)
    End If
End Sub

 

[spartansFC]

I think we're getting somewhere. The command button on frmChildren opens up frmFamilyCentre which is a bound form to tblFamily, primary key is lngFamilyID,
on this form is the subform frmFamilyCentreSub1AddNewFamily which is bound to tblPeople, primary key = lngPeopleID, foreign key is lngFamilyID,
the master and child links for these 2 forms is lngFamilyID.

if i remove the master/child links and click the command button, it jumps straight to the person i'm trying to find but it then lists every child afterwards in lngPeopleID order,
i understand why this is happening.

so having removed the master/child links how do i link the 2 forms back together

i did have to add an extra line back into your amended code

 If Not RS.EOF Then Me.frmFamilyCentreSub1AddNewFamily.Form.Bookmark = RS.Bookmark

 

[MajP]

Sorry, I just assumed because of what you wanted to do that the subform was not linked to the main form. Yes, this will not work as described if the subform is linked to the main form. But if it is linked why not just move the main form to the correct ID? That is why I was confused. If you move the mainform to the correct ID then the subform naturally follows. Maybe I do not understand what you are trying to do. Also you do not have to add that code back in as you state. It does nothing. If you tell the forms recordset to findfirst it moves automatically.

 

[spartansFC]

The main reason for trying to open the form to the individual person via lngPeopleID is that after i've updated the person's details and close the form,
there is an onclose event, i requery frmChildList and a few other forms
which moves back to the first record on the ChildList whereas i would like it to stay on the same record that i've just updated.

I did originally get the form to open with the main form/subform linked and it did open to the correct family but my problem was the onclose requery part.
this piece of code is what's causing my headache

Forms!frmChildren![frmChildList2].Form.Requery

Sorry for confusing the issue and not explaining properly.

i should have started this post with "how do i open a form to a specific person, after updating it, close it, requery the data but stay on the same record"