asked on

Access - open form from another form - link based on subform criteria

I have an item form and I want to add a button that will open my order form and show all orders that have an order line uninvoiced for that item.

The order form shows the order header with the order lines as a subform. It gets opened from loads of other buttons on other forms - for example to show all orders for a customer. To do this I use "DoCmd.OpenForm stDocName, , , stLinkCriteria". This has always meant setting a simple string eg. stLinkCriteria = "[Invoice.Customer_ID]=" & Me![Customer_ID].

This time though I need to be able to read the order line records to match on the item - can't use the header record. So would need something like the code SEE attached.

My question is I know that you have to get rid of the where statement in the sql - but how? Can you do this sort of doCmd open where you have a join statement? I've tried to run the code and I get a syntax error in the sql - not sure if its because of the "WHERE" something else.

I would be great if you could. Otherwise I will have to somehow change the recordsource on the opening order form by checking where is has been opened from. The way above seems more straightforward.

Thanks Lou

Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intnewrec As Integer
    
    stDocName = "Order"
    
      stLinkCriteria = "SELECT Order.Order_ID, Order_Line.Item_No" & _
      " FROM [Order] INNER JOIN (Item INNER JOIN Order_Line ON Item.Item_No = Order_Line.Item_No) ON Order.Order_ID = Order_Line.Order_ID" & _
      " WHERE Order_Line.Item_No = '" & Me!Item_No & "'"
     
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Call NoRecords(Forms!Order, intnewrec)
    
    If intnewrec = True Then
    DoCmd.Close acForm, "Order", acSaveYes
    GoTo Exit_My_Sub
    End If
 
        Forms![Order].AllowAdditions = False

Open in new window

rockiroads

stLinKCriteria is there to have the where clause only for the called form

so if your called form (stDocName) is based on Order and Order_line for example (which by the way is a reserved word) then you do this

stLinkCriteria = "Order_Line.Item_No = '" & Me!Item_No & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

If your item number is numeric, you do not need quotes

stLinkCriteria = "Order_Line.Item_No = " & Me!Item_No

rockiroads

Regarding 2nd part of your logic

Call NoRecords(Forms!Order, intnewrec)

If intnewrec = True Then
DoCmd.Close acForm, "Order", acSaveYes
GoTo Exit_My_Sub
End If

Forms![Order].AllowAdditions = False

I think you would be better off putting this logic in the "Order" form.
and are you passing intnewrec by reference so using the return value in that? Surely it would be better to ensure NoRecords is a function which returns a boolean

Louverril

ASKER

rockiroads.

The order-line subform is actually called Item_order_line I have tried
stLinkCriteria = "Order!Item_Order_Line.Item_No = '" & Me!Item_No & "'"
and
stLinkCriteria = "Item_Order_Line.Item_No = '" & Me!Item_No & "'"

but neither recognises the sub form item number - (Item_Order_Line.Item_No).

Are you sure you can open a form via criteria which only exist on the subform? Remember the item number only appears on the order line subform.

This sort of works (SEE BELOW ) in that the order form is opened but the wrong data is displayed - just displays the first order. Anyway I'd rather use the basic strlinkctieria and avoid having to reprogram due to a recordsource change.

Lou

 stLinkCriteria = "SELECT Order.Order_ID, Order_Line.Item_No" & _
'      " FROM [Order] INNER JOIN (Item INNER JOIN Order_Line ON Item.Item_No = Order_Line.Item_No) ON Order.Order_ID = Order_Line.Order_ID" & _
'      " WHERE Order_Line.Item_No = '" & Me!Item_No & "'"
'
'    DoCmd.OpenForm "Order", acNormal
'    Forms!Order.RecordSource = stLinkCriteria

Open in new window

rockiroads

ok, we are talking about a subform.
So Im alittle confused here now

Where are you running this code from, the main form or the subform?

Or are you saying you want a subform that shows entries for the selected order on the main form? If the latter you can achieve this without any coding whatsoever.

Your form "Order", what is the recordsource that you have defined for it?

Louverril

ASKER

No what I am saying is I have a button on the Item form which I want to open the order form.
The order form consists of a header section for the comon order details - customer, address etc

Louverril

ASKER

Rockiroads,

Ignore above - it got posted accidentally.

What I tried to explain at the beginning

" I have an item form and I want to add a button that will open my order form and show all orders that have an order line uninvoiced for that item.
The order form shows the order header with the order lines as a subform. It gets opened from loads of other buttons on other forms - for example to show all orders for a customer. To do this I use "DoCmd.OpenForm stDocName, , , stLinkCriteria". This has always meant setting a simple string eg. stLinkCriteria = "[Invoice.Customer_ID]=" & Me![Customer_ID]. This time though I need to be able to read the order line records to match on the item "

I other words I have an item form and I want to put a button on that which opens the order form. Simple.
However I only want those orders to appear which have an orderline matching the item on my item screen.

Lou

rockiroads

this called form, what is the rowsource defined as? this is what you need to filter

I would assume this called form with a subform already has the relationship defined

Do you want to filter on order_id or item_no? whatever field you want to filter on, is it available in the rowsource?

I know what you are saying, it can be done, but need to understand your setup

Louverril

ASKER

The data source for the called (order) form is a query based on the order table and the customer table.
The data source for the subform in the called form is a query linking the order lne and item.
The subform is linked to the main order form via the order number.

I want to filter by item - hence the complication.

Lou

ASKER CERTIFIED SOLUTION

Louverril

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

ok, no probs