Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

RECIPES    INGREDIENTS        RECIPELINES
--------   -------------      -------------
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:

RECIPE FORM
  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
add comment

1 Answer

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.