Using MS Access/Referencial Integrity
QUESTION: Ho Scott,
I have 3 tables. One table is used to store data. 2 fields in this table are drop downs pulled from 2 other tables. Primary keys in these two tables are linked to the fields in the data storing table but referential integrity is not forced. The query is made out of these 3 tables. When, in query, I try to leave one of drop downs blank it does not allow me. However, the user may select or not select these fields. What can I do so that a user may leave one or both fields blank. I do not want to add a value "n/a" to these 2 "drop down" tables and make user select it all the time they want to leave it blank. It is not time efficient.
ANSWER: Could the field in the table be set as required or not allowing zero length strings?
[an error occurred while processing this directive]---------- FOLLOW-UP ----------
QUESTION: I have removed the Primary Key from "drop down" tables (not the field itself but the designation of the field as a primary key) and this seems to fix the problem. Will it work? What am I losing but not having a primary key assigned to a field in these drop downs?
OK, that indicates to me that the form isn't set up correctly. Your data storage table should have 2 fields as FOREIGN KEYS in them. The FKs should link to the Primary Keys in the "lookup" tables. In the query, you should be joining on PK to FK, but pulling the FK column into your grid.
Also, you should not be entering data into a query. Data interaction should be through forms only.