Changing a subform source object with code in Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a form I have a tab control with a subform on it. The subform's linking
child & master fields are set to EquipID and works as advertised. I want to
change the source object of the subform in response to an event of a control
on the form. I do not want the new source object to be linked so I am trying
to clear the linking child and master fields with the following code but it
tells me that the setting I've entered isn't a valid property.

Me.sfHolder.LinkChildFields = ""
Me.sfHolder.LinkMasterFields = ""

And if I don't try to clear these fields I get as Enter Parameter Value
window asking me for the value for tblLine.EquipID (tblLine is the record
source for the new subform source object).

What am I doing wrong?
 
The only time you can change the Link Master and Link Child properties is in
design view or in the Open Event of the form.
An alternative may be to have two subform controls and only one visible.
 
Does the code compile?

Your code as you have is normally needed. It turns out that when you assign
(change) a sub-forms source object (the form it will display), then you ALSO
NEED TO CLEAR the link child and mater fields (some of us consider this a
bug).

If you don't clear the link child/master fields, then if you have a relation
setup, then *often* (can't figure out *exactly* when, or when not), then the
relation between the parent form and child form as defined in relationships
is actually used (even if you don't want that filtering -- thus, you REALLY
need your two lines of code!!!).

so,

First, does your code compile:

Me.frmItineraryView.SourceObject = "frmItineraryView"
Me.frmItineraryView.Form.MyReQuery

Me.frmItineraryView.LinkMasterFields = ""
Me.frmItineraryView.LinkChildFields = ""

I find that if I don't set the linkmaster/child as above, then the form
actually filters based on the relationships window, and I don't want that in
the above.

So, your code looks just fine. I would check if your code compiles, and I
would DOUBLE CHECK the name of the sub-form control (does it come up via
inteli-sense when you type in "me." ?
 
well, VBA Help says you can only set those properties in Design view or on
the Open event of the form. that's incorrect; you can set or change both
Child and Master link properties, as well as the SourceObject property, on
the fly. you can also "un-set" the SourceObject property on the fly - but i
haven't been able to "un-set" the Child and Master link properties on the
fly.

i set up a test with a parent table and two child tables, plus a third table
not related to the parent table, and created a parent form and three
subforms bound to the four tables respectively.

i found that if the subform control on the parent form is unbound when the
form opens, the subform control can be bound to the unrelated subform - with
both Link properties set to "" - from the AfterUpdate event of a control on
the parent form. no error occurs.

the subform control can also be bound to either of the related subforms, at
any time and in any order, with no error.

however, once the subform control is bound to a *related* subform - which of
course sets a value in each Link property of the subform control - then at
that point, any attempt to bind the subform control to the *unrelated*
subform causes a runtime error "2101 - The setting you entered isn't valid
for this property" if the Link properties are set to a zero-length string,
and runtime error "13 - Type mismatch" if the Link properties are set to
Null.

i'm stumped at this point.

hth
 
Albert, see my response to Klatuu's post. as noted, i wasn't able to
successfully bind a subform control to an unrelated subform, once the
control had been bound to a related subform. setting the subform control's
Link properties to a zero-length string resulted in a runtime error, as
detailed in my other post.

i noticed that your posted code includes a reference to "Form.MyReQuery". is
that a custom property of the form object? and is it making the difference
between success and failure?

tia, tina
 
tina said:
Albert, see my response to Klatuu's post. as noted, i wasn't able to
successfully bind a subform control to an unrelated subform, once the
control had been bound to a related subform. setting the subform control's
Link properties to a zero-length string resulted in a runtime error, as
detailed in my other post.

i noticed that your posted code includes a reference to "Form.MyReQuery".
is
that a custom property of the form object? and is it making the difference
between success and failure?
Click to expand...


I don't think that "myRequery" makes the difference (but yes, it is simply a
custom function that *most* of my forms have (the Myrequery will reload the
form, and re-position the record pointer).

I would try removing the link child/master first, and then blank out the
sourceboject, (or change it).
 
I would try removing the link child/master first, and then blank out the
sourceboject, (or change it).
Click to expand...

okay, that helped. after fiddling around a bit with the sequence of actions,
i finally got it to run error-free, regardless of the order in which i
choose a subform to bind to the subform control. for testing purposes, i
simply hard-coded the subform names, and the relevant primary and foreign
key field names, in a three-column combo box control with a Value List for
the RowSourceType. the code follows, as

Private Sub cboSubform_AfterUpdate()

If Len(Me!ChildSub.LinkChildFields) > 0 Then
Me!ChildSub.LinkChildFields = ""
End If

If Len(Me!ChildSub.LinkMasterFields) > 0 Then
Me!ChildSub.LinkMasterFields = ""
End If

Me!ChildSub.SourceObject = ""

Me!ChildSub.SourceObject = Me!cboSubform
Me!ChildSub.LinkChildFields = Nz(Me!cboSubform.Column(1), "")
Me!ChildSub.LinkMasterFields = Nz(Me!cboSubform.Column(2), "")
Me!ChildSub.Form.Requery

End Sub

whew...that was an interesting little exercise. thanks, Albert! :)
 
Me!ChildSub.SourceObject = ""

Me!ChildSub.SourceObject = Me!cboSubform
Me!ChildSub.LinkChildFields = Nz(Me!cboSubform.Column(1), "")
Me!ChildSub.LinkMasterFields = Nz(Me!cboSubform.Column(2), "")
Me!ChildSub.Form.Requery

End Sub

whew...that was an interesting little exercise. thanks, Albert! :)
Click to expand...

That looks real nice, and thanks for sharing that......
 
I hope i can understand everything you have shared. You both obviously
understand the bigger picture better than I do. I'm just on a long term,
self-taught learning curve with VBA for Access.

Thanks,

Del
 
hi Del,
In a form I have a tab control with a subform on it. The subform's linking
child & master fields are set to EquipID and works as advertised. I want to
change the source object of the subform in response to an event of a control
on the form. I do not want the new source object to be linked so I am trying
to clear the linking child and master fields with the following code but it
tells me that the setting I've entered isn't a valid property.

Me.sfHolder.LinkChildFields = ""
Me.sfHolder.LinkMasterFields = ""

And if I don't try to clear these fields I get as Enter Parameter Value
window asking me for the value for tblLine.EquipID (tblLine is the record
source for the new subform source object).

What am I doing wrong?
Click to expand...
Not sure, but i'm using the following method of changing subforms:

FormSetSubForm sfHolder, "NameOfNewSubForm", "", "", False

with

Public Sub FormSetSubForm(ASubForm As Access.SubForm, _
ASourceObject As String, _
ALinkChildFields As String, _
ALinkMasterFields As String, _
Optional ALinkMasterChild As Boolean = True)

On Local Error GoTo LocalError

If ASubForm.SourceObject <> ASourceObject Then _
ASubForm.SourceObject = ASourceObject

If ALinkMasterChild Then
If ASubForm.LinkChildFields <> ALinkChildFields Then _
ASubForm.LinkChildFields = ALinkChildFields
If ASubForm.LinkMasterFields <> ALinkMasterFields Then _
ASubForm.LinkMasterFields = ALinkMasterFields
Else
If ASubForm.LinkChildFields <> "" Then _
ASubForm.LinkChildFields = ""
If ASubForm.LinkMasterFields <> "" Then _
ASubForm.LinkMasterFields = ""
End If

Exit Sub

LocalError:
MsgBox Err.Description

End Sub

mfG
--> stefan <--
 
well, join the crowd, hon. i'm on that same curve, self-taught after taking
a basic (very basic) class to get me started in VBA; i'm perhaps just a bit
further along the curve than you are right now, due in no small measure to
the years i've spent lurking in these newsgroups and learning from the
experts. so just hang in there, Del; the more you read code and use code,
the better you'll understand it and the further you'll push the limits of
your skill in using it. :)
 
Back
Top