Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113

    how to get the results of a query into a subform


    Hello, my problem is as follows: I have a query which finds the parts which are under reorder level (PartNumber) and calculates the amount to reorder (QtytoOrder) by supplier. Now I would like to run this query from a form that generates the purchase order after selecting the supplier . This form contains a subform that has the fields PartNumber and QtytoOrder that are based on a table PurchaseOrderDetails. Now ideally I would like to populate these fields with the results from the query and generate the purchase order automatically. Is this possible?? I don't know how to do this any advice that you could give me will be appreciated. Thank you for your help.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    either set the subform SOURCEobject to:
    a form that is bound to your new query, frmNewForm
    or
    directly to the query, query.MyQuery

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Do you have a button on the form to "generate" the purchase order? I think you simply need to use dlookups to get your values from your query, so in some event on your form you will need to add something like this:

    Me.yoursubformname.form.controls("PartNumber")=dlo okup("[PartNumber]","[YourQueryName]","[Suplier] = """ & [Supplier] & """")


    Me.yoursubformname.form.controls("QtytoOrder ")=dlookup("[QtytoOrder ]","[YourQueryName]","[Suplier] = """ & [Supplier] & """")

    Cheers,
    Vlad

  4. #4
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    Thank you to both of you. I have tried your ideas but unfortunately I don't seem to be able to make them work. I made the query the control source for the subform. When I opened the form that contains the subform, the subform is blank. The subform by itself shows some results. So I don't know what is wrong. With the DLookup I get an error in that It doesn't find the subform. I checked all the spelling etc and it looks ok. So maybe I am not addressing correctly to the subform from the main form. Well I will keep trying.

  5. #5
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Me.yoursubformname.form.controls("PartNumber")=dlookup("[PartNumber]","[YourQueryName]","[Suplier] = """ & [Supplier] & """")
    This statement may be confusing you. The name highlighted in red is not the name of the subform; it is the name of the main form control that contains the subform.

    When I opened the form that contains the subform, the subform is blank.
    How are you linking the main form and the subform, i.e. what field(s) are you using? Are you using a combo box on the mainform?

  6. #6
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    Thank you John_G, you are correct I was confused with the red statement, now I changed it to the control from the main form but still gives me an error. The Main form has a combo box which selects the supplier. Then In the supplier_Afterupdate Event
    I have this code
    Code:
    Me.PurchaseOrderTab.Form.Controls("fkProductID")= DLookup("[fkProductID]", "[Query1]","[SupplierID]="&Me.SupplierID
    The subform is inside the tab control. The Linking fields are PurchaseOrderID Master, and fkPurchaseOrderID Child. The error that it gives me highlights the word Form right before control and says "Method or data member not found". Thank you for your help

  7. #7
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The subform is inside the tab control.
    A tab control is a visual convenience. The controls in a tab are still a part of the form they are in, and are referred to as such. To refer to a subform that happens to be on a tab, you just use the name of the subform control, without referring to the tab.

    I'm not sure you DLookup is doing what you want - in Query1, is there only going to be one record that meets your criteria of "[SupplierID]=" & Me.SupplierID? If there is more than one, the DLookup will return the [fkProductID] from the first record it finds that matches - a DLookup will always return only one value (or Null if no records match).

  8. #8
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    You are correct again. The query finds all the partnumbers which are below the reorder level and calculates the amount to be reorder. Ideally I would like to bring all the partnumbers that need to be reordered into the form, and then print the purchase order. Unfortunately the query is not updatable because of the calculated field. So how can I bring the results of the query into the form?? Thanks for your help.

  9. #9
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I'm going to back up a few steps.

    The Linking fields are PurchaseOrderID Master, and fkPurchaseOrderID Child.
    I don't think that is the correct field to use. You are selecting a supplier from a combo box, and you want to see the records in Query1 that match the supplier ID - is that correct?

    If so, all you need to do is change the form - subform linking fields to SupplierID and it should work. You don't need the statement with the DLookup; in fact it is wrong to have it, because you don't want to change anything in Query1, which is what that statement does (or tries to do)

    You are going to need an Orders table that is populated by the main form, with order number, supplier, date, etc.

    To generate the purchase order, design a report that looks like your purchase orders. It will be similar to your form/subform, i.e. with a sub-report. The main report would contain the supplier data, date, order number, etc. . The subreport could have Query1 as its record source (just like the subform does); the main report and the subreport would be linked on supplierID, just as the form/subform are.

    You might also be able to do it without a subreport, by putting all the data from the orders table and Query1 into another query and then filtering that query for a specific order number to generate the report (i.e. purchase order).

    That's a lot to chew on, I know - so first work on getting your form/subform to show you the correct data, and go from there. Post back if you need more assistance.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Would it be possible to upload a sample db with just the form, the empty tables or with a couple rows of sample data and your query that calculates the quantities? I think you might need to run an update query if you want to modify multiple records (for each part number). Do the records exist in the PurchaseOrderDetails table or do they need to be created (in which case you need an append query).

    Anyway, a sample db would help us help you.

    Cheers,
    Vlad

  11. #11
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    I tried changing the linking fields to SupplierID and as you predicted it worked in the right direction. Now I think Vlad is correct in that I need to Append or Update the data on the tables. I have two tables One is 'PurchaseOrdersDetails" and the other is "PurchaseOrders" Both tables need updating from the results found by query1 which finds the PartsBelowOrderLevel. Should I make two append queries with query1 as the source. Is there a way of linking two append queries? Can I make an Update query that updates two tables at the same time? thank you for your help.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You don't need to link the two append queries, you just need to run them in the right sequence. First you add a new purchase order record in PurchaseOrders for the selected supplier. In there I assume you put the supplier id, PO date, PO number, etc. I also assume that you link the PurchaseOrdersDetails to PurchaseOrders via the PO number in the last table. Once you get the record in for the PO you now have all the elements needed to run the second append query (part numbers, quantities to order, etc.) into the PurchaseOrdersDetails. Getting the PO number depends on your table structure and that is why I was asking for a sample or some pics at least. If your PO Number (Primary Key) in the PurchaseOrders is an autonumber you would use a dmax("[PONUM]","[PurchaseOrders]") in your second one to add the foreign key. If your PO number is a custom one (such as a string "PO0012345") you will need to use a dlookup to extract it from the table from the most recently added record (which in your cse will be the one added by the first append query).

    Hope this helps you to get going on the right path.

    Cheers,
    Vlad

  13. #13
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    I have been trying to make the first append query:
    Code:
    INSERT INTO PurchaseOrder ( PurchaseOrderID, fkSupplierID, PurchaseOrderNumber )
    SELECT PurchaseOrderDetails.fkPurchaseOrderID, PurchaseOrder.fkSupplierID, DMax("PurchaseOrderNumber","PurchaseOrder")+1 AS PurchaseOrderNumber
    FROM PurchaseOrder INNER JOIN PurchaseOrderDetails ON PurchaseOrder.PurchaseOrderID = PurchaseOrderDetails.fkPurchaseOrderID
    WHERE (((PurchaseOrder.fkSupplierID) Like "*" & [Forms]![SupplierBoxForm]![CboSupplier] & "*"));
    I made a small form called SupplierBoxForm where I can select the supplier, then I made a button to run the above mentioned Append query. unfortunately It doesn't add any new records to the table PurchaseOrders. My table fields are:
    PurchaseOrderID (Primary Key), PurchaseOrderNumber, fkSupplierID, fkEmployeeID, PaymentMethod, and PONotes , then the PurchaseOrderDetails table contains:ID (Primary Key) autonumber, fkPurchaseOrderID, fkProductID, PODate,QtyOrdered, OurCost, RecivedDate. The two tables are joined by PurchaseOrderID(PurchaseOrder Table) to fkPurchaseOrderID (purchaseOrderDetails Table).What am I doing wrong?? Thanks for your help

  14. #14
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    I did include the fkPurchaseOrder field from table PurchaseOrderDetails, and now wants to insert 9 records instead of just one... What's going on??

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It is getting difficult to follow what you are doing there. Can you strip all the info from your db and upload a sample so we can see what are you trying to do? Or start with an empty new Access file and import only the objects in question (your forms, queries, etc.) and for the two (or more) tables involved select to import the structure only so they come in empty.

    Without seeing your database I would say that you are not really following what I was trying to explain in my previous post. In your first append query there is no need for PurchaseOrderDetails table. You want to add one record, get the PurchaseOrderID and then use that (as dmax) in a second append query based on your query1 (the one that calculates the details of the order).

    Cheers,
    Vlad

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-28-2015, 10:48 AM
  2. Replies: 5
    Last Post: 11-12-2014, 04:36 PM
  3. Replies: 1
    Last Post: 07-20-2012, 09:48 AM
  4. Query results in subform
    By MWMike in forum Forms
    Replies: 5
    Last Post: 09-28-2010, 05:19 PM
  5. Replies: 0
    Last Post: 03-31-2010, 07:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums