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

    Problem with Appending table from Form and SubForm

    I have Form with the Company Name and Company ID. Within this form I have SubForm where I enter the company address. When done I have a button to save the update the Address table with the Company ID and the Address ID. I have written the following code below but am having errors. Any idea?

    Private Sub btnUpdateAddress_Click()
    'Append Company ID and Address ID when saved


    sSql = "INSERT INTO tbl3Company_Address (Company_ID, Address_ID)"
    sSql = sSql & "VALUES ([Forms]![frm1Company]'" & Me.txtCompany_ID & "', [Forms]![frm1Addressessubform]'" & Me.txtAddressCode_ID & "');"
    CurrentDb.Execute sSql, dbFailOnError
    End Sub

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Why not tell us what errors you are getting? Have you done a MsgBox sSql or Debug.Print sSql yet to see what it looks like?

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If the main form is bound and the sub form is bound, there is no need to have code "save" the entry/change.
    The record(s) are saved automatically when you move to a different record.

    If you think you must use code,
    If fields "txtCompany_ID" and "txtAddressCode_ID" are numeric type, use
    Code:
    Private Sub btnUpdateAddress_Click()
       'Append Company ID and Address ID when saved
       sSql = "INSERT INTO tbl3Company_Address (Company_ID, Address_ID)"
       sSql = sSql & "VALUES (" & Me.txtCompany_ID & ", " & Me.txtAddressCode_ID & ");"
       
       CurrentDb.Execute sSql, dbFailOnError
    End Sub
    If fields "txtCompany_ID" and "txtAddressCode_ID" are text type, use
    Code:
    Private Sub btnUpdateAddress_Click()
       'Append Company ID and Address ID when saved
       sSql = "INSERT INTO tbl3Company_Address (Company_ID, Address_ID)"
       sSql = sSql & "VALUES ('" & Me.txtCompany_ID & "', '" & Me.txtAddressCode_ID & "');"
       
       CurrentDb.Execute sSql, dbFailOnError
    End Sub

  4. #4
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32
    I've used the second code and I keep getting the following error:

    Compile Error:

    Method or Data Member not found.

    I've checked all my column and field names and they are correct. Thought?

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can the company have more then 1 address? Why have a separate table just for address?

  6. #6
    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

  7. #7
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32
    I figured it out. Needed to reference the Subform when referring to that control:

    Private Sub btnUpdateCompanyAddress_Click()
    'Append Company ID and Address ID when saved
    sSql = "INSERT INTO tbl3Company_Addresses (Company_ID, Address_ID)"
    sSql = sSql & "VALUES ('" & Me.txtCompany_ID & "', '" & Me.frm1Addresses_subform!txtAddress_ID & "');"

    CurrentDb.Execute sSql, dbFailOnError
    End Sub

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Oops, missed that.

    Glad you figured it out........

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

Similar Threads

  1. Replies: 12
    Last Post: 12-25-2015, 03:25 PM
  2. Replies: 9
    Last Post: 10-24-2014, 11:34 AM
  3. Replies: 2
    Last Post: 12-05-2013, 12:09 PM
  4. Replies: 2
    Last Post: 03-14-2013, 08:39 AM
  5. Replies: 2
    Last Post: 06-14-2010, 03:25 PM

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