You cannot add or change a record because a related record is requ

[Xfree]

I have two tables joined with a many to one relationship. In the many table I
have made a foreign key to match the key ID field in the one table. Then I
made the foreign key a combo box with the row source the primary key in the
one table but to show the info from a text box instead of the ID number which
I have done before in other joins. Now I am getting a error message when I
try to select something from the combo box "You cannot add or change a record
because a related record is required in the table" which is the one table.

I can not figure out why, I have compared my settings to a similar setup and
cant find any differences.

 

[Dirk Goldgar]

I have two tables joined with a many to one relationship. In the many table
I
have made a foreign key to match the key ID field in the one table. Then I
made the foreign key a combo box with the row source the primary key in
the
one table but to show the info from a text box instead of the ID number
which
I have done before in other joins. Now I am getting a error message when I
try to select something from the combo box "You cannot add or change a
record
because a related record is required in the table" which is the one table.

I can not figure out why, I have compared my settings to a similar setup
and
cant find any differences.

It sounds like you may not have have the combo box's Bound Column property
set correctly. What are the values of these properties of the combo:

Control Source
Row Source
Control Source
Bound Column
Column Widths

?

 

[Xfree]

Control Source =PourScheduleID (which is the foreign Key)

Row Source = SELECT PourSchedule.[Master Heat Number] FROM PourSchedule
ORDER BY PourSchedule.[Master Heat Number];

You had Control Source twice in your list , should this been something else?

Bound Column = I have tried several things here, with a 1 I do not get the
error but I can not see the desired text only the ID number, with a 0 I get
the error

Column Count = 1
Column Widths is blank

 

[Xfree]

I just found the same issue I am having posted under "error 3201" this was
the last answer posted:

When 1 builds a one to many relationship and enforces referential integrity,
the system keeps you from adding, changing, or deleting a record from the
many side, before creating a record on the one side.

This effectively, for instance, keeps one from adding or deleting an order,
if there is no customer.

To resolve the error, make sure you have a one-side record first.

What I don't understand about that answer is how to do that if I am trying
to add records to the table with the foreign key with some data from the
primary key field.

If more explanation is needed on my part let me know and I will try to add
info to better explain.

 

[Dirk Goldgar]

Control Source =PourScheduleID (which is the foreign Key)

Row Source = SELECT PourSchedule.[Master Heat Number] FROM PourSchedule
ORDER BY PourSchedule.[Master Heat Number];

You had Control Source twice in your list , should this been something
else?

I meant that to be Column Count, but I forgot to change it.

Bound Column = I have tried several things here, with a 1 I do not get the
error but I can not see the desired text only the ID number, with a 0 I
get
the error

Column Count = 1
Column Widths is blank

Your row source only selects one field from PourSchedule, so how can the
combo box possibly store the ID and show the description? Is [Master Heat
Number] the primary key of PourSchedule, or is there a field named, maybe,
"PourScheduleID" that is the primary key?

Supposing that the primary key is actually a a field named PourScheduleID,
and you want to show [Master Heat Number] but store PourScheduleID, your
combo box's properties would be like this:

Row Source: SELECT PourScheduleID, [Master Heat Number]
FROM PourSchedule ORDER BY [Master Heat Number];

Column Count: 2
Bound Column: 1
Column Widths: 0"; 1"

The second column's width, of course, can be whatever you need it to be, but
the first column's width must be 0.

 

[Dirk Goldgar]

I just found the same issue I am having posted under "error 3201" this was
the last answer posted:


What I don't understand about that answer is how to do that if I am trying
to add records to the table with the foreign key with some data from the
primary key field.

If more explanation is needed on my part let me know and I will try to add
info to better explain.

I see no reason yet to think this is relevant to the problem you're having.

 

[Xfree]

Thank you for the help Dirk it is working now. I do have an additional
question, what is the effect of > Column Widths: 0"; 1" Column Count: 2 and >
Bound Column: 1 in layman's terms?

Mark Pierce

Control Source =PourScheduleID (which is the foreign Key)

Row Source = SELECT PourSchedule.[Master Heat Number] FROM PourSchedule
ORDER BY PourSchedule.[Master Heat Number];

You had Control Source twice in your list , should this been something
else?

I meant that to be Column Count, but I forgot to change it.

Bound Column = I have tried several things here, with a 1 I do not get the
error but I can not see the desired text only the ID number, with a 0 I
get
the error

Column Count = 1
Column Widths is blank

Your row source only selects one field from PourSchedule, so how can the
combo box possibly store the ID and show the description? Is [Master Heat
Number] the primary key of PourSchedule, or is there a field named, maybe,
"PourScheduleID" that is the primary key?

Supposing that the primary key is actually a a field named PourScheduleID,
and you want to show [Master Heat Number] but store PourScheduleID, your
combo box's properties would be like this:

Row Source: SELECT PourScheduleID, [Master Heat Number]
FROM PourSchedule ORDER BY [Master Heat Number];

Column Count: 2
Bound Column: 1
Column Widths: 0"; 1"

The second column's width, of course, can be whatever you need it to be, but
the first column's width must be 0.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thank you for the help Dirk it is working now. I do have an additional
question, what is the effect of > Column Widths: 0"; 1" Column Count: 2
and >
Bound Column: 1 in layman's terms?

Mark Pierce

Hi, Mark. I'm glad you got it working.

Column Count specifies the number of columns in the combo box. In a combo
box that has a query or table for its rowsource, Column Count ought normally
to be the same as the number of fields selected by the query. It can be
less, but I think Access will get confused if you make it more. In your
case, Column Count is 2, because your rowsource is selecting 2 fields and
you want to have both of them in the combo box.

Bound Column specifies which of the combo box's columns represents the value
of the combo box. If the combo box is bound to a field in your form's
recordsource, the bound column is the one whose value (for the selected row)
will be stored in that field. In your case, the combo's Bound Column is 1,
because you want to store PourScheduleID, the first column, in the
underlying field of the form's current record.

Column Widths specifies the display width of each column of the combo box.
By setting Column Widths to {0"; 1"}, you're saying the first column should
be hidden (because its width is 0), and the second column should be 1 inch
wide. As I mentioned, you can certainly change that 1 inch to some other
width. Note that when the combo box is not "dropped down", it always
displays the first column that has a non-zero width; in this case, column
2.