Changing subform .sourceobject

A

Amy Blankenship

I am working with a Form with a subform. The parent form refers to a query
that selects all AuditToolGroups from my AuditToolGroup table which don't
have a parent ID. This is because the AuditToolGroup table is
self-referencing and can be its own child. Therefore, there's another
subform based on the same table, but returning all AuditToolGroups that DO
have a ParentID.

When an AuditToolGroup does not have another AuditToolGroup as a child, it
will have Fields as children. Therefore, there is a second subform,
AuditToolField, which can live in the AuditToolGroup Subform control or can
live in its own control within the AuditToolGroup Subform.

I had all this working fine, but then the next time I opened the database,
it asked me for a parameter [AuditToolGroup]. So I did a little Googling
and changed the queries so that the LinkChildFields is always AuditToolGroup
and the LinkMasterFields is always AuditGroupID.

So ParentID is aliased to AuditToolGroup in the child query of the
AuditToolGroup Subform, and AuditGroupID is always the primary key for the
AuditToolGroup at the particular level of the firm structure.

All this works fine when the AuditToolGroup has no AuditToolGroup children,
and the Fields are directly nested within the main form. However, when the
AuditToolFields is nested in the AuditToolGroup subform, it incorrectly
links to the AuditGroupID of the main form. I can look at the form and SEE
that the AuditGroupID in the AuditToolGroup subform is correctly set, but
the AuditToolFields AuditToolGroup shows as the same as the main form, not
its own parent form.

The code that switches out the subforms is very simple:

Private Sub Form_Current()
Me![AuditToolChildGroups Subform_Label].Caption = "Sub Group"
With Me![AuditToolChildGroups Subform]
.SourceObject = "AuditToolChildGroups Subform"
Debug.Print .Form.RecordsetClone.RecordCount
Debug.Print .SourceObject
If .Form.RecordsetClone.RecordCount = 0 Then
.SourceObject = "AuditToolField subform"
Debug.Print .SourceObject
Me![AuditToolChildGroups Subform_Label].Caption = "Fields"
End If
End With
End Sub

Am I missing something about how this is supposed to work?

TIA;

Amy
 
M

Marshall Barton

Amy said:
I am working with a Form with a subform. The parent form refers to a query
that selects all AuditToolGroups from my AuditToolGroup table which don't
have a parent ID. This is because the AuditToolGroup table is
self-referencing and can be its own child. Therefore, there's another
subform based on the same table, but returning all AuditToolGroups that DO
have a ParentID.

When an AuditToolGroup does not have another AuditToolGroup as a child, it
will have Fields as children. Therefore, there is a second subform,
AuditToolField, which can live in the AuditToolGroup Subform control or can
live in its own control within the AuditToolGroup Subform.

I had all this working fine, but then the next time I opened the database,
it asked me for a parameter [AuditToolGroup]. So I did a little Googling
and changed the queries so that the LinkChildFields is always AuditToolGroup
and the LinkMasterFields is always AuditGroupID.

So ParentID is aliased to AuditToolGroup in the child query of the
AuditToolGroup Subform, and AuditGroupID is always the primary key for the
AuditToolGroup at the particular level of the firm structure.

All this works fine when the AuditToolGroup has no AuditToolGroup children,
and the Fields are directly nested within the main form. However, when the
AuditToolFields is nested in the AuditToolGroup subform, it incorrectly
links to the AuditGroupID of the main form. I can look at the form and SEE
that the AuditGroupID in the AuditToolGroup subform is correctly set, but
the AuditToolFields AuditToolGroup shows as the same as the main form, not
its own parent form.

The code that switches out the subforms is very simple:

Private Sub Form_Current()
Me![AuditToolChildGroups Subform_Label].Caption = "Sub Group"
With Me![AuditToolChildGroups Subform]
.SourceObject = "AuditToolChildGroups Subform"
Debug.Print .Form.RecordsetClone.RecordCount
Debug.Print .SourceObject
If .Form.RecordsetClone.RecordCount = 0 Then
.SourceObject = "AuditToolField subform"
Debug.Print .SourceObject
Me![AuditToolChildGroups Subform_Label].Caption = "Fields"
End If
End With
End Sub

Am I missing something about how this is supposed to work?
Click to expand...


I hope you are still monitoring this thread Amy.

I've been looking at this all week and I think you are doing
what you're supposed to do. In my opinion, the problem
seems to be a bug.

I have seen this happen sometimes when changing a subform's
record source property, but I son't remember seeingit when
changing the SourceObject property. I guess it makes some
kind of sense for it to happen in both cases though.

I think(?) the issue is that Access inappropriately takes it
upon itself to automatically set the Link Master/Child
properties ans to figure out what they should bem it uses
the Relationships information.

I think you can workaround the problem by adding more code
to set the Link Master/Child Fields properties immediately
after you set the SourceObject property. If I followed your
arrangement, the code would be:

.SourceObject = "AuditToolChildGroups Subform"
.LinkChildFields = "ParentID "
 
A

Amy Blankenship

I actually fixed it by deleting and reinserting the subform. There was
something wonky going on :)

Thanks;

Amy

Marshall Barton said:
Amy said:
I am working with a Form with a subform. The parent form refers to a
query
that selects all AuditToolGroups from my AuditToolGroup table which don't
have a parent ID. This is because the AuditToolGroup table is
self-referencing and can be its own child. Therefore, there's another
subform based on the same table, but returning all AuditToolGroups that DO
have a ParentID.

When an AuditToolGroup does not have another AuditToolGroup as a child, it
will have Fields as children. Therefore, there is a second subform,
AuditToolField, which can live in the AuditToolGroup Subform control or
can
live in its own control within the AuditToolGroup Subform.

I had all this working fine, but then the next time I opened the database,
it asked me for a parameter [AuditToolGroup]. So I did a little Googling
and changed the queries so that the LinkChildFields is always
AuditToolGroup
and the LinkMasterFields is always AuditGroupID.

So ParentID is aliased to AuditToolGroup in the child query of the
AuditToolGroup Subform, and AuditGroupID is always the primary key for the
AuditToolGroup at the particular level of the firm structure.

All this works fine when the AuditToolGroup has no AuditToolGroup
children,
and the Fields are directly nested within the main form. However, when
the
AuditToolFields is nested in the AuditToolGroup subform, it incorrectly
links to the AuditGroupID of the main form. I can look at the form and
SEE
that the AuditGroupID in the AuditToolGroup subform is correctly set, but
the AuditToolFields AuditToolGroup shows as the same as the main form, not
its own parent form.

The code that switches out the subforms is very simple:

Private Sub Form_Current()
Me![AuditToolChildGroups Subform_Label].Caption = "Sub Group"
With Me![AuditToolChildGroups Subform]
.SourceObject = "AuditToolChildGroups Subform"
Debug.Print .Form.RecordsetClone.RecordCount
Debug.Print .SourceObject
If .Form.RecordsetClone.RecordCount = 0 Then
.SourceObject = "AuditToolField subform"
Debug.Print .SourceObject
Me![AuditToolChildGroups Subform_Label].Caption = "Fields"
End If
End With
End Sub

Am I missing something about how this is supposed to work?
Click to expand...


I hope you are still monitoring this thread Amy.

I've been looking at this all week and I think you are doing
what you're supposed to do. In my opinion, the problem
seems to be a bug.

I have seen this happen sometimes when changing a subform's
record source property, but I son't remember seeingit when
changing the SourceObject property. I guess it makes some
kind of sense for it to happen in both cases though.

I think(?) the issue is that Access inappropriately takes it
upon itself to automatically set the Link Master/Child
properties ans to figure out what they should bem it uses
the Relationships information.

I think you can workaround the problem by adding more code
to set the Link Master/Child Fields properties immediately
after you set the SourceObject property. If I followed your
arrangement, the code would be:

.SourceObject = "AuditToolChildGroups Subform"
.LinkChildFields = "ParentID "
Click to expand...
 
M

Marshall Barton

I glad you got it working, but be aware that there is a real
problem in some(?) situations like this.
--
Marsh
MVP [MS Access]


Amy said:
I actually fixed it by deleting and reinserting the subform. There was
something wonky going on :)

Amy said:
I am working with a Form with a subform. The parent form refers to a
query
that selects all AuditToolGroups from my AuditToolGroup table which don't
have a parent ID. This is because the AuditToolGroup table is
self-referencing and can be its own child. Therefore, there's another
subform based on the same table, but returning all AuditToolGroups that DO
have a ParentID.

When an AuditToolGroup does not have another AuditToolGroup as a child, it
will have Fields as children. Therefore, there is a second subform,
AuditToolField, which can live in the AuditToolGroup Subform control or
can
live in its own control within the AuditToolGroup Subform.

I had all this working fine, but then the next time I opened the database,
it asked me for a parameter [AuditToolGroup]. So I did a little Googling
and changed the queries so that the LinkChildFields is always
AuditToolGroup
and the LinkMasterFields is always AuditGroupID.

So ParentID is aliased to AuditToolGroup in the child query of the
AuditToolGroup Subform, and AuditGroupID is always the primary key for the
AuditToolGroup at the particular level of the firm structure.

All this works fine when the AuditToolGroup has no AuditToolGroup
children,
and the Fields are directly nested within the main form. However, when
the
AuditToolFields is nested in the AuditToolGroup subform, it incorrectly
links to the AuditGroupID of the main form. I can look at the form and
SEE
that the AuditGroupID in the AuditToolGroup subform is correctly set, but
the AuditToolFields AuditToolGroup shows as the same as the main form, not
its own parent form.

The code that switches out the subforms is very simple:

Private Sub Form_Current()
Me![AuditToolChildGroups Subform_Label].Caption = "Sub Group"
With Me![AuditToolChildGroups Subform]
.SourceObject = "AuditToolChildGroups Subform"
Debug.Print .Form.RecordsetClone.RecordCount
Debug.Print .SourceObject
If .Form.RecordsetClone.RecordCount = 0 Then
.SourceObject = "AuditToolField subform"
Debug.Print .SourceObject
Me![AuditToolChildGroups Subform_Label].Caption = "Fields"
End If
End With
End Sub

Am I missing something about how this is supposed to work?
Click to expand...

"Marshall Barton" wrote
I hope you are still monitoring this thread Amy.

I've been looking at this all week and I think you are doing
what you're supposed to do. In my opinion, the problem
seems to be a bug.

I have seen this happen sometimes when changing a subform's
record source property, but I son't remember seeingit when
changing the SourceObject property. I guess it makes some
kind of sense for it to happen in both cases though.

I think(?) the issue is that Access inappropriately takes it
upon itself to automatically set the Link Master/Child
properties ans to figure out what they should bem it uses
the Relationships information.

I think you can workaround the problem by adding more code
to set the Link Master/Child Fields properties immediately
after you set the SourceObject property. If I followed your
arrangement, the code would be:

.SourceObject = "AuditToolChildGroups Subform"
.LinkChildFields = "ParentID"
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing subform SourceObject 1
Help with SourceObject & Subform coding 1
Capture Data Change in Subform Using a table as SourceObject 2
Subform sourceobject problem 11
subform question 2
Problems setting the sourceobject of a subform... 2
Subform fields changing 1
Single parent form contains multiple instances of the same subform 8

Top