Updating a table

Posted on 2011-10-28
Last Modified: 2012-05-12
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.
Question by:MikeDTE
    LVL 2

    Accepted Solution

    if i have understood you correctly, all you want to do is store those three fields to the table on the AfterUpdate Event?

    I think all you would have to do is use a bit of SQL to insert the data into the table.  Ive modified a bit of SQL i use for this purpose to try and suit your needs but i cant test it so you will have to give it a go and let us know if your get any errors.

    Dim strSQL As String
                       'Build INSERT INTO statement.
                    strSQL = "INSERT INTO " _
                       & "TABLE (TABLEFIELD1, TABLEFIELD2, TABLEFIELD3) " _
                       & "VALUES (jdwb_TotFee, jdwb_Discount, jdwb_Balance")"
                    'View evaluated statement in Immediate window.
                    Debug.Print strSQL
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings True

    Open in new window

    You will obviously have to replace 'TABLE' with the name of the table you are trying to insert data into.  You will also need to replace 'TABLEFIELD1//3'.  I should also point out that i think this has to be in the correct order,  So TABLEFIELD1 should be the field that jdwb_TotFee will be inserted into if that makes sense.

    Anyway give it ago and see how it runs.

    Author Comment

    Hi jdc1944

    OK the SQL statement is this:

    strSQL = "INSERT INTO " _
    & "tJDWBookings (jdwb_TotFee, jdwb_Discount, jdwb_Balance) " _
    & "VALUES (" & curTtlFee & "," & curDisc & "," & curBal & ");"

    This works but it creates a new record in the tJDWBookings table rather than modifying the data is the existing record.  Should the UPDATE command be used thus:

    strSQL = "UPDATE tJDWBookings SET " & _
    "jdwb_TotFee = " & curTtlFee & ", " & _
    "jdwb_Discount = " & curDisc & ", " & _
    "jdwb_Balance = " & curBal & " " & _
    "WHERE jdwb_BookingID = " & lngBookID & ";"

    I've added a WHERE statement to make sure the correct record is updated.

    This works!
    LVL 2

    Expert Comment

    Sorry I should have done update rather than insert, yes to update rather the create a new record what you have done with the UPDATE and Where clause looks correct to me and if you say it works, then all sorted.

    Author Comment

    Well it's "sort of" sorted

    The issue now is that when I try and save the Bookings record I get the following Write Conflict error

    "This record has been changed by another user since you started editing it.  If you save the record you will overwrite the changes the other user made.  etc"

    Yes I can make the right decision which is to 'Save Record' rather than 'Copy to Clipboard' or 'Drop Changes' however the error is not welcome in an User environment.

    Any ideas how to resolve this before the message occurs and write all pending changes first and then do the SQL UPDATE?
    LVL 2

    Expert Comment

    try this just before the SQL update

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Author Comment

    Yes I tried this one - it said "Save isn't available now"

    The SQL UPDATE Statement is in the After Update section so the only orther change (in this case) is to the number of Observers.  So I incorporated the change to the Observers total into the UPDATE statement thus:

    strSQL = "UPDATE tJDWBookings SET " & _
                   "jdwb_TotFee = " & curTtlFee & ", " & _
                   "jdwb_Discount = " & curDisc & ", " & _
                   "jdwb_Balance = " & curBal & ", " & _
                   "jdwb_Observer = " & lngObs & " " & _
                   "WHERE jdwb_BookingID = " & lngBookID & ";"

    and for good measure I set 'Me.Dirty = False' just before running the SQL UPDATE and that seems to have made a difference ... no Write Conflict :>))

    Bouncing ideas about has found the solution - the key was using SQL and although it was an UPDATE rather than a INSERT INTO - it was your prompt that ... anyway the points are yours - thanks

    LVL 2

    Expert Comment

    glad to help and that you got it all sorted.

    Author Closing Comment

    Apart from the INSERT INTO being wrong (replaced by UPDATE) teh solution worked

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now