Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Duplicate records in a subform

Discussion in 'Information Technology' started by Barry, Nov 8, 2005.

  1. Barry

    Barry
    Expand Collapse
    Guest

    I have a form (formA) with a subform (formB) inside formA.
    I need to duplicate the record in formA which is not a problem but i also
    want to duplicate the current record in formB at the same time without having
    to place another duplicate button in this form.

    Can anyone help me with this?
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The example below duplicates the invoice record in the main form and the
    line items from the subform.

    It illustrates 2 techniques:
    - The main record is duplciated in the form's RecordsetClone (using DAO).
    This gives you to new primary key value, which you need for the related
    records.

    - The child records are duplicated by executing an append query statement.
    This creates them all in one pass.

    The code then displays the newly created duplicate.

    Private Sub cmdDupe_Click()
    Dim strSql As String
    Dim db As DAO.Database
    Dim lngInvID As Long

    Set db = DBEngine(0)(0)

    If Me.Dirty Then 'Save first.
    Me.Dirty = False
    End If
    If Me.NewRecord Then
    MsgBox "Select the record to duplicate."
    Else

    'Duplicate the main record
    With Me.RecordsetClone
    .AddNew
    !InvoiceDate = Date
    !ClientID = Me.ClientID
    'etc for other fields.
    .Update
    .Bookmark = .LastModified
    lngInvID = !InvoiceID

    'Duplicate the related records.
    If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
    Amount ) " & _
    "SELECT " & lngInvID & " As NewInvoiceID,
    tInvoiceDetail.Item, " & _
    "tInvoiceDetail.Amount FROM tInvoiceDetail " & _
    "WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
    ");"
    db.Execute strSql, dbFailOnError
    Else
    MsgBox "Main record duplicated, but there were no related
    records."
    End If

    'Display the duplicate.
    Me.Bookmark = .LastModified
    End With
    End If

    Set db = Nothing
    End Sub

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Barry" <Barry@discussions.microsoft.com> wrote in message
    news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    >I have a form (formA) with a subform (formB) inside formA.
    > I need to duplicate the record in formA which is not a problem but i also
    > want to duplicate the current record in formB at the same time without
    > having
    > to place another duplicate button in this form.
    >
    > Can anyone help me with this?
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Barry, this gets asked fairly often, so have created a web page explaining
    the technique, and illustrating it with code that works with Northwind.

    The article is:
    Duplicate the record in form and subform
    at:
    http://allenbrowne.com/ser-57.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > "Barry" <Barry@discussions.microsoft.com> wrote in message
    > news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    >>I have a form (formA) with a subform (formB) inside formA.
    >> I need to duplicate the record in formA which is not a problem but i also
    >> want to duplicate the current record in formB at the same time without
    >> having to place another duplicate button in this form.
     
  5. Barry

    Barry
    Expand Collapse
    Guest

    Thanks allen the code has helped me alot and with the explanation i should
    now be able to get this working for me.

    Thanks

    "Allen Browne" wrote:

    > Barry, this gets asked fairly often, so have created a web page explaining
    > the technique, and illustrating it with code that works with Northwind.
    >
    > The article is:
    > Duplicate the record in form and subform
    > at:
    > http://allenbrowne.com/ser-57.html
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > > "Barry" <Barry@discussions.microsoft.com> wrote in message
    > > news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    > >>I have a form (formA) with a subform (formB) inside formA.
    > >> I need to duplicate the record in formA which is not a problem but i also
    > >> want to duplicate the current record in formB at the same time without
    > >> having to place another duplicate button in this form.

    >
    >
    >
     
  6. Barry

    Barry
    Expand Collapse
    Guest

    I have a problem with this code

    it stops at the line DBEngine(0)(0).Execute strSql, dbFailOnError and gives
    an
    error message "Syntax error in INSERT INTO statement"

    dbFailOnError has a value of 128 if this helps



    "Allen Browne" wrote:

    > The example below duplicates the invoice record in the main form and the
    > line items from the subform.
    >
    > It illustrates 2 techniques:
    > - The main record is duplciated in the form's RecordsetClone (using DAO).
    > This gives you to new primary key value, which you need for the related
    > records.
    >
    > - The child records are duplicated by executing an append query statement.
    > This creates them all in one pass.
    >
    > The code then displays the newly created duplicate.
    >
    > Private Sub cmdDupe_Click()
    > Dim strSql As String
    > Dim db As DAO.Database
    > Dim lngInvID As Long
    >
    > Set db = DBEngine(0)(0)
    >
    > If Me.Dirty Then 'Save first.
    > Me.Dirty = False
    > End If
    > If Me.NewRecord Then
    > MsgBox "Select the record to duplicate."
    > Else
    >
    > 'Duplicate the main record
    > With Me.RecordsetClone
    > .AddNew
    > !InvoiceDate = Date
    > !ClientID = Me.ClientID
    > 'etc for other fields.
    > .Update
    > .Bookmark = .LastModified
    > lngInvID = !InvoiceID
    >
    > 'Duplicate the related records.
    > If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
    > strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
    > Amount ) " & _
    > "SELECT " & lngInvID & " As NewInvoiceID,
    > tInvoiceDetail.Item, " & _
    > "tInvoiceDetail.Amount FROM tInvoiceDetail " & _
    > "WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
    > ");"
    > db.Execute strSql, dbFailOnError
    > Else
    > MsgBox "Main record duplicated, but there were no related
    > records."
    > End If
    >
    > 'Display the duplicate.
    > Me.Bookmark = .LastModified
    > End With
    > End If
    >
    > Set db = Nothing
    > End Sub
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Barry" <Barry@discussions.microsoft.com> wrote in message
    > news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    > >I have a form (formA) with a subform (formB) inside formA.
    > > I need to duplicate the record in formA which is not a problem but i also
    > > want to duplicate the current record in formB at the same time without
    > > having
    > > to place another duplicate button in this form.
    > >
    > > Can anyone help me with this?

    >
    >
    >
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The message indicates that you SQL string is not correct.

    Immediately above the dbEngine... line, add this line:
    Debug.Print strSQL
    Run the code.
    When if fails, open the Immediate Window (Ctrl+G).
    Can you see what's wrong with the statement? E.g.:
    - Spaces missing?
    - No value where you expected one?
    - Wrong field names?
    - Field or table names that contain spaces, that were not in square
    brackets?

    Mock up a dummy query to compare it to if that helps.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Barry" <Barry@discussions.microsoft.com> wrote in message
    news:448C0515-B211-49BF-9936-1FDF6935B247@microsoft.com...
    >I have a problem with this code
    >
    > it stops at the line DBEngine(0)(0).Execute strSql, dbFailOnError and
    > gives
    > an
    > error message "Syntax error in INSERT INTO statement"
    >
    > dbFailOnError has a value of 128 if this helps
    >
    >
    >
    > "Allen Browne" wrote:
    >
    >> The example below duplicates the invoice record in the main form and the
    >> line items from the subform.
    >>
    >> It illustrates 2 techniques:
    >> - The main record is duplciated in the form's RecordsetClone (using DAO).
    >> This gives you to new primary key value, which you need for the related
    >> records.
    >>
    >> - The child records are duplicated by executing an append query
    >> statement.
    >> This creates them all in one pass.
    >>
    >> The code then displays the newly created duplicate.
    >>
    >> Private Sub cmdDupe_Click()
    >> Dim strSql As String
    >> Dim db As DAO.Database
    >> Dim lngInvID As Long
    >>
    >> Set db = DBEngine(0)(0)
    >>
    >> If Me.Dirty Then 'Save first.
    >> Me.Dirty = False
    >> End If
    >> If Me.NewRecord Then
    >> MsgBox "Select the record to duplicate."
    >> Else
    >>
    >> 'Duplicate the main record
    >> With Me.RecordsetClone
    >> .AddNew
    >> !InvoiceDate = Date
    >> !ClientID = Me.ClientID
    >> 'etc for other fields.
    >> .Update
    >> .Bookmark = .LastModified
    >> lngInvID = !InvoiceID
    >>
    >> 'Duplicate the related records.
    >> If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
    >> strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
    >> Amount ) " & _
    >> "SELECT " & lngInvID & " As NewInvoiceID,
    >> tInvoiceDetail.Item, " & _
    >> "tInvoiceDetail.Amount FROM tInvoiceDetail " & _
    >> "WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
    >> ");"
    >> db.Execute strSql, dbFailOnError
    >> Else
    >> MsgBox "Main record duplicated, but there were no related
    >> records."
    >> End If
    >>
    >> 'Display the duplicate.
    >> Me.Bookmark = .LastModified
    >> End With
    >> End If
    >>
    >> Set db = Nothing
    >> End Sub
    >>
    >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    >> news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    >> >I have a form (formA) with a subform (formB) inside formA.
    >> > I need to duplicate the record in formA which is not a problem but i
    >> > also
    >> > want to duplicate the current record in formB at the same time without
    >> > having
    >> > to place another duplicate button in this form.
    >> >
    >> > Can anyone help me with this?
     
  8. Barry

    Barry
    Expand Collapse
    Guest

    i am not sure what the "NewID" is in the statement
    "SELECT " & lngID & " As NewID

    is this a function that is being called or should this be a field in the table



    "Allen Browne" wrote:

    > The message indicates that you SQL string is not correct.
    >
    > Immediately above the dbEngine... line, add this line:
    > Debug.Print strSQL
    > Run the code.
    > When if fails, open the Immediate Window (Ctrl+G).
    > Can you see what's wrong with the statement? E.g.:
    > - Spaces missing?
    > - No value where you expected one?
    > - Wrong field names?
    > - Field or table names that contain spaces, that were not in square
    > brackets?
    >
    > Mock up a dummy query to compare it to if that helps.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Barry" <Barry@discussions.microsoft.com> wrote in message
    > news:448C0515-B211-49BF-9936-1FDF6935B247@microsoft.com...
    > >I have a problem with this code
    > >
    > > it stops at the line DBEngine(0)(0).Execute strSql, dbFailOnError and
    > > gives
    > > an
    > > error message "Syntax error in INSERT INTO statement"
    > >
    > > dbFailOnError has a value of 128 if this helps
    > >
    > >
    > >
    > > "Allen Browne" wrote:
    > >
    > >> The example below duplicates the invoice record in the main form and the
    > >> line items from the subform.
    > >>
    > >> It illustrates 2 techniques:
    > >> - The main record is duplciated in the form's RecordsetClone (using DAO).
    > >> This gives you to new primary key value, which you need for the related
    > >> records.
    > >>
    > >> - The child records are duplicated by executing an append query
    > >> statement.
    > >> This creates them all in one pass.
    > >>
    > >> The code then displays the newly created duplicate.
    > >>
    > >> Private Sub cmdDupe_Click()
    > >> Dim strSql As String
    > >> Dim db As DAO.Database
    > >> Dim lngInvID As Long
    > >>
    > >> Set db = DBEngine(0)(0)
    > >>
    > >> If Me.Dirty Then 'Save first.
    > >> Me.Dirty = False
    > >> End If
    > >> If Me.NewRecord Then
    > >> MsgBox "Select the record to duplicate."
    > >> Else
    > >>
    > >> 'Duplicate the main record
    > >> With Me.RecordsetClone
    > >> .AddNew
    > >> !InvoiceDate = Date
    > >> !ClientID = Me.ClientID
    > >> 'etc for other fields.
    > >> .Update
    > >> .Bookmark = .LastModified
    > >> lngInvID = !InvoiceID
    > >>
    > >> 'Duplicate the related records.
    > >> If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
    > >> strSql = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
    > >> Amount ) " & _
    > >> "SELECT " & lngInvID & " As NewInvoiceID,
    > >> tInvoiceDetail.Item, " & _
    > >> "tInvoiceDetail.Amount FROM tInvoiceDetail " & _
    > >> "WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
    > >> ");"
    > >> db.Execute strSql, dbFailOnError
    > >> Else
    > >> MsgBox "Main record duplicated, but there were no related
    > >> records."
    > >> End If
    > >>
    > >> 'Display the duplicate.
    > >> Me.Bookmark = .LastModified
    > >> End With
    > >> End If
    > >>
    > >> Set db = Nothing
    > >> End Sub
    > >>
    > >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    > >> news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    > >> >I have a form (formA) with a subform (formB) inside formA.
    > >> > I need to duplicate the record in formA which is not a problem but i
    > >> > also
    > >> > want to duplicate the current record in formB at the same time without
    > >> > having
    > >> > to place another duplicate button in this form.
    > >> >
    > >> > Can anyone help me with this?

    >
    >
    >
     
  9. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    NewId is just the alias for the number.
    You could call it anything at all.
    The statement should end up as:
    SELECT 789 AS YNotThisName, ...

    What matters is that the items in the SELECT clause match the same fields as
    those inside the brackets in the INSERT clause.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Barry" <Barry@discussions.microsoft.com> wrote in message
    news:0BF15F7F-C0C0-49E2-B349-D50A609E6A88@microsoft.com...
    >i am not sure what the "NewID" is in the statement
    > "SELECT " & lngID & " As NewID
    >
    > is this a function that is being called or should this be a field in the
    > table
    >
    >
    >
    > "Allen Browne" wrote:
    >
    >> The message indicates that you SQL string is not correct.
    >>
    >> Immediately above the dbEngine... line, add this line:
    >> Debug.Print strSQL
    >> Run the code.
    >> When if fails, open the Immediate Window (Ctrl+G).
    >> Can you see what's wrong with the statement? E.g.:
    >> - Spaces missing?
    >> - No value where you expected one?
    >> - Wrong field names?
    >> - Field or table names that contain spaces, that were not in square
    >> brackets?
    >>
    >> Mock up a dummy query to compare it to if that helps.
    >>
    >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    >> news:448C0515-B211-49BF-9936-1FDF6935B247@microsoft.com...
    >> >I have a problem with this code
    >> >
    >> > it stops at the line DBEngine(0)(0).Execute strSql, dbFailOnError and
    >> > gives
    >> > an
    >> > error message "Syntax error in INSERT INTO statement"
    >> >
    >> > dbFailOnError has a value of 128 if this helps
    >> >
    >> >
    >> >
    >> > "Allen Browne" wrote:
    >> >
    >> >> The example below duplicates the invoice record in the main form and
    >> >> the
    >> >> line items from the subform.
    >> >>
    >> >> It illustrates 2 techniques:
    >> >> - The main record is duplciated in the form's RecordsetClone (using
    >> >> DAO).
    >> >> This gives you to new primary key value, which you need for the
    >> >> related
    >> >> records.
    >> >>
    >> >> - The child records are duplicated by executing an append query
    >> >> statement.
    >> >> This creates them all in one pass.
    >> >>
    >> >> The code then displays the newly created duplicate.
    >> >>
    >> >> Private Sub cmdDupe_Click()
    >> >> Dim strSql As String
    >> >> Dim db As DAO.Database
    >> >> Dim lngInvID As Long
    >> >>
    >> >> Set db = DBEngine(0)(0)
    >> >>
    >> >> If Me.Dirty Then 'Save first.
    >> >> Me.Dirty = False
    >> >> End If
    >> >> If Me.NewRecord Then
    >> >> MsgBox "Select the record to duplicate."
    >> >> Else
    >> >>
    >> >> 'Duplicate the main record
    >> >> With Me.RecordsetClone
    >> >> .AddNew
    >> >> !InvoiceDate = Date
    >> >> !ClientID = Me.ClientID
    >> >> 'etc for other fields.
    >> >> .Update
    >> >> .Bookmark = .LastModified
    >> >> lngInvID = !InvoiceID
    >> >>
    >> >> 'Duplicate the related records.
    >> >> If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0
    >> >> Then
    >> >> strSql = "INSERT INTO tInvoiceDetail ( InvoiceID,
    >> >> Item,
    >> >> Amount ) " & _
    >> >> "SELECT " & lngInvID & " As NewInvoiceID,
    >> >> tInvoiceDetail.Item, " & _
    >> >> "tInvoiceDetail.Amount FROM tInvoiceDetail " & _
    >> >> "WHERE (tInvoiceDetail.InvoiceID = " &
    >> >> Me.InvoiceID &
    >> >> ");"
    >> >> db.Execute strSql, dbFailOnError
    >> >> Else
    >> >> MsgBox "Main record duplicated, but there were no
    >> >> related
    >> >> records."
    >> >> End If
    >> >>
    >> >> 'Display the duplicate.
    >> >> Me.Bookmark = .LastModified
    >> >> End With
    >> >> End If
    >> >>
    >> >> Set db = Nothing
    >> >> End Sub
    >> >>
    >> >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    >> >> news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    >> >> >I have a form (formA) with a subform (formB) inside formA.
    >> >> > I need to duplicate the record in formA which is not a problem but i
    >> >> > also
    >> >> > want to duplicate the current record in formB at the same time
    >> >> > without
    >> >> > having
    >> >> > to place another duplicate button in this form.
    >> >> >
    >> >> > Can anyone help me with this?
     
  10. Barry

    Barry
    Expand Collapse
    Guest

    I have got past this part of the code, I am using an SQL server to store the
    data but now i receive an error " You must use dbSeeChanges option with
    OpenRecordSet when accessing an SQL server table that has an identity column"

    I have tried the following statement but i then get an error "Object required"
    Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

    This gives duplicate a value of empty???
    Can you help me...

    "Allen Browne" wrote:

    > NewId is just the alias for the number.
    > You could call it anything at all.
    > The statement should end up as:
    > SELECT 789 AS YNotThisName, ...
    >
    > What matters is that the items in the SELECT clause match the same fields as
    > those inside the brackets in the INSERT clause.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Barry" <Barry@discussions.microsoft.com> wrote in message
    > news:0BF15F7F-C0C0-49E2-B349-D50A609E6A88@microsoft.com...
    > >i am not sure what the "NewID" is in the statement
    > > "SELECT " & lngID & " As NewID
    > >
    > > is this a function that is being called or should this be a field in the
    > > table
    > >
    > >
    > >
    > > "Allen Browne" wrote:
    > >
    > >> The message indicates that you SQL string is not correct.
    > >>
    > >> Immediately above the dbEngine... line, add this line:
    > >> Debug.Print strSQL
    > >> Run the code.
    > >> When if fails, open the Immediate Window (Ctrl+G).
    > >> Can you see what's wrong with the statement? E.g.:
    > >> - Spaces missing?
    > >> - No value where you expected one?
    > >> - Wrong field names?
    > >> - Field or table names that contain spaces, that were not in square
    > >> brackets?
    > >>
    > >> Mock up a dummy query to compare it to if that helps.
    > >>
    > >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    > >> news:448C0515-B211-49BF-9936-1FDF6935B247@microsoft.com...
    > >> >I have a problem with this code
    > >> >
    > >> > it stops at the line DBEngine(0)(0).Execute strSql, dbFailOnError and
    > >> > gives
    > >> > an
    > >> > error message "Syntax error in INSERT INTO statement"
    > >> >
    > >> > dbFailOnError has a value of 128 if this helps
    > >> >
    > >> >
    > >> >
    > >> > "Allen Browne" wrote:
    > >> >
    > >> >> The example below duplicates the invoice record in the main form and
    > >> >> the
    > >> >> line items from the subform.
    > >> >>
    > >> >> It illustrates 2 techniques:
    > >> >> - The main record is duplciated in the form's RecordsetClone (using
    > >> >> DAO).
    > >> >> This gives you to new primary key value, which you need for the
    > >> >> related
    > >> >> records.
    > >> >>
    > >> >> - The child records are duplicated by executing an append query
    > >> >> statement.
    > >> >> This creates them all in one pass.
    > >> >>
    > >> >> The code then displays the newly created duplicate.
    > >> >>
    > >> >> Private Sub cmdDupe_Click()
    > >> >> Dim strSql As String
    > >> >> Dim db As DAO.Database
    > >> >> Dim lngInvID As Long
    > >> >>
    > >> >> Set db = DBEngine(0)(0)
    > >> >>
    > >> >> If Me.Dirty Then 'Save first.
    > >> >> Me.Dirty = False
    > >> >> End If
    > >> >> If Me.NewRecord Then
    > >> >> MsgBox "Select the record to duplicate."
    > >> >> Else
    > >> >>
    > >> >> 'Duplicate the main record
    > >> >> With Me.RecordsetClone
    > >> >> .AddNew
    > >> >> !InvoiceDate = Date
    > >> >> !ClientID = Me.ClientID
    > >> >> 'etc for other fields.
    > >> >> .Update
    > >> >> .Bookmark = .LastModified
    > >> >> lngInvID = !InvoiceID
    > >> >>
    > >> >> 'Duplicate the related records.
    > >> >> If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0
    > >> >> Then
    > >> >> strSql = "INSERT INTO tInvoiceDetail ( InvoiceID,
    > >> >> Item,
    > >> >> Amount ) " & _
    > >> >> "SELECT " & lngInvID & " As NewInvoiceID,
    > >> >> tInvoiceDetail.Item, " & _
    > >> >> "tInvoiceDetail.Amount FROM tInvoiceDetail " & _
    > >> >> "WHERE (tInvoiceDetail.InvoiceID = " &
    > >> >> Me.InvoiceID &
    > >> >> ");"
    > >> >> db.Execute strSql, dbFailOnError
    > >> >> Else
    > >> >> MsgBox "Main record duplicated, but there were no
    > >> >> related
    > >> >> records."
    > >> >> End If
    > >> >>
    > >> >> 'Display the duplicate.
    > >> >> Me.Bookmark = .LastModified
    > >> >> End With
    > >> >> End If
    > >> >>
    > >> >> Set db = Nothing
    > >> >> End Sub
    > >> >>
    > >> >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    > >> >> news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    > >> >> >I have a form (formA) with a subform (formB) inside formA.
    > >> >> > I need to duplicate the record in formA which is not a problem but i
    > >> >> > also
    > >> >> > want to duplicate the current record in formB at the same time
    > >> >> > without
    > >> >> > having
    > >> >> > to place another duplicate button in this form.
    > >> >> >
    > >> >> > Can anyone help me with this?

    >
    >
    >
     
  11. Barry

    Barry
    Expand Collapse
    Guest

    'Duplicate the related records: append query.
    If Me.[Pre_Installation_SiteSurvey
    subform].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [Pre_Installation_SiteSurvey] (
    lngProgramID, PreInstallationID, [Scheduled Week No], [Completed Week],
    [Survey By], Paperwork)" & _
    "SELECT " & lngID & " As
    lngProgramID,PreInstallationID,[Scheduled Week No],[Completed Week],[Survey
    By], Paperwork " & _
    "FROM [Pre_Installation_SiteSurvey] WHERE lngProgramID=
    " & Me.txtProgramID & ";"

    Debug.Print strSql
    Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset,
    dbSeeChanges)
    DBEngine(0)(0).Execute strSql, dbFailOnError

    Else
    MsgBox "Main record duplicated, but there were no related
    records."
    End If

    "Barry" wrote:

    > I have got past this part of the code, I am using an SQL server to store the
    > data but now i receive an error " You must use dbSeeChanges option with
    > OpenRecordSet when accessing an SQL server table that has an identity column"
    >
    > I have tried the following statement but i then get an error "Object required"
    > Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
    >
    > This gives duplicate a value of empty???
    > Can you help me...
    >
    > "Allen Browne" wrote:
    >
    > > NewId is just the alias for the number.
    > > You could call it anything at all.
    > > The statement should end up as:
    > > SELECT 789 AS YNotThisName, ...
    > >
    > > What matters is that the items in the SELECT clause match the same fields as
    > > those inside the brackets in the INSERT clause.
    > >
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > > Reply to group, rather than allenbrowne at mvps dot org.
    > >
    > > "Barry" <Barry@discussions.microsoft.com> wrote in message
    > > news:0BF15F7F-C0C0-49E2-B349-D50A609E6A88@microsoft.com...
    > > >i am not sure what the "NewID" is in the statement
    > > > "SELECT " & lngID & " As NewID
    > > >
    > > > is this a function that is being called or should this be a field in the
    > > > table
    > > >
    > > >
    > > >
    > > > "Allen Browne" wrote:
    > > >
    > > >> The message indicates that you SQL string is not correct.
    > > >>
    > > >> Immediately above the dbEngine... line, add this line:
    > > >> Debug.Print strSQL
    > > >> Run the code.
    > > >> When if fails, open the Immediate Window (Ctrl+G).
    > > >> Can you see what's wrong with the statement? E.g.:
    > > >> - Spaces missing?
    > > >> - No value where you expected one?
    > > >> - Wrong field names?
    > > >> - Field or table names that contain spaces, that were not in square
    > > >> brackets?
    > > >>
    > > >> Mock up a dummy query to compare it to if that helps.
    > > >>
    > > >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    > > >> news:448C0515-B211-49BF-9936-1FDF6935B247@microsoft.com...
    > > >> >I have a problem with this code
    > > >> >
    > > >> > it stops at the line DBEngine(0)(0).Execute strSql, dbFailOnError and
    > > >> > gives
    > > >> > an
    > > >> > error message "Syntax error in INSERT INTO statement"
    > > >> >
    > > >> > dbFailOnError has a value of 128 if this helps
    > > >> >
    > > >> >
    > > >> >
    > > >> > "Allen Browne" wrote:
    > > >> >
    > > >> >> The example below duplicates the invoice record in the main form and
    > > >> >> the
    > > >> >> line items from the subform.
    > > >> >>
    > > >> >> It illustrates 2 techniques:
    > > >> >> - The main record is duplciated in the form's RecordsetClone (using
    > > >> >> DAO).
    > > >> >> This gives you to new primary key value, which you need for the
    > > >> >> related
    > > >> >> records.
    > > >> >>
    > > >> >> - The child records are duplicated by executing an append query
    > > >> >> statement.
    > > >> >> This creates them all in one pass.
    > > >> >>
    > > >> >> The code then displays the newly created duplicate.
    > > >> >>
    > > >> >> Private Sub cmdDupe_Click()
    > > >> >> Dim strSql As String
    > > >> >> Dim db As DAO.Database
    > > >> >> Dim lngInvID As Long
    > > >> >>
    > > >> >> Set db = DBEngine(0)(0)
    > > >> >>
    > > >> >> If Me.Dirty Then 'Save first.
    > > >> >> Me.Dirty = False
    > > >> >> End If
    > > >> >> If Me.NewRecord Then
    > > >> >> MsgBox "Select the record to duplicate."
    > > >> >> Else
    > > >> >>
    > > >> >> 'Duplicate the main record
    > > >> >> With Me.RecordsetClone
    > > >> >> .AddNew
    > > >> >> !InvoiceDate = Date
    > > >> >> !ClientID = Me.ClientID
    > > >> >> 'etc for other fields.
    > > >> >> .Update
    > > >> >> .Bookmark = .LastModified
    > > >> >> lngInvID = !InvoiceID
    > > >> >>
    > > >> >> 'Duplicate the related records.
    > > >> >> If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0
    > > >> >> Then
    > > >> >> strSql = "INSERT INTO tInvoiceDetail ( InvoiceID,
    > > >> >> Item,
    > > >> >> Amount ) " & _
    > > >> >> "SELECT " & lngInvID & " As NewInvoiceID,
    > > >> >> tInvoiceDetail.Item, " & _
    > > >> >> "tInvoiceDetail.Amount FROM tInvoiceDetail " & _
    > > >> >> "WHERE (tInvoiceDetail.InvoiceID = " &
    > > >> >> Me.InvoiceID &
    > > >> >> ");"
    > > >> >> db.Execute strSql, dbFailOnError
    > > >> >> Else
    > > >> >> MsgBox "Main record duplicated, but there were no
    > > >> >> related
    > > >> >> records."
    > > >> >> End If
    > > >> >>
    > > >> >> 'Display the duplicate.
    > > >> >> Me.Bookmark = .LastModified
    > > >> >> End With
    > > >> >> End If
    > > >> >>
    > > >> >> Set db = Nothing
    > > >> >> End Sub
    > > >> >>
    > > >> >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    > > >> >> news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    > > >> >> >I have a form (formA) with a subform (formB) inside formA.
    > > >> >> > I need to duplicate the record in formA which is not a problem but i
    > > >> >> > also
    > > >> >> > want to duplicate the current record in formB at the same time
    > > >> >> > without
    > > >> >> > having
    > > >> >> > to place another duplicate button in this form.
    > > >> >> >
    > > >> >> > Can anyone help me with this?

    > >
    > >
    > >
     
  12. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    I don't understand what the OpenRecordset is for. Executing the append query
    statement should insert the value, without any OpenRecordset.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Barry" <Barry@discussions.microsoft.com> wrote in message
    news:F04B3E04-6F2B-4EE2-8B04-DDCC59B5B79F@microsoft.com...
    > 'Duplicate the related records: append query.
    > If Me.[Pre_Installation_SiteSurvey
    > subform].Form.RecordsetClone.RecordCount > 0 Then
    > strSql = "INSERT INTO [Pre_Installation_SiteSurvey] (
    > lngProgramID, PreInstallationID, [Scheduled Week No], [Completed Week],
    > [Survey By], Paperwork)" & _
    > "SELECT " & lngID & " As
    > lngProgramID,PreInstallationID,[Scheduled Week No],[Completed
    > Week],[Survey
    > By], Paperwork " & _
    > "FROM [Pre_Installation_SiteSurvey] WHERE lngProgramID=
    > " & Me.txtProgramID & ";"
    >
    > Debug.Print strSql
    > Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset,
    > dbSeeChanges)
    > DBEngine(0)(0).Execute strSql, dbFailOnError
    >
    > Else
    > MsgBox "Main record duplicated, but there were no related
    > records."
    > End If
    >
    > "Barry" wrote:
    >
    >> I have got past this part of the code, I am using an SQL server to store
    >> the
    >> data but now i receive an error " You must use dbSeeChanges option with
    >> OpenRecordSet when accessing an SQL server table that has an identity
    >> column"
    >>
    >> I have tried the following statement but i then get an error "Object
    >> required"
    >> Set Duplicate = dbs.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
    >>
    >> This gives duplicate a value of empty???
    >> Can you help me...
    >>
    >> "Allen Browne" wrote:
    >>
    >> > NewId is just the alias for the number.
    >> > You could call it anything at all.
    >> > The statement should end up as:
    >> > SELECT 789 AS YNotThisName, ...
    >> >
    >> > What matters is that the items in the SELECT clause match the same
    >> > fields as
    >> > those inside the brackets in the INSERT clause.
    >> >
    >> > --
    >> > Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> > Tips for Access users - http://allenbrowne.com/tips.html
    >> > Reply to group, rather than allenbrowne at mvps dot org.
    >> >
    >> > "Barry" <Barry@discussions.microsoft.com> wrote in message
    >> > news:0BF15F7F-C0C0-49E2-B349-D50A609E6A88@microsoft.com...
    >> > >i am not sure what the "NewID" is in the statement
    >> > > "SELECT " & lngID & " As NewID
    >> > >
    >> > > is this a function that is being called or should this be a field in
    >> > > the
    >> > > table
    >> > >
    >> > >
    >> > >
    >> > > "Allen Browne" wrote:
    >> > >
    >> > >> The message indicates that you SQL string is not correct.
    >> > >>
    >> > >> Immediately above the dbEngine... line, add this line:
    >> > >> Debug.Print strSQL
    >> > >> Run the code.
    >> > >> When if fails, open the Immediate Window (Ctrl+G).
    >> > >> Can you see what's wrong with the statement? E.g.:
    >> > >> - Spaces missing?
    >> > >> - No value where you expected one?
    >> > >> - Wrong field names?
    >> > >> - Field or table names that contain spaces, that were not in square
    >> > >> brackets?
    >> > >>
    >> > >> Mock up a dummy query to compare it to if that helps.
    >> > >>
    >> > >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    >> > >> news:448C0515-B211-49BF-9936-1FDF6935B247@microsoft.com...
    >> > >> >I have a problem with this code
    >> > >> >
    >> > >> > it stops at the line DBEngine(0)(0).Execute strSql, dbFailOnError
    >> > >> > and
    >> > >> > gives
    >> > >> > an
    >> > >> > error message "Syntax error in INSERT INTO statement"
    >> > >> >
    >> > >> > dbFailOnError has a value of 128 if this helps
    >> > >> >
    >> > >> >
    >> > >> >
    >> > >> > "Allen Browne" wrote:
    >> > >> >
    >> > >> >> The example below duplicates the invoice record in the main form
    >> > >> >> and
    >> > >> >> the
    >> > >> >> line items from the subform.
    >> > >> >>
    >> > >> >> It illustrates 2 techniques:
    >> > >> >> - The main record is duplciated in the form's RecordsetClone
    >> > >> >> (using
    >> > >> >> DAO).
    >> > >> >> This gives you to new primary key value, which you need for the
    >> > >> >> related
    >> > >> >> records.
    >> > >> >>
    >> > >> >> - The child records are duplicated by executing an append query
    >> > >> >> statement.
    >> > >> >> This creates them all in one pass.
    >> > >> >>
    >> > >> >> The code then displays the newly created duplicate.
    >> > >> >>
    >> > >> >> Private Sub cmdDupe_Click()
    >> > >> >> Dim strSql As String
    >> > >> >> Dim db As DAO.Database
    >> > >> >> Dim lngInvID As Long
    >> > >> >>
    >> > >> >> Set db = DBEngine(0)(0)
    >> > >> >>
    >> > >> >> If Me.Dirty Then 'Save first.
    >> > >> >> Me.Dirty = False
    >> > >> >> End If
    >> > >> >> If Me.NewRecord Then
    >> > >> >> MsgBox "Select the record to duplicate."
    >> > >> >> Else
    >> > >> >>
    >> > >> >> 'Duplicate the main record
    >> > >> >> With Me.RecordsetClone
    >> > >> >> .AddNew
    >> > >> >> !InvoiceDate = Date
    >> > >> >> !ClientID = Me.ClientID
    >> > >> >> 'etc for other fields.
    >> > >> >> .Update
    >> > >> >> .Bookmark = .LastModified
    >> > >> >> lngInvID = !InvoiceID
    >> > >> >>
    >> > >> >> 'Duplicate the related records.
    >> > >> >> If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount
    >> > >> >> > 0
    >> > >> >> Then
    >> > >> >> strSql = "INSERT INTO tInvoiceDetail ( InvoiceID,
    >> > >> >> Item,
    >> > >> >> Amount ) " & _
    >> > >> >> "SELECT " & lngInvID & " As NewInvoiceID,
    >> > >> >> tInvoiceDetail.Item, " & _
    >> > >> >> "tInvoiceDetail.Amount FROM tInvoiceDetail "
    >> > >> >> & _
    >> > >> >> "WHERE (tInvoiceDetail.InvoiceID = " &
    >> > >> >> Me.InvoiceID &
    >> > >> >> ");"
    >> > >> >> db.Execute strSql, dbFailOnError
    >> > >> >> Else
    >> > >> >> MsgBox "Main record duplicated, but there were no
    >> > >> >> related
    >> > >> >> records."
    >> > >> >> End If
    >> > >> >>
    >> > >> >> 'Display the duplicate.
    >> > >> >> Me.Bookmark = .LastModified
    >> > >> >> End With
    >> > >> >> End If
    >> > >> >>
    >> > >> >> Set db = Nothing
    >> > >> >> End Sub
    >> > >> >>
    >> > >> >> "Barry" <Barry@discussions.microsoft.com> wrote in message
    >> > >> >> news:FD587861-DD2F-49D4-95E9-4CE4C0450729@microsoft.com...
    >> > >> >> >I have a form (formA) with a subform (formB) inside formA.
    >> > >> >> > I need to duplicate the record in formA which is not a problem
    >> > >> >> > but i
    >> > >> >> > also
    >> > >> >> > want to duplicate the current record in formB at the same time
    >> > >> >> > without
    >> > >> >> > having
    >> > >> >> > to place another duplicate button in this form.
    >> > >> >> >
    >> > >> >> > Can anyone help me with this?
    >> >
    >> >
    >> >
     

Share This Page