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?
 
garyz31Connect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
RuchiCommented:
I will send you some example code sometime later as I am in a little hurry now.
0
 
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
All Courses

From novice to tech pro — start learning today.