I am trying to create a form that will update multiple tables simultaneously (ie one entry will populate fields in several tables)in order to decrease the ammount of typing involved. If someone has done this or something similar I would appreciate an assist. Feel free to e-mail me with any responses. I need responses as quickly as possible.

Join the related tables in a query and use the query as the recordsource for the form. Be sure to include the key fields from both sides of the join even though that seems redundant. You don't need to show them on the form.

I'm trying to do something similar to this, but it would not let me add records to the form. I then thought that because it was a query, you can't add anything, you could just pull the data in from your tables, not put your data from the form INTO your tables. Am I wrong, can a query-based form update multiple tables???

You might want to try altering the type of query.

The default query type is a select query which will view the underlying data but not allow you to change it.

Try selecting an update query or an append qery and see if you have better results.

The reason it would not let you add new rows is because you need the key fields from both sides of the join in the query. The form only needs to show the key field from the first table though.

Here's an example of an updateable query that joins two tables.

SELECT C.CONTRACT_NUM, C.CUSTOMER_NUM, C.ORDER_TYPE_CD, S.CONTRACT_NUM, S.SHIP_TO_CUST_LOC, S.MONTHLY_CONTRACT_AMT, S.MONTHLY_POSTAGE_HANDLING
FROM DBO_ORDER_CONTRACT AS C INNER JOIN DBO_ORDER_SHIP_TO AS S ON C.CONTRACT_NUM = S.CONTRACT_NUM;


Notice that CONTRACT_NUM is included twice. Once from the contract table and once from the ship to table. The form I built to test this does not show the CONTRACT_NUM from the ship to table. It only shows the number from the contract table. If you omit the CONTRACT_NUM from the ship to side, you can update the data but you can't add rows.

Open the query in data sheet view. Click the >* to open a new record. Type the fields, skipping over the key field from the second table - notice how Access automatically populates it when you enter a value in one of the columns from the second table! That also happens when you use the query as the recordsource for a form.

If your tables are joined on more than one field, ALL of the key fields from both sides need to be selected.

One other thing to observe. If you don't enter data in any of the columns of the second table, NO row will be created there. Only a row in the first table will be created.

I assume you have a one-to-one relationship. There is no point in doing this for a one-to-many relationship since you want a form with a subform to use for update in that case.

One last caveat. If there are no required columns in the second table, you should use a left join rather than an inner join otherwise if you add a row to the left table but none to the right table, that row will seem to disapear when you open the form the next time.

[This message has been edited by Pat Hartman (edited 12-09-2000).]