Solved Strange error message (1 Viewer)

[mikenyby]

Hello all!

In the database I'm working on, I have an form to edit entries on table "items" that includes a subform to add associated persons from table "persons". I have a combo box that allows the user to select the associated person, and it displays a few data points on each entry from the [persons] table. It uses Name: [FamilyName] & ", " & [GivenName] & " | " & [CityOfResidence] & " | " & [Institution]. [Institution] is pulled from the related table "institutions". The form also includes a box in which one can add a new person if they are not in the database already, and the combobox requeries when the person entry form is closed.

The strange thing I'm experiencing is that if I add a new person, and that new person does not have an associated institution, I get this error message: "the microsoft access database engine cannot find a record in the table Institutions with key matching field ",

That's a quotation mark and a comma. It appears in my Name: expression, but I can't understand why it's trying to look that up in the Institutions table.

If I enter a new person and they have an associated institution, no error message.

I checked the persons table and the fk for the Institutions field is not marked as required.

Here's my relationship map:

 

[The_Doc_Man]

The issue is probably that you are enforcing relational integrity (RI) between Institutions and Persons, and if you have no associated institution, the AssociatedInstitution field is probably zero (by default). But you probably don't have an institution with an ID of 0, so RI is stopping you.

Your solution might be to realize that if a field is not required, it probably cannot participate effectively in an RI rule. Since I also see that it should be possible for a person to not (yet) have an associated institution when first entered, the solution must be to remove RI for that relationship. If you really want to maintain RI then the other solution is to make "Institution 0" an entry that acts as a place holder representing "nothing yet chosen."

Your choice as to which way to go.

EDIT: While I'm at it, you have a dangerous situation. You have TWO paths between Persons and Items. One is through what appears to be a standard style of junction table. (Top of your diagram.) The other is through Institutions which, even though it is a "reverse direction relationship", is still a second path between two tables. Things generated by query or form or report wizards might become confused by this.

 

[mikenyby]

Thanks, I eliminated RI in both the items/institutions and the persons/institutions relationship but the error message still comes up!

If it's any help, this issue started after I added the institution to the Name: expression in the subform combobox. Beforehand it worked fine. Oddly, if I remove the institution from the Name: expression now, it does not fix the problem. I don't get it.

 

[Pat Hartman]

Your solution might be to realize that if a field is not required, it probably cannot participate effectively in an RI rule.

I disagree with that statement. It can participate effectively because optional relationships are found frequently in the real world. But as you later said, the solution is to change the default for ALL FKs to null rather than 0. That enforces the relationship when a value is entered but ignores it when the FK is null. So, you need to have the default be null and required = true for required relationships and the default be null and required = false for optional relationships. For example, an order might be picked up so you don't need an address. Therefore the FK for ShipToState should be null and required = False. However, if the user enters a state, it MUST exist in the state table.

MS has vacilated over the years between using null and 0 as the default for long integers. Right now, we're in the 0 land which I don't think helps novices. Instead it causes them to abandon relationships because they don't understand that it is the 0 as a default that is interfering with proper operation. Personally, I think that MS should make the default for long integer null but it is OK for the default for all other numeric data types to be 0. Why? Even though Long Integers are numbers, they are rarely used for arithmetic (quantities come to mind). Their primary use is as foreign keys. This kind of splits the baby but it does it in a logical way.

Let me go further with the two way relationship with Institutions. I do use this type of relationship in very rare instances but you need to know what you are doing for this to work. The way the schema works is that it allows there to be a data anomaly because an Item related via PersonItems to have an Institution value that is different from the Institution value in Persons.

One specific use of this type of relationship is to limit the values in a combo. We have Customers who have Contacts and we have Projects that we do for the Customer. We also have Contacts associated with a Project. That causes the two lines since the Project also has a relationship with Customer. Contacts have a m-m relationship with Projects but a 1-m relationship with Customers. We use the CustomerID in Projects to limit the combo that is used to assign Contacts to a Project.

 

[mikenyby]

I disagree with that statement. It can participate effectively because optional relationships are found frequently in the real world. But as you later said, the solution is to change the default for ALL FKs to null rather than 0. That enforces the relationship when a value is entered but ignores it when the FK is null. So, you need to have the default be null and required = true for required relationships and the default be null and required = false for optional relationships. For example, an order might be picked up so you don't need an address. Therefore the FK for ShipToState should be null and required = False. However, if the user enters a state, it MUST exist in the state table.

MS has vacilated over the years between using null and 0 as the default for long integers. Right now, we're in the 0 land which I don't think helps novices. Instead it causes them to abandon relationships because they don't understand that it is the 0 as a default that is interfering with proper operation. Personally, I think that MS should make the default for long integer null but it is OK for the default for all other numeric data types to be 0. Why? Even though Long Integers are numbers, they are rarely used for arithmetic (quantities come to mind). Their primary use is as foreign keys. This kind of splits the baby but it does it in a logical way.

Let me go further with the two way relationship with Institutions. I do use this type of relationship in very rare instances but you need to know what you are doing for this to work. The way the schema works is that it allows there to be a data anomaly because an Item related via PersonItems to have an Institution value that is different from the Institution value in Persons.

One specific use of this type of relationship is to limit the values in a combo. We have Customers who have Contacts and we have Projects that we do for the Customer. We also have Contacts associated with a Project. That causes the two lines since the Project also has a relationship with Customer. Contacts have a m-m relationship with Projects but a 1-m relationship with Customers. We use the CustomerID in Projects to limit the combo that is used to assign Contacts to a Project.

Thanks, I put the referential integrity back into place and tried changing the default value to null for both foreign keys, but the error message remains.

What I really don't get is why it's looking for ", as the matching field. Where does a quotation mark and a comma come into play?

Edit: I just deleted the relationship between Items and Institutions as well, as it's not really necessary. Still get the error message.

 

[mikenyby]

Development: There are a few persons on the persons table that do not have associated institutions. I just searched through the combobox on the subform and it will not display any persons who do not have an associated institution.

I'm not sure what could be causing this. Here's the SQL for the subform:

SELECT [FamilyName] & ", " & [GivenName] & " | " & [CityOfResidence] & " | " & [Institution] AS Name, Persons.PersonID
FROM Institutions INNER JOIN Persons ON Institutions.InstitutionID = Persons.AssociatedInstitution
WHERE (((Persons.PersonID) Not In (select PersonIDFK from PersonItem where ItemIDFK = forms![EditItem].[itemID])))
ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [CityOfResidence] & " | " & [Institution];

 

[Pat Hartman]

I just searched through the combobox on the subform and it will not display any persons who do not have an associated institution.

For optional relationships, you MUST use a left join so that you can see the persons whether or not they have a related institution. An inner join (the default) requires at least one matching row in each table.

 

[mikenyby]

For optional relationships, you MUST use a left join so that you can see the persons whether or not they have a related institution. An inner join (the default) requires at least one matching row in each table.

Like this?

SELECT PersonItem.PersonIDFK, PersonItem.ItemIDFK, Persons.FamilyName, Persons.GivenName, Persons.CityOfResidence, [FamilyName] & ", " & [GivenName] & " | " & [CityOfResidence] & " | " & [Institution] AS Name, PersonItem.PageNumbers
FROM (Institutions LEFT JOIN Persons ON Institutions.InstitutionID = Persons.AssociatedInstitution) LEFT JOIN PersonItem ON Persons.PersonID = PersonItem.PersonIDFK;

I also tried
SELECT PersonItem.PersonIDFK, PersonItem.ItemIDFK, Persons.FamilyName, Persons.GivenName, Persons.CityOfResidence, [FamilyName] & ", " & [GivenName] & " | " & [CityOfResidence] & " | " & [Institution] AS Name, PersonItem.PageNumbers
FROM (Institutions INNER JOIN Persons ON Institutions.InstitutionID = Persons.AssociatedInstitution) LEFT JOIN PersonItem ON Persons.PersonID = PersonItem.PersonIDFK;

Neither worked. Same error message. No persons without associated institutions are showing up.

 

[Pat Hartman]

In a "left" join, the table on the LEFT side of the join is primary. In a "right" join, the table on the RIGHT side of the join is primary. In an "inner" join, the tables are equal. I had to improvise because the schema doesn't include several of the fields in the queries.
When you don't know how to build queries, use the QBE. Point and click is pretty easy.

1. NEVER use "Name" as a column name or alias.
2. It is far easier on your brain and the rest of us if you use the same names for the PK and FK. If it makes you more comfortable then add a "_FK" suffix on the FK side so, after you change the name in the Persons table:


Sorry, because the Instutions table is the 1-side of the relationship, you use a Right Join rather than a Left Join.

SELECT Persons.PersonID,
    PersonItem.ItemIDFK,
    Persons.FamilyName,
    Persons.GivenName,
    Persons.FamilyName & ", " & Persons.GivenName & ", " & Instutions.Institution As FullName
FROM (Institutions RIGHT JOIN Persons ON Institutions .InstitutionID = Persons.InstitutionIDFK) INNER JOIN PersonItem ON Persons.PersonID = PersonItem.PersonIDFK;

I also aliased the table names to make the query more readable and qualified ALL columns with table names, not just some.