?
Solved

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

Posted on 2009-04-21
10
Medium Priority
?
915 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Louverril
  • 5
  • 5
10 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24195490
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24195514
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
0
 

Author Comment

by:Louverril
ID: 24196031
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24196665
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?
0
 

Author Comment

by:Louverril
ID: 24196725
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
0
 

Author Comment

by:Louverril
ID: 24196752
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24199373
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
0
 

Author Comment

by:Louverril
ID: 24204506
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
0
 

Accepted Solution

by:
Louverril earned 0 total points
ID: 24204911
This is the solution

 stLinkCriteria = "Order_ID IN(SELECT Order_ID " & _
    " FROM Order_Line " & _
    " WHERE Item_No = " & Me.Item_No & ")"
   
I was on the right road but was missing the IN sql feature.

Rockiroads - thanks for trying.

Lou
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24205025
ok, no probs
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question