One to Many data Error

[Guest]

When I try to create a new record on my form and insert data into a
particular field I recieve the following message:

The current field must match the join key '?' in the table that serves as
the 'one' side of one-to-many relationship. Enter a record in the 'one' side
table with the desired key value, and then make the entry with the desired
join key in the 'many-only' table.

The thing is that although this field is used as a key in my 'StockUnit'
table it is a foreign key for this table. but in trying to fix the problem
i've even removed the link to the 'StockUnit' table.

Also, when I open the record source for the form and looking at the SQL
View, I can enter values as a new record into this field. It worked
yesterday with my last test and I havn't added much to the form today.

Any insight into what I may have done would be great.
Thanks in advance,

Pip'n

 

[Allen Browne]

Pip'n, I'm guessing that the source of this form is a query that uses more
than one table?

If so, and you are quite sure you have the foreign key field (not the
primary key from the other table) in your query, it sounds like a bug in the
way Access handles the assignment of Default Value. It ought to use the
Default Value only for the fields in the table you are seeking to add the
new record to. Unfortunately, it sometimes tries to assign the Default Value
for the lookup table as well, which fails with the nonsense message you
quoted.

The clue that you have stuck this bug is the timing: it happens on the first
field you try to assign, regardless of which field that is (even on the
first keystroke IIRC.)

The workaround is to remove the Default Value from any fields in the lookup
table, and also the Default Value from any controls on the form.

There could be other causes, but your description sounds like this issue.

 

[Guest]

Allen,

Thanks for your advice. The answer wasn't the exact solution but it gave me
a direction to look in. I found that the default value of two of the fields
from my "StockUnits" table that were not keys of any sort were causing the
problem. The problem had only occurred when I tried to enter a value for the
foreign key field all other fields accepted values without fault. Once I
removed the default values for these 2 fields the problem disappeared.

Thanks again,
Pip'n