By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

Set Subform sourceobject and recordsource

P: n/a
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?

--
Message posted via http://www.accessmonster.com
Jan 19 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.

--
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.

"robert d via AccessMonster.com" <u6836@uwe> wrote in message
news:5a90a3385d51f@uwe...
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?

Jan 19 '06 #2

P: n/a
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.

Allen Browne wrote:
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.
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?


--
Message posted via http://www.accessmonster.com
Jan 19 '06 #3

P: n/a
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.

--
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.

"robert d via AccessMonster.com" <u6836@uwe> wrote in message
news:5a9140c068953@uwe...
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.

Allen Browne wrote:
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.
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?

Jan 19 '06 #4

P: n/a
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.

Allen Browne wrote:
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.
Allen, thanks for the response.

[quoted text clipped - 29 lines]
of
the subform after it has loaded?


--
Message posted via http://www.accessmonster.com
Jan 19 '06 #5

P: n/a
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.)

--
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.

"robert d via AccessMonster.com" <u6836@uwe> wrote in message
news:5a91f4a7ee463@uwe...
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.

Allen Browne wrote:
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.
Allen, thanks for the response.

[quoted text clipped - 29 lines]
> of
> the subform after it has loaded?

Jan 19 '06 #6

P: n/a
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.

Allen Browne wrote:
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.)
O.K.

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


--
Message posted via http://www.accessmonster.com
Jan 19 '06 #7

P: n/a
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.

robert d wrote:
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?


--
Message posted via http://www.accessmonster.com
Jan 19 '06 #8

P: n/a
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.

--
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.

"robert d via AccessMonster.com" <u6836@uwe> wrote in message
news:5a924282e1f87@uwe...
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.

Allen Browne wrote:
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.)
O.K.

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

Jan 19 '06 #9

P: n/a
Okay: that's solved. Good.

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

--
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.

"robert d via AccessMonster.com" <u6836@uwe> wrote in message
news:5a92791fa5eff@uwe...
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.

Jan 19 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.