Updating a table

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.
Who is Participating?
jdc1944Connect With a Mentor Commented:
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.
MikeDTEAuthor Commented:
Hi jdc1944

OK the SQL statement is this:

& "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!
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.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

MikeDTEAuthor Commented:
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?
try this just before the SQL update

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MikeDTEAuthor Commented:
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

glad to help and that you got it all sorted.
MikeDTEAuthor Commented:
Apart from the INSERT INTO being wrong (replaced by UPDATE) teh solution worked
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.