record source

[pero1970]

hi

i have frmMain which contains subfrm1 and subfrm2. i'd like to change recordsource of subfrm2 from subform1's event. i tried several ways to do it but i didn't succeed. if i try to do it from some frmMain event, there is no problem (me.subfrm2.form.RecordSource = "SELECT ..." and it's working).

here is what i tried to do:

1. forms!frmMain!subfrm2.RecordSource = "SELECT ...."
2. forms!frmMain.subfrm2.RecordSource = "SELECT ...."
3. forms!frmMain!subfrm2.Form.RecordSource = "SELECT ...."
4. forms!frmMain.subfrm2.Form.RecordSource = "SELECT ...."

if someone could help me about this, i'd appreciate it. thanks in advance !

 

[Alisa]

Is subform 2 *on* subform 1, or are there two independent subforms on the main form? If the latter, then forms!frmMain!subfrm2.Form.Recordsource= . . . should work. But if subform 2 is on subform 1 then it should be Me!subfrm2.Form.Recordsource= . . .

 

[pero1970]

subfrm1 & 2 are independent subforms on frmMain

as u could see in my post, i tried your way but it didn't work...

as i change record on subfrm1, the recordsource of subfrm2 should change according to change of id in selected record on subfrm1, that's why i need this

 

[Alisa]

what about it didn't work?

 

[GolferGuy]

You could also try Me.Parent.subfrm2.form.recordsource = "SELECT ...."
Plus, if you could tell us what is the error message you are getting, that would help. If there is no error message, then what is happening? No records appear, or what? By the way, are the Child/Master fields the same with this different record source?

 

[pero1970]

Me.Parent.subfrm2.form.recordsource = "SELECT ...." will not work.

there is frmMain, and subfrm1 and subfrm2 are placed on frmMain. the point is: as i change record on frmMain (OnCurrent event), subfrm1 must show related records on frmMain, and when i select or change record on subfrm1 (OnCurrent event), subfrm2 must show related records on subfrm1. so, when i change record on subfrm1 i have to reference subfrm2 and change its recordsource depending on id selected on subfrm1.

i tried to reference it using one of the code that you could see in my first post, but i always get an error.

if i reference it by (from subfrm1, oncurrent event):
Forms!frmMain.subfrm2.Form.RecordSource = "SELECT ...."
and run frmMain i get the following error:
'You entered an expression that has an invalid reference to the property Form/Report.'

if i write (from subfrm1, oncurrent event):
Forms!frmMain!subfrm2.SetFocus
and run frmMain, it sets focus on subfrm2

 

[GolferGuy]

When I have something like what you are dealing with, that is a subform that is not directly tied to the main form, I put some hidden, Master fields on the main form that are filled in by actions within subform1. For example, with the main form having customer data, subform1 having order data, and subform2 having order detail data. So, subform1 can show all the orders for the customer showing on the main form, but something needs to be done for subform 2 because it is not a subform to subform1. That is why, I assume, you have to change the RecordSource for subform2. But, if you make a hidden field on the mainform, and (for my example) the field is named mainOrderNumber. Then, within the subform control for subfrm2 you can set the Master/Child fields to use the CustomerID from the main form and mainOrderNumber as Master fields, then the customerID and OrderNumberID from the subform as the child fields.
Of course, within subform1, I would think in the OnCurrent event, you would write the OrderNumber to Me.Parent!mainOrderNumber. And this would then give you the desired detail records in subform2 that reflect what has been choosen in subform1.