A few comments:

  Both snippets of code you posted look identical, are you trying to insert 2 rows into the Same Table?

  Also, seems like strTempAccIDs contains a string of comma delimited Account IDs. Wouldn't it be best to use the Split method and convert the string into an Array instead of using the Mid function?

e.g.

arrAcctIDs = Split(strTempAccIDs, ",")

The you could do something like

Do While i <= UBound(arrAcctIDs)

  acctID = arrAcctIDs(i)   'Your current AccountID

  i = i + 1  'Increate your Index to iterate your Account

Loop

Not sure if I am missing anything from the above, are you trying to create more than one record for the Same Account ID on the DCLALLOCHIST table?

One thing that I noticed is that you are calling .Update outside the loop. Maybe you could try doing an UpdateBatch (or call Update within each loop iteration).

Hi Raul

Thanks for the reply

There are two tables seperate tables that I want to update DCLALLOCHIST and LOA_ALLOCHIST - sorry i pasted the same code in twice...!!

At the moment I just have the first piece of code in place that works fine, I just want to update another table within the same function.

Here is my 2nd code snippet:

strSQL = "SELECT * FROM LOA_ALLOCHIST WHERE 1=2"<br />    With objRS
        .Open strSQL, objSLXDB.Connection
        if .State = 1 Then

            Do While(nStart <= nMax)

                strCurrentID = Mid(strTempAccIDs, nStart, nEnd-nStart)
                .AddNew
                .Fields("ACCOUNTID").Value = strCurrentID
                .Fields("USERID").Value = strUserID
                .Fields("USERNAME").Value = "TEST"
                .Fields("SOURCE").Value = "LOA"
                .Fields("CREATEDATE").Value = Now
                .Fields("TYPE").Value = "LOA"
                .Fields("ADMINID").Value = strUserID

                nStart = nEnd + 1
                nEnd = InStr (nStart, strTempAccIDs, ",")
                if nEnd = 0 Then
                   nEnd = nMax + 1
                End if
             Loop
        End If
        ' Write changes back to database and close recordset
        .Update
        .Close
    End With

Thanks

Are you using the Same Variables?

If so, are you initializing them before each loop?

I would change the code slightly:

a) Use 2 different Variables for the Recordsets

b) Use a Single Loop for the Account IDs:

As per my previous response:

arrAcctIDs = Split(strTempAcctIDs, ",")    'Create an Array of IDs

i = 0

If UBound(arrAcctIDs) > -1  'If there are account IDs on the Array, Initialize your Recordsets

    recTbl1.Open strSQL, connection   'First Table

    recTbl2.Open strSQL2, connection  'Second Table

End If

Do While i <= UBound(arrAcctIDs) 

  strAcctID = arrAcctIDs(i)   'Get an ID from the Array

  If Trim(strAcctID) <> ""   ' Verify it isn't blank

      'Table 1 Add Row

       recTbl1.AddNew

         ....

       recTbl1.Update

      'Table 2 Add Row

       recTbl2.AddNew

         ....

       recTbl2.Update

  End If

  i = i + 1

Loop

'Close your Recordsets if necessary

  If recTbl1.State = 1 Then

    recTbl1.Close

  End If

  If recTbl2.State = 1 Then

    recTbl2.Close

  End If

'Cleanup code as necessary

Set recTbl1 = Nothing

Set recTbl2 = Nothing

Hi Raul

Would it be something like this:

<p class="MsoNormalCxSpFirst" style="line-height: normal;">Dim arrAcctIDs, recTbl1, recTbl2, strSQL2, i
 
arrAcctIDs = Split(strTempAccIDs, ",")   
i = 0
If UBound(arrAcctIDs) > -1  Then
 recTbl1.Open strSQL, objSLXDB.Connection
 strSQL = "SELECT * FROM DCLALLOCHIST WHERE 1=2"  
 recTbl2.Open strSQL2, objSLXDB.Connection  

 strSQL2 = "SELECT * FROM LOA_ALLOCHIST WHERE 1=2"
 End If
Do While i <= UBound(arrAcctIDs)
  strAcctID = arrAcctIDs(i)   
  If Trim(strAcctID) <> ""   Then
        recTbl1.AddNew
       recTbl1.Fields("DCLALLOCHISTID").Value = Application.BasicFunctions.GetIDFor ("DCLALLOCHIST")
       recTbl1.Fields("ACCOUNTID").Value = strCurrentID
       recTbl1.Fields("CREATEUSER").Value = strCurrUserID
       recTbl1.Fields("CREATEDATE").Value = Now
       recTbl1.Fields("MODIFYUSER").Value = strCurrUserID
       recTbl1.Fields("MODIFYDATE").Value = Now
       recTbl1.Fields("USERID").Value = strUserID
       recTbl1.Update
 
       recTbl2.AddNew          recTbl2.Fields("ACCOUNTID").Value = strCurrentID
       recTbl2.Fields("USERID").Value = strUserID
       recTbl2.Fields("USERNAME").Value = "TEST"
       recTbl2.Fields("SOURCE").Value = "LOA"
       recTbl2.Fields("CREATEDATE").Value = Now
       recTbl2.Fields("TYPE").Value = "LOA"
       recTbl2.Fields("ADMINID").Value = strUserID
       recTbl2.Update
  End If
  i = i + 1
Loop

Looks good.

However, I noticed that when writing the ACCOUNTID field you are using a Variable called strCurrentID when you probably should be using the strAcctID  (the Current ID as read out of the Array).

That said, it looks as if this should work (off course, you need to define variables and add Error Catching if needed).

when you have an Open ADO recordset, it's usually best to CLOSE it first, Check the STATE, then reopen it.....then test for EOF

rs.Open 1st Table Record for insert

IF rs.EOF Then rs.AddNew

rs.Update

rs.close

if rs.State=1 then rs.close

rs.Open 2nd table record for insert

if rs.EOF then rs.AddNew

rs.Update

rs.close

You were Opening a record set THEN checking the state.....wrong order.....

Also, if you are using TWO tables, you run into issues where the ADO recordset's are different structures.....I usually use another ADO recordset. for a 2nd table. (3rd, 4th, 5th....)

Why are you being so verbose in your code? CreateDate, ModifyDate, CreateUser, ModifyUser usually aren't necessary to code. SLX OLE DB PRovider will populate these for you (and the ID Field but you may need it in subsequent code).

Also if you are working with ONE Table, you can experiment with Update batch and BatchOptimistic cursor settings.....why not inssert 50 records into one recordset\table and the rs.UpdateBatch

rs.Close

Set RS = Nothing....