Search code examples
formsms-access

Create multiple records in a table using 1 form in MS Access 2013

I know this has been asked before, but I couldn't figure it out in my case.

I have a table called t_JOB, and one of the columns is "PO #". Each PO # can have multiple PO Line Items. So I created a table called t_PURCHASES_PO_ByLI which has the columns "PO #", "Line #" and "Line Amount". PO # from the t_Job table is a primary key and is a 1 to many relationship with the PO # in the t_PURCHASES_PO_ByLI Line Items table.

I want the user to be able to enter the PO # in the t_JOB table as well as the line # and line amount for as many lines items as they want in 1 form. Now after some research I added a subform and was able to do this BUT, it only worked if I entered 1 line # and line amount. I had the textboxes for more but it wouldn't send the values.

I understand forms are supposed to be for 1 record only, but in this case it would make sense for the user to be able to enter the PO #, and well as all of the line #'s and amounts associated with it all at once. How can I do this or is there a different way to go about solving this?

Edit

Here is what I enter in my form:

enter image description here

Notice how it only stores the value 5 for the Line #'s? The default values for each are 1, 2, 3 respectively. Only when the form first loads and I switch from the Line #1 textbox to the description #1 textbox does it change to 5. If I go and change it back to 1, all the values for line # switch to 1. They don't stay at 1, 2 and 3.

Also, notice how all of the description textboxes are the same? I don't want this to be the case, but whenever I edit one of the description boxes, it edits them all. How can I make them unique?

Here is what gets written to the t_JOB table:

enter image description here

Here is the data stored in the t_PURCHASES_PO_ByLI:

enter image description here

Also, I read up on Multiple-item forms, but I can't figure out how to add one as a sub-form. Any ideas on how to do that?

Solution

  • Your subform Line Items seems to be a "single form", i.e. its DefaultView property is Single Form.

    And you have 3 sets of controls, probably each of them with the same control source, therefore they will always show the same value.

    Change the Default view to Continuous Forms, and remove all but one instance of each field (remove the #2 and #3 controls). Then you will be able to enter multiple records in the Line table.