Use a form to add a new supplier and then create a new table for the supplier

[RobertIngles]

It's been a very long time since I last created an Access DB...8 years. I am note a programmer so if the solution uses code, hopefully y'all can help with that!



1. I have an existing Supplier table. I want the user to add a new supplier.

2. Once the new supplier is created I would like to add a command button to create a new table for the new supplier and have the new table pop up with the new supplier info already populated ready for multiple entries related to the supplier ie adding different sales team members...new line for each sales rep.

3. The new table would be the same design/fields as other supplier tables to add multiple sales team members to the table (new supplier/sales team table template?

Many, many thanks for sharing your expertise!!

Rob

[June7]

You SHOULD NOT have a table for each supplier. Should be one table for Suppliers, a table for Members, and a junction table for SupplierTeams. This assumes a member can associate with multiple suppliers. Basic relational database concept.

[RobertIngles]

Thanks June 7! The client has asked for individual tables however I'll do what you've suggested as well. Can the supplier info be pre-populated into the Suppliers table to begin entering sales tem data for that Supplier and is it possible to create a control (button) that says "Enter A new line for this supplier" where it adds a new line automatically containing supplier info so the user doesn't have to input the supplier manually for each new line?

[xps35]

There is no need to enter the supplier info multiple times. If you have a supplier table and a related member table, you wil have a main form for the supplier and a subform for the teammembers.
In this respect nothing changed the last 8 years

[mike60smart]

Hi Rob
Can you upload a copy of the database?

[June7]

The client does not understand relational database concepts.

You could probably use a refresher course on them as well as on Access functionality. As xps35 suggested, a form/subform arrangement may be most suitable for your situation. Main form bound to Suppliers, subform bound to SupplierTeams with a combobox to select members. This presumes your Members table has already been populated with all possible members, however, there is method to add new members "on-the-fly" during data entry. Does required VBA for the combobox NotInList event - a common topic.

You should also probably split the db into frontend and backend modules.

If you do want to provide db for analysis, follow instructions at bottom of my post.

[RobertIngles]

Of course, thanks June 7!! Using subforms to achieve the results I'm looking for is perfect (and should have been obvious). Thank you!

[RobertIngles]

Perfect! Thanks xp35!!

[Micron]

I'm with anyone who says 1 table for an entity (e.g. Suppliers). If anyone has to ask about that, then I have to believe they are as unaware of what is proper as anyone who requests that it be done. Since I know it should not be done, if I had a client that insisted upon it after I advised against it, I just might suggest that they need to find another developer. That's because when things go wrong, those who won't allow proper development are the same people that will blame the developer for the ensuing issues. If you're ok with setting yourself up for a bad review or reference, then go for it.

[RobertIngles]

Agreed, thanks!