Full Version: Supertypes-subtypes Issue: Combobox Whose Rowsource Is A Union All Query
UtterAccess Forums > Microsoft® Access > Access Forms
javert
Here at work we prepare solutions from chemical reactives; that said, sometimes we create intermediate solution which then are used to prepare other solutions. I need to set that in the database. There is a Reactives table, a Solutions tables and a SolutionIngredients table which is a junction table to specify which reactives are used to prepare which solutions, and includes other data such as the quantity.

Reactives

ID (PK)
Name

Solution
ID (PK)
Name
QuantityPrepared

SolutionIngredients
IngredientID(PK)
Solution(PK)
IsReactive (Yes for Reactives, No for solutions)
QuantityNeeded

I plan to use the boolean field IsReactive for the database to know whether it has to look up for a solution or a reactive. To choose an ingredient, I made a ComboBox whose RowSource is an UNION query for both Reactives and Solutions. My problem is that it is not possible to get the precise ingredient with the ID field alone and, even if I add a constant Boolean field on the UNION query to discern between a solution or a reactive, the combobox automatically defaults to display the reactive name when the ID has registers for both a reactive and a solution.

Although there is no ambiguity in the tables, it would be moot if I can't get the ComboBox to work properly. Which is the standard way of setting a ComboBox which looks for a supertype built from its subtypes?
orange999
I suggest you give a few examples using your data showing the various conditions. I'm not understanding the description given in the post.

What is your data model where you use/consider supertype/subtype?
javert
Let's say the reactives are water, sulfuric acid and copper sulfate. I need a solution which require 0,5 g of copper sulfate and 0,3 L of a solution 1:1 of sulfuric acid and water (can be prepared mixing 1 L of sulfuric acid and 1 L of water).

The Reactives Table is (how do you make tables in posts?)

1 Water
2 Sulfuric Acid
3 Copper Sulfate

The Solution Table is

1 Sulfuric acid 1:1
2 Copper sulfate solution

The SolutionIngredients table would be:

IngredientID, SolutionID, IsReactive, QuantityNeeded
1 1 Yes 1
2 1 Yes 1
3 2 Yes 0,5
2 2 No 0,3

I have found some info about Cascading comboboxes that could solve this. Is there a way to get Cascading Comboboxes work in Worksheet or Continous Forms? As far as I'm trying, changing the rowsource of the combobox via code will change all the registers at the same time.
azolder
The issue is the combo box doesn't know which PK ID is which (a ReativeID could be the same as a SolutionID). You could make the combo unbound. For the bound to field (modified ID) you could then concatenate a table identifier with the ID in the union query. giving you unique IDs. In the combo's after update event you can can split it the concatenated parts to set your Solution PK and IsReactive fields.

A better solution would be to include both Reactives and Solutions in a single Ingredients table, giving you unique IDs with which to work. If needed you could have a FK to the Solution table to identify IF the ingredient is a Solution.
javert
I did that concatenation as you suggested and the combobox works right.

I agree that a single table for both reactives and solutions would be the best solution. Sadly, I need to talk with my boss about it since that table is mainly the domain for another department so I can't mess with it easily. But for now it has solved the issue, thanks.

Is there a way to mark this threads as solved?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.