Referencing a subform controls from another subform

[mcclunyboy]

Hi,

I've tried searching for this and am struggling a bit...

I have a form with four unlinked sub forms on - I am hoping to 1) add a value from one subform to another and more importantly 2) update 1 subform when the other is amended...how exactly do I reference subform 2 from subform1.

I have tried me.parent!form2 and forms!mainform!form2.requery etc but can't get it right

 

[Steve R.]

Take a look at this website.
http://access.mvps.org/access/forms/frm0031.htm

 

[mcclunyboy]

Thanks but I have already been looking at this page, it led me to try two things - both of which report Access cannot find the reference to SUBFORM2

So this is all happening on the SUBFORM1 exit both SUBFORM1 and SUBFORM2 exist on FORM1

Me.Parent!FORM1.SUBFORM2.requery

Forms!form1!Subform1.Form.Requery

I know it is a matter of finding the right reference but I have tried everything I can see...should I give up and just use a button or something on the main form to requery the other sub-form?

 

[JHB]

If all subforms are placed on the mainform and not nested into each other, then:

Me.Parent.YourSubFormsName.Requery

YourSubFormsName is the name coming from property "Name" when the subform is marked in designview, it can be different from the form's real name.

 

[mcclunyboy]

I have tried that one too

It returns the message "The System can't find the field'|1' referred to in your expression."

The code is now being executed on a button click from within 1 sub form, the exact code is:

Me.Parent.[TESTFORM_SUBFRM_NEW_DETAIL].Requery

 

[JHB]

Hmm - could you post a stripped version of your database with some sample data + name of the form you have problem in?

 

[Steve R.]

The two examples below are executed from the main form and refresh a subform. Child0 references the control holding the subform. It does not reference the name of the subform itself.

Me.Child0.Requery

In the example below, I do not recall why I switched from "requery" to "refresh". Possibly because one or more fields were being changed on the main form itself.

Rem Forms("mailmergeStartfrm").Controls("child0").Requery
Forms("MailMergeStartfrm").Refresh

 

[mcclunyboy]

In the end I just added "Me.SUBFORM.requery" to the on-exit event.

I have a follow up question, the whole form is basically trying to simplify and speed up invoice production. The purpose of this form is to try an streamline order production from suppliers.

-> I have a DDL allowing the user to select supplier (or a subform to enter a new one) (customer table)
-> then I have another subform to add the new products to the database (product table)
-> then I have another subform which begins to create the order (using the supplierwhich was selected (this is basically the order table)
-> Finally I want to add the products to the invoice automatically after they have been added - (order detail table) - is there a way to do this?

At the moment the user has to select the products from a DDL however if I could make it that they are added when entered into the database it would be much better.

Thanks, I hope that makes sense.