Update Record on Form with Calulated Fields (Record are Already in Table) RRS feed

  • Question

  • Dear All, I Have Table [TripRecord] having Fields ([ BowzerNo] Type: Text, [Date] type: Date), ([Diesel], Type number, ([Cash], Type Number), [Amount] Type Number).

    1.  I Have called Last Record of [Bowzer#] Combo Box to call Values of ([Date], [Diesel], [Cash], [Amount]) values to unbound Text Boxes (Diesel1, Cash1, Amount1) on Form.

    2.  I have added new unbound Text Boxes (Diesel2, Cash2, Amount2) on Form.

    3.  I have Created new unbound Text Boxes to Sum Values (TotalDiesel = Diesel1 + Diesel2, TotalCash = Cash1 + Cash2,  TotalAmount = Amount1 + Amount2).

    4.  Now I want to update Values, which I called earlier from Table [TripRecord] Fields (Diesel, Cash, Amount).

    Kindly help me in regard to correct syntax of Update Statement to update current record.

    Friday, December 30, 2016 8:35 AM

All replies

  • Hi,

    Actually, I think the third rule of "normalization" is to not store calculated values. You should be able to calculate the value each time you need it to make sure it is always correct.

    Just my 2 cents...

    Friday, December 30, 2016 3:37 PM
  • If you need to enter two (or more) diesel, cash and amount values per trip record you should decompose the TripRecord table into two related tables:

    ….TripRecordID  (PK)

    ….TripDetailID  (PK)
    ….TripRecordID  (FK)

    The interface for inserting data would be a form based on TripRecord, in single form view, and within it a subform, in continuous forms view, based on TripDetails, linked to the parent form on TripRecordID.  In the subform's footer you can sum the values of Diesel, Cash and Amount per trip by calling the Sum operator in unbound controls.  Multiple rows per trip can then be entered in the subform with the need for no code whatsoever.

    Having created the TripDetails table, and having added an autonumber TripRecordID column to TripRecord as its primary key, it is a trivial task to insert rows for all existing trip records into TripDetails with a simple 'append' query.  The redundant Diesel, Cash and Amount columns can then be deleted from TripRecord.

    Note that I have changed the name of the Date column to TripDate.  Date is the name of a built in function and, as a 'reserved' word, should not be used as an object name.

    Ken Sheridan, Stafford, England

    Saturday, December 31, 2016 12:41 PM