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

How to change the RowSource in a SubForm in VBA code

P: n/a
Dear reader,

The syntax for the VBA code to change the RowSource of a Form is:

Forms!FormName..RowSource = "TableOrQueryName"

Bu now the syntax to change the RowSource of a SubForm:

Forms!MasterFormName..!SubFormName...RowSource =
"TableOrQueryName"

Is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon
Aug 19 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
See if this link helps: http://www.mvps.org/access/forms/frm0031.htm

Simon wrote:
>Dear reader,

The syntax for the VBA code to change the RowSource of a Form is:

Forms!FormName..RowSource = "TableOrQueryName"

Bu now the syntax to change the RowSource of a SubForm:

Forms!MasterFormName..!SubFormName...RowSource =
"TableOrQueryName"

Is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 19 '07 #2

P: n/a
forms don't have a RowSource property. you can change the *RecordSource*
property of a subform from code running in the main form, as

Me!MainForm!SubformControlName.Form.RecordSource = "something"

where Me refers to the main form. if the code is running *outside* both the
mainform and subform, replace Me with Forms.

hth
"Simon" <Sv********@Versatel.nlwrote in message
news:46**********************@news.tele2.nl...
Dear reader,

The syntax for the VBA code to change the RowSource of a Form is:

Forms!FormName..RowSource = "TableOrQueryName"

Bu now the syntax to change the RowSource of a SubForm:

Forms!MasterFormName..!SubFormName...RowSource =
"TableOrQueryName"

Is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon


Aug 20 '07 #3

P: n/a
Hi Tina,
I think you will find the syntax is either:
Me.SubFormControlName.Form.RecordSource
or
Forms.MainFormName.SubFormControlName.Form.RecordS ource

tina wrote:
>forms don't have a RowSource property. you can change the *RecordSource*
property of a subform from code running in the main form, as

Me!MainForm!SubformControlName.Form.RecordSource = "something"

where Me refers to the main form. if the code is running *outside* both the
mainform and subform, replace Me with Forms.

hth
>Dear reader,
[quoted text clipped - 16 lines]
>>
Simon
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 20 '07 #4

P: n/a
Simon, just a follow up note.

When you do something like:
Me.[SubFormControlName].Form.RecordSource = "SELECT ...
Access will reset the LinkMasterFields and LinkChildFields property of the
subform control. If it gets it right, things will be fine. If it gets it
wrong, the subform may show all record (not just the related ones), no
records at all, or the wrong records.

An alternative approach might be to set the Filter of the subform:
With Me.[SubFormControlName].Form
.Filter = "[SomeField] = 999"
.FilterOn = True
End With

However, there's a bug in Access (all versions before 2007) such that this
doesn't work correctly either if you filter the main form as well. Details
in this article:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

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

"ruralguy via AccessMonster.com" <u12102@uwewrote in message
news:76f44264fa476@uwe...
Hi Tina,
I think you will find the syntax is either:
Me.SubFormControlName.Form.RecordSource
or
Forms.MainFormName.SubFormControlName.Form.RecordS ource

tina wrote:
>>forms don't have a RowSource property. you can change the *RecordSource*
property of a subform from code running in the main form, as

Me!MainForm!SubformControlName.Form.RecordSource = "something"

where Me refers to the main form. if the code is running *outside* both
the
mainform and subform, replace Me with Forms.
Aug 20 '07 #5

P: n/a
yeah, i blew that one, all right - must have been asleep when i wrote it!
"ruralguy via AccessMonster.com" <u12102@uwewrote in message
news:76f44264fa476@uwe...
Hi Tina,
I think you will find the syntax is either:
Me.SubFormControlName.Form.RecordSource
or
Forms.MainFormName.SubFormControlName.Form.RecordS ource

tina wrote:
forms don't have a RowSource property. you can change the *RecordSource*
property of a subform from code running in the main form, as

Me!MainForm!SubformControlName.Form.RecordSource = "something"

where Me refers to the main form. if the code is running *outside* both
the
mainform and subform, replace Me with Forms.

hth
Dear reader,
[quoted text clipped - 16 lines]
>
Simon

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 21 '07 #6

P: n/a
Been there...Done that. ;^)

Take care

tina wrote:
>yeah, i blew that one, all right - must have been asleep when i wrote it!
>Hi Tina,
I think you will find the syntax is either:
[quoted text clipped - 17 lines]
>>
Simon
--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

Aug 21 '07 #7

P: n/a
On Aug 19, 10:46 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Simon, just a follow up note.

When you do something like:
Me.[SubFormControlName].Form.RecordSource = "SELECT ...
Access will reset the LinkMasterFields and LinkChildFields property of the
subform control. If it gets it right, things will be fine. If it gets it
wrong, the subform may show all record (not just the related ones), no
records at all, or the wrong records.
That's an interesting observation.
>From the A97 help for LinkChildFields, LinkMasterFields Properties:
When you create a subform or subreport by dragging a form or report
from the Database window onto another form or report or by using the
Form Wizard, Microsoft Access automatically sets the LinkChildFields
and LinkMasterFields properties under the following conditions:

Both the main form or report and the child object are based on
tables, and a relationship between those tables has been defined with
the Relationships command. Microsoft Access uses the fields that
relate the two tables as the linking fields.
The main form or report is based on a table with a primary key, and
the subform or subreport is based on a table or query that contains a
field with the same name and the same or a compatible data type as the
primary key. Microsoft Access uses the primary key from the main
object's underlying table and the identically named field from the
child object's underlying table or query as the linking fields.

Although setting a subform's RecordSource dynamically isn't mentioned
specifically, I imagine that the same principles apply. I've never
had Access get this wrong, but thanks to your warning I'll be on the
lookout for it.

James A. Fortune
CD********@FortuneJames.com

Aug 22 '07 #8

P: n/a
Yes, that's a good description of what is it supposed to do.

Most times it gets it right if the conditions you met are right. But there
are complicating factors where Access may not be able to figure it out
correctly. For example, where the related table has more than one foreign
key of the right data type to choose from (regardless of whether the names
match or not), and you are using queries as the source.

It makes sense that the same problem situtions could arise when setting the
subform's RecordSource on the fly.

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

<CD********@FortuneJames.comwrote in message
news:11**********************@i13g2000prf.googlegr oups.com...
On Aug 19, 10:46 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Simon, just a follow up note.

When you do something like:
Me.[SubFormControlName].Form.RecordSource = "SELECT ...
Access will reset the LinkMasterFields and LinkChildFields property of the
subform control. If it gets it right, things will be fine. If it gets it
wrong, the subform may show all record (not just the related ones), no
records at all, or the wrong records.
That's an interesting observation.
>From the A97 help for LinkChildFields, LinkMasterFields Properties:
When you create a subform or subreport by dragging a form or report
from the Database window onto another form or report or by using the
Form Wizard, Microsoft Access automatically sets the LinkChildFields
and LinkMasterFields properties under the following conditions:

Both the main form or report and the child object are based on
tables, and a relationship between those tables has been defined with
the Relationships command. Microsoft Access uses the fields that
relate the two tables as the linking fields.
The main form or report is based on a table with a primary key, and
the subform or subreport is based on a table or query that contains a
field with the same name and the same or a compatible data type as the
primary key. Microsoft Access uses the primary key from the main
object's underlying table and the identically named field from the
child object's underlying table or query as the linking fields.

Although setting a subform's RecordSource dynamically isn't mentioned
specifically, I imagine that the same principles apply. I've never
had Access get this wrong, but thanks to your warning I'll be on the
lookout for it.

James A. Fortune
CD********@FortuneJames.com

Aug 23 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.