Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47

    Unanswered: Subform Combo-box Selection - need advice please

    I am creating a purchase order form.

    On the main form you select the supplier you are placing the Order upon, and then you fill in the required items wanted via a subform.

    The Subform allows a Qty to be entered and then allows a Product to be selected from a Combo-box. The combo-box selects on the Products Table, displaying product name.

    How do I limit the products displayed in the combo-box to those only supplied by the Supplier selected in the main form?

    I dont know anything about SQL but is it possible to get a WHERE statement into the combo-box selection, so that the supplierID on the product file must equal the supplierID selected on the main form?

    Any help suggestions would be appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Subform Combo-box Selection - need advice please

    Originally posted by Anduril
    I am creating a purchase order form.

    On the main form you select the supplier you are placing the Order upon, and then you fill in the required items wanted via a subform.

    The Subform allows a Qty to be entered and then allows a Product to be selected from a Combo-box. The combo-box selects on the Products Table, displaying product name.

    How do I limit the products displayed in the combo-box to those only supplied by the Supplier selected in the main form?

    I dont know anything about SQL but is it possible to get a WHERE statement into the combo-box selection, so that the supplierID on the product file must equal the supplierID selected on the main form?

    Any help suggestions would be appreciated.

    Thanks
    In the query of the Combo box use an criteria based in the supplier from the Main Form.
    Saludos
    Norberto

  3. #3
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    My selection at the moment is:

    SELECT [Products].[ProdID], [Products].[ProdName], [Products].[SuppProdRef], [Products].[BuyPrice] FROM Products;

    How/where do I place the necessary selection?

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by Anduril
    My selection at the moment is:

    SELECT [Products].[ProdID], [Products].[ProdName], [Products].[SuppProdRef], [Products].[BuyPrice] FROM Products;

    How/where do I place the necessary selection?
    The first question is if you have in the table Products the field supplier ???
    Saludos
    Norberto

  5. #5
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    Yes SuppID is on the product file, as all products are assigned a supplier at creation.


  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by Anduril
    Yes SuppID is on the product file, as all products are assigned a supplier at creation.

    The second question is were in main form you select the supplier ???
    Saludos
    Norberto

  7. #7
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    Supplier is selected from a combo-box on the main form, the selection is:

    SELECT [Suppliers].[SuppID], [Suppliers].[SuppName] FROM Suppliers;

  8. #8
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by Anduril
    Supplier is selected from a combo-box on the main form, the selection is:

    SELECT [Suppliers].[SuppID], [Suppliers].[SuppName] FROM Suppliers;
    Give me the name of that combo box !!!!!
    Saludos
    Norberto

  9. #9
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    Combo-box name SuppID

  10. #10
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by Anduril
    Combo-box name SuppID
    Ok make this is Select:

    SELECT [Products].[ProdID], [Products].[ProdName], [Products].[SuppProdRef], [Products].[BuyPrice], [Products].[SuppID] FROM Products WHERE [SuppID] = [Forms]![YourMainForm]![SuppID].Column(0);

    Change [YourMainForm] for the name of your main form an then prube
    Saludos
    Norberto

  11. #11
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    The Main Form is called Purchase Orders.

    The statement now reads:

    SELECT [Products].[ProdID], [Products].[ProdName], [Products].[SuppProdRef], [Products].[BuyPrice], [Products].[SuppID] FROM Products WHERE [SuppID]=[Forms]![Purchase Orders]![SuppID].Column(0);

    However, no products are displayed at all now

    On the Supplier Select

    SELECT [Suppliers].[SuppID], [Suppliers].[SuppName] FROM Suppliers;

    I have the colum widths set to 0cm and 4cm so that only the SuppName is displayed, will this affect the Products statement?

  12. #12
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by Anduril
    The Main Form is called Purchase Orders.

    The statement now reads:

    SELECT [Products].[ProdID], [Products].[ProdName], [Products].[SuppProdRef], [Products].[BuyPrice], [Products].[SuppID] FROM Products WHERE [SuppID]=[Forms]![Purchase Orders]![SuppID].Column(0);

    However, no products are displayed at all now

    On the Supplier Select

    SELECT [Suppliers].[SuppID], [Suppliers].[SuppName] FROM Suppliers;

    I have the colum widths set to 0cm and 4cm so that only the SuppName is displayed, will this affect the Products statement?
    What you have in Column Count and in Column Widths in the combo???
    Saludos
    Norberto

  13. #13
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    For Products statement:

    SELECT [Products].[ProdID], [Products].[ProdName], [Products].[SuppProdRef], [Products].[BuyPrice], [Products].[SuppID] FROM Products WHERE [SuppID]=[Forms]![Purchase Orders]![SuppID].Column(0);

    Column Count = 5

    Column Widths = 0cm;4.709cm;0cm;0cm;0cm



    For Suppliers statement:

    SELECT [Suppliers].[SuppID], [Suppliers].[SuppName] FROM Suppliers;

    Column Count = 2

    Column Widths = 0cm;3.334cm

  14. #14
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    In the event Exit of the combo box SuppID put this sentence:
    [Forms]![Purchase Orders]![YourSubFormName].Requery
    You must do a Requery of the Subform after select the supplier that why
    no products are displayed.
    Saludos
    Norberto

  15. #15
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    On the event exit of the SuppID combo box it now says:

    =[Forms]![Purchase Orders]![Purchase Order Details Subform].Requery

    But still no products displayed in the Products Combo box

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •