I have a table that i filter to get 300 records. For each of of those filtered records I want to add a new record with the same info in another sub table. I call it a sub table but it is really just another table that has a one to many relationship with the filtered table. So Table one has records and table two can have multiple records based on the primary key in table 1.
I need a way for you users to be able to select a group of records in the main form using a filter and then use that filtered set as criteria for an insert / append query to add one new record the sub table for each filtered record.
I am just not sure how to do this:
1. i can use a button to open a select query with the results based on some criteria from the form but I dont know how to use that in the append query
2. I can create a temp table with all the records id in it to give me a good list, but again, not sure what to do from there.
3. it can be a multi step process. Something like using a make table query to get the necessary id's in a table, then use vba to iterate through the recordset and create a variable used on the insert and use static variables from a form for the data to insert into the other fields.
Not sure really how to go about this. I guess really I am trying to find a way to create a list that can then be used in an append query.