Creating a new table record from a form

Dear BestSTL,

(Please see accompanying email for any diagrams)

I have a table (TBL_STORES) with a list of stores, each identified with an autonumber primary key (ID_Store). Because many (but fewer than half) have a company name as well, I've created a separate table (TBL_STORES_CoName) to hold these company names - see diagram.

I've created a data entry form for stores including both these tables, amongst others. The form has a TextBox (TextBox_CoName), showing a company name if a store has one, blank when there isn't one.
My problem is when I want to enter a new company name, I also need ID_Store to be written to TBL_STORES_CoName, so when I return to the form, there is a link with the store (and the company name will show).
At present, if I enter a company name in the form, it is written to TBL_STORES_CoName, but there is no link with the store.
In other words, I need a way to write to two fields at the same time in the new record of TBL_STORES_CoName.

I tried with VBA:
Private Sub TextBox_CoName_LostFocus()
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO TBL_STORES_CoName (Store_ID, CoName) SELECT [TextBox_ID_Store] AS Store_ID, [TextBox_CoName] AS CoName;"
DoCmd.SetWarnings True
End Sub

When I go to the next record, or try to close the form, I get the following message:
"Index or primary key cannot contain a Null value" and I can't forward through the form records.
However, when I look at TBL_STORES_CoName, the new record has been entered. When returning to the form, the new entry also appears correctly.

I need to get rid of this error message so I can go to the next record in the form.
Alternatively if you have a better solution (with or without VBA) I'd be happy to try it.

Best regards
Joe