Open a new form based on form but has a subform (1 Viewer)

[Number11]

I have a form created called "View_Orders" it has a subform called "Order", I have added a button to open a new form but its not working getting error saying it cant find "ORDER_ACCOUNT_NO" referred to in your expressions. guessing its because the field is within the sub form


DoCmd.OpenForm "New Form", acNormal, , "[Order_Number]=" & Me![ORDER_ACCOUNT_NO]

How would I go about changing the form to look at "View_Orders" sub form?

 

[arnelgp]

dont you think "New Form" there should be "View_Orders"?

 

[Number11]

dont you think "New Form" there should be "View_Orders"?

Sorry yes thats correct still getting same error message

DoCmd.OpenForm "View_Orders", acNormal, , "[Order_Number]=" & Me![ORDER_ACCOUNT_NO]
think the issue is with the & Me![ORDER_ACCOUNT_NO] as this is not on the Order form is on the sub form called Picking

 

[arnelgp]

what you can do is pass your Criteria as OpenArgs:

DoCmd.OpenForm FormName:="View_Orders", View:=acNormal, OpenArgs:="[Order_Number]=" & Me![ORDER_ACCOUNT_NO]


before you run the code, open "Order" subform in design view.
add code to it's Load Event:

Private Sub Form_Load()
    Dim sFilter As String
    sFilter = Me.Parent.OpenArgs & ""
    If Len(sFilter) Then
        Me.Filter = sFilter
        Me.FilterOn = True
    End If
End Sub

 

[Minty]

If [Order_Account_Number] is on a subform you need to identify it correctly

Me.YoursubformCONTROL_NAME.Form.[Order_Account_Number]

Bookmark this page:

Forms: Refer to Form and Subform properties and controls

 

[Number11]

If [Order_Account_Number] is on a subform you need to identify it correctly

Me.YoursubformCONTROL_NAME.Form.[Order_Account_Number]

Bookmark this page:

Forms: Refer to Form and Subform properties and controls

so like this?

DoCmd.OpenForm FormName:="View_Orders", View:=acNormal, OpenArgs:="[Order_Number]="Me.Order.[Order_Account_Number]

 

[Minty]

No, you have mixed up my post and Arnelgp's suggestion.
Do one or the other, not both.

Mine will only work if the value you need is on a subform on the form where the button is pressed.

 

[Number11]

Oh i see your code is also on load?

 

[arnelgp]

describe your First form (where you use DoCmd.OpenForm), does it has a subform? what is the name of the subform?
my code will filter the (subform) of the Form you are trying to open.

 

[Number11]

describe your First form (where you use DoCmd.OpenForm), does it has a subform? what is the name of the subform?
my code will filter the (subform) of the Form you are trying to open.

The main form is called "View_Orders" it has a subform called "Orders" with one of the fields being called "ORDER_ACCOUNT_NO"

which form do i put the code Load Event?

 

[arnelgp]

The main form is called "View_Orders"

based on post#1, you want to Open another Instance of the same form?

 

[Number11]

based on post#1, you want to Open another Instance of the same form?

Sorry yes the other form is called "DATA_VIEW" this form has the field "Order_Number"

 

[arnelgp]

so Minty's suggestion will work for you:

DoCmd.OpenForm FormName:="DATA_VIEW", View:=acNormal, WhereCondition:="[Order_Number]=" & Forms!View_Orders!Orders.Form!Order_Account_Number

 

[Number11]

so Minty's suggestion will work for you:

DoCmd.OpenForm FormName:="DATA_VIEW", View:=acNormal, WhereCondition:="[Order_Number]=" & Forms!View_Orders!Orders.Form!Order_Account_Number

Thanks for your help its not working getting this..

 

[Minty]

With 500+ posts you should know that you need to post up the code you have used, so we might be able to see what you have not got right?