Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a table called people, a table called tests and a table called data. To make the database work a little like excel I added all the records in tests for all the records in people into the data table. The problem is now if I add a new test I have to add that test is each record in people into the data table.

    So I'm using a form to manage the tests table. On a update I want to create a procedure that will create in the records in the data table for the new tests. Is there some quick code to do this, something like.

    Do while recordset (tests).eof

    insert record (new test from from, person from people) in database

    next

    Thanks.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    [quote name='sarnelli' post='781491' date='24-Jun-2009 12:13']I have a table called people, a table called tests and a table called data. To make the database work a little like excel I added all the records in tests for all the records in people into the data table. The problem is now if I add a new test I have to add that test is each record in people into the data table.

    So I'm using a form to manage the tests table. On a update I want to create a procedure that will create in the records in the data table for the new tests. Is there some quick code to do this, something like.[/quote]

    First of all, this needs to be done in the AfterInsert event of your form. Then, you need to use an append query to do it rather than cycling through a recordset of all the people. Let's assume you have a txtTestID control on your form that you enter Tests on; and also assume TestID is numeric. The code would be as simple as:

    Code:
    dim strSQL as String
    
    strSQL = "INSERT INTO Data (PeopleID, TaskID) SELECT PeopleID, " & myTestID & " AS TestID FROM People"
    currentdb.execute strSQL, dbseechanges
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •