Generic Subforms: Syncronize but not Filter

[PC User]

I have a form with a subform control that can change subforms. I have a filter on the main form and I've managed to find a way to change the record sources for any active subform. However, I need to syncronize them whether or not they are filtered. I've tried to use the LinkChildFields and LinkMasterFields properties with the PrimaryKey (PK) field; however, this only displays the field associated with the PK.

As I flip from one subform to another, I need it to stay with the current PK, but still be able to navigate through the recordset. If I'm on any one subform and go to the next record, I need the next subform to use the next PK value and show the next record also. The filter that I'm using is not on the PK field, its on a different field.

Possibly there's a way to use bookmarks. Does anyone know how I can apply this to subforms to syncronize them?

Thanks,

PC

 

[PC User]

Syncronize Generic Subforms

I have been making use of the concept of subform swapping from the Subform Swapping Demo; however, now I've come across a problem of syncronizing the subforms. I've been able to use a filter combobox on the main form and control the results in the subforms by changing the RecordSource. However, now when I flip from one subform to the next, I need the next appearing subform to show the same record (or Primary Key [PK]) that was on the previous subform and still be able to navigate through the entire recordset if I choose. So I thought that using bookmarks would be the answer.

I'm using the PK (PersonID) which is an Autonumber field with a "Long" property in a global variable "Public gintPersonID As Long". I've been trying to use a public function bookmark code in that I'm thinking that I can match the PK of the table's recordset with the PK of the subform's recordclone. Is this something that can be done? Currently the problem is that I'm getting an error "Error #13: Type Mismatch".

Public Function FormSync()
    On Error GoTo Whoops

    Dim rs As DAO.Recordset, frs As DAO.Recordset
    Dim frm As Form, sfrm As Form
    Dim intCriteria As Integer
    Dim db As DAO.Database

    Set frm = Forms!frmMain
    Set sfrm = frm!ctlGenericSubform.Form
    Set frs = sfrm.RecordsetClone
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblPeople", dbOpenDynaset)
        
    intCriteria = "[PersonID] = " & gintPersonID
    
    If IsNull(gintPersonID) Then
        MsgBox "No PersonID found"
    Else
        rs.FindFirst intCriteria
        frs.Bookmark = rs.Bookmark
    End If

rs.Close
frs.Close
db.Close
    
OffRamp:
    Exit Function
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    Resume OffRamp
End Function

Thanks,
PC

 

[PC User]

Ok, for those who are interested I've finally solved the problem and I am sharing it for you. I've made a public function that I call to do the bookmarking.

=====================
Public Function SubformSync()
On Error GoTo Whoops

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, sfrm As Form
Set frm = Forms!frmMain
Set sfrm = frm!ctlGenericSubform.Form
Set db = CurrentDb
sfrm.FilterOn = False
Set rst = sfrm.RecordsetClone

If gvarPersonID > 0 Then
rst.FindFirst "[PersonID] = " & gvarPersonID
sfrm.Bookmark = rst.Bookmark
Else
Exit Function
End If
rst.Close

OffRamp:
Exit Function
Whoops:
MsgBox "Error #" & Err & ": " & Err.Description
Resume OffRamp
End Function
=====================

I call the function from the main form when I do the switching of the subforms.

=====================
Forms("frmMAIN")!ctlGenericSubform.SourceObject = "fsubMember"
Forms("frmMAIN")!ctlGenericSubform.RecordSource = gstrFormSQL
Call SubformSync
=====================

Thanks to those who helped me and good luck to those who use my solution.

PC

 

[gemma-the-husky]

i general do stuff like this by using the main form to set a global variable

the sub form then includes (dereferences) this variable in its source query

so in the main form after you change variables etc all you need is something like

gblvar - whatever
subdetails.requery

even
subdetails!sourceobject = "something else", where you are using a different subform in the main form container.