-1

I have a main table that is populated by a single form. This table has all of my customer info and lists all of the products we sell with the amount sold of each product to each customer. I am creating seperate tables for each individual product. I would like to know how to make this single form populate a row in the table for each individual product if the amount sold to a single customer is at least 1.

For example, say I'm selling widget_1, widget_2, and widget_3. On my main table, the customer info will be populated no matter what. Now, I have one table for widget_1,one table for widget_2, and one table for widget_3. If a customer orders only a #1 and a #3, I want their info to be input into the tables for widget_1 and widget_3, but not widget_2.

How would I do this?

HansUp
  • 95,961
  • 11
  • 77
  • 135
Nate F
  • 1
  • 5
    "*Now, I have one table for widget_1,one table for widget_2, and one table for widget_3.*" You're heading for pain. Use one table for widgets with a widget_ID field to distinguish between them. – HansUp Sep 13 '12 at 16:42
  • Thanks for the answer, but that's not possible. I guess I should have said that my customers place orders once a year for products that are delivered individually throughout the year. All of our widget_1's are delivered to us once a year, widget_2's on a different date, and widget_3's on yet another date. I can only order widgets right before we are scheduled to deliver them. I want to make this as idiot-proof as possible for the times that I am not in the office to make the order. – Nate F Sep 13 '12 at 16:56
  • My short answer is... VBA and some INSERT SQL queries. But how is the form used? Do you indicate a customer, and then what they ordered? What will you expect the user to do in order to generate the result you want? – Daniel Sep 13 '12 at 18:48
  • The form is used to input all of the customer and order information into the main table. This table will only be used to track orders for 6 different products. In the form, I have included fields for customer name, date of purchase,and an individual field for each product, as well as fields for general contact info. All I want the user to have to do is enter the order information into the main form. From there, they can go to the separate tables and see the orders for the individual products. – Nate F Sep 13 '12 at 19:28
  • 1
    I think you're trying to build this in a fundamentally and structurally unsound way. You might be better off posting this on UtterAccess where you can post an example database and have other people modify it to show you the correct way to do this. SO is not well suited to lots of discussion, going back and forth, sharing files, etc. – HK1 Sep 14 '12 at 13:45

1 Answers1

0

Is there any reason why you cannot have a subform for widgets 1 to 3, if only a limited number of widgets are available? The main form does not have to be bound, the link master fields can be set to controls matched to the relevant subform fields, so that the data is automatically populated to the widget table when a quantity is entered into the relevant subform.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152