Results 1 to 6 of 6
  1. #1
    aSHLEYBECK is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6

    Unhappy Insert many records into table from Multiple Items Form (MS Access 2016)

    CoA Template.zipI have a multiple value form: sbfrmTests_multi*Please ignore sbfrm - at present that is the name, but it has no parent right now.
    Form use:
    Select tests and insert them into a template with the same templateID.


    I have a static table: tblTests

    • A table of tests, to populate the selection options (Checkboxes) on sbfrmTests_multi

    I have a data entry table: TestRecord

    • A table to hold the templateID and the tests selected from sbfrmTests_multi



    Help requested:
    Insert records into other table based on selection.

    • When you select the available options, the form returns with the tests selected
    • Need: Insert one field from the form results, and two unbound textboxes, into table TestRecord, from ALL displayed records based on the selection
    • Unbound textbox one is the SAME for all: this is the template ID
    • Unbound textbox two needs to be different for each row - right now it updates ALL rows when I enter the top value
    • Have another form (TBA) that I can enter constantly changing data into for a report.
      • If I select "Template 1" I want it to pull the tests from TestRecord that have "1" as the template ID, then assign an additional value that will change every time I use that form
      • This is based on the previously selected tests associated to template 1.
        • (Create the template, select the tests for that template)
        • (Create the unique report, based on that template, and enter the test results for that particular day)

    • I'm only referencing this if it changes the way THIS form must be laid out.
    • I have a VBA macro that inserts the desired fields into the table, but only inserts the FIRST row

    The form's data source is a Select Totals query (to prevent duplicate records):
    SELECT DISTINCTROW Testing.testid, Testing.TestCategory, Testing.Test, Max(Testing.TestMethod) AS MaxOfTestMethod, Max(Testing.NPN) AS MaxOfNPN, Max(Testing.Cosmetic) AS MaxOfCosmetic, Max(TestRecord.TemplateID) AS MaxOfTemplateID, Max(TestRecord.TestRecordID) AS MaxOfTestRecordID
    FROM Testing, CoATemplate, TestRecord
    GROUP BY Testing.testid, Testing.TestCategory, Testing.Test
    HAVING (((Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_hm] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_hma] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_mb] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_mb2] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_pe] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_rs] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_pc] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_me] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_bo] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_ad1] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_ad2] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_wa] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_cu1] Or (Testing.TestCategory)=[forms]![sbfrmTests_multi]![t_cu2]))
    ORDER BY Testing.TestCategory, Testing.Test;
    I added two unbound textboxes to the form: (TemplateID_1) that will eventually auto-populate from a parent form (header), (TemplateID_2) in the data field entry of the multiple items form.
    TemplateID_2 updates to = TemplateID_1 (working as desired).
    What I need is a command button to insert all visible records from the multi-select form into another table.
    So if I select test a, b and c, the multiple items form shows test a,b, and c - and templateID updates to be the same for all the results that display.
    I need help inserted all displayed records on the form into table: TestRecord.
    I can only get the first row to insert with a command button right now.
    I'm sorry for the convoluted questions and truly appreciate any help! <3


    Last edited by aSHLEYBECK; 07-11-2019 at 12:10 PM. Reason: Uploaded DB

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you zip and upload your db?

    Cheers,
    Vlad

  3. #3
    aSHLEYBECK is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6
    Oh of course! Thank you for responding.

    Attached to main post

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you are, I replace the source of the form with a temporary table and made the controls bound. You will need to add some validations and error handling but it works as you intended.

    Cheers,
    Vlad
    Attached Files Attached Files

  5. #5
    aSHLEYBECK is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    6
    Vlad - you're nothing less than incredible. I cannot thank you enough.

    This is outstanding - how can I thank you?!

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome, glad to hear I could help!

    Cheers,
    Vlad

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

Similar Threads

  1. Replies: 9
    Last Post: 01-24-2018, 06:41 PM
  2. Replies: 5
    Last Post: 12-01-2014, 11:31 AM
  3. Replies: 3
    Last Post: 11-27-2014, 06:38 AM
  4. INSERT multiple records from form
    By thart21 in forum Forms
    Replies: 5
    Last Post: 02-28-2013, 08:35 AM
  5. Replies: 2
    Last Post: 07-10-2011, 07:22 PM

Tags for this Thread

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