473,289 Members | 2,091 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

How to change the RowSource in a SubForm in VBA code

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
8 23658
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Saintor | last post by:
The following function works when used directly from a form. It locks controls depending on a value stored in the tag property. Called from a button on a subform, it does not work . TIA. ...
3
by: Sam Gledhill | last post by:
Hi, I'm hoping someone can help me out. I have a database with a form. This form has no recordsource (I'm using it as a complex MsgBox). It has a single textbox of type Date. It also has three...
4
by: Corey | last post by:
I am having some real trouble getting a listbox rowsource to load properly. The listbox is actually on a subform. My main form has several command buttons that change the MASTER_SUB_FORM object...
5
by: Guoqi Zheng | last post by:
I think I have this kind of problem very often, now it is really making me crazy. VS.NET always want to change my html code. Ok, that is fine as long as Vs.Net can ask my confirmaton first. For...
11
by: sreekandan | last post by:
Hi everybody, I want to retrieve the details from the database by the date from (MM/DD/YYYY) to (MM/DD/YYYY). For that I have to change the following code as follows rs.Open "select * from...
3
by: Peter Hofmann | last post by:
When I change the source code and save it I have currently always to press explicitely F6 zu "compile" the code (and get new error list). Isn't there an automatic build function which is called...
1
by: noneedforthis | last post by:
I'm a new user of Access/VB and ran in to this nasty wall. (Access 2003, VB6.3, Windows XP) Runtime Error 2001: You cancelled the previous operation. The highlighted line is the one where the...
10
by: mukeshrasm | last post by:
Hi the code written below is for calculating the number of days remain for a particular event based on the specific local time i.e. time in new york or time in london. <style style="text/css">...
1
by: neelsfer | last post by:
When i use this code to count the number of records on display in a specific subform in the "txtRecordNo" field that is open now, it seems to "update" all the time as the cursor changes every second...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.