Page 1 of 1

Multiple Entries = Multiple Tables/Subforms?

PostPosted: Thu May 17, 2018 12:20 pm
by Maax555
Hi All, I am hoping I get get some help on this one as this one problem is stopping me from progressing with my database. I have asked other questions and viewed tutorials but still have not been able to progress. Therefore I thought I would try again but with a clearer definition of my requirement.

I require a form (with or without subforms) to allow the entry of details received from a customer enquiry. The customer with send in an enquiry with a reference (CustRef). The enquiry will consist of PartNumbers. Each PartNumber will have its own PartRef.

So when adding a new CustRef I could be adding several PartNumbers on the same form. Each PartNumber would need to have its own Primary Key to be auto created at time of entry. I believe the PartRef would also need to have its own Primary Key?

I have attached a simple sheet showing roughly what I need to achieve. The PartNumber is the main factor in this database. I already have a PartNumber Table which uses PartNumberID as a Primary Key. I am unsure which new table I need to allow the above. I think I need CustRef and PartRef but not been able to make it happen in a single user friendly form.

I really appreciate any help here as struggling badly despite checking out other posts and example. I have actually had to put this to one side for a number of weeks as I was not progressing.

Re: Multiple Entries = Multiple Tables/Subforms?

PostPosted: Thu May 17, 2018 1:53 pm
by Villeroy
What you describe is called a "many-to-many relation" (m-n relation). There is only one way to implement this in a formally correct way.
-- A list of customers
-- A list of parts
-- A table with part-IDs and customer-IDs

[Example] Relations reflected by list boxes in forms
1-n between animals and persons, m-n between persons and things.

A simple many-to-many form consists of:
- One main form for either customers or parts. You can show the parts of a customer or the customers that use a certain part.
- One subform with a table grid. The subform is bound to the connecting table of related IDs and linked through the ID it has in common with the main form.
- Within the table grid one list box with the items of the other side, either the parts of customers selected in main form or the customers of parts selected in the main form.

The subform shows all parts belonging to the main form's customer (or vice versa) by means of a list box.
You can add a new part to a customer by appending a new record to the subform by picking a part from the list box.

There is no alternative. A many-to-many relation with a form has to be implemented in this manner.

Re: Multiple Entries = Multiple Tables/Subforms?

PostPosted: Thu May 17, 2018 2:36 pm
by Maax555
Hi Villeroy, Either I have explained incorrectly or I have viewed articles that have been incorrect or I have misunderstood. I am pretty sure I had seen a post that allowed multiple entries of "PartNumber" directly (without them already being existing).
So because i think I can manage the customer and purchaser part of this I wanted to focus more on the entering of multiple new partnumbers in the same form. With each added partnumber being assigned its own Part_ID. I believe a table control was used in the example I viewed.
Each Partnumber entered would have its unique Part_ID primary key (integer auto value), have the same ENQ_ID as generated in the form and finally each of the entered Partnumbers would have a PartRef field.

Am i totally incorrect? I can upload a simple example of my thoughts without the important part of it actually working :-(

Re: Multiple Entries = Multiple Tables/Subforms?

PostPosted: Thu May 17, 2018 4:44 pm
by Villeroy
If you are working with relations between your tables, referencial integrity prevents that you enter relations to non-existing items. The database does not allow this.
If you do not establish any relations and allow yourself to enter spurious records, then you will run into trouble sooner or later.

What is a new part number worth if you do not store any details behind that number?

In order to enter a new part record (not just the number) with all its mandatory data, you have to use a separate form. You can attach a separate main form on the same form document. Nothing wrong with that.
Switch that form to "append new data only" mode, add at least the mandatory fields for a new record and two buttons to cancel input and to go to the next new record storing the entered one.
After you have stored a complete record of a new item, you can go back to the subform's list box and push the second refresh button on tha navigation toolbar. This second refresh button refreshes the list content currently focussed list box or combo box. Now you can select the new part and add it to the main form's customer.
Example database with a most simple refresh macro
[Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes

Re: Multiple Entries = Multiple Tables/Subforms?

PostPosted: Thu May 17, 2018 6:01 pm
by UnklDonald418
I've also looked at your problem and I agree with Vileroy.
Look first at the basic tables you might need.
Perhaps an Inventory table with columns for PartNumber, Description, and PartRef, assuming there is only one PartRef for each PartNumber plus an auto generated Primary key (ID). At this time it isn't clear if PartRef would be a text field or a foreign key referencing another item in the Inventory table.

A Customer table with the name and other pertinent information about the customer including an auto generated Primary key (ID).
It isn't clear if Purchaser would require a separate table or could be found in the Customer table.

You have a table, I guess we could call it “Enquires”, with columns for CustRef, Customer and Purchaser. Customer should probably be a foreign key referencing the Customer table and likewise Purchaser would be a foreign key referencing either the Customer table or a Purchaser table.
Since there can be more than one PartNumber associated with each CustRef, including PartNumbers in this table would be a normalization violation.

To solve that issue requires an intersection (some times referred to as intermediate) table with columns to hold a foreign key referencing the Enquiry table and another to hold a foreign key referencing the Inventory table plus an auto generated Primary key (ID).

If you haven't seen this I recommend you look at this example
[Example] Invoice Forms (without macros)