I need to output to a field conditionally... 2

[jeez]

OK, on my form I have customer ID, customer code, meal type, and meal cost fields. When I enter in a customer ID, the customer code is filled in automatically (that's working). What I need is to display and save to the table the meal cost depending on what the customer code is and the meal type. Here is an example:

If the customer code is 101 and the meal type is brk then meal cost is $1.25
If the customer code is 101 and the meal type is lun then meal cost is $1.75
If the customer code is 202 and the meal type is brk then meal cost is $2.00
ETC..

Thanks for reading this. I hope it makes sense!

 

[DougP]

Well you have it just needs a little VBA tweaking.
here is a function that should work nicely.
Call it like so:

=MealCost([me]![Customer code],[Me]![Meal Type])

--------------------------------
Public Function MealCost(Code, MealType)
If Code = 101 And MealType = "brk" Then
MealCost = 1.25
ElseIf Code = 101 And MealType = "lun" Then
MealCost = 1.75
ElseIf Code = 202 And MealType = "brk" Then
MealCost = 2
Else
MsgBox "Customer code is incorrect " & Code, vbExclamation, "Customer Code invalid"
End If

End Function



DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[evalesthy]

Another solution would be to have a table of values. In a simple example it would be tblMealCost with 3 fields: CustomerCode (number), MealType (text 3), and MealCost (currency). On your form you state you have already selected the CustomerCode (named RCustomerCode) and MealType (named RMealType) (these could be combo boxes drawing their rowsource from the tblMealCost). On some event, maybe after selection of MealType (if both CustomerCode and MealType are filled in) you could run the following code in afterUpdate

dim curMealCost as Currency

curMealCost = DLookup("[MealCost]", "tblMealCost", "[CustomerCode] = [RCustomerCode] and [MealType] = [RMealType]&quot

Me.MealCost.value = curMealCost

This could also be accomplished through a recordset but this example will work. If you need me to e-mail you an example let me know.
humesgs@smart.net