use insert into with two relational tables (1 Viewer)

[mhakim]

how to make data enter two relational tables using insert into statement

i have two tables for invoicing one for invoic head data
and other table for invoice items and details

so how to use insert into with in two tables they are related

 

[CJ_London]

You use two insert queries, the first to create the header record, the second for the details table

if you are using the standard form/subform arrangement for this, it happens automatically, no need for queries

 

[mhakim]

You use two insert queries, the first to create the header record, the second for the details table

if you are using the standard form/subform arrangement for this, it happens automatically, no need for queries

would you please give small example with attached fill

 

[GPGeorge]

Check out the Northwind Developers Edition for great examples of completing Header and Header Details in two related tables using main form/sub form designs. You can download the template from here.

Typically, one would not normally encounter a situation in which you'd do that with two queries because the source data would have to be found in a third table or maybe a third and a fourth table, or come from an unbound form, or something else out of the mainstream. A situation where you'd need to implement this two table-two query process just would not normally come up.

 

[The_Doc_Man]

The rule is that Access, when dealing with parent/child table relationships (or header/detail cases), is that the parent record MUST be created first. That is because until the parent record exists, you cannot create a child record. Access does not allow creation of orphan records (children with no parents.) Therefore, you enter data in two steps. First, create the parent record. Or as you called it, header. Then you can create as many child records (detail records) as you need.

The two most common ways to do this are to have a parent/child FORM combination that has a form bound to the independent (parent, header) record and a sub-form bound to the dependent (child, detail) record. You use the form's parent/child link properties so that the child records you enter will contain the linking field that expresses the relationship between parent and child. If you are going to have multiple detail records, this is the most common way to go. You can look up in the article immediately below.

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

If you wanted to do this with VBA, you would probably want to use the CurrentDB.Execute method on SQL statements "INSERT INTO" - which you can look up in the linked article immediately below. For beginners this method might be more complex than you are comfortable attempting right now, particularly if you have multiple detail records to add.

INSERT INTO statement (Microsoft Access SQL)

Office developer client VBA reference documentation

learn.microsoft.com