set recordsource of the subform from the parent form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerry Qu - 03 Jan 2005 17:50 GMT

Hi All,

thanks for all the previous helps

now I am trying to set recordsource for a subform from the main form.

I got error 2455, when I try:

Me.ChildForm.Form.RecordSource = strSQL

I also got error when I try to call a public sub of subform from the main
form

Me.Childform.form.SubName

please advise

TIA

Jerry

Reply to this Message

PC Datasheet - 03 Jan 2005 18:39 GMT

Open your main form in design view, select the subform control, open
properties, go to the Other tab and note the name of the subform control.
Use the following code to set recordsource for a subform from the main form:
Me!NameOfSubformControl.Form.Recordsource = strSQL

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> Hi All,
>
[quoted text clipped - 16 lines]
>
> Jerry

Reply to this Message

Steve Schapel - 03 Jan 2005 18:41 GMT

Jerry,

Is "ChildForm" the correct name of the subform control on the main form
(this may or may not be the name of the form used for the subform)?

Signature

Steve Schapel, Microsoft Access MVP

> Hi All,
>
[quoted text clipped - 16 lines]
>
> Jerry

Reply to this Message

Jerry Qu - 03 Jan 2005 18:57 GMT

it is the name of the subform control on the main form,
not the name of the form used for the subform

but I get error "2455"

THE CODES:

there are two subform in the main form. Child1 and Child2 as the subform
control names.

in subform Child1:

Private Sub Form_Current()

Forms!frmMain.fillDetail Me.PPID

End Sub

in the main form:

Public Sub fillDetail(PPID As Long)

strSQL = "select * ...where ID=" & PPID

Me!Child2.Form.RecordSource = strSQL -> error "2455"
End Sub

TIA

Jerry

> Jerry,
>
[quoted text clipped - 24 lines]
> >
> > Jerry

Reply to this Message

Steve Schapel - 03 Jan 2005 19:08 GMT

Jerry

What is error 2455?

Signature

Steve Schapel, Microsoft Access MVP

> it is the name of the subform control on the main form,
> not the name of the form used for the subform
[quoted text clipped - 58 lines]
>>>
>>>Jerry

Reply to this Message

Jerry Qu - 03 Jan 2005 19:26 GMT

Steve:

run time error'2455':

you enter an expresion that has a invalid refernece to the property
form/report

thanks

Jerry

> Jerry
>
[quoted text clipped - 65 lines]
> >>>
> >>>Jerry

Reply to this Message

Rob Oldfield - 03 Jan 2005 20:02 GMT

I think it's down to the order that Access is loading the controls. I've
just set up the same thing and am also getting the same error. Though if I
just continue after the error then everything starts working fine after the
form is loaded. The way round it is, I think, to leave the source object of
Child1 blank, and set it in the load event of the main form, then it works.

Think the way that Jerry has it, it's attempting to set the recordsource of
Child2 before it has actually loaded Child2.

I actually already had much the same setup going from a thread yesterday...
"Set access filter"... which does the same thing in a different way.

> Steve:
>
[quoted text clipped - 76 lines]
> > >>>
> > >>>Jerry

Reply to this Message

Jerry Qu - 03 Jan 2005 20:33 GMT

thanks

I tryed to put some in the load event of Child2 but it never acted

in child1:

in subform Child1:

Private Sub Form_Current()
Me.Parent.fillDetail Me.PPID

end Sub

in the main form:

Public flag as ....

private Sub Form_load()
flag = 0
end sub

Public Sub fillDetail(PPID As Long)

if flag = 1
strSQL = "select * ...where ID=" & PPID

Me!Child2.Form.RecordSource = strSQL

end if

End Sub

Public sub initDetail()
flag = 1
Me!Child2.Form.RecordSource = "select *"
end sub

in the Child2 form:

private sub form_load()
Me.parent.initDetail
end sub

the load event for Child2 never fired and the initdetail sub never called

Please advise

TIA

Jerry

> I think it's down to the order that Access is loading the controls. I've
> just set up the same thing and am also getting the same error. Though if I
[quoted text clipped - 90 lines]
> > > >>>
> > > >>>Jerry

Reply to this Message

Rob Oldfield - 03 Jan 2005 21:00 GMT

As Steve says, you're making things way too complicated. Take a look at the
"Set access filter" thread which has a way of doing it from me that uses
about one line of code, and another from Albert Kallal which uses none.

> thanks
>
[quoted text clipped - 149 lines]
> > > > >>>
> > > > >>>Jerry

Reply to this Message

Steve Schapel - 03 Jan 2005 20:36 GMT

Jerry,

I can't immediately put my finger on exactly why it's not working.
There appears to be an error n your code where you have...
Forms!frmMain.fillDetail Me.PPID
... shouldn't this be...
Forms!frmMain.fillDetail(Me.PPID)
And also, you are changing the names of your subforms... in your first
post it was called ChildForm and now it's called Child2, so what's going
on with this?

But in the end, you are doing something a bit odd, in my experience. In
one subform, you are trying to call a main form routine which refers
back to a control value from the calling subform and then manipulates a
property of the other subform as a result. So, let's just say I am not
surprised that it doesn't work.

And after all that, it looks like the whole purpose of this is to have
the second subform's records relate to the current record in the first
subform. Am I correct? If so, it would be preferable to simply do this
via the Link Child Fields and Link Master Fields properties of the
second subform, no code at all required.

Signature

Steve Schapel, Microsoft Access MVP

> Steve:
>
[quoted text clipped - 6 lines]
>
> Jerry

Reply to this Message

Jerry Qu - 03 Jan 2005 20:46 GMT

you are right.

but how to link a subform to another subform without bund the main form?

what I am trying to do is very straight forward.

there are two subform both in datasheet view and modifiable

the first show the order
the second show the orderdetail

when select different line in the order data subform

the orderdetail subform will show related orderdetails.

please suggest a better way

TIA

Jerry

> Jerry,
>
[quoted text clipped - 32 lines]
> >
> > Jerry

Reply to this Message

Steve Schapel - 03 Jan 2005 20:59 GMT

Jerry,

If you wanted to use code, you could untangle what you have been trying,
and simply put this for the Current event of the Order subform...
Me.Parent!Child2.Form.RecordSource = "SELECT * ...WHERE ID=" & Me.PPID
That's it.

However, do it like this. On the main form put an unbound textbox,
let's say it is named PPRef, with its Control Source set to...
=Child1!PPID
Then set the second subform's LinkChildFields property to ID and its
LinkMasterFields property to PPRef.

Signature

Steve Schapel, Microsoft Access MVP

> you are right.
>
[quoted text clipped - 16 lines]
>
> Jerry

Reply to this Message