The Form That Edits the Wrong Table
A clinic builds a data-entry form for patient visits. The form is bound to a query that joins Patients and Visits. Staff type a new visit into the form and click save. Some visits save correctly, but others fail with an error, and one receptionist reports that editing a patient's phone number on the form did not stick. The developer assumed any form bound to a query would update the underlying tables automatically.
1. Explain why a form bound to a multi-table query may not save edits reliably, and what the developer should do.
Read the worked answer
A form bound to a join query is not always updatable. The database can only push an edit back to a base table when it can identify a single, unique row in one table. If the query joins tables without a clear one-to-many direction, or lacks a unique key on the 'one' side, edits become ambiguous and the database refuses them. The fix is to bind the form to a single table (e.g. Visits) and use a subform or a combo box to pick the patient, rather than editing two tables through one joined recordset. The developer's assumption - that any query-bound form updates automatically - is the root error.
2. The phone number edit 'did not stick'. Give a step-by-step check to find out why.
Read the worked answer
- Confirm which table the phone field belongs to (
Patients) and whether the form's record source includes it as an editable field. 2. Check whether the field is locked or disabled on the form, or read-only because it comes from the non-updatable side of the join. 3. Verify the edit was actually committed - did the user move off the record or click save? An uncommitted edit is discarded. 4. Test by editing the phone directly in thePatientstable; if that works, the problem is in the form binding, not the data. 5. Check for validation rules silently rejecting the change. Work from the field outward to the form to the table.