troubleshoot subform

[Guest]

I have three tables A, B and C.
Table C is an intersection table for a many-to-many relationship between A
and B

A has one primary key field
B has two primary key fields
C has three foreign key fields: one to A, two to B

I have a query Q that joins B and C on the two primary key fields of B

I have a form based on A and a subform based on Q

The form and subform are linked via the primary key field of A
In the subform the first field of Q is a combo-box which is enabled and not
lock
The first field is the first of B's two primary keys
The combo-box selects a record from B and uses the AfterUpdate event
procedure to update C's second foreign key.

Here is what happens:
When I click in the combo-box and press a key, I get the error message:
"this property is read only & can't be set"

After dismissing the error message, the combo-box allows me to set the first
foreign key and then correctly updates the second foreign key in the event
procedure.

After filling in all the fields, attempting to update the record gives the
following message: "index or primary key cannot contain a null value"

This is puzzling because the foreign key to A is handled by the parent :
child relationship of form and subform and the two foreign keys to B contain
valid data.

Can someone help me understand what is going on ?

 

[Guest]

here is some additional info for this puzzle:
using the form/subform, updates to an existing record succeeds
using the form/subform, adding a new record fails
using the query Q datasheet, adding a new record succeeds

 

[Guest]

Hi plc,

replies in order;

here is some additional info for this puzzle:
using the form/subform, updates to an existing record succeeds
using the form/subform, adding a new record fails
using the query Q datasheet, adding a new record succeeds

Is the above combobox bound to the correct rowsource field? Is the correct
column the bound column?

Is the second foreign key included in the combobox, perhaps as a hidden
column? If so, try removing it if above fails.

Can you put a stop in the code and check the value of each foreign key in
the vba window BEFORE the error message appears (probably forms beforeUpdate
event)

TonyT..

 

[Guest]

Thank you TonyT for your suggestions

It took about 8 hours to debug but yesterday and last night I finally found
out what was going on.

Table C had a combo box for selecting records from Table A
Since the column for this foreign key was ignored ("hidden") in the subform
the table A foreign key was read only.

Changing the table A foreign key to a text box helped matters but then I
discovered it was putting in the Form/Table name instead of the value it was
supposed to be getting from the parent link. This left the foreign key to
table A null which is why I got the second message.

The solution was to assign the foreign key to table A in the event procedure
OnInsert. This event procedure gets called on the first keypress. Once that
happened everything worked. What was really confusing is that putting in
text boxes with the ControlSource on the foreign key to A looked normal, but
they weren't updating.