Problem Adding Records To Child Table

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!

Re: Problem Adding Records To Child Table

On Tue, 04 Sep 2007 06:31:09 GMT, "Phil Reynolds" wrote:

>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]

Re: Problem Adding Records To Child Table

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" wrote in message
news:lp2rd35t23hbjj9i7ipfrone4265140m7q@4ax.com...
> On Tue, 04 Sep 2007 06:31:09 GMT, "Phil Reynolds"
> wrote:
>
>>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]

Re: Problem Adding Records To Child Table

On Tue, 4 Sep 2007 14:49:28 -0500, "Phil Reynolds" wrote:

>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]

Re: Problem Adding Records To Child Table

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.


"John W. Vinson" wrote in message
news:2sprd3dectbejdpd3oboksnv6f9jc2s6qi@4ax.com...
> On Tue, 4 Sep 2007 14:49:28 -0500, "Phil Reynolds"
> wrote:
>
>>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]

Re: Problem Adding Records To Child Table

On Tue, 4 Sep 2007 19:19:46 -0500, "Phil Reynolds" wrote:

>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]

Re: Problem Adding Records To Child Table

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]![Inv tryID]))
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!



"John W. Vinson" wrote in message
news:okfsd3p70ok4p8e3vuggrdhdnhsekn2saj@4ax.com...
> On Tue, 4 Sep 2007 19:19:46 -0500, "Phil Reynolds"
> wrote:
>
>>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]

Re: Problem Adding Records To Child Table

"John W. Vinson" wrote in message
news:okfsd3p70ok4p8e3vuggrdhdnhsekn2saj@4ax.com...
On Tue, 4 Sep 2007 19:19:46 -0500, "Phil Reynolds"
wrote:

>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]