Results 1 to 3 of 3
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    Adding one new record to sub table for each record in filter

    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.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    For each of of those filtered records I want to add a new record with the same info in another sub table.
    if the query that generates your records is something like

    SELECT *
    FROM mainTable

    and your form is then filtered by the user - lets say they have filtered so they only see customernames beginning with A using the standard (right click) access facilities

    the form filter property will be populated with customernames like 'A*'

    then your append query would be

    INSERT INTO subTable
    SELECT *
    FROM mainTable
    WHERE customernames like 'A*'

    so on a button you might have the code

    currentdb.execute "INSERT INTO subTable SELECT * FROM mainTable WHERE " & me.filter

    If you are looking for a way to have users filter the records, there are hundreds - google 'access search forms' or similar and find one that works for you - or look at the free templates provided with Access.

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    OK, I think the execute command would work.

    I have a form that users can filter, then based on the filter conditions I run a make table and create temp table. I then open the temp table for the user to review the data.
    I can then run an update query to update any data and use the execute command on that update form to get the data appended to the new table.

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding new record to a table from a different form.
    By IncidentalProgrammer in forum Programming
    Replies: 5
    Last Post: 04-03-2015, 12:04 PM
  2. Adding New record to Table
    By Robert2150 in forum Forms
    Replies: 4
    Last Post: 03-24-2015, 10:42 AM
  3. Replies: 4
    Last Post: 08-14-2012, 07:14 AM
  4. Replies: 14
    Last Post: 05-25-2012, 02:40 AM
  5. Replies: 3
    Last Post: 10-28-2010, 07:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums