Problem Adding Records To Child Table

[Phil Reynolds]

I have two tables: table A (parent) and table B (child) which have a
one-to-many relationship. Fields A.1 and B.1 are both the primary key fields
of tables A and B, and each is an autonumber field. Field B.2 is the foreign
key field which links to field A.1.

I have a query with SQL as follows:

SELECT B.*, A.1, A.2
FROM B INNER JOIN A ON B.2 = A.1
ORDER BY B.1

When I open the query and go to the new record, I enter in B.2 the value for
A.1 for one of the existing A records. The new record is added in the B
table. No problem.

However, when I base a form on the above SQL and then try to add a record, I
am unable to do so.

In the form's BeforeInsert event, I have the following code:

Me!2 = lngVariableContainingA1value

(the field is not really called 2; I just use that to show that it's the
"B.2" field noted above).

When I type a value in the form (corresponding to one of the B fields, such
as B.3 or whatever), the above BeforeInsert code executes, and I get the
error: "Field cannot be updated" (error 3164).

What am I doing wrong? Thanks!

 

[John W. Vinson]

What am I doing wrong? Thanks!

I'd say trying to use the wrong tools. The simplest way to handle parent-child
data is to use a Form for the parent table, with a Subform for the child
table; use the parent table's primary key as the Master Link Field and the
child table's foreign key as the Child Link Field.

John W. Vinson [MVP]

 

[Phil Reynolds]

I usually use subforms (which is probably why I've never run into this
problem before). But, in this case, the fields are all being entered for the
child table, except for one field from the parent table, and so I wanted to
avoid that, if possible (since it's a little awkward to navigate in and out
of).

Re. this configuration, I know it's possible to set up a form with a parent
and child table joined together in the underlying query. But, for some
reason, it's not working here.

 

[John W. Vinson]

Re. this configuration, I know it's possible to set up a form with a parent
and child table joined together in the underlying query. But, for some
reason, it's not working here.

Is [1] the Primary Key of the parent table? Do you have a relationship defined
with enforced referential integrity?

John W. Vinson [MVP]

 

[Phil Reynolds]

Yes, [1] is the primary key in each table. B.2 is the foreign key in the B
table to A.1 in the parent table.

Yes, relationship with referential integrity is defined.

Re. this configuration, I know it's possible to set up a form with a
parent
and child table joined together in the underlying query. But, for some
reason, it's not working here.

Is [1] the Primary Key of the parent table? Do you have a relationship
defined
with enforced referential integrity?

John W. Vinson [MVP]

 

[John W. Vinson]

Yes, [1] is the primary key in each table. B.2 is the foreign key in the B
table to A.1 in the parent table.

Please post the actual SQL and your code. There's something else going on
here!

Hmmm... are both these tables *local* tables in this database, or linked from
another database? If the latter, is it an Access database or something else
(SQL/Server, MySQL, Excel, ...)?

John W. Vinson [MVP]

 

[Phil Reynolds]

Here is the actual SQL:

SELECT DescriptionTitles.*, Descriptions.IncludeGeneralTitle,
Descriptions.InvtryID AS DescriptionsInvtryID
FROM DescriptionTitles INNER JOIN Descriptions ON DescriptionTitles.InvtryID
= Descriptions.InvtryID
WHERE (((DescriptionTitles.InvtryID)=[Forms]![frmCataloguing]![InvtryID]))
ORDER BY DescriptionTitles.ID;

And here is the actual code:

Private Sub Form_BeforeInsert(Cancel As Integer)

On Error GoTo Error_Label

Me!InvtryID = mlngMainIndex

Exit_Label:
Exit Sub

Error_Label:
MsgBox Err.Description, vbCritical, "Error " & Err.Number
Resume Exit_Label

End Sub

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Error_Label

mlngMainIndex = Forms!frmCataloguing!InvtryID

Exit_Label:
Exit Sub

Error_Label:
MsgBox Err.Description, vbCritical, "Error " & Err.Number
Resume Exit_Label

End Sub

Thx!

Yes, [1] is the primary key in each table. B.2 is the foreign key in the B
table to A.1 in the parent table.

Please post the actual SQL and your code. There's something else going on
here!

Hmmm... are both these tables *local* tables in this database, or linked
from
another database? If the latter, is it an Access database or something
else
(SQL/Server, MySQL, Excel, ...)?

John W. Vinson [MVP]

 

[banty]

Yes, [1] is the primary key in each table. B.2 is the foreign key in the B
table to A.1 in the parent table.

Please post the actual SQL and your code. There's something else going on
here!

Hmmm... are both these tables *local* tables in this database, or linked
from
another database? If the latter, is it an Access database or something else
(SQL/Server, MySQL, Excel, ...)?

John W. Vinson [MVP]