Accessing recordsource of a subforms datasheet

J

jonathan

Hi, i am trying to change the recordsource of a subforms datasheet based on
whether a tick box on the main form is ticked or not.
This is my error message: invalid reference to the property form/report.
My forms are:

Main form: job_no subform
Subform: Tasks
subforms Datasheet: frmGroupTasksSubform

Thanks in advance.


Dim frm1 As Form
Dim ctl1 As Control
Dim ctl3 As Control

Dim strNaRecords As String
Dim strAllRecords As String

strNaRecords = "qryGroupTasksNA"
strAllRecords = "qryGroupTasksALL"

Set frm1 = Forms("job_no subform")
Set ctl1 = frm1.tasks
Set ctl3 = ctl1.Form.frmGroupTasksSubform

If Me.HideNASubTasks = True Then
ctl3.Form.RecordSource = strNaRecords
Else
ctl3.Form.RecordSource = strAllRecords
ctl3.Form.Requery

'Clean up objects
Set ctl1 = Nothing
Set ctl3 = Nothing
Set frm1 = Nothing

End If
 
T

TC

This will change a subform's recordsource when executed from the main form:

me![sfWhatever].form.recordsource = ...

sfWhatever is the name of the subform control on the main form.

HTH,
TC
 
J

jonathan

Hi TC,
that will change the recordsource of the subform. I need to go down one more
level to the subforms subform
cheers in advance

TC said:
This will change a subform's recordsource when executed from the main form:

me![sfWhatever].form.recordsource = ...

sfWhatever is the name of the subform control on the main form.

HTH,
TC


jonathan said:
Hi, i am trying to change the recordsource of a subforms datasheet based on
whether a tick box on the main form is ticked or not.
This is my error message: invalid reference to the property form/report.
My forms are:

Main form: job_no subform
Subform: Tasks
subforms Datasheet: frmGroupTasksSubform

Thanks in advance.


Dim frm1 As Form
Dim ctl1 As Control
Dim ctl3 As Control

Dim strNaRecords As String
Dim strAllRecords As String

strNaRecords = "qryGroupTasksNA"
strAllRecords = "qryGroupTasksALL"

Set frm1 = Forms("job_no subform")
Set ctl1 = frm1.tasks
Set ctl3 = ctl1.Form.frmGroupTasksSubform

If Me.HideNASubTasks = True Then
ctl3.Form.RecordSource = strNaRecords
Else
ctl3.Form.RecordSource = strAllRecords
ctl3.Form.Requery

'Clean up objects
Set ctl1 = Nothing
Set ctl3 = Nothing
Set frm1 = Nothing

End If
Click to expand...
Click to expand...
 
T

TC

Ok, I must have misread it.

Say main form frmMain has a subform control sfA containing a (sub)form
frmSub.

frmSub has a subform control sfB containing a (sub)(sub)form frmSubSub
(gak!).

This is how code running in frmMain's code module, can change the
recordsource of frmSubSub:

me![sfA].form![sfB].form.recordsource = ...

You just iterate the pair: <form>, <subform control> for as many levels
as required. At the top level, <form> is Me - a reference to the current
form. In each successive level, <form> is Form - a reference to the form
within the subform control.

My practice is to reference >properties< using the dot syntax - eg. .Form -
and >user named controls< with the ![...] syntax - eg, ![sfA]

HTH,
TC


jonathan said:
Hi TC,
that will change the recordsource of the subform. I need to go down one more
level to the subforms subform
cheers in advance

TC said:
This will change a subform's recordsource when executed from the main form:

me![sfWhatever].form.recordsource = ...

sfWhatever is the name of the subform control on the main form.

HTH,
TC


jonathan said:
Hi, i am trying to change the recordsource of a subforms datasheet
Click to expand...
based
on
whether a tick box on the main form is ticked or not.
This is my error message: invalid reference to the property form/report.
My forms are:

Main form: job_no subform
Subform: Tasks
subforms Datasheet: frmGroupTasksSubform

Thanks in advance.


Dim frm1 As Form
Dim ctl1 As Control
Dim ctl3 As Control

Dim strNaRecords As String
Dim strAllRecords As String

strNaRecords = "qryGroupTasksNA"
strAllRecords = "qryGroupTasksALL"

Set frm1 = Forms("job_no subform")
Set ctl1 = frm1.tasks
Set ctl3 = ctl1.Form.frmGroupTasksSubform

If Me.HideNASubTasks = True Then
ctl3.Form.RecordSource = strNaRecords
Else
ctl3.Form.RecordSource = strAllRecords
ctl3.Form.Requery

'Clean up objects
Set ctl1 = Nothing
Set ctl3 = Nothing
Set frm1 = Nothing

End If
Click to expand...
Click to expand...
Click to expand...
 
J

jonathan

Hi Tc, i tried what sugested but i still get this error message. invalid
reference to the property form/report.
i can go back one level and change the record source of the first subform. I
can also do
Me![frmtbltwo].Form![frmtbltree].Form.SubdatasheetExpanded = True, this
works fine. I just can't change the recordsource.

jonathan

TC said:
Ok, I must have misread it.

Say main form frmMain has a subform control sfA containing a (sub)form
frmSub.

frmSub has a subform control sfB containing a (sub)(sub)form frmSubSub
(gak!).

This is how code running in frmMain's code module, can change the
recordsource of frmSubSub:

me![sfA].form![sfB].form.recordsource = ...

You just iterate the pair: <form>, <subform control> for as many levels
as required. At the top level, <form> is Me - a reference to the current
form. In each successive level, <form> is Form - a reference to the form
within the subform control.

My practice is to reference >properties< using the dot syntax - eg. ..Form -
and >user named controls< with the ![...] syntax - eg, ![sfA]

HTH,
TC


jonathan said:
Hi TC,
that will change the recordsource of the subform. I need to go down one more
level to the subforms subform
cheers in advance

TC said:
This will change a subform's recordsource when executed from the main form:

me![sfWhatever].form.recordsource = ...

sfWhatever is the name of the subform control on the main form.

HTH,
TC


Hi, i am trying to change the recordsource of a subforms datasheet based
on
whether a tick box on the main form is ticked or not.
This is my error message: invalid reference to the property form/report.
My forms are:

Main form: job_no subform
Subform: Tasks
subforms Datasheet: frmGroupTasksSubform

Thanks in advance.


Dim frm1 As Form
Dim ctl1 As Control
Dim ctl3 As Control

Dim strNaRecords As String
Dim strAllRecords As String

strNaRecords = "qryGroupTasksNA"
strAllRecords = "qryGroupTasksALL"

Set frm1 = Forms("job_no subform")
Set ctl1 = frm1.tasks
Set ctl3 = ctl1.Form.frmGroupTasksSubform

If Me.HideNASubTasks = True Then
ctl3.Form.RecordSource = strNaRecords
Else
ctl3.Form.RecordSource = strAllRecords
ctl3.Form.Requery

'Clean up objects
Set ctl1 = Nothing
Set ctl3 = Nothing
Set frm1 = Nothing

End If
Click to expand...
Click to expand...
Click to expand...
 
T

TC

jonathan, the error message you are getting, suggests that you are saying:

zzzzzz.form ...

and zzzzzz is not a reference to a subform control - so it does not have a
..Form property.


In your statement:

Me![frmtbltwo].Form![frmtbltree].Form.SubdatasheetExpanded = True

frmtbltwo should be the name of a subform >control< on the main form. Then,
Me![frmtbltwo].Form refers to the form >within< that subform control. In the
syntax used above, frmtbltwo should not be the name of a >form< - unless you
happened to have named the subform >control<, the same as the form.

Post the exact text of the statement that gives you the error. Also say,
what is each thing in that statement. For example, if the statement is
Me![xxx].etc..., say whether xxx is a subform control, a form name, or
whatever

Sorry this is taking so long, but I only get to post, once a day. Also, when
you post the exact line, someone else might jump in with the answer.

Cheers,
TC


jonathan said:
Hi Tc, i tried what sugested but i still get this error message. invalid
reference to the property form/report.
i can go back one level and change the record source of the first subform. I
can also do
Me![frmtbltwo].Form![frmtbltree].Form.SubdatasheetExpanded = True, this
works fine. I just can't change the recordsource.

jonathan

TC said:
Ok, I must have misread it.

Say main form frmMain has a subform control sfA containing a (sub)form
frmSub.

frmSub has a subform control sfB containing a (sub)(sub)form frmSubSub
(gak!).

This is how code running in frmMain's code module, can change the
recordsource of frmSubSub:

me![sfA].form![sfB].form.recordsource = ...

You just iterate the pair: <form>, <subform control> for as many levels
as required. At the top level, <form> is Me - a reference to the current
form. In each successive level, <form> is Form - a reference to the form
within the subform control.

My practice is to reference >properties< using the dot syntax - eg. .Form -
and >user named controls< with the ![...] syntax - eg, ![sfA]

HTH,
TC


jonathan said:
Hi TC,
that will change the recordsource of the subform. I need to go down
Click to expand...
one
more
level to the subforms subform
cheers in advance

This will change a subform's recordsource when executed from the main
form:

me![sfWhatever].form.recordsource = ...

sfWhatever is the name of the subform control on the main form.

HTH,
TC


Hi, i am trying to change the recordsource of a subforms datasheet based
on
whether a tick box on the main form is ticked or not.
This is my error message: invalid reference to the property form/report.
My forms are:

Main form: job_no subform
Subform: Tasks
subforms Datasheet: frmGroupTasksSubform

Thanks in advance.


Dim frm1 As Form
Dim ctl1 As Control
Dim ctl3 As Control

Dim strNaRecords As String
Dim strAllRecords As String

strNaRecords = "qryGroupTasksNA"
strAllRecords = "qryGroupTasksALL"

Set frm1 = Forms("job_no subform")
Set ctl1 = frm1.tasks
Set ctl3 = ctl1.Form.frmGroupTasksSubform

If Me.HideNASubTasks = True Then
ctl3.Form.RecordSource = strNaRecords
Else
ctl3.Form.RecordSource = strAllRecords
ctl3.Form.Requery

'Clean up objects
Set ctl1 = Nothing
Set ctl3 = Nothing
Set frm1 = Nothing

End If
Click to expand...
Click to expand...
Click to expand...
 
J

jonathan

Hi TC,
Sorry its took me so long to reply.
Here is my code:

Dim strqrysubform2 As String
Dim strtbl3 As String
strqrysubform3 = "qrysubform2"
strtbl3 = "tbl3"

If Me.chkchangerecordsource = True Then
Me![subform1].Form![subform2].RecordSource = "strqrysubform2"
Else
Me![subform1].Form![subform2].RecordSource = "strtbl3"
End If

The control names in each parent form is the name of the actuall form. It
drops into debug mode on both the lines that change the recordsource with
the invalid reference to the property messege.
I also saw somewhere that access does not support nested references to
controls on subforms within subforms. If this is the case what other
solutions are there to my problem. On the main form i need a tick box that
when checked changes the record source of my mainform>subform1>subform2.
Could i somehow get the tick box to call an event in subform1 or subform2
that then changes the recordsource, if so how would i do this?
Again, thanks for your help.

TC said:
jonathan, the error message you are getting, suggests that you are saying:

zzzzzz.form ...

and zzzzzz is not a reference to a subform control - so it does not have a
.Form property.


In your statement:

Me![frmtbltwo].Form![frmtbltree].Form.SubdatasheetExpanded = True

frmtbltwo should be the name of a subform >control< on the main form. Then,
Me![frmtbltwo].Form refers to the form >within< that subform control. In the
syntax used above, frmtbltwo should not be the name of a >form< - unless you
happened to have named the subform >control<, the same as the form.

Post the exact text of the statement that gives you the error. Also say,
what is each thing in that statement. For example, if the statement is
Me![xxx].etc..., say whether xxx is a subform control, a form name, or
whatever

Sorry this is taking so long, but I only get to post, once a day. Also, when
you post the exact line, someone else might jump in with the answer.

Cheers,
TC


jonathan said:
Hi Tc, i tried what sugested but i still get this error message. invalid
reference to the property form/report.
i can go back one level and change the record source of the first
Click to expand...
subform.
I
can also do
Me![frmtbltwo].Form![frmtbltree].Form.SubdatasheetExpanded = True, this
works fine. I just can't change the recordsource.

jonathan

TC said:
Ok, I must have misread it.

Say main form frmMain has a subform control sfA containing a (sub)form
frmSub.

frmSub has a subform control sfB containing a (sub)(sub)form frmSubSub
(gak!).

This is how code running in frmMain's code module, can change the
recordsource of frmSubSub:

me![sfA].form![sfB].form.recordsource = ...

You just iterate the pair: <form>, <subform control> for as many levels
as required. At the top level, <form> is Me - a reference to the current
form. In each successive level, <form> is Form - a reference to the form
within the subform control.

My practice is to reference >properties< using the dot syntax - eg. .Form -
and >user named controls< with the ![...] syntax - eg, ![sfA]

HTH,
TC


Hi TC,
that will change the recordsource of the subform. I need to go down one
more
level to the subforms subform
cheers in advance

This will change a subform's recordsource when executed from the main
form:

me![sfWhatever].form.recordsource = ...

sfWhatever is the name of the subform control on the main form.

HTH,
TC


Hi, i am trying to change the recordsource of a subforms datasheet
based
on
whether a tick box on the main form is ticked or not.
This is my error message: invalid reference to the property
form/report.
My forms are:

Main form: job_no subform
Subform: Tasks
subforms Datasheet: frmGroupTasksSubform

Thanks in advance.


Dim frm1 As Form
Dim ctl1 As Control
Dim ctl3 As Control

Dim strNaRecords As String
Dim strAllRecords As String

strNaRecords = "qryGroupTasksNA"
strAllRecords = "qryGroupTasksALL"

Set frm1 = Forms("job_no subform")
Set ctl1 = frm1.tasks
Set ctl3 = ctl1.Form.frmGroupTasksSubform

If Me.HideNASubTasks = True Then
ctl3.Form.RecordSource = strNaRecords
Else
ctl3.Form.RecordSource = strAllRecords
ctl3.Form.Requery

'Clean up objects
Set ctl1 = Nothing
Set ctl3 = Nothing
Set frm1 = Nothing

End If
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

If it's dropping into the debugger, then Access is not recognizing your
structure.

I think I have seen this before (in some of my code). As I recall, I dimmed
a form variable.

dim frm as Form

set frm = forms![subform1].Form![subform2].form
set frm.recordsource = ....

At least this seemed to work for me...

hth,
Andy

jonathan said:
Hi TC,
Sorry its took me so long to reply.
Here is my code:

Dim strqrysubform2 As String
Dim strtbl3 As String
strqrysubform3 = "qrysubform2"
strtbl3 = "tbl3"

If Me.chkchangerecordsource = True Then
Me![subform1].Form![subform2].RecordSource = "strqrysubform2"
Else
Me![subform1].Form![subform2].RecordSource = "strtbl3"
End If

The control names in each parent form is the name of the actuall form. It
drops into debug mode on both the lines that change the recordsource with
the invalid reference to the property messege.
I also saw somewhere that access does not support nested references to
controls on subforms within subforms. If this is the case what other
solutions are there to my problem. On the main form i need a tick box that
when checked changes the record source of my mainform>subform1>subform2.
Could i somehow get the tick box to call an event in subform1 or subform2
that then changes the recordsource, if so how would i do this?
Again, thanks for your help.

TC said:
jonathan, the error message you are getting, suggests that you are saying:

zzzzzz.form ...

and zzzzzz is not a reference to a subform control - so it does not have a
.Form property.


In your statement:

Me![frmtbltwo].Form![frmtbltree].Form.SubdatasheetExpanded = True

frmtbltwo should be the name of a subform >control< on the main form. Then,
Me![frmtbltwo].Form refers to the form >within< that subform control. In the
syntax used above, frmtbltwo should not be the name of a >form< - unless you
happened to have named the subform >control<, the same as the form.

Post the exact text of the statement that gives you the error. Also say,
what is each thing in that statement. For example, if the statement is
Me![xxx].etc..., say whether xxx is a subform control, a form name, or
whatever

Sorry this is taking so long, but I only get to post, once a day. Also, when
you post the exact line, someone else might jump in with the answer.

Cheers,
TC


jonathan said:
Hi Tc, i tried what sugested but i still get this error message. invalid
reference to the property form/report.
i can go back one level and change the record source of the first
Click to expand...
subform.
I
can also do
Me![frmtbltwo].Form![frmtbltree].Form.SubdatasheetExpanded = True, this
works fine. I just can't change the recordsource.

jonathan

Ok, I must have misread it.

Say main form frmMain has a subform control sfA containing a (sub)form
frmSub.

frmSub has a subform control sfB containing a (sub)(sub)form frmSubSub
(gak!).

This is how code running in frmMain's code module, can change the
recordsource of frmSubSub:

me![sfA].form![sfB].form.recordsource = ...

You just iterate the pair: <form>, <subform control> for as many
levels
as required. At the top level, <form> is Me - a reference to the current
form. In each successive level, <form> is Form - a reference to the form
within the subform control.

My practice is to reference >properties< using the dot syntax - eg.
.Form -
and >user named controls< with the ![...] syntax - eg, ![sfA]

HTH,
TC


Hi TC,
that will change the recordsource of the subform. I need to go
Click to expand...
down
one
more
level to the subforms subform
cheers in advance

This will change a subform's recordsource when executed from the main
form:

me![sfWhatever].form.recordsource = ...

sfWhatever is the name of the subform control on the main form.

HTH,
TC


Hi, i am trying to change the recordsource of a subforms datasheet
based
on
whether a tick box on the main form is ticked or not.
This is my error message: invalid reference to the property
form/report.
My forms are:

Main form: job_no subform
Subform: Tasks
subforms Datasheet: frmGroupTasksSubform

Thanks in advance.


Dim frm1 As Form
Dim ctl1 As Control
Dim ctl3 As Control

Dim strNaRecords As String
Dim strAllRecords As String

strNaRecords = "qryGroupTasksNA"
strAllRecords = "qryGroupTasksALL"

Set frm1 = Forms("job_no subform")
Set ctl1 = frm1.tasks
Set ctl3 = ctl1.Form.frmGroupTasksSubform

If Me.HideNASubTasks = True Then
ctl3.Form.RecordSource = strNaRecords
Else
ctl3.Form.RecordSource = strAllRecords
ctl3.Form.Requery

'Clean up objects
Set ctl1 = Nothing
Set ctl3 = Nothing
Set frm1 = Nothing

End If
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 40036 1
Referencing a control on a subform 6
save recordset of subform 2
Error 40036 0
how to find subforms not dirty ... 1
Create a SubForm on a Tab Control 0
How do I move focus to a control in a nested subform ? 2
Attaching subform(Datasheet) to a subform(Single Form) 1

Top