Enter/Display Many side

[Guest]

We are a custom manufacturer. Customers send us their PO which includes
quantity, manufacturing specifications, and other details. After reviewing
the PO's, our production planning department "converts" these POs into
"Jobs", which include the necessary internal instructions for our own
production floor. Sometimes more than one PO (from the same customer) is
included on the same Job Number. And sometimes a single PO is split up into
multiple jobs, each with its own unique Job Number.

So there is a many to many join between Job Numbers and PO numbers. I have
set this up (properly I believe) with an intermediate table containing
JobNumber and POnumber.

Here is my first challenge: I would like to devise a main form for entering
the job number and other fields in the job table. I want a dropdown list that
allows the user to select one or more PO numbers (previously entered into the
PO table) to associate with that job. I am struggling with the exact form of
control for this, and how to set it up.

Art

 

[Guest]

Hi, Arthur.

"Associating POs" with the job number really means adding a new record to
the intermediate table, which is in a one-to-many relationship with Jobs. So
base your main form on Jobs or query thereof, and insert a continuous subform
based on the intermediate table, linking the subform on the JobNumber.

Your subform only needs the PONumber field, the correct JobNumber will be
entered into the intermediate table via the link. For it, use a combo box
that selects PO Numbers belonging to this customer. Assuming the main form
has a textbox named txtCustID bound to the field CustID, the Row Source of
the combo box would be something like:

SELECT PO.PONumber FROM PO WHERE CustID = Forms!YourParentForm!txtCustID
ORDER BY PO.PONumber;

As you scroll through main form records, you will need to requery the combo
box in the main form's On Current event procedure:

Me!YourSubformName.Form!YourComboBoxName.Requery

Hope that helps.
Sprinks

 

[Guest]

Thanks Spinks, just what I needed. Let me chew on it a while and I'll post
back if I get stuck.
Art