Results 1 to 4 of 4
  1. #1
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32

    INSERT INTO multiple tables from form

    I have a form that contains text boxes that when filled out, the data should update 3 different tables. The code I have is only updating the last table. What do i have wrong in the code?

    Private Sub btnSave_Click()
    Dim sSql As String
    'Append Company Name, Address, and Address Type
    sSql = "INSERT INTO tbl1Company (CompanyName)"


    sSql = sSql & "VALUES ('" & Me.txtCompanyName & "');"
    sSql = "INSERT INTO tbl1Addresses (AddressType_ID, AddressLine1, AddressLine2, City, State, Zip)"
    sSql = sSql & "VALUES ('" & Me.txtAddressType_ID & "', '" & Me.txtAddressLine1 & "', '" & Me.txtAddressLine2 & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" & Me.txtZip & "');"
    sSql = "INSERT INTO tbl1PhoneNumbers (PhoneNumber, PhoneType_ID)"
    sSql = sSql & "VALUES ('" & Me.txtWorkPhone & "', '" & Me.txtPhoneType_IDWork & "');"
    CurrentDb.Execute sSql, dbFailOnError
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't need code.
    create 3 queries. Each one updates the table you want.
    put them in a maco.
    then run the macro.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You are continually overwriting the sSql string and replacing it with a new query string - and only execute the one you did last. Either execute each time you populate or else use three different strings. Or do what ranman suggests.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try

    Code:
    Private Sub btnSave_Click()
        Dim sSql As String
        'Append Company Name, Address, and Address Type
        sSql = "INSERT INTO tbl1Company (CompanyName)"
        sSql = sSql & " VALUES ('" & Me.txtCompanyName & "');"
        CurrentDb.Execute sSql, dbFailOnError 'insert to tbl1Company
    
        sSql = "INSERT INTO tbl1Addresses (AddressType_ID, AddressLine1, AddressLine2, City, State, Zip)"
        sSql = sSql & " VALUES ('" & Me.txtAddressType_ID & "',  '" & Me.txtAddressLine1 & "', '" &      Me.txtAddressLine2 &  "', '" & Me.txtCity & "', '" & Me.txtState & "', '"  & Me.txtZip & "');" 
      CurrentDb.Execute sSql, dbFailOnError 'insert to tbl1Addresses
    
        sSql = "INSERT INTO tbl1PhoneNumbers (PhoneNumber, PhoneType_ID)"
        sSql = sSql & " VALUES ('" & Me.txtWorkPhone & "', '" & Me.txtPhoneType_IDWork & "');"
        CurrentDb.Execute sSql, dbFailOnError   'tbl1PhoneNumbers
    End Sub

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

Similar Threads

  1. Replies: 7
    Last Post: 07-01-2016, 01:13 AM
  2. insert into multiple tables
    By vicsaccess in forum Programming
    Replies: 4
    Last Post: 01-25-2016, 07:03 PM
  3. Insert data from multiple tables into one table
    By mohanmoni in forum Queries
    Replies: 3
    Last Post: 02-05-2015, 01:31 AM
  4. Replies: 10
    Last Post: 12-13-2010, 11:49 PM
  5. INSERT INTO and UPDATE to multiple tables
    By lupis in forum Import/Export Data
    Replies: 6
    Last Post: 05-19-2010, 05:21 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