Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Let's say I'm building an Access database to hold recipes and calculate the cost of those recipes. I need a table of recipes, a table of possible ingredients, and a table of recipe line items.

Each ingredient's cost is stored in the Ingredients table, along with the unit of measurement. For example, we might store an ingredient "Hamburger" with a measurement unit of "ounces" and cost of $0.25 per ounce.

--------   -------------      -------------
ID         ID                 ID
Name       Name               RecipeID
           MeasurementUnit    IngredientID
           CostPerUnit        Quantity

I'm creating a subform to add recipe lines. I want to allow input of the Quantity field, but I also want to display the ingredient's measurement unit. In other words, I want the form to look like this:

  ID:   [ 5 ]
  Name: [ Guacamole ]

  Recipe line items: (this is the subform)

      INGREDIENT           AMT     UNIT
      [ Hass Avocado   ] [ 2.0 ] [ EA  ]
      [ Red Onion      ] [ 0.5 ] [ CUP ]
      [ Habanero Chile ] [ 2.0 ] [ EA  ]

Once the user enters an ingredient name (a combo box over the IngredientID field), I want to display the measurement unit so the user knows if they're typing in ounces, cups, pounds, or what. Is this possible?

share|improve this question
up vote 0 down vote accepted

I wrote out the entire question, then Super User pointed me to another question that incidentally had the answer I needed.

On the form itself, change the Record Source to be a query rather than a table. In this case:

SELECT RecipeLines.*, Ingredients.MeasurementUnit
FROM Ingredients
INNER JOIN RecipeLines ON Ingredients.ID = RecipeLines.IngredientID; 

Then, MeasurementUnit becomes a selectable Control Source on a text box. By disabling the text box, it becomes a view-only display.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .