Insert into with new record causing error

[Guest]

I am using paired listboxes to input information into a table that I would
like to have bound by a one to many relationship. With the add button, I
have an insert into statement that adds the value to the foreign table and
moves that item from the first listbox to the other listbox. This works fine
if it is an existing record but if it is a new record it errors because of
the relationship of the primary key to foreign key because the autonumber is
not excepted yet. I did find that if I put an entry into one of my subforms
on one of my pages that it makes the record perminant and the sql statement
will work, however that form will not be used until a later time. If I
remove the relationship with the primary and foreign key it will work but if
the primary record is deleted the foreign record is not. Is there an easy
way to make it so the new record will become permanent through code to solve
this so I can use the relationship? Thanks in advance.

 

['69 Camaro]

Hi.

This works fine
if it is an existing record but if it is a new record it errors because of
the relationship of the primary key to foreign key because the autonumber
is
not excepted yet.

If referential integrity is enforced (and it should be), then the record in
the parent table must exist before it can be used in any record in the child
table.

I did find that if I put an entry into one of my subforms
on one of my pages that it makes the record perminant and the sql
statement
will work, however that form will not be used until a later time.

Either insert the record into the parent table programmatically or do it
with a form, but it's required before any record in the child table can
reference it. Obviously, it's easier with a form that the user fills out,
so consider moving up the date when that subform is available to the users.

If I
remove the relationship with the primary and foreign key it will work

You don't want to do that. Referential integrity will no longer be
enforced.

but if
the primary record is deleted the foreign record is not.

Referential integrity prevents these "orphan records." Don't remove
referential integrity.

Is there an easy
way to make it so the new record will become permanent through code to
solve
this so I can use the relationship?

The easy way is through a form that the user fills out and populates in the
parent table prior to the child record being created in the child table.
But you could also use a SQL INSERT INTO statement in the VBA code to save
the parent record, and then it will exist when it's needed in the child
table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi.

The easy way is through a form that the user fills out and populates in
the parent table prior to the child record being created in the child
table. But you could also use a SQL INSERT INTO statement in the VBA code
to save the parent record, and then it will exist when it's needed in the
child table.

If you're inserting the record into the parent table programmatically in a
database that's Access 2000 or later, then you'll want to retrieve the new
AutoNumber assigned to the record so that you can use it in the record in
the child table. Please see the following Web page for Access MVP Allen
Browne's code that inserts a new record into a table and retrieves the
resulting AutoNumber of that record:

http://groups.google.com/group/microsoft.public.access.queries/msg/c24c444d1f7a07b1

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.