Can not enter value in blank field

[Andren]

Hi all - and Happy New Year!

I feel a little bit embarrased to ask this but I'll do it anyway.

It is about "joins".

I have constructed a lot of tables (one-to-many) that are properly joined. Then I have based a query on them.

Then I made an input form based on the query. I could put in two reccords through that form. Trying to put in a third reccord gave me the following error message:

"Can not enter value in blank field on one side of outer join".

Why after two reccords? How do I handle it?

I'm as always thankful for all suggestions.

Yours: Anders

 

[The_Doc_Man]

The problem appears to be that you are changing the data in a field on the ONE side of a MANY-to-ONE relationship defined by an explicit JOIN. Access has realized that you are doing something improper. I think this action that you attempted violates normalization rules.

The field in the parent record suddenly has to become multi-valued because you now have two records with that field one way, and you have asked Access to make the next record with that field set to another way. But it isn't really another record! It is a JOIN of two recordsets and only ONE of the recordsets supports being multi-valued. (The child record components...) You are trying to change a field that isn't in that part of the JOINed record.

I know that wasn't clear in English, but I think it is correct.

Look at it this way. For the JOIN record, it transparently appears that for Primary Parent Key 1, you can have two different records with Primary Child Keys 1 and 2. But when you start to insert the third record, you change something in the parent that doesn't change the primary key value. (i.e. Primary Parent Key is still 1.) Now Access sees that this non-key field in the parent table depends on a child key, which is backwards.

Hope this helps.