Is there a way to cause a database form to save and reload automatically when a field is changed?

[hbquikcomjamesl]

I have a simple database for keeping track of charity contributions. Each record has space for seven dates and seven amounts (to be cleared during end-of-year processing).

There is also a field that shows the total amount, calculated from a query:

coalesce(“Amount 1”, 0) + coalesce(“Amount 2”, 0) + coalesce(“Amount 3”, 0) + coalesce(“Amount 4”, 0) + coalesce(“Amount 5”, 0) + coalesce(“Amount 6”, 0) + coalesce(“Amount 7”, 0)

But this doesn’t show the total amounts in the form; rather, it shows the total amounts in the database record.

Is there a way (perhaps with a macro) to have the record immediately written out to the database, and the form refreshed, when one of the “Amount” fields loses focus?

[Villeroy]

t95976.odb (14.0 KB)
The OK button does the trick:
Action: Save form
Take focus: No (focus remains on current control)
Default button: Yes (triggered by Enter key)
You may set Visible=No. It will do the job anyway.

[hbquikcomjamesl]

Thanks, @Villeroy . I downloaded your solution (an invisible button tripped by the Enter key certainly works until and unless something else comes along).

But I’m having trouble with the refresh:
I have your database and mine running in parallel. In both, hitting Enter writes the record to the database immediately. In yours, the “Sum” field on the form then refreshes immediately. In mine, the “Total” field doesn’t refresh. I can’t see what could be different between the two.

[Villeroy]

How can we know what’s wrong without seeing your database or the form at least?
Open the table. Make sure that the column types are numeric.
Open the form navigator. Make sure that your magic push button belongs to the right logical form and not any subform or parent form.

[hbquikcomjamesl]

Charities.test.odb (45.6 KB)
I figured you might want to see what I have. Here it is with the live data stripped out.

Thanks again for your willingness to assist, @Villeroy .

[Villeroy]

My form’s data source is a query which selects all the table fields for editing plus the calculation.

SELECT "T".*, 
COALESCE ( "Amount 1", 0 ) + 
COALESCE ( "Amount 2", 0 ) + 
COALESCE ( "Amount 3", 0 ) + 
COALESCE ( "Amount 4", 0 ) + 
COALESCE ( "Amount 5", 0 ) + 
COALESCE ( "Amount 6", 0 ) + 
COALESCE ( "Amount 7", 0 ) AS "Sum" 
FROM "T"

By the way: 7 columns for the amounts is poor database design. A relational database can handle this more elegantly.
You must not store any calculated values (“total”) in tables. You would not do this in a spreadsheet neither. In a spreadsheet you store a formula which dynamically adjusts to modified values. Same here.

This is what I mean:
t95976a.odb (20.4 KB)

[hbquikcomjamesl]

Fascinating, @Villeroy : in yours, the entire form has the query as its data source, whereas in mine, only the total field in the form is on the query, with the rest of the form tied directly to the table.

(If the version I posted has a “Total” field in the table, it was something I stuck in recently, thinking I might need it; it’s ignored, and simply abandoned in place.)

Is there an easy way to connect the entire form to the query, without too much disturbance? And without too much disturbance to the record list shown on the right side of the form?

[Villeroy]

My second attachment tries to do it right in the most simple way. There is a list of clients with an auto-ID and one column for each client property (I reduced it to a name). And there is a list of payments with a date, amount, the id of the client who did the payment and an auto-ID. Each payment is linked to exactly one client by the clients ID number. This is called a one-to-many relation in database terms.
The form lets you select one particular client on the left, the subform on the right side shows all payments of the selected client, no matter if there are no payments or thousands of them. In addition there is a calculated sub-subform with simple statistics about the current client’s payments. It updates automatically as you hit the Enter key which triggers the save button and updates the sub-subform automatically.

• Database tables grow vertically. They should never grow horizontally, for instance when a client did an 8th payment.
• Database tables rarely have any blank values, so you can omit the coalesce function in this case. Apart from the payment’s auto-ID, each payment consists of a valid client ID, a date and amount. All these values are not optional, otherwise the payment entry would not make any sense.
• You can’t save any payments with missing values.
• You can not even enter a client-ID in the payments table which does not exist in the clients table.
• And you can not delete any client who is related to some payment in the payments table.
• This so called “referencial integrity” can be set up in the relations window (Tools>Relations).
  All the functionality is based on the primary keys (automatic ID numbers) which do not visibly appear in the user interface (the form). The form only shows names, dates, amounts.

[hbquikcomjamesl]

The reason the “total” form field wasn’t updating when I clicked the default button (or hit Enter) in my database turned out to be something as simple as it was bizarre:

Somehow, it was isolated in its own sub-form, something I wasn’t able to see or correct until I looked at it in “Form Navigator” (something I’d forgotten even existed).

As to restructuring the database as a header-and-detail system:
Setting up such systems (whether in DB2/400 or in MySQL) is a rather large chunk of how I make my living. And it was something that was covered back when I was getting my computer science degree, back in the early 1980s. It was a conscious decision to avoid such a structure for this application.