Syncronizing Generic Subforms

[PC User]

I have been making use of the concept of subform swapping from a
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

 

[Albert D. Kallal]

A few things:

if the main form has the PersonID, I not sure you need a global var here...

Anway, note that a bookmark is NOT valid between two different reocrdsets
(unless you clone the first one). Also, if you just so much as requery a
recordset, then the bookmarks are invalid.

Somthing like this should do the trick:

Public Function FormSync()

me.ctlGenericSubform.Form.
RecordSet.FindFirst "[PersonID] = " & gintPersonID

End Function

The above should be on one line, but it wraps because of this newsgorup.

Note that if PersonID is the parent "pk" value, then ALL OF THE child
reocrds will have that same PK id if this a child set of reocrds. What you
have to pull from that previous sub-form is the actual pk id of the
**child** table if you want to position the form/reocrd to the same id you
were previous on.

The above code assumes that FromSync is in the parent form. if not, then
replace "me" with your form name.

You could for readability use two lines of code..

Set sfrm = me.ctlGenericSubform.Form
sfrm.Recordset.FindFirst "PersonID = " & gintPersonID