Very Computer

I am trying to update a set of variables from the fields
of a recordset.  I want to use a with statement to
economize on the code, but I don't believe I am addressing
the correct property of the recordset.  Here is what I
have.  

Thanks in advance for your help.

Steve

For i = 1 To 10
    With oRs

         .oRs(0) = mSelect5
         .oRs(1) = mSelect6
         .oRs(2) = mSelect7
         .oRs(3) = mSelect8
         .oRs(4) = mSelect9
         .oRs(5) = mSelect10

     mSelect4 = "INSERT INTO Temp.dbf(custno, item, invno,
cost, price, invdte) VALUES (" & mSelect5 & ", " &
mSelect6 & ", " & mSelect7 & ", " & mSelect8 & ", " &
mSelect9 & ", " & mSelect10 & ")"

        oRs1.Open mSelect4, oCn
        oRs1.MoveNext
     End With
 Next

Hi
I think you need to read ADO documentation  little bit better..:)
If you are using INSERT , UPDATE statements , you have to use Connection
or command objects depending on your requirement.
If you want to update recordset using with(not tested),
with rs
   .fields(0).value="xx"
..............
..............
   .update
end with

If you are using INSERT statement:

Quote:>mSelect4 = "INSERT INTO Temp.dbf(custno, item, invno,
>cost, price, invdte) VALUES (" & mSelect5 & ", " &
>mSelect6 & ", " & mSelect7 & ", " & mSelect8 & ", " &
>mSelect9 & ", " & mSelect10 & ")"

use connection object here..
cn.execute mSelect4

Best of luck
Harinatha Reddy Gorla
        Software Engineer
Smart Software Technologies,
Hyderabad, India

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Stephen,

First of all, you cannot open recordset usinf action
query, like INSERT, UPDATE, DELETE. And it does not make
any sence. If you want to insert new record, then you have
two choices:
1. Prepare INSERT SQL statement (like you do it now) and
execute it using Execute method of your connection or
Command object.

2. Open recordset (in your case oRs1) ONLY ONCE, before
loop. Add new record to that recordset using AddNew method
of recordset.

oRs1.AddNew
oRs1.Fields("MyField1").Value="test"
.
.
.
oRs1.Fields("MyFieldn").Value="testn"

and call Update method of recordset to accept new record.

oRs1.Update

Val

Quote:>-----Original Message-----
>I am trying to update a set of variables from the fields
>of a recordset.  I want to use a with statement to
>economize on the code, but I don't believe I am
addressing
>the correct property of the recordset.  Here is what I
>have.  

>Thanks in advance for your help.

>Steve

>For i = 1 To 10
>    With oRs

>         .oRs(0) = mSelect5
>         .oRs(1) = mSelect6
>         .oRs(2) = mSelect7
>         .oRs(3) = mSelect8
>         .oRs(4) = mSelect9
>         .oRs(5) = mSelect10

>     mSelect4 = "INSERT INTO Temp.dbf(custno, item,
invno,
>cost, price, invdte) VALUES (" & mSelect5 & ", " &
>mSelect6 & ", " & mSelect7 & ", " & mSelect8 & ", " &
>mSelect9 & ", " & mSelect10 & ")"

>        oRs1.Open mSelect4, oCn
>        oRs1.MoveNext
>     End With
> Next

>.