How to change the RowSource in a SubForm in VBA code

[Simon]

Dear reader,

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

Forms!FormName. .RowSource = "TableOrQueryNa me"

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

Forms!MasterFor mName..!SubForm Name...RowSourc e =
"TableOrQueryNa me"

Is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[ruralguy via AccessMonster.com]

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 = "TableOrQueryNa me"

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

Forms!MasterFor mName..!SubForm Name...RowSourc e =
"TableOrQueryN ame"

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.c om
http://www.accessmonster.com/Uwe/For...ccess/200708/1

[tina]

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!Sub formControlName .Form.RecordSou rce = "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********@Ver satel.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 = "TableOrQueryNa me"

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

Forms!MasterFor mName..!SubForm Name...RowSourc e =
"TableOrQueryNa me"

Is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[ruralguy via AccessMonster.com]

Hi Tina,
I think you will find the syntax is either:
Me.SubFormContr olName.Form.Rec ordSource
or
Forms.MainFormN ame.SubFormCont rolName.Form.Re cordSource

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!Sub formControlName .Form.RecordSou rce = "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.c om
http://www.accessmonster.com/Uwe/For...ccess/200708/1

[Allen Browne]

Simon, just a follow up note.

When you do something like:
Me.[SubFormControlN ame].Form.RecordSou rce = "SELECT ...
Access will reset the LinkMasterField s 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.[SubFormControlN ame].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.c om" <u12102@uwewrot e in message
news:76f44264fa 476@uwe...

Hi Tina,
I think you will find the syntax is either:
Me.SubFormContr olName.Form.Rec ordSource
or
Forms.MainFormN ame.SubFormCont rolName.Form.Re cordSource

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!Sub formControlName .Form.RecordSou rce = "something"

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

[tina]

yeah, i blew that one, all right - must have been asleep when i wrote it!
"ruralguy via AccessMonster.c om" <u12102@uwewrot e in message
news:76f44264fa 476@uwe...

Hi Tina,
I think you will find the syntax is either:
Me.SubFormContr olName.Form.Rec ordSource
or
Forms.MainFormN ame.SubFormCont rolName.Form.Re cordSource

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!Sub formControlName .Form.RecordSou rce = "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.c om
http://www.accessmonster.com/Uwe/For...ccess/200708/1

[ruralguy via AccessMonster.com]

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

[CDMAPoster]

On Aug 19, 10:46 pm, "Allen Browne" <AllenBro...@Se eSig.Invalid>
wrote:

Simon, just a follow up note.

When you do something like:
Me.[SubFormControlN ame].Form.RecordSou rce = "SELECT ...
Access will reset the LinkMasterField s 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 , LinkMasterField s 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 LinkMasterField s 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********@Fort uneJames.com

[Allen Browne]

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********@For tuneJames.comwr ote in message
news:11******** **************@ i13g2000prf.goo glegroups.com.. .
On Aug 19, 10:46 pm, "Allen Browne" <AllenBro...@Se eSig.Invalid>
wrote:

Simon, just a follow up note.

When you do something like:
Me.[SubFormControlN ame].Form.RecordSou rce = "SELECT ...
Access will reset the LinkMasterField s 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 , LinkMasterField s 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 LinkMasterField s 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********@Fort uneJames.com