Hello,



I'm programming some code in MS Access that will automatically copy the 

contents from one record into other records.



What I've got is a Form/SubForm.   On the FORM, you can select the record 

that contains the data you will copy into other records.



When you choose that record, the SUBFORM returns all records that belong 

in the same category as the record that was chosen on the FORM.



The SUBFORM is displayed in Continuous Form view; next to each record that 

is returned, there is a command button, which says "PASTE INFO".  So -- 

when you push the button for a record, it is updated with the original 

record's contents, eliminating the need to do a lot of redundant data 

entry.



I can get this to work just fine, but I think I'm doing this the hard way.

My major problem is that each record has 122 fields that need to be 

updated!!!



Here's the crux of what I'm doing:



First, I place the original record's unique id in a global variable.  Then 

open up a recordset, loop through it, and if the unique id matches the 

global variable -- then -- I want to retain that record's details in a 

variable for *each field*, so that I can then paste the info into the 

record(s) selected by the user.



But who wants to declare 122 variables, type them all over again so that 

they retain values, and then type them AGAIN for the PASTE part of this 

routine?!



There's just gotta be an easier way.  I wonder if I can use "For 

Each/Next" to any effect, but I haven't yet figured out a way to make it 

work.



Also... should I consider using a Transaction to do this?  The PASTE 

button works on only one record at a time, so I can't really see any use 

for programming it using Transaction commands.  Also I wonder... are 

there  Transactions, and then Batch Transactions, and are those two 

separate things?  Or did I just make that up?    Augh!



Any attempts to make my life easier will be GREATLY appreciated!  'Til 

then... I'm Dim, Dim, Dimming away into infinity!



Susan

Hello again,



Oh, I see I had a *pretty* good idea going in the code I posted 

previously, but it needed a lot of sprucing up!  All that looping through 

recordsets put a tremendous strain on my database's performance.



Some kind, merciful soul emailed me and suggested I use querydefs with 

parameters.  I don't know how to use querydefs with parameters, though, so 

instead I just built a basic SQL statement with a WHERE clause as my 

recordsets.  And that eliminated all the unncessary looping!



The improved code is below.  Suggestions for improving it further are much 

more than welcome!  I learn tons of fun new tricks when I expose my awful 

code to the experts here, lol!



Cheers,

Susan



------------------------------------------

Public Function fncPaste()

On Error GoTo fncPasteErrs



Dim dbMe As Database

Dim rsItemCopy As Recordset

Dim rsItemPaste As Recordset

Dim intCount As Integer





    Set dbMe = CurrentDb

    Set rsItemCopy = dbMe.OpenRecordset("SELECT * FROM qryPasteItems WHERE 

ItemPrimItemNum = " & gblLngItemPaste)

    Set rsItemPaste = dbMe.OpenRecordset("SELECT * FROM qryPasteItems 

WHERE ItemPrimItemNum = " & Me.ItemNum)

        

    intCount = 1

    

        With rsItemCopy

             'rsItemPaste represents the record whose contents should be 

pasted into

              rsItemPaste.Edit

                    For Each Index In rsItemPaste.Fields

                        'MsgBox "PasteField :" & rsItemPaste.Fields

(intCount).Name & ": " & rsItemPaste.Fields(intCount) & " CopyField: " 

& .Fields(intCount).Name & ": " & .Fields(intCount)

                        rsItemPaste.Fields(intCount) = .Fields(intCount)

                        intCount = intCount + 1

                        If intCount = rsItemPaste.Fields.Count Then

                            Exit For

                        End If

                    Next

                rsItemPaste.Update

                MsgBox "Fields have been updated!"

        End With

        

    

    'Tidy up!

    rsItemCopy.Close

    Set rsItemCopy = Nothing

    

    rsItemPaste.Close

    Set rsItemCopy = Nothing

    

    Set dbMe = Nothing



                            



ExitfncPaste:

    Exit Function



fncPasteErrs:

    If Err.Number = 3265 Then

        Resume Next

    Else

        MyErrorHandler

        Resume ExitfncPaste

    End If



End Function

Hi there,



As usual, I've managed to answer my own question.  Here, for anyone who's 

interested, is the code I wrote.  It's a function that's called from 

the "PASTE INTO" command button.

----------------------------------------------------------------'



Public Function fncPaste()

On Error GoTo fncPasteErrs



Dim dbMe As Database

Dim rsItemCopy As Recordset

Dim rsItemPaste As Recordset

Dim intCount As Integer





    Set dbMe = CurrentDb



    'Not sure if this is a good idea, but I'll give it a shot:

    'I'll have two different recordsets, one for copying from, and

    'one for pasting into.....

    '.... since it's all the same fields, I'll use the same query.

    'Don't know if this is a No-No!  It does, however, work.



    Set rsItemCopy = dbMe.OpenRecordset("qryPasteItems")

    Set rsItemPaste = dbMe.OpenRecordset("qryPasteItems")

     

    'intCount will be used in my "For Each Field in rsItemPaste" statement.

    'It'll iterate through each field in the recordset (except the first

    'field, 0, because that's the unique id).

    intCount = 1

    

        With rsItemCopy

            .MoveFirst

            Do Until .EOF

                If ![ItemNum] = gblLngItemPaste Then

                    'This is the record which has the info to be copied.

                    'gblLngItemPaste is a global variable.  When user

                    'selects, from a combo box, the Item Number that has

                    'the info -- that number is stored in gblLngItemPaste.



                    'Now find record which needs the info pasted in it

                    rsItemPaste.MoveFirst

                    Do Until rsItemPaste.EOF

                        If rsItemPaste![ItemNum] = Me.ItemNum Then

                             'This is the record whose contents should be

                             'pasted *into*, as chosen by user when they 

                             'pressed PASTE button

                                rsItemPaste.Edit

                            For Each Index In rsItemPaste.Fields

                                    rsItemPaste.Fields(intCount) = .Fields

(intCount)

                                    intCount = intCount + 1

                                    If intCount = rsItemPaste.Fields.Count 

Then

                                        Exit For

                                    End If

                            Next

                                rsItemPaste.Update

                                MsgBox "Fields have been updated!"

                                Exit Do

                        End If

                    rsItemPaste.MoveNext

                    Loop

                End If

            .MoveNext

            Loop

        End With

        

    

    'Tidy up!

    rsItemCopy.Close

    Set rsItemCopy = Nothing

    

    rsItemPaste.Close

    Set rsItemCopy = Nothing

    

    Set dbMe = Nothing



                            



ExitfncPaste:

    Exit Function



fncPasteErrs:

    If Err.Number = 3265 Then

        'Error Nubmer 3265 -- "Item Not Found In This Collection".  I 

        'always got this error message when the recordset got to the 

        'last field.  I've since resolved this by putting in my 

        '"If intCount = rsItemPaste.Fields.Count Then Exit For" statement.

        Resume Next

    Else

        MyErrorHandler

        Resume ExitfncPaste

    End If



End Function

Hi again,



Just one more comment -- my two posts above are in the wrong order!  I 

don't know how that happened.  Anyway, the shorter code -- the one that 

uses SQL statments in its recordsets -- the new improved version.  The 

post that follows that was actually my first attempt.



Silly List order! ;)

Susan