Inserting values from one table into another table

[Robert Robinson]

I have two tables which are joined by a common field. In the parent table,
this key field is an autonumber field. I would like to write some code which
takes that value in this key field and enters it into the foreign key field
in the second table. What is the code that is used for such a transaction?

I am betting this that is a simple bit of code, and I am just overlooking it
in my reference materials.

 

[jahoobob]

How can they be joined by a common field, yet you want to add this
common field to the second table? I'm confused.

 

[Jeff Boyce]

Robert

Why? As in "why do you need the foreign key value in your child table?"

Access can handle this for you, without coding. Create a form based on the
parent table. Create a form based on the child table. Open the "parent"
form in design mode and add a subform -- pick the "child" form. You'll need
to tell Access which field(s) match up (parent-child).

Then, every time you have a parent form record, entering a record in the
child form automatically inserts the parent form's primary key as a foreign
key.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Robert Robinson]

To clarify:

These two tables are the culmination of a set of processes to create
invoices based on a set of sales. The "parent" table has the sales
information; the "child" table has invoice number, invoice date and payment
terms. Both tables have forms attached to them. Once the sales lines are
accumulated in the sales (parent) table, the user opens the invoice form
(which triggers an invoice number), and selects the payment terms. I then
have the invoice form close, but would like to insert the invoice number in
the sales (parent) table. This will assist with the generation of invoices
and related sales reports.

 

[Pat Hartman]

That isn't the way the parent/child relationship works. As you have
described the relationship, the SalesID goes in the Invoice table rather
than backwards as you are asking. You need to do some more thinking about
this relationship. Can a Sale have more than one Invoice. Can an Invoice
be for more than one Sale?

 

[Robert Robinson]

Please excuse my misspeak. There is not a parent/child relationship; rather a
direct relationship between the two tables. Please note that my other
considerations have been met; all I want to do is insert the invoice number
back into my sales table. Everything else has been done; all I am looking for
is the code that is used to insert a value from 1 table into another. Thank
you.

 

[Pat Hartman]

I don't know the context of your question. You can't just write a query to
get a record who's key value you don't know so you can place it in some
other record. If you use a main form with a subform and the master/child
relationship are properly defined, Access automatically populates the
foreign key. You didn't answer my question about how the two tables relate
to each other. I still think you are trying to make a pathological
connection between the two tables. i.e. tblA's key in tblB and tblB's key
in tblA. That is a deadly embrace since you can't add a record to either
table without first having a related record in the other table.