How to place values in field depending on choice

[MAC]

Hello:

I'd really appreciate your assistance in the following: I have a table with
3 columns and 5 rows. It relates to students' meal plans. They need to know
the full amounth they're paying depending on the plan. It looks like this:

Choice Group A Group B

Light $ 2,250.00 $
1,600.00

Small $ 2,475.00 $
1,825.00

Regular $ 2,675.00 $
2,025.00

Large $ 2,875.00 $
2,225.00

X-Large $ 3,075.00 $
2,425.00



I'm thinking of having a form with checkboxes for each one of options.
Then, depending on the choice, I need to have the value inserted in a
predefined field called mealexp. A calculated field with a long formula
would be impractical. What would be the best way of doing this, and how?



Any assistance would greatly appreciated!

 

[Duane Hookom]

I would change the table structure to remove data from the field names
(Group A and Group B). A more flexible structure would be:
PlanID autonumber PK
Choice
Group
Price
Then, you could either store the PlanID or lookup and store the Price.

 

[MAC]

Sorry, Duane, but what do you mean by "store" and "lookup"? I'm devouring
the books on Access but haven't got this yet. Please explain. If it's too
lengthy to explain, any place I could read? Thank you very much!

 

[Duane Hookom]

The Orders Subform in Northwind has a Product dropdown box that finds the
UnitPrice based on the ProductID and inserts it into a field. I don't care
for their method but it works. Their code with some extra lines removed:
Private Sub ProductID_AfterUpdate()
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID
' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)
End Sub

I would rather place the UnitPrice field in the dropdown and use code in the
after update:
Private Sub ProductID_AfterUpdate()
Me!UnitPrice = Me.ProductID.Column(2)
End Sub

--
Duane Hookom
MS Access MVP

Sorry, Duane, but what do you mean by "store" and "lookup"? I'm devouring
the books on Access but haven't got this yet. Please explain. If it's too
lengthy to explain, any place I could read? Thank you very much!