Open a form based on the value in the first forms subform

[mbannen]

FORM # 1
I have a main form (FRM_AUDIT_) that contains a sub form (SUBFORM_CLAIM) that has a value (claim_folder_id).

I want users to click on the claim_folder_id to open another form (FRM_AUDIT_CLAIM) where the claim_folder_id on form #1's sub form (SUBFORM_CLAIM) is equal to the folder id (audit_claim_folder_id) on the second form #'s sub, sub-form [SUBFORM_AUDIT_ITEM_DETAIL).

Sooo....I added an openform macro on the double-click event and set the following as the 'WHERE CONDITION'.

Forms![FRM_AUDIT]![SUBFORM_CLAIM].Form![claim_folder_id]
=
Forms![FRM_AUDIT_CLAIM]![SUBFORM_AUDIT_CLAIM_ITEM].Form![SUBFORM_AUDIT_ITEM_DETAIL].Form![audit_claim_folder_id]

But it doesn't work...it opens to form but doesn't open to the records w/the same value of the clicked on folder id from form #1.

I haven't done Access stuff in years...I know it's possible to open another form and filter it based on the value in the current field, but don't remember how. PLEASE HELP!!!

 

[MakeItSo]

Just try
"[claim_folder_id] = Forms![FRM_AUDIT_CLAIM]![SUBFORM_AUDIT_CLAIM_ITEM]![SUBFORM_AUDIT_ITEM_DETAIL]![audit_claim_folder_id]"

BTW: You have a subform on a subform? Ever thought about restructuring the form so you don't have to nest it up?

If the above doesn't work, you could also use some code instead of a macro:

DoCmd.OpenForm "FRM_AUDIT", acNormal, "", "[claim_folder_id] = " & Me![audit_claim_folder_id]

BEWARE
You never open a subform, always the parent form!
Thus: The claim folder ID must be part of the underlying query for the form.


Regards,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[mbannen]

When I tried the above I get prompted for both claim_folder_id and for

Forms!FRM_AUDIT_CLAIM!SUBFORM_AUDIT_CLAIM_ITEM!SUBFORM_AUDIT_ITEM_DETAIL!audit_claim_folder_id


ADDITIONAL INFO
I should mentioned that the subform on FORM 1 is displayed as continuous forms and thus has multiple folder ID's listed. I am try to open another form where a value on the sub-sub form of the second form = the folder id clicked on in the first form.

Help is greatly appreciated...this is my last, big task.