Cloning a record

[TeeSee]

This is a small TRIAL database!
I have the following code which is a snippet of Allen Brownes code.

Private Sub cmdDupe_Click()

'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.

'Save and 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
!SISitemCode = Me.SISitemCode
!Description = Me.Description
!Price = Me.Price
'etc for other fields.
.Update
End With
End If
End Sub

All I am trying to do is to clone/duplicate a "main Form" record but
need to capture the "NEW" data in a form called frmCloneItemUpdate in
order to first change the "value" in the [SISitemCode] field in order
that the record can eventually be saved and also update any other
fields which might pertain to the new item.
The above code runs up to the .Update line because [SISitemCode] in
the underlying table is set to "NoDuplicates"
Two example lines of data follow as [SISitemCode], [Description],
[Price]
SISitemCode Description Price
MU10060, 6" x 1" CorePlus 1.0
MU15060, 6" x 1-1/2" CorePlus 1.5
MU20060, 6" x 2" CorePlus 2.0


Any help would be greatly appreciated. Regards

 

[TeeSee]

This is a small TRIAL database!
I have the following code which is a snippet of Allen Brownes code.

Private Sub cmdDupe_Click()

    '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.

    'Save and 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
                !SISitemCode = Me.SISitemCode
                !Description = Me.Description
                !Price = Me.Price
                'etc for other fields.
            .Update
        End With
    End If
End Sub

All I am trying to do is to clone/duplicate a "main Form" record but
need to capture the "NEW" data in a form called frmCloneItemUpdate in
order to first change the "value" in the [SISitemCode] field in order
that the record can eventually be saved and also update any other
fields which might pertain to the new item.
The above code runs up to the .Update line because [SISitemCode] in
the underlying table is set to "NoDuplicates"
Two example lines of data follow as [SISitemCode], [Description],
[Price]
SISitemCode     Description     Price
MU10060,        6" x 1" CorePlus                      1.0
MU15060,        6" x 1-1/2" CorePlus                  1.5
MU20060, 6" x 2" CorePlus                     2.0

Any help would be greatly appreciated. Regards

I'm almost there but have one remaining problem .... Pls see the
following code
DoCmd.Close acForm, "frmMaster"
Debug.Print NewCode
DoCmd.OpenForm "frmMaster", acNormal, , "SISitemCode = 'NewCode'",
acFormEdit

The Str var "NewCode" holds the correct info right down to the debug
line, the form opens in edit mode but has no data. Could someone pls
point me in the right direction. Thanks

 

[Steve Sanford]

The diect answer to your question is: since "'NewCode'" is a string variable,
you must concantate it the the command.

Try this:

DoCmd.Close acForm, "frmMaster"
Debug.Print NewCode
DoCmd.OpenForm "frmMaster", acNormal, , "SISitemCode = '" & NewCode & "'",
acFormEdit



Another way, without closing and opening the form, is to set the filter
property.

Me.Filter = "[SISitemCode] = '" & NewCode & "'"
Me.FilterOn = True


Expanded, to see the delimiters, the filter looks like:

"[SISitemCode] = ' " & NewCode & " ' "


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

This is a small TRIAL database!
I have the following code which is a snippet of Allen Brownes code.

Private Sub cmdDupe_Click()

'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.

'Save and 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
!SISitemCode = Me.SISitemCode
!Description = Me.Description
!Price = Me.Price
'etc for other fields.
.Update
End With
End If
End Sub

All I am trying to do is to clone/duplicate a "main Form" record but
need to capture the "NEW" data in a form called frmCloneItemUpdate in
order to first change the "value" in the [SISitemCode] field in order
that the record can eventually be saved and also update any other
fields which might pertain to the new item.
The above code runs up to the .Update line because [SISitemCode] in
the underlying table is set to "NoDuplicates"
Two example lines of data follow as [SISitemCode], [Description],
[Price]
SISitemCode Description Price
MU10060, 6" x 1" CorePlus 1.0
MU15060, 6" x 1-1/2" CorePlus 1.5
MU20060, 6" x 2" CorePlus 2.0

Any help would be greatly appreciated. Regards

I'm almost there but have one remaining problem .... Pls see the
following code
DoCmd.Close acForm, "frmMaster"
Debug.Print NewCode
DoCmd.OpenForm "frmMaster", acNormal, , "SISitemCode = 'NewCode'",
acFormEdit

The Str var "NewCode" holds the correct info right down to the debug
line, the form opens in edit mode but has no data. Could someone pls
point me in the right direction. Thanks