Updating a table
Posted on 2011-10-28
In this application I have 2 forms that are nearly always displayed and a number of other forms that are opened to complete specific tasks. The Events form lists events and includes a financial summary of income and expenditure with a surplus or deficit displayed and the Bookings form displays bookings for the event that is chosen in the Events form.
As bookings are made the Bookings form details all the data about the booking which includes accommodation, for the participant and any accompanying members of their party. Usually a participants partner will only require accommodation but occasionally the partner will become an observer at the event.
As bookings are taken the financial summary on the Bookings form for the booking is updated and the financial summary on the Events form is also updated. This gives a current financial position on the event at all times.
Most form fields are not bound to a control source and are initialized in the Form_Load from fields in one or more tables. Then if a form field is changed I use the After Update event to detect a change and simply store the new value into the table using:
Table field = Form field
The data is written to the table when the form is saved.
Mostly this works well but some form fields are dependant on each other. For example a discount applied affects the balance due. I can wok these by recalculating the booking financials and updating the form fields, updating the table fields as above and then saving the record.
I rebuild all the Event form financials from scratch from the tables if anything changes in a booking. This is the only way to ensure the event financials are correct. Using running totals by working out changes in each value is too complex because there are too many component values – (Total Due, Total Received, Total Expenses, Donations, Withheld Deposits, Discounts, Accom Costs, Accom Charges, Accom Extras, Staff Expenses, Catering Expenses and the Surplus / Deficit).
The problem (I guess you wondered when I would get here) is that I need to rebuild the Event form financials on the fly before the bookings data is subject to a Booking form save. Thus changes to a booking are not yet updated in the table and thus the Event form financials are incorrect.
What I need is to replace the following After Update code with something that writes the data straight to the table rather than leaves it pending for a form Save.
Me.jdwbBalance = Me.jdwbToBePaid - Me.jdwbTotPaid
jdwb_TotFee = Me.jdwbTotFee ')
jdwb_Discount = Me.jdwbDiscount ') Store to table
jdwb_Balance = Me.jdwbBalance ')
‘Update table – need syntax
Call UpdateEventsFinancials 'Update Events form finance summary
This will not be used on every form field as some form fields do not affect the Event financial summary.