Table fields not updating from form entries | Access forum

Dear BestSTL,

I have a
- Table (TBL_STORES) containing a list of stores. Its primary key (auto number) is called 'ID_Store'. The other field holding store names is 'StoreName'
- Table (TBL_STORES_Hours) containing the store opening hours for those stores that have this information - in the field 'Hours '. TBL_STORES_Hours has no primary key but a child key field called 'Store_ID' (which links to ID_Store in TBL_STORES). The Store_ID field is also a lookup to the stores table.
So far so good
I created a form with
- a Combo where I could choose the store and
- a text box (TextBox_Hours) displaying the opening hours for those stores that have this information.
I've assigned the form's Record Source SQL with VBA:
SELECT TBL_STORES.ID_Store, TBL_STORES.StoreName, TBL_STORES_Hours.Store_ID, TBL_STORES_Hours.Hours FROM TBL_STORES LEFT JOIN TBL_STORES_Hours ON TBL_STORES.ID_Store = TBL_STORES_Hours.Store_ID ORDER BY TBL_STORES.StoreName;

Everything displays fine: Those stores which have opening hour information in TBL_STORES_Hours have this displayed in the text box.
The problem is when I try to enter opening hour information for a store that never had an entry. I enter the text in TextBox_Hours, tab out, go to the next store record & go back - the text is still there.
Now I close and re-open the form. If I go to the store for which I just entered opening hour information, the text is no longer there.
On opening the table TBL_STORES_Hours, I can see the entry I just made in the 'Hours' field, at least the text I typed, but the field Store_ID has nothing within - the reason my new entry is not showing in the form.

The Form's Recordset Type has been set to Dynaset (Inconsistent Updates) in its properties
I've tried applying the form's Record Source with a separate query - the result is the same
I've tried giving TBL_STORES_Hours a primary key - the result is the same

The question is why? What am I missing?

Many thanks and best regards.
Joe

After hours working on my problem, while thinking about it away from the PC, I realized ‘why’ it wasn’t working … isn’t it always the case?!
The field I expected to update didn’t, because it’s just another normal table field, not a primary key that automatically assigns an auto number to the new record.
I’ve worked out a way to populate the field using an event attached to VBA.
I wish I’d thought of it earlier!
Best regards
Joe