Adding Data to a linked table for multiple filtered records on the main table

[ashraf]

Hi,


I've One to many linked tables by a short text field "ItemID", the main table "MainT" contains 100s of records, the linked table "DataT" is blank & contains 3 fields; "ID", "ItemID" and "Job", jobs are listed on a table "JobT"As shown on the below picture.
Is it possible to filter "MainT" by one or many of its fields then insert many "job"s records into the linked table "DataT" for all filtered record on "MainT"?
e.g. If I filtered the "MainT" by material "SS" the result will be 2 records (A9 & A10) then from an unbounded controller(s) I will select 3 jobs to be added to the "DataT" for all filtered items i.e. 3 jobs will be added to A9 & to A10 on "DataT".

Note: Access example is attached

Targeted items to be added to DataT

ID	ItemID	Job
10	A9	Cutting
11	A9	Forming
12	A9	Welding
13	A10	Cutting
15	A10	Forming
16	A10	Welding

Note: I used Multi Values Field for Job on the main table before but I changed to a linked table due to its difficulty for selecting, copying, editing and erasing.

[June7]

You want to batch create records instead of record-by-record data input. This commonly involves an INSERT SELECT sql action.

Can build a query object with static criteria.

INSERT INTO DataT(ItemID, JobID) SELECT ItemID, JobID FROM MainT, JobT WHERE ItemID IN("A9","A10") AND JobID IN(1,2,4);

Note lack of JOIN clause in the SELECT. This is a Cartesian association for the 2 tables because they don't have relationship. Unfortunately, this type of query can perform very slowly with large datasets.

Allowing the IN() criteria to be dynamic will require VBA code that loops through multi-select listboxes to build the arrays of elements then constructing SQL statement in VBA and use CurrentDb.Execute to run it. This SQL would still be utilizing Cartesian query so if it is slow, VBA procedure will be more complicated to avoid Cartesian query approach.


Glad you learned about multi-value field issues early on.

[ashraf]

Thanks June7 for your quick responding.
Few days ago I was trying with to make it dynamic with DoCmd.RunSQL but failed
My be Multi selection list is a good Idea, I'll try to use it with CurrentDb.Execute

[June7]

This article demonstrates looping listbox to build array for IN() criteria. http://allenbrowne.com/ser-50.html

[ashraf]

Thanks June7 finally it works, multi-Select Listbox was good idea instead of using subform