Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    West Chester, Pennsylvania, USA
    Posts
    5

    Unanswered: Add record to Multiple Tables

    I could use some quick help this morning. I am having a problem with adding multiple new records using a form. Here is my situation:

    I have created a form where a user can enter a new First_Name and Last_Name to the database. I have six tables where First_Name and Last_Name are my primary key.

    I need the form to add records to all of these tables. As an example, I need the Employee_Data table updated with a new record (works fine), but I also need tables called ACP_Data and ICOMS_Data updated as well with new entries.

    Can anyone help me with this? I am sorely lacking in my programming skills and I have spent the last two days surfing the web and trying different things to get this to work. I thought I could update the OnClick event where I add to the table tied to the form (Employee_Data) but nothing has seemed to work.

    Any help anyone can offer would be great.

    Thanks.

    Neil Klusky

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    make recordsets of all tables, and than add the necessary code on the onclick event.
    if needed i will give some elementary code. i am still on line for some time

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    also consider inline SQL along the lines of...

    dim strSQL as string

    strSQL = "insert into ACP_Data (nameOfTheDestinationField) VALUES ('" & FirstName & "');"
    docmd.setwarnings false
    docmd.runsql strSQL
    docmd.setwarnings true

    strSQL = "insert into ICOMS_Data (nameOfTheDestinationField etc etc
    docmd.setwarnings false
    docmd.runSQL strSQL
    docmd.setwarnings true

    not clear from your post if firstname and lastname are concatenated into one field or in two separate fields... same principle applies for updating two separate fields at the same time...

    strSQL = "insert into ACP_Data (FirstNameField, LastNameField) VALUES ('" & FirstName & "', '" & LastName & "');"

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    btw

    adding the names to three tables is a violation of normalisation

    also "Philippe-Christian de Courtenay-Marchand" takes a lot more storage than a long-integer.

    also, your scheme is in deep trouble when you have two employees "Fred Smith"

    alternative is that one table has:
    IDperson, autonumber, PK
    FirstName, text
    LastName, text

    all the other tables look at IDperson as FK

    smaller, normalised, and it can handle 1000 clones of Fred in the workplace.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Oct 2004
    Location
    West Chester, Pennsylvania, USA
    Posts
    5
    Thank you all. All this information has been very helpful and I have been able to code so it updates/adds correctly.


Posting Permissions

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