Multiple tables on a form

[HL]

I have two tables which are related on one form.

The form is showing the data from the two tables which is great, but I have added a Cmd button for adding records for the two tables.

When I click on the button, I have an error message coming up say at the end of the record set. and it will not allow me to add a record.

How can I overcome this problem?????

I do not want to use subforms..... I know this is an easy way out of the problem, but it is not what i want....

 

[llkhoutx]

I assume that you have a form (showing one table) and a subform (showing the other). I don't believe that you can directly show data from two tables at one on a form unless you're using a query of those 2 tables as the recordsource.

Update your tables, one at a time. You might be getting the error message because the first table has a new record and the second can't tumble because there's no record in the second table yet. To solve this build youir second table record first with a key to tumble, then your first record, then the second record tumbles.

 

[The_Doc_Man]

It is not clear from your discussion that you are showing more than one record at a time from each table. If you are not, then build a query that selects a JOINED record from the combination of tables. You can update through a query as an atomic operation and it is nearly transparent most of the time.

On the other hand, if you are doing a sub-form case, you have a parent-child relationship. If you have the query between the parent and child set up correctly, there can be no orphaned child records, particularly if you are enforcing the relationships between parent and child. Therefore you must set up the parent data first, then the child data.

 

[Keith P]

assuming that you are using a query as the recordset with a join on the tables then try setting the forms Recordset Type to Dynaset(inconsistent updates). This will work in some situations.