Combo Box Values based on another combo box selection

[tweety]

Hi



i have a form, with a subform and another subform


once a supplier name is selected from the combo box i only want it displayed that suppliers products in the subform, subform. (products combo box) I can get a basic query to show these results but can get the combo box to do the same.

the design screen is also attached below


thanks

[June7]

Sounds like dependent (cascading) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html

[tweety]

thanks i didnt know it was called cascading, iv linked both the boxes up but cant do the requery bit, how can i tell access that i looking at a form, subform and another subform then a control?

thanks

[June7]

Don't understand the question. Why can't you requry the dependent combobox? What are you doing that is different from the tutorial?

[tweety]

hm i started it again to make iam not doing anything wrong

i put the following code in the second combo box


as so i open the form now it asks me to enter enter parameter value??? for the first combo box?

just to refresh i have the supplier combo box on the purchase form, and the product combo box with the code is on the subform and another subform

any ideas anyone???

[June7]

So the two dependent comboboxes are not on the same form. This is different from the tutorial and definitely more complicated. Actually never seen this attempted. I don't really understand the forms. If you want to provide database for analysis, follow instructions at bottom of my post. However, I won't be able to review it until after Feb 22.

[tweety]

ok, so the record purchase orders form from the main menu, thats the form with the problem. when a supplier name is selected its set to create a blank record with todays date. the user should only be able to select products from the supplier selected, so for example when your select vista, then from the products combo box you should only see jelly cones and jelly sticks, when sweet world is selected then only bubble gum, gomets 8, gomets 10, lol pop and gummies etc.

i can run a simple query, to get this results....

now the supplier get is values from the supplier.supplierName and the products from product.

hoping iv explained myself, if you have any question please ask

thanks

[June7]

Oh Boy! This is a form/subform/subsubform arrangement with dependent comboboxes on form and subsubform. Never seen this before.

RowSource of the Product combobox needs a WHERE clause.
SELECT Product.ProductID, Product.ProductName FROM Product WHERE (((Product.SupplierID)=[Forms]![RecordSupplierPurchase].[ComboSupplierName])) ORDER BY Product.ProductName;

Then need to requery the Product combobox after selecting Supplier.

Getting the correct reference in form code module to the subsubform combobox is tricky but this seems to work:
Me.Purchase.Form.[PurchaseDetails Subform]!ComboProductID.Requery

[tweety]

thanks again for your help, i have added some more bits to that form now, and after adding the above code, the extra bits do not work (in short i added, with the help from the friendly people here multibuy options, so if more then 1 product is sold then discount of my choice can be given)

the way it works is the product combo box reads the price and displays it in the list, then the price is edited in the purchase field

can i upload the new copy again please?

thanks

[June7]

You may do so.

[tweety]

please see the attached system

thanks

this database only has sales on the 9 feb 2012 - if you like to see the system is play!

[June7]

Need more specifics. What 'extra bits' do not work? What forms are involved? What happens and what do you want to happen?

[tweety]

sorry i didnt explain it well, the record purchase orders form, the extra bit that was added by bob fitz on https://www.accessforums.net/forms/r...les-32363.html
what this does is allow the user to record a discount to products purchased, (multibuy) so in short an extra field was added in the sales details called sold price, this reads the price from selling price from the product table, if 5 items are purchased £1 and be taken off each item as an example, depending on the customer.

in order for this to work the combo list box product has 2 items the product name and the price, once the code you provided is added this whole multibuy system stops working

hoping this is clear, but if you have any questions please ask thanks again

[June7]

I see the SoldPrice field in table and it is populated for some records but not seeing form or code handling that. How did field get populated?

Need the db that isn't working to analyse problem code.

[tweety]

sorry for the delay. please see attached

the following code show the price in the list

Private Sub ComboProductID_AfterUpdate()
Me.BuyingPrice = Me.ActiveControl.Column(2)
Quantity.SetFocus
End Sub