Results 1 to 8 of 8
  1. #1
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118

    Append or Insert Command on Form for multiple table entries

    I have 4 tables linked by a single Employee ID Number.



    Table1.EmployeeID
    Table2.EmployeeID
    Table3.EmployeeID
    Table4.EmployeeID

    I have a form that I use for data input into Table1. I want to add a simple code after I enter the EmployeeID to automatically append the EmployeeID to table2 table3 and table4. But nothing is working.
    I went with a simple DoCMD.RunSQL insert... tried an append just using table2 and a where statement in my sentax for current record, everything. It seems this is super super simple but most the questions on appends are moving massive amounts of data, dozens of fields, and archiving entire tables.
    I just want me to put EmployeeID 33342332 and BOOM, 33342332 is added to my other 3 tables. Cannot be that darned hard, right?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,514
    Why do you need to do this
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    Because I am tracking about 3000 data points and I really dont want 3000 columns in my one table. THe DB is split across the network, and for certain departments to have access to the data they need to edit, etc. No one person except for me and my supervisor have admin rights to each of the tables.
    I guess the why doesnt seem like a relevant question, so far as to how.

  4. #4
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    I have a table for personnel actions... Well, the person in charge of inputting and verifying they had attended a safety training doesnt need to have access to their personnel file to see if there was adverse actions or see there pay rate..

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,514
    Quote Originally Posted by Steven.Allman View Post
    Because I am tracking about 3000 data points and I really dont want 3000 columns in my one table. THe DB is split across the network, and for certain departments to have access to the data they need to edit, etc. No one person except for me and my supervisor have admin rights to each of the tables.
    I guess the why doesnt seem like a relevant question, so far as to how.
    I can't imagine any normalized db having 3000 fields. I could be wrong but AFAIK the limit in Access is two hundred and fifty something fields per table. In the thirty or so years that I have dabbled with Access Iv'e never come across a db that had anywhere near that number. Not even one that got even remotely close to one hundred fields.

    I don't understand what the db is supposed to do but IMHO there's something wrong if you need 3000 fields in one table. If that is the case then I would urge you to read up on Database Design and Normalization.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,925
    The maximum number of fields allowed in a table is 255 and only a poorly designed database would ever have tables with anything approaching that limit.
    You should never have multiple tables with the same structure.

    To me, it sounds like you may be trying to use Access like an Excel spreadsheet.
    Whilst possible, that's not how to use Access efficiently.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    Jesus H Christ....
    My wife asks me how much golf costs and I reply $4,566,398. Just a random number. I think I explained why in the other responses. Not really sure why the question is asked to begin with. Multiple tables with a joined key field, is that not NORMALIZED db design?

    This forum has helped me thousands of times.... wait, sorry, its helped me 23 times, and never got so much grief over my questions.

    Again, split database, 4 tables, tables in different network locations with limited access depending on user. No one has rights to all the data but myself and one other person.
    So, I want the HR folks to worry about Table2 (also not its real name) and I need the training folks to worry about table3 (also not its real name).
    So when I put someone in table1 (guess what, not its real name) I want to automatically just append the EmployeeID number, and nothing else, a single field, into table 2, 3 and 4.

    So if there is anyone with an answer to that question, which considering the complexity of things others have helped me out with before, this is very very minor, I would appreciate it.

    Form1 fieldID employeeID onupdate append/insert employeeID table2 and table3 and table4.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,925
    That's fine. I'll drop out of this thread now
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 5
    Last Post: 07-11-2019, 02:13 PM
  2. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  3. Form Entries into Table Using INSERT INTO
    By ajobrien in forum Programming
    Replies: 4
    Last Post: 06-28-2017, 10:28 AM
  4. Replies: 4
    Last Post: 04-21-2014, 11:44 AM
  5. Replies: 1
    Last Post: 12-09-2011, 07:34 AM

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