Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002

    Post Unanswered: Insert values from another table

    I need to update a table on a database with values from another table. The update should be triggered by a table and it should affect several rows in the table.
    The first table is called Allowed_Students and has four colums Student_ID and Student_Name, FirstName, LastName. The second table is called Students and has many columns and the common key should be Student_ID. I have a previous form that sets the Student_ID in the Allowed_Students table, and from this parameter, I need to select the corresponding values from the columns in the Student table (Student_Name, FirstName and LastName) where Allowed_Student.Student_ID = Students.Student.ID.
    Can anyone help me with this?

  2. #2
    Join Date
    Dec 2001
    We've create a database with a generalized routine for updating one table from another. It's called Table Record Maintenance and you can download it for free here

    The basic idea is to open two recordsets, a master, and a transaction, then loop throgh the transaction recordset, updating the master recordset as you go.

    Hope this helps,
    Peter De Baets
    Peter's Software - Microsoft Access Tools for Developers

  3. #3
    Join Date
    Jan 2002


    Thanks for the info. The problem is that I cannot use an update of the table within the Access database (I think this is how it works with your tool) because I need to get some variables from a multiple selection box. These variables update two tables (e.g. A and B) with an ID, and then select values from a third table (C) where the ID matches the IDs of the other tables and update the corresponding rows of table B with these values. I am trying to do it with a simple select of values from table C, storing them as variables and then executing an insert, but I keep getting the error: Too few parameters: 4 expected.
    Can you help me with this? The following is the query I am using:

    sub newMember()
    on error resume next
    if Request.Form("Authorized") = "" then
    exit Sub
    end if
    Users = split(Request.Form("Authorized"),",")
    for count = Lbound(Users) to Ubound(Users)
    strSql = "SELECT * FROM Table C WHERE MEMBER_ID = (" & Users(count)
    set rs = my_Conn.Execute (strSql)

    user = rs("NAME")
    password = rs("PWD")
    firstname = rs("FIRST")
    lastname = rs("LAST")

    strSql2 = "INSERT INTO A ("
    strSql2 = strSql2 & " ID) VALUE ( "& Users(count) & ")"
    strSql3 = "INSERT INTO B ("
    strSql3 = strSql3 & "ID, Name, Pwd, FirstName, LastName) VALUES (" & Users(count) & ", user, password, firstname, lastname)"

    set rs2 = my_Conn.Execute (strSql2)
    set rs3 = my_Conn.Execute (strSql3)

    if err.number <> 0 then
    Go_REsult err.description, 0
    end if


    end sub

Posting Permissions

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