Copy & Paste A Row

PC User

PC User

Registered User.
Local time
Today, 06:35
Joined
Jul 28, 2002
Messages
193
I found this code somewhere, but I keep getting errors on it. Try it and see what I mean. Is anyone familiar with the Scripting Library and can you help me on this?

===================================
Private Sub btnCopyRow_Click()
Dim dict As New Scripting.Dictionary
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
dict.Add ctl.NAME, ctl.value
End If

If ctl.ControlType = acCheckBox Then
dict.Add ctl.NAME, ctl.value
End If
Next ctl
End Sub
===================================
Private Sub btnPasteRow_Click()
Dim dict As New Scripting.Dictionary
Dim ctl As Control
For Each ctl In Me.Controls
ctl.value = dict(ctl.NAME)
Next ctl
End Sub
===================================

Thanks,
PC
 
First off...

What are you trying to accomplish with this code?
What is the Error?
Where to you get the Error?

.
 
Thanks for your reply. I'm trying to Copy & Paste A Row in Access 2007. In the code above, I don't get any errors, no response and no results. So I'm open to any other suggestions. In my database of chemicals, sometime I get new chemicals or waste chemicals with properties and components of the same primary chemical. So in these cases rather than re-enter all the identical information for the sake of a name change of the chemical, I'm trying to duplicate the entire row which contains all the chemical properties and then just change the name of the chemical in the new row.

By programming a copy & paste for a row of data for this purpose, I also get the benefit of not making a mistake in any part of the chemical properties information. Do you know of a way to accomplish this or do you have a sample database that I can examine?

Thanks,
PC
 
I do this very thing in one of my hobby dbs.

I created a User defined Type (UDT) that mirrors the structure of the record to be copied and pasted (except PK)

Then I have a simple button to copy from the current record and all it does is to copy the fields to the UDT and the Paste button checks if there is a valid record in the structure and asssign the controls for the new record to the values in the UDT.
 
Simple Software Solutions

An alternative method:

Dim Rs as DAO.Recordset
Dim RsArray()
Dim rCount As Integer

Set Rs = CurrentDb.OpenRecordset("Select * From [YourTable] Where [YourField]= [YourCriteria]"

rCount = Rs.Fields.Count -1

For X = 0 To rCount

RsArray(X) = Rs(X)

Next


Rs.AddNew

For x = 0 To rCount

Rs(X) = RsArray(X)

Next

Rs.Update

Rs.Close

Set Rs. = Nothing


What has not been coded is the revised name of the chemical. If you know the ordinal field number then simply place an if statement in the loop to check the value of X and enter the new name there.

CodeMaster::cool:
 
Thanks DCrake for your suggestion. I'll give it a try. I think your idea of using arrays may be the way to go. I'm new to them and not too many users are familiar with them. I came accross some other code that I was testing too that deals with arrays, but I'm still going to try yours. Here it is:
Code: 
Public Function CopyRow(frm As Form) 
    Dim lngIndex As Long 
    Dim i As Integer 
    Dim n As Integer 
    Dim ctl As Control 
    Dim strTextName As String 
    Dim strTextContent As String 
    Dim strCheckboxName As String 
    Dim strCheckboxValue As Variant 
        n = 0 
        For Each ctl In frm.Controls 
        i = n 

        If ctl.ControlType = acTextBox Then 
                strTextName(i) = ctl.NAME 
                strTextContent(i) = ctl.Text 
        End If 

        If ctl.ControlType = acCheckBox Then 
                strCheckboxName(i) = ctl.NAME 
                strCheckboxValue(i) = ctl.Value 
        End If 

        n = i + 1 
        Next ctl 
    Set ctl = Nothing 
End Function

Of course, after I copy the data, I'll need a way to paste it and not include the key field.

Thanks,
PC
 
I tried your code with some minimal changes and I'm getting a syntex error on the line code Set Rs = CurrentDb.OpenRecordset(strSQL). The ChemicalID is a AutoNumber field and I thought I made the where criteria correctly for a numeric field, but maybe not.
Code: 
Private Function DuplicateRow()
Dim Rs As DAO.Recordset
Dim RsArray()
Dim rCount As Integer
Dim X As Integer

strSelect = "SELECT tblChemicalProperties.* "
strWhere = "WHERE tblChemicalProperties.[ChemicalID] = " & Forms!frmMain!ctlGenericSubform.Form!ChemicalID
strSQL = strSelect & strWhere

'Set Rs = CurrentDb.OpenRecordset("Select * From [YourTable] Where [YourField]= [YourCriteria]"

        Set Rs = CurrentDb.OpenRecordset(strSQL)

        rCount = Rs.Fields.Count - 1
            For X = 0 To rCount
            RsArray(X) = Rs(X)
            Next

        Rs.AddNew
            For X = 0 To rCount
            Rs(X) = RsArray(X)
            Next

Rs.Update
Rs.Close
Set Rs = Nothing

End Function

Do you think you see the source of my error? Also I need to avoid trying to duplicate the AutoNumber field. There may be an error when I try to overwrite its calculated value.

Thanks,
PC
 
What it looks like you are doing is duplicating the Record. Why not just use the Command Button wizard to accomplish this (Duplicate Record)?

You can use the Runcommand method to also accomplish the same thing.

Or did you want to be more specific?

.
 
Copy & Paste A Record

I guess I misnamed my posting. It should be "Copy & Paste A Record" or "Duplicate A Record". Thanks for your suggestion. I tried the button wizard, but I didn't see anything to accomplish this. I'm not sure what you are describing in your reply. Could you provide more information?

I made corrections to my query code, but the WHERE statement seems to be designed to use text, because I get a type mismatched error on the line code Set Rs = CurrentDb.OpenRecordset(strSQL). The key field ChemicalID is a numeric field. I'm not sure how to change the code to use a number. If you know how to do that, it will be one step closer to be working.
Code: 
Private Function Duplicate()
Dim RS As DAO.Recordset
Dim RsArray()
Dim rCount As Integer
Dim X As Integer

strSelect = "SELECT tblChemicalProperties.* "
strFrom = "FROM tblChemicalProperties "
strWhere = "WHERE tblChemicalProperties.ChemicalID = '" & [Forms]![frmMain]![ctlGenericSubform].[Form]![ChemicalID] & "'"

strSQL = strSelect & strFrom & strWhere

        Set RS = CurrentDb.OpenRecordset(strSQL)

        rCount = RS.Fields.Count - 1
            For X = 0 To rCount
            RsArray(X) = RS(X)
            Next


        RS.AddNew
            For X = 0 To rCount
            RS(X) = RsArray(X)
            Next

RS.Update
RS.Close
Set RS = Nothing

End Function
Thanks,
PC
 
Last edited:
Ok DCrake, I've gotten a little further.
Code: 
Private Function Duplicate()
Dim RS As DAO.Recordset
Dim RsArray()
Dim rCount As Integer
Dim X As Integer

strSelect = "SELECT tblChemicalProperties.* "
strFrom = "FROM tblChemicalProperties "
strWhere = "WHERE tblChemicalProperties.ChemicalID = " & [Forms]![frmMain]![ctlGenericSubform].[Form]![ChemicalID]
strSQL = strSelect & strFrom & strWhere

        Set RS = CurrentDb.OpenRecordset(strSQL)

        rCount = RS.Fields.Count - 1
            For X = 0 To rCount
            RsArray(X) = RS(X)
            Next


        RS.AddNew
            For X = 0 To rCount
            RS(X) = RsArray(X)
            Next

RS.Update
RS.Close
Set RS = Nothing

End Function
I get an error
Run-time error "9":
Subscript out of range

On line: RsArray(X) = RS(X)

I'm kind of lost on arrays at this point.

PC
 
Simple Software Solutions

This error (in this context) usually means that the number of elements in the array is less than the value you are passing to it. To get arround this you could declare the number of elements to 254 before you run the code.

Dim MyArray(254)

Then in you code do a

For X = 0 to 254

If rCount < X then Exit For

Next


In you AddNew Loop section start your loop from 1 as field zero Rs(0)
is the AutoNumber field and is automatically populated with the next sequential ID This is why you may be getting some errors.

David
 
Duplicating A Record

Thanks David,
Your guidance did the trick. Here is my working code for duplicating a record excluding the key field.
Code: 
Private Function Duplicate()
Dim RS As DAO.Recordset
Dim RsArray(254)
Dim rCount As Integer, X As Integer
    strSelect = "SELECT tblChemicalProperties.* "
    strFrom = "FROM tblChemicalProperties "
    strWhere = "WHERE tblChemicalProperties.ChemicalID = " & [Forms]![frmMain]![ctlGenericSubform].[Form]![ChemicalID]
                Debug.Print strSql
    strSql = strSelect & strFrom & strWhere
            Set RS = CurrentDb.OpenRecordset(strSql)
                Debug.Print strSql
            rCount = RS.Fields.Count - 1
                For X = 0 To 254
                If rCount < X Then Exit For
                RsArray(X) = RS(X)
                Next
    
            RS.AddNew
                For X = 1 To rCount
                RS(X) = RsArray(X)
                Next
    RS.Update
    RS.Close
    Set RS = Nothing

End Function
Thanks,
PC
 
Simple Software Solutions

Hi

Glad that everything works now, all you need to do now is to remove the Debug.Print command lines from your code as this is now redundant.

David
 
I guess I misnamed my posting. It should be "Copy & Paste A Record" or "Duplicate A Record". Thanks for your suggestion. I tried the button wizard, but I didn't see anything to accomplish this. I'm not sure what you are describing in your reply. Could you provide more information?
Click to expand...

I realize this is now solved...but, perhaps I missed something somewhere. No big deal in any case.

With the Form in Design View:

Draw (drag) a Command Button onto the Form by way of selecting the Command Button control from the Controls bar.

The Command Button Wizard should display;

Select Record Operations from the Categories list;

Select Duplicate Record from the Actions list.

Select the Finish button

Done Deal.

.
 
Copy & Paste A Record

That's very convenient and works pretty good.
Code: 
Private Sub cmdDupeRecord_Click()
On Error GoTo Err_cmdDupeRecord_Click

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste

Exit_cmdDupeRecord_Click:
    Exit Sub

Err_cmdDupeRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdDupeRecord_Click
    
End Sub
Thanks CyberLynx for your help.
PC
 
Or to shorten it up a little more....this should work as well:

Code: 
Private Sub cmdDupeRecord_Click()
   On Error GoTo Err_cmdDupeRecord_Click

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand [B]acCmdPasteAppend[/B]

Exit_cmdDupeRecord_Click:
    Exit Sub

Err_cmdDupeRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdDupeRecord_Click
End Sub

.
 
You must log in or register to reply here.

Similar threads

D
Clearing data in a form after clicking print report
Replies
9
Views
1,239
Ken Sheridan
Ken Sheridan
T
50/50 Chance of Working
Replies
5
Views
492
June7
June7
J
Solved Conditional formatting without conditional formatting
Replies
6
Views
787
John Sh
J
T
You can't hide a control that has the focus
Replies
9
Views
947
TheSearcher
T
S
Delete All controls from A Form
Replies
18
Views
2,460
sonic8
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom