Selecting records within a form

[crowegreg]

I'm creating a form that contains a combo box using an ODBC connected table that contains purchase orders. The user selects the desired purchase order. Once the purchase order is selected, I need to display the purchase order items that are a part of this purchase order. The purchase order items are located in a different table. Once all the purchase order items are displayed, I need for the user to select some or all of the items, and these are written to another file. Thanks for your help!

[RuralGuy]

It sounds like your PurchaseOrders and the PurchaseOrderItems have a 1:m relationship which is best delt with in a Form/SubForm arrangement with the many side bound to the SubForm in continuous form mode.

[crowegreg]

OK. So I create a main form with the combo box to select the purchase order numbers, then a subform will display the purchase order items linked to the main form's purchase order. How do I have the user to select specific purchase order items and add them to a new file?

[RuralGuy]

A useful interface is using two ListBoxes and < & > arrows to move the records.

[crowegreg]

I can't seem to make any progress on this. The subform does not recognize the combo box for the relationship between the main form and subform. So let me make sure you know what my needs are, and what my tables are.
From the ODBC connection:
ICPurchaseOrder - contains the purchase order number
ICPOLineItems - contains the line items within the po, linked by the po number from the table ICPurchaseOrder
Since both of these tables are from an ODBC connection, I'm unable to add any fields to either of these tables.

Within the DB I've created:
tblProductPrintRunRecord - contains the following fields. PrintRunID, Series#, PrintRunDate
Each line item within ICPOLineItems needs to have a record within tblProductPrintRunRecord, each field containing data.

The PrintRunID will have a PO# associated with it, and thus the ICPOLineItems associated also. A PO# can also be associated with multiple PrintRunID's. a ICPOLineItem will only be associated with 1 PrintRunID

What are you suggestions?

[RuralGuy]

The LinkChild/MasterFields can point to either controls on the respective form or fields in the RecordSources of each respective form. I would start by getting that part of the form working.

[crowegreg]

So I create a form using the recordsource of tblProductPrintRunRecord. The user enters the PrintRunID, and I have a combo box displaying the records within ICPurchaseOrder. The user selects a PONumber. My subform will contain the appropriate records from ICPOLineItems with the link between the PO numbers between those 2 tables?

[RuralGuy]

I would think the RecordSource of the MainForm would be ICPurchaseOrder and the SubForm RecordSource would be ICPOLineItems.

[crowegreg]

Thanks. I'll start in that direction. Where in CO are you located?

[RuralGuy]

Four Corners area, Pagosa Springs. Post back here if you need further suggestions.

[crowegreg]

Thanks. I passed through their last week. Beautiful country!!

[crowegreg]

In linking the main form to the sub form, how do I utilize the combo box as the link on the main form to a field on the subform? When I try to create the link, the combo box is not listed, only the fields within the table are listed.

[RuralGuy]

Use the ComboBox wizard and select option #3 and just move to the record you want. The cbo is not directly involved in the linking at all.

[crowegreg]

Thank you!! That all worked. My last question is within this form, if I select a PO, all the POLineItems will be displayed. Let's say their are 5 line items on this PO. For 4 of them I enter a PrintRunID. If I save this PrintRunID, I will refresh the screen to remove all the data. If the user then enters the same PO, I should only have the 1 remaining line item to appear. How do I do that?

[crowegreg]

On the subform I added a checkbox field for the user to select this line item to be included within this print run. Now, I'm writing the VBA to add the new record to the printrun table. How do I loop through the records on the subform to only create records for the items that have been checked?