set recordsource of the subform from the parent form

  • Thread starter Thread starter Jerry Qu
  • Start date Start date
J

Jerry Qu

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
 
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
 
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)?
 
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
 
Steve:

run time error'2455':

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

thanks

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
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.
 
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
 
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.
 
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,

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.
 
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.
 
Back
Top