Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22

    Duplicating record with 2 subforms on main form.

    I have this code that I get an error message # 438 - Object doesn't support this object or method.

    The code that is highlighted I have pasted below.



    strSql = "INSERT INTO [BContacts] ( AddressesCRN, SigningOfficier, PrefixCRN, ContactTitle, [First Name], LastName, WorkPhone, HomePhone, MobilePhone, FaxNumber, Birthdate, ContactNote, E-mail, Extension, Direct, Landlord, Client, Dispositions, Broker, Party, LastUpdateDate, LastUpdateUser, NonClientRetailer ) " & _
    "SELECT " & lngAddressID & " As AddressesCRN, SigningOfficier, PrefixCRN, ContactTitle, [First Name], LastName, WorkPhone, HomePhone, MobilePhone, FaxNumber, Birthdate, ContactNote, E-mail, Extension, Direct, Landlord, Client, Dispositions, Broker, Party, LastUpdateDate, LastUpdateUser, NonClientRetailer " & _
    "FROM [BContacts] WHERE AddressesCRN = " & Forms.BRolodexFrm.Form.BRolodexAddressSub.Addresse sCRN & ";"

    I am not sure what this error message is telling me??!!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The subform reference is incorrect.

    Recommend naming the subform container control different from the object it holds, like ctrRolodex. Then:

    Forms.BRolodexFrm.ctrRolodex!AddressesCRN

    or

    Forms.BRolodexFrm.ctrRolodex.Form.AddressesCRN

    However, I am confused by the title of thread. What are you trying to do?
    Last edited by June7; 05-09-2013 at 07:02 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22
    I am trying to duplicate the record in the main form and the subforms.

    The main form is BRolodexFrm, the 1st subform is BRolodexAddressSub and the 2nd subform is BRolodexContactsSub.

    The 2nd subform gets information from the 1st subform THROUGH the main Form. So the 2nd subform used a field in the Link Master to the main form. That control references a control on the 1st subform.

    I am not sure I am describing it very well - hope you understand.

    Thanks for looking into this for me.

  4. #4
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22
    I reworked my code with your suggestion and got a run time 3134 error - Syntax error in INSERT INTO statement.

    When I click Debug on the error message, this line is highlighted

    DBEngine(0)(0).Execute strSql, dbFailOnError

    This line is the first line after my strSql.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Don't think will make difference but I use:

    CurrentDb.Execute strSQL

    I can't see any syntax error in the posted SQL.

    No, that does not improve my understanding of why you are duplicating data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22
    Basically we have a list of customers. Almost all our customers have a legal firm working for them. But some customers do their own legals, so I am trying to duplicate the record, change a field called Contact Type to Legal and duplicate all the fields. Subform 1 is the address and subform 2 are the contacts.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why not just save the Address ID in Contacts? Why duplicating all the related info?

    I don't see a field called ContactType in the SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22
    Not sure why they built this database originally.

    I am posting the entire code for you to look at.

    Private Sub Command110_Click()
    'On Error GoTo Err_Handler
    'Purpose: Duplicate the main form record and related records in the subform.
    Dim strSql As String 'SQL statement.
    Dim lngID As Long 'Primary key value of the new record.
    Dim lngAddressID As Long 'Primary key value of the new record.

    'Save any edits first
    If Me.Dirty Then
    Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
    MsgBox "Select the record to duplicate."
    Else
    'Duplicate the main record: add to form's clone.
    With Me.RecordsetClone
    .AddNew
    !TradeName = Me.TradeName
    !ContactTypeCRN = 174
    'etc for other fields.
    .Update

    'Save the primary key value, to use as the foreign key for the related records.
    .Bookmark = .LastModified
    lngID = !CustomersCRN
    'Duplicate the related records: append query.
    If Me.[BRolodexAddressSub].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [BAddresses] ( CustomersCRN, CountryCRN, Address, Address2, Address3, City, Province, PostalCode, Intersection, LastUpdateDate, LastUpdateUser ) " & _
    "SELECT " & lngID & " As CustomersCRN, CountryCRN, Address, Address2, Address3, City, Province, PostalCode, Intersection, LastUpdateDate, LastUpdateUser " & _
    "FROM [BAddresses] WHERE CustomersCRN = " & Me.CustomersCRN & ";"

    DBEngine(0)(0).Execute strSql, dbFailOnError
    Else
    MsgBox "Main record duplicated, but there were no related records."
    End If


    lngAddressID = DMax("AddressesCRN", "Baddresses")

    '*********************second subform
    'Duplicate the related records: append query.
    If Me.[BRolodexContactsSub].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [BContacts] ( AddressesCRN, SigningOfficier, PrefixCRN, ContactTitle, [First Name], LastName, WorkPhone, HomePhone, MobilePhone, FaxNumber, Birthdate, ContactNote, E-mail, Extension, Direct, Landlord, Client, Dispositions, Broker, Party, LastUpdateDate, LastUpdateUser, NonClientRetailer ) " & _
    "SELECT " & lngAddressID & " As AddressesCRN, SigningOfficier, PrefixCRN, ContactTitle, [First Name], LastName, WorkPhone, HomePhone, MobilePhone, FaxNumber, Birthdate, ContactNote, E-mail, Extension, Direct, Landlord, Client, Dispositions, Broker, Party, LastUpdateDate, LastUpdateUser, NonClientRetailer " & _
    "FROM [BContacts] WHERE AddressesCRN = " & Forms.BRolodexFrm.BRolodexAddressSub!AddressesCRN & ";"

    DBEngine(0)(0).Execute strSql, dbFailOnError
    Else
    MsgBox "Main record duplicated, but there were no Contact records."
    End If
    '************************

    'Display the new duplicate.
    Me.Bookmark = .LastModified
    End With
    End If
    Exit_Handler:
    Exit Sub
    Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
    End Sub

  9. #9
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22
    The main form copies perfectly (BRolodexFrm), as does the 1st subform (BRolodexAddressSub), but the 2nd subform (BRolodexContactsSub) does not duplicate at all.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Review post 2. Sometimes it does make a difference if the subform container name is different from the object it holds.

    Since the code is behind the main form, can use Me alias:

    Me.ctrRolodex.Form!AddressesCRN

    Step debug. Reference link at bottom of my post for debug guidelines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22
    Thanks for all your help, but one quick question before I close down for the night.

    What exactly do you mean by the subform container name and the object name?

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A subform/subreport is created by placing a subform/subreport container control on another form/report. The container's SourceObject can be table, query, form, report. If the form/subform was created by wizard, the container likely has the same name as the object it holds. Good practice is to give controls meaningful names. If bound to a field, data control should be named different from the field. Subform/subreport container should be named different from the object it holds.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22
    Changed the container name and it still didn't duplicate the 2nd subform.

    The 2nd subform links to the main form, and the main form links to the 1st subform.

    The 2nd subform has a Link child Field of AddressesCRN and a Link Master Field of TXTLINK.

    The main form TXTLINK field has a Control Source of [BRolodexAddressSub].[Form]![AddressesCRN]

    Do you think this could be an issue?

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    jasonm is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2012
    Posts
    22
    The person I am trying to do this for would disown me instantly if he found out - he is very protective of his data.

    Any other thoughts??

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple subforms to one main form
    By mick3911 in forum Forms
    Replies: 8
    Last Post: 02-25-2013, 09:40 PM
  2. Replies: 4
    Last Post: 02-13-2013, 10:46 AM
  3. Replies: 1
    Last Post: 08-02-2012, 02:11 PM
  4. Replies: 0
    Last Post: 12-16-2008, 07:49 AM
  5. Replies: 2
    Last Post: 04-11-2006, 08:40 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