Can a form create new records in one table that corresponds to data in another table?

[leeer333]

I'm trying to make a form that will enter data into two different tables. Each row in the two tables corresponds to the same customer (and can have the same ID) but they need to be split in two because the data is imported separately at different times from Excel spreadsheets when people fill out online forms.



I made a multi-form and set up a relationship between the tables and if there is a record in the second table that has the same ID as the first table, then I can open the record and edit them fine. If both tables don't have an entry with the same ID, then I can't open that record with the form.

I could technically get around this by just making a bunch of blank entries in the second table but then those blank entries will have to be deleted to add information later if I want to upload by excel. Also, then I have a bunch of blank entries in the database.

Anyone know how I can use one form that will create a new record in one table that corresponds to an already existing record in another table?

[rpeare]

It sounds to me like you need to look at using subforms. Your main form would be based on your customer, and your subforms would be based on the data in the spreadsheets you import from excel. All you'd need to do is create two subforms for the excel data and link it to the main form through the customer ID.

[leeer333]

I did create a subform. The problem is that the entire form won't open when there isn't any data for the fields that are in the subform. For example, I have two customers and their IDs are 1 and 2, respectively. For customer 1 I have information in both tables and I can open the record. For customer 2, I only have information to fill one of the tables. When I try to open the form, it says that "You can't go to the specified record" and it won't open. As soon as I create a row in the second table that has the ID for customer 2, the record will open.

[rpeare]

Can you post a sample database, just make a copy of your database, clear out any information you don't want seen and put in some dummy data then zip it up and upload it to this site.