Issue with related tables in form-subform. Error "The Microsoft Access database engine cannot find a record in the table" RRS feed

  • Question

  • I am redesigning an old database (db) and implementing some forms to improve its use. It had separate tables for clients and providers, both of which included all the information about the companies as well as address. After doing some research, I decided to separate the addresses from the company information and join all the addresses on a new table. The key is the Client ID / Provider ID as well as a type string to specify which one I am choosing.
    When it comes to retrieving information, there is no problem. The issue appears when I try to add a new record.

    I have created a form where all the info about a Client is displayed and also a subform that can show the address of the existing companies. When I try to add a new Client, (after filling some info of the form) when I click on the subform to type the address it prompts the following error message: "The Microsoft Access database engine cannot find a record in the table 'tblCompanyAddress' with key matching field(s) 'ID Client, Type'."
    Strangely enough, it fills the info (ID Company and Type) of the record I just started typing.

    Any idea on how to fix this error?
    Also, is what I am doing a good practice or would it be better to leave it as it was? I am pretty new to MS Access and I am still figuring out how things should be done.

    Thank you very much in advanced.

    • Edited by t1emp0 Tuesday, July 30, 2019 11:18 AM small typo corrected
    Tuesday, July 30, 2019 11:17 AM

All replies

  • In my opinion, Clients should have their addresses in the Client table. Likewise with the Providers. You are complicating table structure for no reason.

    Bill Mosca

    Tuesday, July 30, 2019 2:42 PM
  • just for testing purposes: I am guessing you have set relationship joins between these 2 tables.  The same thing is effectively set up when you insert a sub form into a main form.  So try deleting the fixed relationship joins between the two tables.  Your main/sub form relationship will still be fine and this deletion will not break any other objects.... see if that makes a difference....
    Wednesday, July 31, 2019 1:02 PM
  • The only cogent reason for having a single Addresses table would be if a provider can also be a client or vice versa, in which case clients and providers are sub-types of a single (super) type, let's call it Contacts.  You would then have a type-hierarchy which is modelled by the relationship types between Contacts and Clients, and between Contacts and Providers being one-to-one, i.e. the primary keys of Clients and Providers are also foreign keys referencing the primary key of Contacts.

    A sub-type is characterized by sharing all attributes of its (super) type, but not those of other sub-types, so the Contacts table would have columns representing those attributes which are common to both clients and providers, whereas the Clients and Providers tables would each have columns representing only those attributes which are specific to the clients and providers entity types respectively.

    In this scenario the Address table would reference the Contacts table by means of a ContactID foreign key or similar.  There would be no need for a column to identify whether the address is that of a client and/or provider as this information is inherent in the relationships, unless a contact can have one or more addresses as a contact, and one or more different addresses as a provider, in which case the relationship type between Addresses and AddressTypes would be many-to-many, and modelled by an additional table which resolves the relationship type into two one-to-many relationship types by means of AddressID and AddressTypeID foreign key columns.

    Moreover, there is only the need for a separate Addresses table if a contact can have more than one address, in which case the relationship type is one-to-many, or, albeit less likely, more than one contact can have the same address, in which case the relationship type is many-to-many and should be modelled by an additional table ContactAddresses, which resolves the relationship type into two one-to-many relationship types by means of ContactID and AddressID foreign key columns.

    If, on the other hand, clients and providers are distinct entity types, there is no type hierarchy, and you should do as Bill recommends, and include the addresses in the separate Clients and Providers tables, or, if a client or provider can have more than one address, by separate ClientAddresses and ProviderAddresses referencing tables.  Both tables can reference the same other tables, e.g. a Cities table by means of a CityID foreign key in each.

    For an example of how to model a type hierarchy you might like to take a look at TypeHierarchy.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file uses the personnel of an academic institution as its example.

    For examples of how to model different relationship types, including a hierarchical addresses model (cities, regions, and countries) see DatabaseBasics.zip in the same OneDrive folder.

    Ken Sheridan, Stafford, England

    Wednesday, July 31, 2019 10:58 PM