Ok, finally got it to work. It was harder than I expected and requires a Reload event. Here’s how I did it:
A) SETUP THE QUERY
For a query with a joined table to be editable, any secondary tables need to have their key included in the output of the query.
In this case the query needs to look like this, with
Colors.Color ID included:
- Without the key of the secondary table (in this case
Colors.Color ID) included in the query it’s not editable.
- Only the first three fields are editable (the ones from the primary table), in this case Houses.
- Don’t try to edit the fields for the Colors table in this query, even if it seems you can! You can sort on them, but you can’t edit them.
- Making secondary fields (e.g.
Color) visible is optional. For example, if you are manually editing the
Houses.Color ID numeric key it helps see the results.
B) SETUP THE FORM
Your form design will look like this:
And like this when first opened, and you go to add your first record:
When you hit enter or tab to move out of the new record your display updates incorrectly!. Notice how the NEW record got incorrectly copied up into the 2nd row position, and it’s key value is still zero:
C) INSTALL AN UPDATE EVENT
If you close the Form, and then reopen it, you can see that your data is correct. You need to add an event that does a form refresh when a record is inserted. Here’s what it looks like:
And here is where you attach it to your Form: