Set Subform sourceobject and recordsource

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

robert d - 19 Jan 2006 00:33 GMT

I'm not sure why the following isn't working. The subform loads correctly,
but no data is displayed. I'm certain that there is data and I have checked
that the SQL statement does in fact return that data (checked by pasting the
statement into a new Access Query in the database window).

Me.FutureSubform.SourceObject = "FutureProjectionSubForm"
Me.FutureSubform.Form.RecordSource = "SELECT * FROM [TEMP_PROJECT_TABLE]"
Me.FutureSubform.Form.Requery

Is there something about the fact that I'm trying to set the recordsource of
the subform after it has loaded?

Reply to this Message

Allen Browne - 19 Jan 2006 00:57 GMT

Robert, when you set the SourceObject of a subform control, Access has a go
at assigning what it thinks you want for the LinkMasterFields and
LinkChildFields properties. If the subform displays nothing, my guess would
be that it has got this wrong, and there are no related records based on
whatever it thinks are the link fields.

To test that idea, ask it to show you what it did after you assigned the
SourceObject:
Debug.Print Me.FutureSubform.LinkMasterFields
Debug.Print Me.FutureSubform.LinkChildFields

The solution is probably to set these 2 properties to the correct fields.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm not sure why the following isn't working. The subform loads
> correctly,
[quoted text clipped - 11 lines]
> of
> the subform after it has loaded?

Reply to this Message

robert d - 19 Jan 2006 01:44 GMT

Allen, thanks for the response.

Everything is unbound. In the properties window for the subform control, the
"Link Child Fields" and Link Master Fields" properties are blank.

I must confess that I don't understand what these are or do, but at the same
time by setting the Recordsource of the subform, why doesn't it just fill
with the data from the SQL statement?

Thanks.

>Robert, when you set the SourceObject of a subform control, Access has a go
>at assigning what it thinks you want for the LinkMasterFields and
[quoted text clipped - 14 lines]
>> of
>> the subform after it has loaded?

Reply to this Message

Allen Browne - 19 Jan 2006 02:19 GMT

A subform is designed to show only those records that match the record in
the main form. The RecordSource of the subform is typically a table related
to the table that main form draws its records from. LinkMasterFields and
LinkChildFields define how the subform records "match" the main form record.
Even if these properties are blank at design time, you may find that they
are no longer blank after you assign the RecordSource of the subform.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen, thanks for the response.
>
[quoted text clipped - 29 lines]
>>> of
>>> the subform after it has loaded?

Reply to this Message

robert d - 19 Jan 2006 03:04 GMT

O.K.

I got the values as you suggested, after setting the SourceObject, and they
are both blank. They stay blank even after the recordsource is set and the
requery line of code is executed.

>A subform is designed to show only those records that match the record in
>the main form. The RecordSource of the subform is typically a table related
[quoted text clipped - 8 lines]
>>>> of
>>>> the subform after it has loaded?

Reply to this Message

Allen Browne - 19 Jan 2006 03:10 GMT

If the LinkMasterFields and LinkChildFields are not limiting the subform's
records, I guess it is somethig else.

Open the subform itself in design view, and check its properties, For
example, if its Data Entry property is set to Yes, it will not show any
existing records.

Or is there are Filter applied? Try:
Debug.Print Me.FutureSubform.Form.Filter
Debug.Print Me.FutureSubform.Form.FilterOn

If not, ask it to show you the assigned RecordSource:
Debug.Print Me.FutureSubform.Form.RecordSource
(I am assuming it may be more complex than the example you posted
initially.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> O.K.
>
[quoted text clipped - 18 lines]
>>>>> of
>>>>> the subform after it has loaded?

Reply to this Message

robert d - 19 Jan 2006 03:39 GMT

Nope. Data Entry is No. No filters are set. The RecordSource of the form
is shown as being equal to the SQL statement in my first post.

I put the Me.RecordSource statement for the subform in the load event of the
subform. Still nothing is populated. So out of frustration. I put a
listbox on the subform and, in the Form Load event of the subform, set it's
RowSource property to the SQL statement listed above.

The listbox is populated with the exact records I want! But I really wanted
to use a subform because I wanted to allow for easy "cell like" editing and
deleting.

Thanks for being patient with me. I still don't have a clue. It looks like
for some reason, the RecordSource of the form is not being set even though
there is clearly an SQL statement assigned to it.

>If the LinkMasterFields and LinkChildFields are not limiting the subform's
>records, I guess it is somethig else.
[quoted text clipped - 17 lines]
>>>>>> of
>>>>>> the subform after it has loaded?

Reply to this Message

robert d - 19 Jan 2006 04:03 GMT

Okay, I found my mistake and a stupid one it is at that.

The ControlSource of the textboxes on the subform (to be displayed in
continuous mode) was not set. Hence no data was displayed. Once set, data
is displayed as expected.

Thanks for your patience, Allen.

>Nope. Data Entry is No. No filters are set. The RecordSource of the form
>is shown as being equal to the SQL statement in my first post.
[quoted text clipped - 17 lines]
>>>>>>> of
>>>>>>> the subform after it has loaded?

Reply to this Message

Allen Browne - 19 Jan 2006 04:31 GMT

Okay: that's solved. Good.

Thanks for posting the solution, in case someone else hits the same issue.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Okay, I found my mistake and a stupid one it is at that.
>
> The ControlSource of the textboxes on the subform (to be displayed in
> continuous mode) was not set. Hence no data was displayed. Once set,
> data
> is displayed as expected.

Reply to this Message

Allen Browne - 19 Jan 2006 04:04 GMT

You could try moving the assignment of the RecordSource into the Open event
of the subform instead of its Load event. (The Requery is superfluous, so
you can drop that line.)

Your first paragraph implies that you verified the RecordSource property is
being correctly set, but I was not sure about that after your last
paragraph.

If the query statement really is that simple, then I fail to see why you are
assigning the property in the first place. Why not just save the form with
TEMP_PROJECT_TABLE as its RecordSource?

As always, you will want to uncheck the Name AutoCorrect boxes under:
Tools | Options | General
and then compact the database. That's one source of huge confusion to
Access.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Nope. Data Entry is No. No filters are set. The RecordSource of the
> form
[quoted text clipped - 39 lines]
>>>>>>> of
>>>>>>> the subform after it has loaded?

Reply to this Message