I have a table that tracks items provided to clients of a social service agency. There are fields for each of the items that potentially could be provided with a corresponding field for the amount spent. Since one family can receive more than one of a particular item, the fields for the items contain the number of items provided. For example, if a field is called Beds, it contains a number of beds purchased for a family. The corresponding field BedCost contains the amount spent (the number of beds times the price). Since the amount allocated to be spent for a bed changes, I have a separate table called tblItemsH containing only one row with a field for the current cost for each item, e.g. there is a field called BedPrice, another called CribPrice, etc.
I’m using a form for data entry—actually it’s a subform, since the main form contains other case information from a related table (one-to-one relationship). What I want is that when the user enters the number of items (say, 2) in the txtBeds textbox, the amount spent text box, txtBedCost, will populate with amount spent, the number purchased multiplied by the price for that item from the BedPrice field in the tblItemsH table.
I tried it a number of different ways, but I can’t get it to work. The simplest way seems to be an After Update event in the txtBeds text box that populates the txtBedCost text box with the contents of the BedPrice field from tblItemsH and multiples it by the number of items from txtBeds, but I can’t get the code right.