0

In a continuous form that is based on HarvestTable, I want a drop down for Units with values from CommodityTable that correlate to the Commodity, but will populate the HarvestTable Units column upon selection. The Commodity is already in the HarvestTable, so if the Commodity is equal in both, I'd like the Units for that Commodity to show in the drop down.

    CommodityTable
    ID        Commodity     Units     UOM
    1         Beans         27        LB
    2         Broccoli      37        LB
    3         Broccoli      18        BU
    4         Broccoli      33        LB
    5         Carrot        24        BU

So for Broccoli, I'd like the drop down to have 37, 18, and 33 as options in the Units drop down on the form. Then the selection will populate the Units column in the HarvestTable.

I tried this in the Row Source: SELECT Units, Commodity FROM CommodityTable WHERE Commodity = HarvestTable.Commodity; This is not working. If you know what I am doing wrong and want to help, I'd sure appreciate it very much. Thank you for your time.

Rebecca
  • 1
  • 1
  • In where condition you have to refer controls name (not table field) like `me.ComboCommodity` or `Form1.ComboCommodity`. – Harun24hr Jan 24 '22 at 02:58
  • Also you need run vba code to requery unit dropdown box on after update event of commodity combobox. Try `Me.ComboUnit.Requery` – Harun24hr Jan 24 '22 at 03:11
  • 1
    You already asked this question and received guidance. There is nothing new in this question. https://stackoverflow.com/questions/70818157/how-do-you-create-a-drop-down-in-a-form-with-values-that-are-specific-to-the-rec – June7 Jan 24 '22 at 03:40
  • Technique of cascading combobox still applies. Or call it a conditional or dependent combobox. The combobox list is dependent on value of another field/control. Should not duplicate units and UOM into HarvestTable. Just save the Commodity ID into HarvestTable. – June7 Jan 24 '22 at 05:05

0 Answers0