Access - how to derive a field upon entry of a record

Question

I have an Access database for a media rental company which includes the following tables, among others;

LOAN: customer_id (pk), loan_datetimeLeant (pk), loan_dateReturned (pk)

LOAN_ITEMS: customer_id (pk), loan_datetimeLeant (pk), item_id (pk), loanItem_cost

ITEM: item_id (pk), product_id, item_availability

PRODUCT: product_id (pk), product_name, product_type

MEDIA_COST: product_type (pk), product_cost

So basically the 'product type' (DVD, VHS etc) determines the cost. The 'product id' determines what movie, platform etc each item is.

My question is: When creating a form for the Loan_Items table, how can I populate the loanItem_cost field (so it is stored) whenever a new Loan_Item record is added? I need to store it as the cost of the item (product_cost) may change over time and I'd like to record what the customer paid at the time the loan was made.

Thanks in advance

Answer 1

Go to the properties for the button and find the OnClick event. Select Event Builder (or something like that, I'm just going from memory) and double-click. This should take you into the code editor. Here you can add something like loanItem_cost = product_cost.

Answer 2

If you have two bound text box controls on your form named txtProductCost and txtLoanItemCost you can set up something like this in your form's before update event:

If Len(Me.txtLoanItemCost & vbNullString) = 0 Then
    Me.txtLoanItemCost = Me.txtProductCost
End if

If you don't want txtProductCost to be displayed on your form, you can set its Visible property to No in the Format tab on the properties sheet for that control.