FIGURE OUT HOW TO HAVE MULTIPLE ENTRIES ON FORM

[baudouxnorthrup]

I am a novice at this. I am creating a database that houses data about our products. Their are 5 fields associated with each item number.

Companies order these products, and can order up to 40 different products on one order. The problem I am having is, in my data table, I have the item numbers of each product and associated info, but on my order form, I have them listed as Item1 - Item40.

when I go to create the query or report, I cannot bring up the associated data with each item number.

How do I create a order form that will allow me to bring up my associated information?

 

[FancyPrairie]

You need at least 2 tables: A table that defines the companies and a table that contains the items the company ordered (this table has the 5 fields in it that you mentioned). Here's an example of how the tables would look.

tblCompany
lngCompanyID ... Autonumber
strCompanyName ... text
strAddress ....... text
strPhone ......... text
whatever you need to know about the company

tblOrder
lngCompanyId ... Number (long)
lngOrderNumber .. Number (long) (Assigned by whom?)
Field1
Field2
Field3
Field4
Field5

Now you need a main form that contains the name of the company that is requesting the order and an Order Number (since the company can have more than one set of orders). On the main form is a subform whose Recordsource refers to tblOrder. The Link Child and Link Master properties of the subform control should reference lngCompanyID and lngOrderNumber.


Consequently, when the user selects the company they want and the order number, the subform will display all of the items ordered by that company based on that order number.

 

[baudouxnorthrup]

OK, so now that I have that, how do I set up my form when they order?

I keep thinking item1 item2 item3, but in reality I need the report or query to look up item# ...so I guess I want the form to be something like this:
order #
item
item
item
item

instead of item1, item2?

 

[FancyPrairie]

In my haste, I missed part of your description of the problem. If I'm reading it correctly, your products are defined by item number. Each product (item number) has 5 fields associated with it. A company will order 1 to 40 different products (items).

If my assumption is correct, then somehow you have to identify the customer and the specific order and the items they selected. Therefore, on the main form, the user should be able to select the company and assign an order number. The subform would allow the user to select the items they want.

The easiest way to do this is to define the subform in datasheet view with a combobox on the subform. The customer selects (via the combobox) the item they want. The user then moves to the next record and selects the next item they want, and so on. Remember, the subform is based on tblOrder.

Another way to do it is to have a dual list box. This gets a little more complicated to code. The user selects the items from the list box on the left and transfers them to the list box on the right.

 

[baudouxnorthrup]

Okay, I can set this up to the point of creating the subform to capture each item ordered.

If my mainform sections are customerID and OrderNo, then the subform needs to be items, but how? When I go to click on subform, it gives me an unbound form and it gets me nowhere.

Can you tell I am in way over my head.

 

[FancyPrairie]

The subform's Recordsource must be set to tblOrder (or whatever you called the table that contains the orders). The LinkChild and LinkMaster properties of the subform control (which is defined in the main form) must be set as stated above. By setting the LinkChild and LinkMaster properties, you're telling the subform to filter its recordset based on the values defined in LinkMaster. That is, only those records in the subform that match the company ID and Order ID will be displayed.

 

[baudouxnorthrup]

I have my tables: Customers, Orders, Materials, I also have created a mainform that is called orders. In this mainform Orders, I have customerID along with OrderID, and then it lists Matl1, Matl2, Matl3...and so on. In this form I can select my customer and create an order # and then input each matl. However, my ultimate goal, is to run a report that shows the order with each material ordered, but it also lists the other properties associated with the material.

In my material table, I have an item# as the identifier for each material, and then the other properties. But, how do I link this up with my Matl1, Matl2, Matl3 in my order form?

I guess my ultimate problem is that I don't know how to set up the form so that I can input many different matl #'s without naming them as different fields, so my report can just look up the material#'s and print the associated info.