insert into 2 diferent tables

[Rafaeljunio.ti]

Good morning!
How do I make to insert into two tables using the same access form?


I have a table of records, and the button click event I need to insert some fields in another table.

Below is the link to the file. Mdb
https://dl.dropbox.com/u/19875180/Teste.mdb

Does anyone have an example?

[orange]

You may get some help using Google.
I found this that may be useful to you.
http://office.microsoft.com/en-ca/ac...001034548.aspx

You should create the relationships between your tables in the relationships window.

You only enter data into 1 table at one time.
You could have 2 buttons
--btn1 -- insert the proper fields/values into your first table
--btn2 -- insert the proper fields/values into your second table

Good luck with your project.

[June7]

Why do you need to do this? Sounds like duplication of data.

[RonL]

You only enter data into 1 table at one time.

I still don't understand why folks make this assertion. The very reference you cite explicitly describes how a form can be bound to a query which itself joins two tables. So updating the controls on that form, or creating a new record with them, would write simultaneously to both tables, no?

[orange]

No.
Try and write an SQL update query that updates 2 tables in the same statement.

You can certainly have 1 or more tables involved in a query that can be a recordsource for a Form.
But the Form doesn't necessarily Update either of those tables, it could be an entirely different table, but there will only be 1 table UPDATED regardless.

Do some research on SQL UPDATE query and you will see that only 1 table gets updated.
Now it's possible that many, even all records in the table get updated, but only records in 1 table get updated.

[June7]

Sorry, Orange, there are situations that allow editing two tables in same query.

If two tables have a 1-to-1 relationship, both can be edited and new records created. In at least one of the tables, the linking field must be set as primary key, both can be but one must be. That one PK can be autonumber but the other cannot and in either case will have to manually input the FK. So essentially, a form/subform arrangement is really the most practical method for entering related records.

Also, with cascade update activated in Relationships, in a query with join to 'lookup' table a key value in the 'lookup' can be edited and this change will reflect in all related records. However, this edit will probably trigger a warning message.

[RonL]

No.
Try and write an SQL update query that updates 2 tables in the same statement.

You can certainly have 1 or more tables involved in a query that can be a recordsource for a Form.
But the Form doesn't necessarily Update either of those tables, it could be an entirely different table, but there will only be 1 table UPDATED regardless.
..........

I'm familiar with the SQL Update syntax, but I was thinking of DoCmd.RunCommand acCmdSaveRecord, which, if I understand correctly, is at the core of saving a new record on a form. I know I'm confused about this, but I'm trying to understand because I think it's fundamental. What I was thinking was that when you save a new (or edited) record that way, you're adding a record to (or modifying) the associated recordset. But since that recordset was derived from a query of multiple (joined or related) tables, the tables are also modified accordingly.

Obviously, I'm missing something really basic. I'd appreciate if someone could detail what happens when a record is saved from a form whose recordsource is a query. If only the corresponding recordset is modified, how does the change get reflected back to the underlying tables? I suspect I should really be regaarding a recordset as equivalent to a *single* table, albeit a transient one, and that at the "real" table level, the illusion of updating multiple tables is due to the fact that link keys are being updated in a single table. Am I on the right track?

Thanks, -Ron

[June7]

A record (new or edit) is committed to table when one of the following happens:

1. move to another record

2. close form (or table/query if working direct with table/query)

3. run 'save' code

Until one of those events takes place, entry/edits can be canceled.

I've never harbored the 'illusion' of updating multiple tables from a single query/form. However, can't think of where I have set up forms that would even seem to be doing that. I use form/subform arrangements.

Did you review my last post?

[orange]

Agree that there are conditions where 2 tables do get updated by JET or DBMS. You could even argue that using transaction logic would appear to do that from user perspective also-- Trans Start -Trans End encompassing multiple update queries. I was thinking specifically of an Update SQL query.

Form/subform is typical method for entering records. Here the key is to complete the Form (1 side) before entering info into the subform (Many side), then moving to a new (Form) record. If you do it the other way around, you may get records in both tables, but no value for the FK in the Many (subform) table.

I find Cascade Update quite limited. I tend to use autonumber PKs.
see http://www.access-programmers.co.uk/...php?t=233345#2

As for the original OP question, I would create some queries in events to do the updates as required. I often have forms to populate Junction tables with combos and a button, where combos are populated by each of the tables involved. Select the appropriate value from each combo and click button to invoke an append query in vba. Update the junction table , and give message saying what was done. I don't see why this couldn't be done for multiple tables if that's what is required.

Depending on what exactly is required (because everything is UNBOUND and I don't understand the mdb), probably better to set up as Form/Subform with some logic to ensure necessary fields are completed before saving or moving off the record.

[RonL]

..........Did you review my last post?

Yes, though not til after mine. Thank you June and Orange for your considered responses.

This is the OP's thread, but as a general comment, this is a wonderful set of forums for solving immediate problems. I've learned and continue to learn a lot here.

My problem is insufficient understanding at the conceptual level. So when, for example, June enumerates the ways in which a table is updated from a form, I'm asking myself, yes, but what is actually happening at the *recordset* level? We know that the recordset is, in effect, the bound query, and that query was built on multiple tables. Therefore (I was thinking), if the recordset is updated (by any of the ways June mentions), then the multiple tables must be updated. ie. There must be something like a SQL Update command buried in the underlying engine capable of updating several tables at a time. OTOH, I was neglecting the fact that the multiple tables are *related* to each other, so whatever is happening in the recordset update, it must honor the relations. I think Orange is pointing this out in his post. I'm guessing that the recordset is a single, internal table which gets updated and is then parsed by the engine back to the tables from which the original SQL Select query is formed.

It's been years since my last rdbms project. Hopefully, this stuff will clarify as I get back into it. Thanks again for the responses.

-Ron