I have a form that is based on information held in a table using a primary location ID that is unique to that particular location. I have a subform in this form that is referencing data that contains inventory stored at this location, linking using the primary unique location ID from the main table. This subform has controls that use the "Link Child Fields" location ID to add data in separate pop-up forms that are used to add and archive inventory that the subform references. These pop-up forms are given access to the "Link Child Fields" by passing it through with a DoCMD call as listed below.
DoCmd.OpenForm "AddInventoryFormName", acNormal, , , , , Me.SomeChildLinkedValue
The above scenario works 100% as long as there is already inventory in the table for the subform to populate. My issue occurs when I load a location that does not have an entry in the subform inventory table. I get a pop-up that states "Invalid use of NULL" and the subform displays the headers.
I've added a hidden variable to display the location ID that is held in the linked "Child Field" for debugging purposes. This value displays the correct location ID on a successful case and will show the LAST successful case when switching to a location without a corresponding location ID in the inventory table.
How am I able to reference the Link Child Field of a subform in Access 2016 when there is no corresponding record that exists?
I found a functional workaround. I'll try and post a "picture" below to explain how I fixed it by moving the elements around for anyone else who ends up in my subform dilemma.
My initial setup:
------------------------------------
| Main form |
| |-------------------------------|
| | Sub form | Form Button |
| | --------------|
| | |
| |-------------------------------|
------------------------------------
Main form was linked with Sub form using a locations unique ID in two corresponding tables. Form button was nested in the sub form using the linked field in the inventory table and used for inventory addition and removal. If Main_form.location_ID existed and Sub_form.location_ID did not, the buttons would not function. I fixed the NULL error issue by removing the button from the subform and redesigning the interface a bit.
Me redesigned setup:
------------------------------------
| Main form | Form Button |
| |-------------------------------|
| | Sub form |
| | |
| | |
| |-------------------------------|
------------------------------------
This allows the users to add new inventory into a site and allow a new entry to be entered without relying on the relay through the linked subform.