Master/Child Link

[PC User]

Is there an alternate method to link a subform to the main form other
than using the Master/Child properties in the subform control?

Thanks,

PC

 

[Douglas J. Steele]

You could play with the Filter property of the subform.

Remember that to do this, you need to go through the subform control on the
parent form. Depending on how you added the form as a subform, the name of
the subform control may be different than the name of the form being used as
a subform.

From the parent form, you'd use

Me!NameOfSubformControl.Form.Filter = "...."
Me!NameOfSubformControl.Form.FilterOn = True

From elsewhere, you'd use

Forms!NameOfParentForm!NameOfSubformControl.Form.Filter = "...."
Forms!NameOfParentForm!NameOfSubformControl.Form.FilterOn = True

 

[PC User]

Thanks Doug,

I'm using the generic subform concept where various
subforms can be displayed on request by the user. Each subform will
essentially be using the same recordsource table, but each subform has
a different purpose and way of displaying the data. I've tried to
normalize the data where some of the subforms have subsubforms that
are linked to other tables using a primary key (PK).

Essentially, 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?

Thanks,

PC

 

[PC User]

Trying once again with a private function on each subform activated
with the "On Load" event. This gives no response, but it seems that
there may be something wrong with the code. Can someone help me with
this?

--------------------------------------------------------------------------------

Private Function SyncMe()
Dim rs As DAO.Recordset
Set rs = Me.Parent.ctlGenericSubform.Form.RecordsetClone
rs.FindFirst "PersonID = " & glngPersonID
'Me.Parent.ctlGenericSubform.Form.Bookmark = rs.Bookmark
Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Function

 

[James A. Fortune]

Is there an alternate method to link a subform to the main form other
than using the Master/Child properties in the subform control?

Thanks,

PC

If you don't mind a little bloat you can set the subform's RecordSource
dynamically in the main form's OnCurrent event. The bloat should be
minor in your case if the subform's RecordSource will only be pulling in
one record (or a few) at a time. This method works even in some unusual
cases where the Link Child/Master Fields property does not. It might
look something like:

Private Sub Form_Current()
SubformToMain.Form.RecordSource = "SELECT * FROM tblPeople WHERE
PersonID = " & txtPersonID.Value & ";"
End Sub

Here SubformToMain is the name of the subform control on the main form
and frmSubformMain is the SourceObject for the Subform control. Then if
frmSubformMain has a subform you can use the OnCurrent event of
frmSubformMain to set the RecordSource of its subform. Changing the
RecordSource of the subform in frmMain should also cause the subform's
OnCurrent event to fire and thereby bring the sub-subform to the first
record of an appropriate set. Once the correct datasets are in all the
subforms, you can still use RecordsetClone with the Bookmark property to
navigate to a particular record.

James A. Fortune
(e-mail address removed)

Christmas is about forgiveness.

 

[PC User]

James,

I do appreciate your response. I don't think many programmers
use generic subforms; so any suggestion is welcome. I do use a change
of recordsource based on a combobox filter on the main form and the
subform shows only those records that the filter permits. Your
suggestion goes a step further in that the subform will only show the
specific record that meets the criteria for the ID. The reason I'm
headed in the direction of using bookmarks is that once the filtered
subform opens, I want it to start at a specific record and not be
limited to a specific record. I still want the flexiblility to page
through the filtered recordset and then move back to the other subform
with the same bookmark ability. At first I though that the Master/
Child subform links would do this, but they don't.

Thanks much,
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