Unable to enter new data in form with fields from multiple tables

[Jamie021]

Hi everyone,

I'm new to the site and relatively slow with access so I am hoping you all can help me. I had an existing database with 2329 records entered into it. All of the fields (220ish) were all in one table. Myself and my co-workers wanted to rebuild the database without losing the data. We wrote queries to transfer the data from the original database to the new database and split the data from the original 1 giant table to 9 smaller tables.

The transfer of data worked so I went to start making forms. When I went to add fields from different tables I had to built a relationship, which I did. All of the data that transferred over from the new database is in the form (now multiple forms linked by button) but I can't add new information. I get an error stating, "You cannot add or change a record because a related record is required in table ..." and the table referenced keeps changing.

Any advice?? Thanks in advance for any/all help!

Note: I can post pictures of design view of the forms and tables but I can't post any of the data already stored. It's private data relating to domestic violence cases for a police department.

 

[CJ_London]

Having split your data into separate tables, it sounds like you are going back to a 'spreadsheet' view to maintain them.

Try to keep each form to have a recordsource of one table, you can combine the view using subforms.

So for example you have a customer table, an invoice header table and an invoice line table and you want to add an invoice.

Very simply, your main form would use the invoice header, you would have a subform to display the customer data and another subform to display the invoice line data. The subforms are linked back to the main form using the linkchild and linkmaster properties of the subform control.

If you have to have a recordsource of more than one table - refer to this link

http://www.access-programmers.co.uk/forums/showthread.php?p=1320139#post1320139

If you still have to have a multi table recordsource, try changing the recordset type to dynaset - inconsistent updates. But you will need to maintain the links between the tables yourself

 

[Jamie021]

Thank you CJ_London!!

I am going to try splitting it up into subforms. I'll let you know if I run into issues.