Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicating Current Record with SubForms (A2K)

    I have been using this marvellous piece of code, and my apologies to the author for losing his/her name, that works perfectly. The concept is that you click on the command button to duplicate the current record.

    The problem that I have now is that I'm attempting to duplicate a record/form which also contains 2 subforms with related data. It duplicates the root information perfectly, but totally ignores the subform data. Has anyone else used this and has had the same problem. Would very much appreciate any help with this.
    The following is the code.

    Private Sub T12_btnDuplicateModel_Click()
    Dim fld(300), Duplicates%, x%, P%, Answer$, exclude$
    exclude$ = "ReportCombo,CostSqFt,ModelCost"
    Duplicates% = 1
    If Duplicates% <> 1 Then Exit Sub
    Do
    Loop Until Answer$ = ""
    With RecordsetClone
    .Bookmark = Bookmark
    On Error Resume Next
    For x% = 0 To .Fields.Count - 1
    fld(x%) = .Fields(x%)
    Next
    For P% = 1 To Duplicates%
    .AddNew
    For x% = 0 To .Fields.Count - 1
    If (Not .Fields(x%).Attributes And dbAutoIncrField) And InStr(exclude$, .Fields(x%).Name) = False Then .Fields(x%) = fld(x%)
    Next
    .Update
    Next
    On Error GoTo 0
    End With
    Me.T13_msgSelectProject.Visible = True
    Me.T14_cboSelectProject.Visible = True
    End Sub
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicating Current Record with SubForms (A2K)

    It doesn't make a great deal of sense to me to duplicate not only the parent record but also the child records, even if you could do it in one pass. You must create the parent record first before you can create child records. Why would have you a bunch of identical records like this?
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    Charlotte,

    It may not make sense to you because you are not aware of the circumstance that exists. I wouldn't ask the question if it were not pertinent. Please assume that my question is relevant to me if not to yourself.
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    There are several possible methods for doing what you want: append queries, DAO code or ADO code. It would be useful to know:
    - Is the primary key of the record source of the main table an AutoNumber field?
    - If not, how do you set the primary key? that is not clear from the code you posted.
    - Is the primary key also the field that links the main form to the subforms?

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    Hans,

    Yes, the primary key is an AutoNumber field and yes, it is also the field that links the main form to the subforms. Thank you for appreciating that my question was pertinent to me.
    Cheers,
    Andy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    You can use code like this. You must substitute the correct table and field names.

    Private Sub cmdDuplicate_Click()
    Dim lngOldID As Long, lngNewID As Long
    Dim fld() As Variant
    Dim x As Integer
    Dim exclude As String

    exclude = "ReportCombo,CostSqFt,ModelCost"
    ' Save current ID
    lngOldID = Me.ID

    With RecordsetClone
    ReDim fld(.Fields.Count - 1)
    .Bookmark = Me.Bookmark
    On Error Resume Next
    For x = 0 To .Fields.Count - 1
    fld(x) = .Fields(x)
    Next
    .AddNew
    For x = 0 To .Fields.Count - 1
    If (Not .Fields(x).Attributes And dbAutoIncrField) And _
    InStr(exclude$, .Fields(x).Name) = False Then
    .Fields(x) = fld(x)
    End If
    Next
    ' Pick up new ID
    lngNewID = .Fields("ID")
    .Update
    On Error GoTo 0
    End With

    ' Insert records into first subtable
    CurrentDb.Execute "INSERT INTO SubTable1 SELECT " & lngNewID & _
    " AS ID, Field1, Field2 FROM SubTable1 WHERE ID=" & lngOldID

    ' Insert subtable into second subtable
    CurrentDb.Execute "INSERT INTO SubTable2 SELECT " & lngNewID & _
    " AS ID, Field3, Field4, Field5 FROM SubTable2 WHERE ID=" & lngOldID
    End Sub

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    Charlotte's point is that truly duplicate records are the bane of a database developer's existance. One of the most important tasks in designing a database is that each record have some sort of unique indentifier - and since your database has not been posted to my knowledge, we have to take your statement at face value.
    Wendell

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    Hans,
    Thank you as always for your quick and more than appreciated response. I won't be able to get to this until tomorrow, but will do so first thing. Thank you again,
    Cheers,
    Andy

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    Wendell,

    I appreciate your jumping in with your more balanced comments. In this particular case, the specific record is based on information defining costs for the construction of a particular house for a specific building project. The same record is duplicated and then related to another buidling project making it a unique record for that project and thereby saving the person using it reentering a 100 or so identical fields. So, it is sometimes inappropriate to immediately deem something as incorrect unless all of the facts are known. Rather than bore you folks with all of the extraneous details as to necessity of the need for a particular answer, I just try and give you the bare miniumum to solve the problem. It works. Thank you again,
    Cheers,
    Andy

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicating Current Record with SubForms (A2K)

    Andy,

    A simple explanation for those who may not have been following your other threads makes the whole thing more understandable, not only to those who might answer your questions, but also to those who might be searching for similar answers. Providing the "bare minimum" information isn't the best way to get useful answers in the Lounge and it makes the thread less helpful to others who may be looking for answers because they don't have any way to know whether the situations are analogous.
    Charlotte

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    Charlotte,

    I agree totally with your comments. I guess I was thinking more of the amount of verbiage that you have to go through and was trying to eliminate as much as possible. On the other, when I put myself in the shoes of the other folks looking on, the extra verbiage would be more than useful. I realize now, that in the past, I gained a great deal of information by "looking over somebody else's shoulder" when they were describing their problems. From now on I'll make sure that all of the support information goes along with the question. So, please accept my apologies and, politically correct or not, to you and yours and Hans and Wendell and all of the other folks looking on, have a Merry Christmas and a Happy New Year,

    Andy
    Cheers,
    Andy

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    Hans,

    Ongoing questions with respect to this problem. I am having problems substituting the correct names in the the correct context of your code. I've tried a number of variations and end up with bigger and better error codes.

    Here are the table/field descriptions that I am currently using

    Parent Source File: qry AGMEst - Models
    Parent Source KeyField: AGMSysKey

    SubTable 1 Source File: qry AGMEst - Models - HardCosts
    SubTable 1 Source KeyField: HardSysKey

    SubTable 2 Source File: qry AGMEst - Models - SoftCosts
    SubTable 2 Source KeyField: SoftSysKey


    The second question I have is when you are referring to Field1, Field2 & Field3, Field4 in your code, are these to be equated to specific field names within my file.

    Sorry about going on to such lengths with respect to this problem.
    Cheers,
    Andy

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    You'll have to use the name of your key fields instead of the generic "ID" I used as an example in my code.
    And yes, you must replace Field1, Field2 etc. with a list of relevant field names, which may be longer or shorter than what I used in the sample code. You will have to decide which fields you want to duplicate. You must also replace the table names SubTable1 and SubTable2 with the names of the tables or queries you are using.

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    Hans,

    Thanks for your quick response. I'm off to Coding Land.
    Cheers,
    Andy

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicating Current Record with SubForms (A2K)

    You must put square brackets around names with spaces and punctuation, and there must be a space between WHERE and what follows.
    <pre> CurrentDb.Execute "INSERT INTO [qry AGMEst - Models - SoftCosts] SELECT " & _
    lngNewID & " AS SoftSysKey, SC_005, SC_010, SC_015 " & _
    "FROM [qry AGMEst - Models - SoftCosts] WHERE SoftSysKey=" & lngOldID
    </pre>

    And yes, it would be much better to avoid using spaces and dashes in table names.

Page 1 of 2 12 LastLast

Posting Permissions

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