Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    83

    Unanswered: help needed in updating a table

    In one of my application, I have to update the table B automatically when a new entry has been added,modified or deleted from the table A. The table A has two fields 'ID' and 'Name'. The table B also has the same two fields. Unfortunately this database was designed by someone else, and there are lot of redundent tables.
    How can I achieve this. Any clue or help is greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Location
    Warren, PA
    Posts
    52
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO TableB ( ID, Name )SELECT [Forms]![Form1]![ID1], [Forms]![Form1]![Name1] FROM TableB;"
    DoCmd.SetWarnings True

    Change the form and control name to match yours and that should work
    Brent

  3. #3
    Join Date
    Sep 2003
    Posts
    83
    Originally posted by bhummel
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO TableB ( ID, Name )SELECT [Forms]![Form1]![ID1], [Forms]![Form1]![Name1] FROM TableB;"
    DoCmd.SetWarnings True

    Change the form and control name to match yours and that should work
    Brent
    Thanks Brent. Where do I have to write the above code.

  4. #4
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Sorry for Brent but his code will only work if you enter a new value. It will not update any existing values.
    The problem is solvable but will requuire quite some coding. The best solution for you is to remodel the database and get rid of that redundent data!
    Don't try to solve it otherwise, you'll just get further into the sh....

  5. #5
    Join Date
    Nov 2003
    Location
    Warren, PA
    Posts
    52
    Originally posted by skodidhi
    Thanks Brent. Where do I have to write the above code.
    To update a record the code would be

    CurrentDb.Execute "Update TABLENAME Set FIELD=" & Me![VALUE] & "Where TABLERECORDID = " & Me![FORMID]

    TABLENAME = Table to update
    FIELD = Field in table to update
    VALUE = field name on form that has data you want to update the table field with
    TABLERECORDID = Table record ID
    FORMID = Form Record ID

    this and the previous code could be placed in a button click or after update of a field event

    I also agree with Herman you should rewrite the database to remove the redundant data

    Brent
    Last edited by bhummel; 12-05-03 at 08:10.

Posting Permissions

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