How to set the recordset of a subform of a subform?

[Albert]

I use the following methods but fail, #Name? appears......
[Forms]![History Result
List]![Holdings].Form![subform_control].Form.RecordSource = sub_subform_sql
history Result List is a Form and Holdings is the control name of the first
subform
subform_control is the name of the subform in the first subform

 

[tina]

well, the syntax looks okay to me, except i think that "sub_subform_sql"
should be put in double quotes, as

....RecordSource = "sub_subform_sql"

if that doesn't take care of the issue by itself, suggest you *also* double
check the names of the subform CONTROL and sub-subform CONTROL. a subform's
*control* name (within its' parent form) is not always the same as the name
of the subform object in the database window. using the name of the subform
object, instead of the name of the subform control, is a mistake that people
frequently make.

hth

 

[Albert]

thx tina,
I have tried both of your method.
But both of them fail with the sum error - run-time error 2245

i am sure that list_result_subform and subform_control are both controls
name only, not the form names.

 

[Albert]

sorry, the run-time error is 2455

 

[tina]

what *text* description do you see when the error message comes up?
also, in what form or control event are you running the code? and in which
form module?

 

[Albert]

tina thanks very much!
The error message is ...."RunTime error 2455, You entered an expresssion
that has
an invalid reference to the property Form/Report."

My forms are constructed like this....
A Parent form named "List" (Level 1), a subform in "List" Named "List
Subform" (Level 2) and finally a subform in "List Subform" named "List
Subform Holdings" (Level 3)
The control name of "List Subform" in "List" is "list_subform"
The control name of "List Subform Holdings" is "list_subform_subform"

I build this format because i want to make a datasheet that show the
relationship between two tables.

I use VBA to set the recordsource of "List"'s Subform(Level2) in "List"
(Level1) by the following
Me.list_subform.Form.RecordSource = sql
The above line is okay and it run properly

Then, i use VBA to set the recordesource of "List Subform"'s Subform
(Level3) in "List Subform"(Level2) by the following

[Forms]!

• ![list_subform].Form![list_subform_subform].Form.RecordSource
  = sql_subform
  But it fails......
  
  Today, the code can run without error for sometimes, but later the error
  2455 pop-up again ;_;......
  
  
  "tina" 來函：

 

[tina]

also, in what form or control event are you running the code? and in which

form module?

you didn't answer the above questions. also, please copy your entire
procedure from the VBA Editor window, and paste it into your reply. and
which versions of Access and Windows are you using?

tina thanks very much!
The error message is ...."RunTime error 2455, You entered an expresssion
that has
an invalid reference to the property Form/Report."

My forms are constructed like this....
A Parent form named "List" (Level 1), a subform in "List" Named "List
Subform" (Level 2) and finally a subform in "List Subform" named "List
Subform Holdings" (Level 3)
The control name of "List Subform" in "List" is "list_subform"
The control name of "List Subform Holdings" is "list_subform_subform"

I build this format because i want to make a datasheet that show the
relationship between two tables.

I use VBA to set the recordsource of "List"'s Subform(Level2) in "List"
(Level1) by the following
Me.list_subform.Form.RecordSource = sql
The above line is okay and it run properly

Then, i use VBA to set the recordesource of "List Subform"'s Subform
(Level3) in "List Subform"(Level2) by the following

[Forms]!

• ![list_subform].Form![list_subform_subform].Form.RecordSource
  = sql_subform
  But it fails......
  
  Today, the code can run without error for sometimes, but later the error
  2455 pop-up again ;_;......
  
  
  "tina" ??:
  

  what *text* description do you see when the error message comes up?
  also, in what form or control event are you running the code? and in which
  form module?

 

[Albert]

Thanks tina, I write the above in form_load event. Here is my code, I have
cut my sql since it is working fine

Option Compare Database

Private Sub Form_Load()
'Build the subform's sql base on the entry
'===================================================

'Set the recordsource of the subform

[Forms]![History Result
List]![list_result_subform].Form![list_result_subform_subform].Form.RecordSource = sub_subform_sql

End Sub

I am sure my sql string is fine because i have seen the code can work as
expected.
I suspect that the error comes up becuase the "Hisory Result List" is not
loaded
May be figure out the correct sequence of opening a form which have two
levels of subform is the key.

I have tried the code in two environments
1. Access XP + Windows 98
2. Access 2003 + Win XP

also, in what form or control event are you running the code? and in which
form module?

you didn't answer the above questions. also, please copy your entire
procedure from the VBA Editor window, and paste it into your reply. and
which versions of Access and Windows are you using?

tina thanks very much!
The error message is ...."RunTime error 2455, You entered an expresssion
that has
an invalid reference to the property Form/Report."

My forms are constructed like this....
A Parent form named "List" (Level 1), a subform in "List" Named "List
Subform" (Level 2) and finally a subform in "List Subform" named "List
Subform Holdings" (Level 3)
The control name of "List Subform" in "List" is "list_subform"
The control name of "List Subform Holdings" is "list_subform_subform"

I build this format because i want to make a datasheet that show the
relationship between two tables.

I use VBA to set the recordsource of "List"'s Subform(Level2) in "List"
(Level1) by the following
Me.list_subform.Form.RecordSource = sql
The above line is okay and it run properly

Then, i use VBA to set the recordesource of "List Subform"'s Subform
(Level3) in "List Subform"(Level2) by the following

[Forms]!

• ![list_subform].Form![list_subform_subform].Form.RecordSource
  = sql_subform
  But it fails......
  
  Today, the code can run without error for sometimes, but later the error
  2455 pop-up again ;_;......
  
  
  "tina" ??:
  

  what *text* description do you see when the error message comes up?
  also, in what form or control event are you running the code? and in which
  form module?
  
  
  sorry, the run-time error is 2455
  
  :
  
  thx tina,
  I have tried both of your method.
  But both of them fail with the sum error - run-time error 2245
  
  i am sure that list_result_subform and subform_control are both controls
  name only, not the form names.

 

[tina]

I suspect that the error comes up becuase the "Hisory Result List" is not

loaded
May be figure out the correct sequence of opening a form which have two
levels of subform is the key.

that is very possibly the problem. since you didn't say which form's module
the code is running from, i can't really comment specifically. you can add a
MsgBox to each form's Load event, as

MsgBox Me.Name

and then open the mainform. the order in which the message boxes pop up will
tell you the forms' Load sequence. i think the main form will load *last*.
if so, you may want to just run the "set RecordSource" code in the
mainform's Load event, followed by a requery of the sub-subform's Form
object.

if all else fails, you might try removing the .Form object references from
the code. in my experience it's usually not necessary in VBA.

hth

Thanks tina, I write the above in form_load event. Here is my code, I have
cut my sql since it is working fine

Option Compare Database

Private Sub Form_Load()
'Build the subform's sql base on the entry
'===================================================

'Set the recordsource of the subform

[Forms]![History Result

List]![list_result_subform].Form![list_result_subform_subform].Form.RecordSo
urce = sub_subform_sql

End Sub

I am sure my sql string is fine because i have seen the code can work as
expected.
I suspect that the error comes up becuase the "Hisory Result List" is not
loaded
May be figure out the correct sequence of opening a form which have two
levels of subform is the key.

I have tried the code in two environments
1. Access XP + Windows 98
2. Access 2003 + Win XP

also, in what form or control event are you running the code? and in which
form module?

you didn't answer the above questions. also, please copy your entire
procedure from the VBA Editor window, and paste it into your reply. and
which versions of Access and Windows are you using?

tina thanks very much!
The error message is ...."RunTime error 2455, You entered an expresssion
that has
an invalid reference to the property Form/Report."

My forms are constructed like this....
A Parent form named "List" (Level 1), a subform in "List" Named "List
Subform" (Level 2) and finally a subform in "List Subform" named "List
Subform Holdings" (Level 3)
The control name of "List Subform" in "List" is "list_subform"
The control name of "List Subform Holdings" is "list_subform_subform"

I build this format because i want to make a datasheet that show the
relationship between two tables.

I use VBA to set the recordsource of "List"'s Subform(Level2) in "List"
(Level1) by the following
Me.list_subform.Form.RecordSource = sql
The above line is okay and it run properly

Then, i use VBA to set the recordesource of "List Subform"'s Subform
(Level3) in "List Subform"(Level2) by the following

[Forms]!

• ![list_subform].Form![list_subform_subform].Form.RecordSource

  = sql_subform
  But it fails......
  
  Today, the code can run without error for sometimes, but later the error
  2455 pop-up again ;_;......
  
  
  "tina" ??:
  
  what *text* description do you see when the error message comes up?
  also, in what form or control event are you running the code? and in which
  form module?
  
  
  sorry, the run-time error is 2455
  
  :
  
  thx tina,
  I have tried both of your method.
  But both of them fail with the sum error - run-time error 2245
  
  i am sure that list_result_subform and subform_control are both controls
  name only, not the form names.

 

[Albert]

Thank you tina,
actually i don't understand what do you mean by form's module, i have
already told you all the structure in my previous post.

you tell me to set the recordset and then requery, but i can't set the
recordset because i can't specify the path to the level 3 subform.....

today, i have tried another method, i want to share so that other encounter
have the same problem will have a look.

Firstly, i set the source object of the level 3 subform to be a query (not
exist yet, but i will create it before the parent form load. Then i create a
query dynamically.
I find that the level 3 subform can locate the source correctly.

I suspect that the error comes up becuase the "Hisory Result List" is not
loaded
May be figure out the correct sequence of opening a form which have two
levels of subform is the key.

that is very possibly the problem. since you didn't say which form's module
the code is running from, i can't really comment specifically. you can add a
MsgBox to each form's Load event, as

MsgBox Me.Name

and then open the mainform. the order in which the message boxes pop up will
tell you the forms' Load sequence. i think the main form will load *last*.
if so, you may want to just run the "set RecordSource" code in the
mainform's Load event, followed by a requery of the sub-subform's Form
object.

if all else fails, you might try removing the .Form object references from
the code. in my experience it's usually not necessary in VBA.

hth

Thanks tina, I write the above in form_load event. Here is my code, I have
cut my sql since it is working fine

Option Compare Database

Private Sub Form_Load()
'Build the subform's sql base on the entry
'===================================================

'Set the recordsource of the subform

[Forms]![History Result

List]![list_result_subform].Form![list_result_subform_subform].Form.RecordSo
urce = sub_subform_sql

End Sub

I am sure my sql string is fine because i have seen the code can work as
expected.
I suspect that the error comes up becuase the "Hisory Result List" is not
loaded
May be figure out the correct sequence of opening a form which have two
levels of subform is the key.

I have tried the code in two environments
1. Access XP + Windows 98
2. Access 2003 + Win XP

also, in what form or control event are you running the code? and in which
form module?

you didn't answer the above questions. also, please copy your entire
procedure from the VBA Editor window, and paste it into your reply. and
which versions of Access and Windows are you using?


tina thanks very much!
The error message is ...."RunTime error 2455, You entered an expresssion
that has
an invalid reference to the property Form/Report."

My forms are constructed like this....
A Parent form named "List" (Level 1), a subform in "List" Named "List
Subform" (Level 2) and finally a subform in "List Subform" named "List
Subform Holdings" (Level 3)
The control name of "List Subform" in "List" is "list_subform"
The control name of "List Subform Holdings" is "list_subform_subform"

I build this format because i want to make a datasheet that show the
relationship between two tables.

I use VBA to set the recordsource of "List"'s Subform(Level2) in "List"
(Level1) by the following
Me.list_subform.Form.RecordSource = sql
The above line is okay and it run properly

Then, i use VBA to set the recordesource of "List Subform"'s Subform
(Level3) in "List Subform"(Level2) by the following


[Forms]!

• ![list_subform].Form![list_subform_subform].Form.RecordSource
  = sql_subform
  But it fails......
  
  Today, the code can run without error for sometimes, but later the error
  2455 pop-up again ;_;......
  
  
  "tina" ??:
  
  what *text* description do you see when the error message comes up?
  also, in what form or control event are you running the code? and in
  which
  form module?
  
  
  sorry, the run-time error is 2455
  
  :
  
  thx tina,
  I have tried both of your method.
  But both of them fail with the sum error - run-time error 2245
  
  i am sure that list_result_subform and subform_control are both
  controls
  name only, not the form names.

 

[tina]

Thank you tina,

actually i don't understand what do you mean by form's module, i have
already told you all the structure in my previous post.

"Then, i use VBA to set the recordesource of "List Subform"'s Subform
(Level3) in "List Subform"(Level2) by the following"

as the above snip from one of your previous posts says, you're using VBA. so
you're working with either a public module, or a form module. a previous
post also said you were using the form's Load event, so you're working with
a form module. you have a main form, subform, and sub-subform. each of these
are form objects, and each one has a form module behind it. so there are
three Load events available to you, one for each form object - and you place
the code in a specific form's module. that's what i was asking you: which
form's module were you running the code from.

when working in form design view with forms/subforms, it's easy to get
confused about just "where you are". when you enter the VBA Editor window,
you can look at the title bar at the top of the window to see the name of
the form that the open module belongs to.

today, i have tried another method, i want to share so that other encounter
have the same problem will have a look.

Firstly, i set the source object of the level 3 subform to be a query (not
exist yet, but i will create it before the parent form load. Then i create a
query dynamically.
I find that the level 3 subform can locate the source correctly.

you found a solution that worked for you - that's the important thing. good
job, and good luck with the rest of your project.

 

[alberty]

oh...i understand what you mean, i am placing the code at the level 3 the
subform's subform Load event