Copy current record to another table ADO

What I have
one form called "frmData"
adodc1 control- Data1.mdb
bound to table called "tblFirst"
Three fields in table called
fldOne
fldTwo
fldThree
Three text boxes on form called and bound to
fldOne bound to txtBoxOne
fldTwo bound to txtBoxTwo
fldThree bound to txtBoxThree

ANother adodc control called
Adodc2-Bound to  Data1.mdb
table called   "tblSecond"
The table structure is the same as "tblFirst"

What I need to happen:
What ever current record I'am on  in the "frmData"
I need to press a command button and insert this record into
tblSecond table.
thanks
fordraiders

LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RuchiCommented:
Set an "Insert Into" statement to copy all the data into the new table. If the data is coming from another database then connect to this database, create a recordset to hold all the data, then use a for loop to loop through all the records.

i.e. "Insert Into NewTable Select * From OldTable
0
FordraidersAuthor Commented:
Ruchi,
I'am basically going from adodc1 to adodc2,  as I have stated.
I'am fairly new to vb.
Can you present some example code please.
fordraiders
0
RuchiCommented:
I will send you some example code sometime later as I am in a little hurry now.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

garyz31Commented:
What you want to do is manipulate the underlying ADO Recordset.  Try this:

adodc2.addnew
for i = 0 to 2
   adodc2.Recordset.Fields(i).value = _
    adodc1.Recordset.fields(i).value
next i
adodc2.Recordset.Update

Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Gary,
Thanks
Works Great.
I assume if I want to revise this routine I can extend the i interger to  0 to 20
fordraiders
0
garyz31Commented:
You can use however many fields you have in the recordset, or apply any other ADO Recordset operations.

Glad to help you.
0
FordraidersAuthor Commented:
garyz31,
for some reason I 'am now getting an error on this statement.

runtime error 91
Object Variable or with block variable not set.


adodc2.addnew
                   for i = 0 to 2
                      adodc2.Recordset.Fields(i).value = _
                       adodc1.Recordset.fields(i).value
                   next i
                   adodc2.Recordset.Update

My Code is:
             adodc2.recordset.addnew
                   for i = 0 to 2
                      adodc2.Recordset.Fields(2).value = frmDesc.txtMfgnum
                           next i
                             adodc2.Recordset.Update
 
any suggestions?
fordraiders
0
RuchiCommented:
use adodc2.refresh

0
FordraidersAuthor Commented:
Ruchi,
Thanks for the heads up.
I also had the LockType and Mode incorrect.
Thanks
fordraiders
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.